Forum Discussion

MichaelZeppieri's avatar
MichaelZeppieri
Qrew Trainee
6 years ago

Merging Tables

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?
Data import and export
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    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
    ------------------------------
    • MichaelZeppieri's avatar
      MichaelZeppieri
      Qrew Trainee
      6 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        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
        ------------------------------