I currently have access to my EHR database so I can write reports. 99% of them are simple straight-forward reports using the named fields off of the various tables. Not a problem.

However, in my EHR I do have the ability to add in custom fields.

My problem is that I cannot get the values in the fields to display properly in reports.

So in Table CLIENT_CUSTOM_DATA I have {CLIENT_CUSTOM_DATA.FIELDID}
wherein I have IDs ranging from 1000-1025 and I need to get the {CLIENT_CUSTOM_DATA.RECODED_RESPONSE} which is the data in the field for each of those fields to show up for their IDs.

I have tried using a formula, and it works for just ONE Field. But if I need to do a report requiring FIELDIDs 1000, 1002, and 1007 for example I cannot get them to show up in the columns I need.

I know I am missing something obvious, so I need some help.

Thank you in advance.

Yes, I have to use the CLIENT_CUSTOM_DATA table multiple times... they would be the column headings, and the data for each client would be on the client rows.

So I would have for instance:

NAME     CLIENT#     DOB      FIELDID1000   FIELDID1002   FIELDID1007
Smith      2004          x/x/xx        007                  Yes                   Pick UP
Jones      4654          x/x/xx        005                  No                    Pick UP
Anders    7651          x/x/xx        012                  Yes                   Delivery

I can get one to appear on a report, but I cannot get any additional/multiple uses of the CLIENT_CUSTOM_DATA.RECODED_RESPONSE to appear.  

I was trying to use a formula to return this information. So if FIELDID1000 is say Family Number, for the Smith entry, it would return the 007.

I hope this makes more sense.   


Edited by RCruse - 26 Oct 2017 at 7:54am

If this is SQL source can you create views or stored procedures for your report data source?

Maybe a Crystal Command?

The thing would be you will need to join custom table multiple times to the client table with conditional joins
select
c.Clientid
,ccd1.RECODED_RESPONSE as FieldID1000
,ccd2.RECODED_RESPONSE as FieldID1002
,ccd3.RECODED_RESPONSE as FieldID1007
from
Clients c
left join CLIENT_CUSTOM_DATA ccd1 on c.clientid = ccd1.clientid and ccd1.FieldId = 1000
left join CLIENT_CUSTOM_DATA ccd2 on c.clientid = ccd2.clientid and ccd2.FieldId = 1002
left join CLIENT_CUSTOM_DATA ccd3 on c.clientid = ccd3.clientid and ccd3.FieldId = 1007