Forum Discussion

DavisWard's avatar
DavisWard
Qrew Trainee
2 years ago

Need some help understanding how to sort or index in order to concatenate fields across records

I have two tables, a source table that receives a CSV daily, and a destination table that is interacted with by users. The source table drops in multiple records with the same order number. The differ...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
Well you can create a combined text summary field to rule out text from children up into a parent record. The trick is how to control the sort, because they will sort alphabetically.  

So I have a collection of crazy formulas that attempt to deal with the sort issue, but I will need to test before posting.


------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
DavisWard's avatar
DavisWard
Qrew Trainee
2 years ago
I just remembered why I haven't added a relationship. Each day, that table is automatically cleared after the data is pulled into the destination table (all records deleted). This is because there is no unique ID to link up/merge to when a new legacy program csv is pulled each day.

------------------------------
Davis Ward
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 years ago
    Actually, I can't really picture what you want the result to be. Can you post an example with an example of what you want the result to look like and in which table the result would be.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • DavisWard's avatar
      DavisWard
      Qrew Trainee
      2 years ago
      The above is the source field (blue) and the destination field (red)


      Completed it should look like this, but in order by date (text field)



      ------------------------------
      Davis Ward
      ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Commander
      2 years ago
      Davis,
       
      Here is what I am envisioning for your solution.



      Following off Mark's suggestion I would make a new child table of the table you have the [sono] field.   As long as it a unique field a Pipeline should easily be able to find the parent record in [Another Table] and then add the child record.

      ------------------------------
      Don Larson
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        2 years ago
        I love Don :) but this time I'm not sure what he is suggesting.  I think that you need to have a table of Unique Sonos with key field of Sono and then a child table of the imported CSV notes.

        If you can get that setup I will show you how to get that sorted concatenated field.

        My suggestion for how to create the Unique Sono, is to have a Pipeline trigger when a note is created in the CSV import table.  There will be a field on the Unique Sono called [Unique Sono exists?] with a formula of true and looked up down to  CSV import.

        Then the Pipeline will create a Bulk Upsert and create the missing Sono. The reason for the bulk upsert to create just one record is that many instances of he Pipeline will fire when you do the import and several of them may fire at the same time and try to create the new unique Sono record, causing annoying emails of the Pipeline errors.

        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------