Forum Discussion

ShalomEguale's avatar
ShalomEguale
Qrew Member
31 days ago

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 A

Table B

Table B

 

  • 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.

     

  • 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's avatar
      ShalomEguale
      Qrew Member

      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

  • Just to make sure I understand, you are building an application with only one table?

    • ShalomEguale's avatar
      ShalomEguale
      Qrew Member

      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. 

  • 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's avatar
      ShalomEguale
      Qrew Member

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

      P.s. That sounds awesome!

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        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.