FORMULA FOR MULTIPLE IN ONE FIELD

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.

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

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.

I had the two suggestions separated incorrectly and now it's working!!!!

Thank you so much!!!!