Multiple Tables Report

Question

Hi all,I have three project tables from different business units, each with fields for contract value, contract date, and other details.We want to create a dashboard with reports combining the data from all three tables.What is the most efficient way to achieve this? Does anyone have any suggestions?Thanks!

miketamoush · Answer

I don't know if someone out there has a better idea but I have accomplished this using a combined table. If the 3 tables with my data are Table 1, Table 2, and Table 3 I do the following:

Make relationships where One Table 1 has many Combined Table. One Table 2 has many combined table....etc.
Make actions (or pipelines) that say, when there a record is added in Table 1, add a record in the Combined Table where [Related Table 1] = Record ID of new record. Now every record from Table 1, 2 and 3 has a linked record in the combined table.
Make lookup fields for the fields you want to compare. In your case, value and contract date for example.
Make a bunch of formula fields to present the data nicely.
For example: [Value Formula] =
If(
[Related Table 1]>0, [Lookup Value Field from Table 1],
[Related Table 2]>0, [Lookup Value Field from Table 2]...etc....)

I have even brough over dates and made a shared calendar, etc.

franciscoojeda · Answer

Thanks!!&nbsp;

markshnier__you · Answer

I have an alternate suggestion. I would create the fourth table that Mike talks about but I would set the key field to be a prefix corresponding to each of your three separate tables and then a dash and then the record ID of the source table.

In each of the three tables I would create a formula to make that key field of the fourth table so for example it would read like

List("-", "Business Unit 1", ToText([Record ID#]))

Then create three saved table to table copies to copy the fields from respectively each of the source tables into the combine table.

Then I would set a pipeline to run every hour say which would be to first delete all the records in the combined table and then to successively run each of the saved table to table copies.

The make Request step to run the saved T2T copy would look like.

mycompany.quickbase.com/xxxxxxxx/?act=API_RunImport&ID=10

miketamoush · Answer

Mark,I like the idea of the Combined table having a key field in that way but I had a question. Lets say each of your 3 tables has 5000 records. Wouldn't that mean every hour your pipeline is deleteing 15000 records from the combined table, and recreating them, every hour? I was under the impression that a record ID could only go up to about 4 million. Wouldn't recreating 15,000 record IDs every hour, hit that limit quite quickly?

markshnier__you · Answer

The limit for maximum record ID in a table is 4,294,967,295.

by my calculation, if you burn through 5000 record IDs every hour, then you would run out of numbers after about 10 years. But you probably don't need to refresh every hour outside of business hours and maybe there would be a more intelligent way of deleting just the records that are no longer required. The only purpose of deleting is to cover the possibility that record in one of the individual tables was deleted. But you can set up relationships and be more intelligent about deleting.

Forum Discussion

Multiple Tables Report