Merge data from two tables on a form
In a project management app, I have two tables to capture expense records. Expense A contains accounts payable bills, entered manually. Expense B contains credit card transactions. They must be separ...
Forum Discussion
I think you did it backwards, so that combined expense needs to be a parent of Expense A and Expense B, then you could bring in summary fields and use formulas to combine the two
------------------------------
Sean Boat-Moore
Manager SMB Development
Project Management Office and Quality Control
NCR Corporation
sean.boat-moore@ncr.com | ncr.com
------------------------------
------------------------------
Sean Boat-Moore
Manager SMB Development
Project Management Office and Quality Control
NCR Corporation
sean.boat-moore@ncr.com | ncr.com
------------------------------
Sean,
Thanks for your feedback. I already have summary fields and formulae to combine, but I would like to be able to have a line item list (report) in the parent form, not simply a summary.
Thank You,
------------------------------
David Revel
------------------------------
Thanks for your feedback. I already have summary fields and formulae to combine, but I would like to be able to have a line item list (report) in the parent form, not simply a summary.
Thank You,
------------------------------
David Revel
------------------------------
- MarkShnier__You
Qrew Legend
One approach is to create a third table to hold the combined data from both tables.
I would set the Key field of that combined table to be a custom Key field on the frmat like
A-1234
The prefix would be an identifier of the first table for example table A or whatever it is. The suffix will be the [Record ] field of the source table.
I would then create fields in the two respective source tables to calculate the Key field of the consolidated tables, for example
List ("-", "A", ToText([Record ID#])
Then set up two saved table to table imports to merge in the data from the source tables into the target table. When you run them it will populate the consolidated table.
Question will be how you keep the consolidated table synchronized with the source tables as records are added edited or deleted in the source tables.
There are a variety of approaches and it may depend on how current you need the data to be in the consolidated table.
For example, if it was good enough to have the table only updated once a day then that could be an overnight process which delete all the records in the consolidate the table and then uses a pipeline copy record step or a make request step to call an API to run those to save table to table imports.
If you need the data to be updated in the consolidated table more on a "live" basis then one approach would be to continue with the overnight process as a safety net but meanwhile during the day you would have a pipeline that would run which would trigger when records were added or modified in the source tables to make that same update in the consolidated table. Actually would probably need three different pipelines, one to deal with the situation when the record is added in the star stable one for a modification and one for a delete. Then of course you will need to replicate those pipelines for changes in the other source table.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
