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 separate because Expense B syncs to credit card company using the credit card company's record ID as the key field​.
Both tables have similar data: transaction date, vendor, amount, etc. Both tables are related as children to a parent Job table. Data from the two Expense tables is summarized in a formula field that combines the two sets of related Expenses. I would like to be able display a list of all expenses related to a given job as a single report on the Job form like one might with a report link field.
My current, clunky solution is to have a third table: Combined Expense. Since I want a list/report of all data from Expense A and Expense B, I made Combined Expense a child to both Expense A and Expense B with lookup fields to gather the data, and a third field to combine the two sets of lookup records. Pipelines handle adding and deleting child records in the Combined Expense table, triggered whenever entries are made in Expense A or Expense B.
My solution is functional but clunky, as data displayed in the Combined Expense table is not easily edited (grid edit for example), and the slow response of Pipelines can cause some consternation among users, not to mention the fact that it's a fair amount of pipeline cycles and management of pipelines.
Ideally, I'd like to generate the Combined Expense table report on the fly, so to speak, to display records from both Expanse A and Expense B on a form in a single report.
Thanks for your help!
------------------------------
David Revel
------------------------------
