Summary Report from 2 Tables
Hi everyone! I hope someone can help me with a problem. Here's my situation: I have a table for each of the following; Projects, Time Entries, Revenue Budgets. Revenue Budgets and Time Entrie...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendNo really possible without adding a new table. You would need to have a table called Project Days where the Key field was a text field in the format:
[Project #] - YYYY-MM-DD
ie the Record ID# of the project followed by a date in text format.
You will have a Relationships like this
1 Project < Project Days (reference field will be a formula field calculated off the beginning of the Key field up to the hyphen)
1 project Day < Time Records (reference field will be a formula text field to calculate [Project #] - YYYY-MM-DD] )
1 Project Day < Revenue Budgets. (reference field will be a formula text field to calculate [Project #] - YYYY-MM-DD] )
To get this table initially populated you would create a formula checkbox field in the Project Days Table called [Project day Exists?] with a formula of true and look it up down to the two child table.
Then create a summary report in each of the respective child tables filtered where the [Project Day Exists?] is false and copy those records to the project days table. Then do the same with the other child table to copy across any missing project day records.
Then subscribe yourself to those report to come to oyu if any are missing in future.
OK, but how to Automate the creation of the Project Days table going forward? The answer is to set up an Automation to create the parent Project Days record when a child Time entry is created which has no parent [Project Day exists] = unchecked. Then another Automation to to the same on the Revenue Budgets table.
Now you can do your summary fields for the total revenue per Project Day and the total time entries per Project Day.
Lastly, put the Report Link field on the Projects form for the Project Days child table records.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
[Project #] - YYYY-MM-DD
ie the Record ID# of the project followed by a date in text format.
You will have a Relationships like this
1 Project < Project Days (reference field will be a formula field calculated off the beginning of the Key field up to the hyphen)
1 project Day < Time Records (reference field will be a formula text field to calculate [Project #] - YYYY-MM-DD] )
1 Project Day < Revenue Budgets. (reference field will be a formula text field to calculate [Project #] - YYYY-MM-DD] )
To get this table initially populated you would create a formula checkbox field in the Project Days Table called [Project day Exists?] with a formula of true and look it up down to the two child table.
Then create a summary report in each of the respective child tables filtered where the [Project Day Exists?] is false and copy those records to the project days table. Then do the same with the other child table to copy across any missing project day records.
Then subscribe yourself to those report to come to oyu if any are missing in future.
OK, but how to Automate the creation of the Project Days table going forward? The answer is to set up an Automation to create the parent Project Days record when a child Time entry is created which has no parent [Project Day exists] = unchecked. Then another Automation to to the same on the Revenue Budgets table.
Now you can do your summary fields for the total revenue per Project Day and the total time entries per Project Day.
Lastly, put the Report Link field on the Projects form for the Project Days child table records.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
