Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Custom Fields Post Reply Post New Topic
Author Message
RCruse
Newbie
Newbie
Avatar

Joined: 09 Dec 2015
Location: United States
Online Status: Offline
Posts: 4
Quote RCruse Replybullet Topic: Custom Fields
    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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
RCruse
Newbie
Newbie
Avatar

Joined: 09 Dec 2015
Location: United States
Online Status: Offline
Posts: 4
Quote RCruse Replybullet 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.   


Edited by RCruse - 26 Oct 2017 at 7:54am
IP IP Logged
RCruse
Newbie
Newbie
Avatar

Joined: 09 Dec 2015
Location: United States
Online Status: Offline
Posts: 4
Quote RCruse Replybullet 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...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Oct 2017 at 8:46am
If this is SQL source can you create views or stored procedures for your report data source?
IP IP Logged
RCruse
Newbie
Newbie
Avatar

Joined: 09 Dec 2015
Location: United States
Online Status: Offline
Posts: 4
Quote RCruse Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.063 seconds.