Concatenating multiple cross-ref text and date fields into one text area
There are two applications:
• Plan app
List of Milestones (text area)
• Milestone app
Description (Text area)
Due Date (Date field)
Each Plan can have 1 or more Milestones. The "List of Milestones" field in Plan app is meant to contain the list of milestones "Descriptions" and "Due Dates".
What formula can be use to concatenate all of the cross-ref milestones "Description" and "Due Dates" into the "List of Milestone" field in Plan app?
- Community Thread
- Forum Thread
- RSA Archer
- RSA Archer Suite
The issue here is that when you have a cross-reference field which allows multiple selections, you can only really reference one of them. Or, if you want to reference all of them, you must use an aggregate function, which doesn't work for concatenating text fields (unless you only want the text from the most recent record, or something comparable). If there was an iterative quality to calculated formulas for grid control fields (cross-reference, related records, and sub-forms), then we could easily pull this data over into the parent record, which in your case is the Plan application.
My simple formula works if there is only one (cross-ref) record, and it fails when there is more than one. Is it possible to solve this via Archer 2 Archer feed? If so... how?
I don't think there is any way to concatenate the fields values of multiple records returned from the REF(...) function.
But there is a way to avoid an ugly error by checking how many records you have with COUNTA(...). For example:
COUNTA([My cross reference field])=1
,CONCATENATE(REF([My cross reference field],[An interesting field]), " - ", [A local field])
,"(Multiple things here...) - ", [A local field])
I hope this helps. And by the way, if you find a better solution, please share!
The only way I know of to do this is to use a SQL based data feed to aggregate your information on the SQL side, and then feed it into your description field.
Really unfortunate that there doesn't exist a text calc function similar to the COMBINESELECTIONS which I believe does exactly what you (and I) are looking for but for a text field on a cross referenced app when there are multiple records.
Should be able to have a COMBINEREFTEXT(REF([Related Issues],[Issue Name]))
This returns a list (in text) of all the unique values list values for the related records.
COMBINESELECTIONS(REF([Related Issues],[Issue Source ID]))