Discussions

 View Only
Expand all | Collapse all

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

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

    Posted 11-07-2022 12:53
    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 difference between each record is that in this other program (the source of the CSVs) is listing any notes/messages on the order as a separate line. I want to concatenate them in a text field in order of their posting. How would I go about doing that?

    ------------------------------
    Davis Ward
    ------------------------------


  • 2.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-07-2022 16:38
    Davis,

    To make sure the data looks something like this assuming five columns of data:

    2,  4,  6,  Count,  Address
      This was a strange thing make sure sales ...

    3, 7, 11, Count, Address
       Really good lead move to the front of the list ....

    and what you want is this with six columns

    2,  4,  6,  Count,  Address, This was a strange thing make sure sales ...

    3, 7, 11, Count, Address, Really good lead move to the front of the list ....


    ------------------------------
    Don Larson
    ------------------------------



  • 3.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-07-2022 18:16
    I need the notes in the third column to all be merged into a matching record in another table (merged by SONO) and entered into a text field that sorts the notes by [datetime]

    ------------------------------
    Davis Ward
    ------------------------------



  • 4.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 09:06
    Davis, do you have a Relationship where one Sono has Many Notes?

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



  • 5.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 11:04
    No, but one could be easily added. Since the notes table was just an ingest into QB table, I hadn't added one.

    ------------------------------
    Davis Ward
    ------------------------------



  • 6.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 11:17
    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
    ------------------------------



  • 7.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 13:41
    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
    ------------------------------



  • 8.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 16:12
    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
    ------------------------------



  • 9.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 16:31
    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
    ------------------------------



  • 10.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 16:32
    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
    ------------------------------



  • 11.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 16:42
    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
    ------------------------------



  • 12.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 16:56
    Mark,

    There was 1 minute lag between Davis' posting 9. and me putting 10. into the issue.  Now that I can see his tables, here is my suggestion using his table names.



    I would want a new Table called AR Aging Notes instead of appending a field in the AR Aging table.   It allows for much easier reporting.   I have had too many clients ask about the notes from Q2 when I started with appended text field.  Much pain......

    The key to my solution is that [sono]  in the AR Aging table has to be a unique field for the Pipeline to search for it and create the AR Aging Note.

    ------------------------------
    Don Larson
    ------------------------------



  • 13.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 19:22
    My approach would be to have a relationship, and then use a combined text summary field to roll up the Notes up to the Parent Sono record.

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



  • 14.  RE: Need some help understanding how to sort or index in order to concatenate fields across records

    Posted 11-08-2022 19:33
    It's a bit convoluted, but this is what I did:

    I created a relationship between the notes table and the destination table with a combined text summary field. Then seeing the lukewarm response from the key stakeholders to the formatting (they want a separate line for each and for no shortening of the words in the reports), I then used the Part function to split it out into separate lines in a formula text field.

    The only thing needing solving at this point is that when the child data is dumped daily, all the notes will disappear on the parent record until the child table receives its new notes for the day. But I think I'll solve this using a second table I created in the past that minimizes the downtime between refreshes.

    ------------------------------
    Davis Ward
    ------------------------------