Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: FORMULA FOR MULTIPLE IN ONE FIELD Post Reply Post New Topic
Author Message
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet Topic: FORMULA FOR MULTIPLE IN ONE FIELD
    Posted: 16 Apr 2018 at 9:22am
I have to list out codes in this format on a Crystal report.   DX.3,X18,250.00,560.MN

I thought it was simple enough to do this as my formula.

If {Authorization_ProcedureData.SequenceNumber} = 1
Then
{Authorization_ProcedureData.Code} + ','
else
If {Authorization_ProcedureData.SequenceNumber} = 2
Then
{Authorization_ProcedureData.Code} + ','
else
If {Authorization_ProcedureData.SequenceNumber} = 3
Then
{Authorization_ProcedureData.Code} + ','
else
""

Within the Authorization_Procedure table, there is our field called "Code" and each code has a sequence number identifying it 1-20.

Any suggestions on why this is not working and it's only showing the first line for seq code = 1.
Always appreciate the help!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 16 Apr 2018 at 1:00pm
Are the sequence numbers in a list in one record or are they in a single column in multiple records?

If they're in multiple records, you'll have to use a variable to get the value to carry over from the previous records and you'll only be able to display the value after all of the details have been processed. So, assuming that you've grouped your data by authorization, you'll need two formulas that look something like this:

{@GetProcedureList}

WhilePrintingRecords;
StringVar codeList;

//reset the variable to an empty string at the start of each authorization
If OnFirstRecord or {Authorization.AuthorizationCode} <> previous({Authorization.AuthorizationCode}) then
codeList := '';

//add the current code to the list
codeList := codeList + {Authorization_ProcedureData.Code};

//add the comma to then end if we're not on the last record for this authorization
If not OnLastRecord or {Authorization.AuthorizationCode} = next({Authorization.AuthorizationCode}) then
codeList := codeList + ", ";
"";

Notice the use of ":=" to assign a value and ";" to end a line - these are required for multi-line formulas. Place this formula in the details section - it will calculate but not display anything.

{@ShowCodes}

WhilePrintingRecords;
StringVar codeList

Place this formula in the group footer to get the list of codes as a comma-delimited string.

-Dell

Edited by hilfy - 16 Apr 2018 at 1:01pm
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet Posted: 17 Apr 2018 at 5:00am
One authorization will have multiple codes, they will not be the same and when the data is stored in the table, each code is stored with a separate sequence numbers.

Auth 123456 might have 5 codes;
seq 1 DX.23
seq 2 250.00
seq 3 564.00
seq 4 AS.45
seq 5 J564.0

I tried the suggestion on the two formulas and that brought back no data.
Always appreciate the help!
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet Posted: 17 Apr 2018 at 6:12am
I had the two suggestions separated incorrectly and now it's working!!!!

Thank you so much!!!!
Always appreciate the help!
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.032 seconds.