Discussions

Expand all | Collapse all

Merging Tables

  • 1.  Merging Tables

    Posted 01-29-2020 17:00
    Under one app, I currently have three tables. Each table represents the accumulation of different business workflows that result in the generation of financial transactions.

    I want to now aggregate this financial data into one table so that I can generate reports from the aggregate data.

    I created a new table to serve as the aggregation point. My assumption is that an automation can help with this, but I am not sure which one. Because I already have over a 100 existing records that I will never modify again, there is not a create or modify trigger to copy the data. 

    What I need is a daily batch job where Quickbase scans the three source tables and only brings over new financial records into the aggregate table. It's basically a table synchronization automation.

    Any thoughts on how to approach this?


  • 2.  RE: Merging Tables

    Posted 01-29-2020 19:48
    Edited by Mark Shnier (YQC) 01-29-2020 19:49
    np,
    You can create a new field in the target table which will automatically be populated in the format SSS-Record ID#, where SSS is some acronym for each respective Source table and then the Record ID# is the record ID of each respective source table's records.  of course, you will also need the other fields too.

    The in Table 1, create a formula text field which will result in like 

    BUS1-123

    So, for example 

    List("-", "BUS1", ToText([Record ID#])

    Then set up three Saved table to table imports to MERGE the records for table 1 into the target table.  Select the Merge choice.

    Then set up an Automation to run each night to run the three imports as three steps.

    The only problem will be if records in he source tables may get deleted.  If that is the case, post back and we can discuss a solution to that flaw.

    EDIT - Set the Key field of the target table to be that SSS-Record ID# field.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Merging Tables

    Posted 01-30-2020 14:39
    Hi Mark,

    Thank you, will give this a try. Do the three source tables also need to have relationships established to the merge table?

    ------------------------------
    Michael Zeppieri
    ------------------------------



  • 4.  RE: Merging Tables

    Posted 01-30-2020 16:32
    They do not, but if you want to be able to link from the merge table to the source table, which would probably be a nice feature, then yes I would suggest having three relationships and having the Saved table to table copy map over the record ID of the source table into the merge table. 

    The Relationship direction would be One Source table has many Merge records, and you would need three relationships, one per source table.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------