How to consolidate multiple rows into one

Hello, Is there a way to consolidate multiple rows into one row? I have Table A right now. The Site ID column is the key column for the table since it’s a numeric field with unique values. I know abo...

formulas

MarkShnier__You
5 days ago
The formula text below will summarize the text field of Vendor Code for all the brothers and sister record which share the same Site Name, Vendor Name, and Vendor ID. YOU will substitute in your own fields IDs corresponding to those field names.

var text QUERY =
"{9.EX.'" & [Site Name] & "'}"
& " AND "
& "{6.EX.'" & [Vendor Name] & "'}"
& " AND "
& "{7.EX.'" & [Vendor ID] & "'}";

SearchAndReplace(
ToText(
GetFieldValues(
GetRecords($QUERY),8)), " ; ", ", ")

In the formula above it first defines the Query which looks for the brother and sister records which match.
Then the second part of the formula, starting from the inside out, gets the records which meets the filter criteria, then it gets field ID#8 which in my example is the vendor code, and then it converts that textlist to a regular text string separated by a semi colon, and then it changes the semi colon to a comma space.
Then I made a summary report with this grouping.

ShalomEguale

Qrew Member

How would I create a formula query and what would the formula be?

P.s. That sounds awesome!

MarkShnier__You

Qrew Legend

5 days ago

The formula text below will summarize the text field of Vendor Code for all the brothers and sister record which share the same Site Name, Vendor Name, and Vendor ID. YOU will substitute in your own fields IDs corresponding to those field names.

var text QUERY =
"{9.EX.'" & [Site Name] & "'}"
& " AND "
& "{6.EX.'" & [Vendor Name] & "'}"
& " AND "
& "{7.EX.'" & [Vendor ID] & "'}";

SearchAndReplace(
ToText(
GetFieldValues(
GetRecords($QUERY),8)), " ; ", ", ")

In the formula above it first defines the Query which looks for the brother and sister records which match.

Then the second part of the formula, starting from the inside out, gets the records which meets the filter criteria, then it gets field ID#8 which in my example is the vendor code, and then it converts that textlist to a regular text string separated by a semi colon, and then it changes the semi colon to a comma space.

Then I made a summary report with this grouping.

ShalomEguale
Qrew Member
3 days ago
That worked perfectly. Thank you

Forum Discussion

How to consolidate multiple rows into one