Custom Fields
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22487
Printed Date: 27 Apr 2024 at 9:33pm
Topic: Custom Fields
Posted By: RCruse
Subject: Custom Fields
Date Posted: 26 Oct 2017 at 6:05am
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.
|
Replies:
Posted By: DBlank
Date Posted: 26 Oct 2017 at 6:59am
|
I can't quite visualize your data set up but if it is sounding vaguely like you have to use the Client_Custom_data table several times as you have row based structure for each "new custom field" as compared to a column based structure.
|
Posted By: RCruse
Date Posted: 26 Oct 2017 at 7:38am
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.
|
Posted By: RCruse
Date Posted: 26 Oct 2017 at 8:20am
I tried just a basic IF THEN type statement in a formula, which will return the correct answer for only one FIELDID.
So say @FamilyID formula is this:
if {CLIENT_CUSTOM_DATA.FIELDID} = 1000 then {CLIENT_CUSTOM_DATA.RECODED_RESPONSE}
And the second I create @Delivery formula for 1002 as
if {CLIENT_CUSTOM_DATA.FIELDID} = 1002 then {CLIENT_CUSTOM_DATA.RECODED_RESPONSE}
the information in FIELDID1002 fails to show up...
|
Posted By: DBlank
Date Posted: 26 Oct 2017 at 8:46am
|
If this is SQL source can you create views or stored procedures for your report data source?
|
Posted By: RCruse
Date Posted: 26 Oct 2017 at 9:55am
|
No, I cannot create those. I just have access to the table views that are set by the company that hosts/runs our EHR system.
|
Posted By: DBlank
Date Posted: 26 Oct 2017 at 10:42am
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
|
|