How to consolidate multiple rows into one

Question

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

MarkShnierYou · Accepted Answer

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 · Answer

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.

Sign up

Login to the community