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 about creating a relationship and then creating a summary field but I don't think it would work in this case because I want to use the Account ID column to summarize instead of the Site ID column.

The only columns I'm interested in are the ones in the red square. Basically, I want to extract that data and consolidate it using the Account ID field and then have all the different Account codes related to the Account ID in one cell (like Table B). I don't mind if it has to be in a separate table. Is there a way to do this?

Table A

Table B

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.

DonLarson
Qrew Elite
5 days ago
How have you built the application? Does it look like this:

The data in your example fits this architecture. So to get the report you would need, build a Combined Text field in Vendors for the Account Code.
Then you make a Table Report in Vendors which would match your second example.
- ShalomEguale
  Qrew Member
  5 days ago
  Hi Don, I currently don't have a Vendors table. I only have the Site table (Table A). Also, I'm not sure making a table report will work. I ultimately want to use the combined Account Code field in a new form I'm building
DonLarson
Qrew Elite
5 days ago
Just to make sure I understand, you are building an application with only one table?
- ShalomEguale
  Qrew Member
  5 days ago
  That's the only table I have for now. But I don't mind creating another table if its necessary to achieve the results in Table B.
MarkShnier__You
Qrew Legend
5 days ago
Don,
I am currently wandering around a giant food market in Busan, South Korea, as part of a cycling trip.

A clean solution would be to use a formula query to summarize the account codes, and then use that field on a summary report which is grouped by vendor and vendor ID.
- ShalomEguale
  Qrew Member
  5 days ago
  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.

Forum Discussion

How to consolidate multiple rows into one