Print Page | Close Window

Concatenating multiple formula fields

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12592
Printed Date: 01 May 2024 at 4:22pm


Topic: Concatenating multiple formula fields
Posted By: 00331data
Subject: Concatenating multiple formula fields
Date Posted: 12 Mar 2011 at 10:55am
I have a formula:
 
Trim ({Group2_csv.Source } & IIF (LEN({Group2_csv.SourceRole}) > 0, " (", "") & {Group2_csv.SourceRole}
 & IIF (LEN({Group2_csv.SourceRole}) > 0, ")", ""))

The “Source” column is a person’s name.  I’ve discovered I may not have just one source, but can have an undetermined number of sources and their roles.  How can I indicate that in the formula?  I would like the formula to say:  format the first Source & role as follows:  John Smith (Donor) and if there is more than one source & their roles formulate subsequent source/roles in the same manner as the first source/role AND concatenate the subsequent source/roles with a comma and a space as follows: 
Gregory Holt (Donor), Ami Whitus (Agent), John Maxwell (Executor) [and so on]

Any suggestions?


-------------
thanks,
00331data


Replies:
Posted By: Keikoku
Date Posted: 14 Mar 2011 at 3:27am
How are your multiple sources formatted? Are they provided as a single string separated by a common delimiter (ie: "terry, bob, rae"; where you would split on commas to get individual names)? Or are they separate records?

Depending on how your data is formatted you may use a For loop, or you may have to use variables.

Posted By: 00331data
Date Posted: 14 Mar 2011 at 8:32am
They are separate records in another table, which is why they are contained in a a group.

-------------
thanks,
00331data

Posted By: Keikoku
Date Posted: 14 Mar 2011 at 9:18am
If they are separate records, you will have to use a variables to concatenate strings across multiple records and then display the final result in the group footer.

It is the same as doing a running total that changes on groups: you will need three formulas.

Formula: Reset. Place this in the group header 


whileprintingrecords;

global stringvar temp := '';


Formula: Join. Place this in the details section and suppress it. 

whileprintingrecords;

global stringvar temp := temp & ' ' & <data in this record>;


Formula: Display. Place this in the group footer. 

whileprintingrecords;

global stringvar temp;


You may only want to display the final concatenated string, so you would suppress the detail section.


Print Page | Close Window