Summing like fields
I'm trying to come up with a creative solution to an issue. Two tables, projects and time cards. Time cards is child. I have many time cards (hundreds) for dozens of employees (lets say 40). Each ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendMike, if you would like to have easy summary totals for each unique combination of Employee and Projects you can make a field on your time card record in the format 1234-Related Project where 1234 is the EE#.
Then set up a new table called Employee Project Time and set the key field to be a text field called EE#-Project.
Then you can make a summary report on Time Cards on that field and use the More button to copy these to another table and initialize that summary table. Make relationships to pull in the project name and the company name. make summary fields to summarize totals up to that Employee Projects table.
Also make a formula check ox field on that Summary table called EE#-Project exists, with a formula value of true and look that up down to the Time cards table. Lookup that field down to the time cards table.
To maintain the table going forward you can use an Automation to fire off the time cards when a record is added or edited and the EE#-Project record does not exists.
Then you can also go back to that initial summary report off the time card table and filter it on the EE#-Project Exists field = unchecked, and subscribe to it. That will give you an easy safety net in case the Automation fails.
As for the record count issue, its' not likely to be an issue until you get over 500,000 records. maybe even 1 million. If the app starts to get to slow, you can always just copy the whole app with data and called it an archive version. Then give you main app a haircut to purge out old time records. But it may end up being more than 500,000 records before you have a problem, so I would not even worry about that now.
Quick Base keeps making incremental improvements to performance especially on high record counts, so that will also be helping you each year avoid a purge.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
Then set up a new table called Employee Project Time and set the key field to be a text field called EE#-Project.
Then you can make a summary report on Time Cards on that field and use the More button to copy these to another table and initialize that summary table. Make relationships to pull in the project name and the company name. make summary fields to summarize totals up to that Employee Projects table.
Also make a formula check ox field on that Summary table called EE#-Project exists, with a formula value of true and look that up down to the Time cards table. Lookup that field down to the time cards table.
To maintain the table going forward you can use an Automation to fire off the time cards when a record is added or edited and the EE#-Project record does not exists.
Then you can also go back to that initial summary report off the time card table and filter it on the EE#-Project Exists field = unchecked, and subscribe to it. That will give you an easy safety net in case the Automation fails.
As for the record count issue, its' not likely to be an issue until you get over 500,000 records. maybe even 1 million. If the app starts to get to slow, you can always just copy the whole app with data and called it an archive version. Then give you main app a haircut to purge out old time records. But it may end up being more than 500,000 records before you have a problem, so I would not even worry about that now.
Quick Base keeps making incremental improvements to performance especially on high record counts, so that will also be helping you each year avoid a purge.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
- Mark,
[EDIT: 2.22pm PST: One sec. I'm onto something]
I don't follow this step:
"Then you can make a summary report on Time Cards on that field and use the More button to copy these to another table and initialize that summary table. Make relationships to pull in the project name and the company name. make summary fields to summarize totals up to that Employee Projects table."
What do you mean make a summary report on Time cards on that field? And then initialize that summary table?
------------------------------
Mike Tamoush
------------------------------- MarkShnier__YouOk, post back if you are stuck
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------ - OK,
New update. I understood what you said and got it all to work. However, I am copying and pasting time card entries in for the import (I will likely do this bi-monthly). When I do that, the automation fires 4 times and then stops. I am wondering if maybe the new records are being created too quickly for the automation to fire every time? You mentioned a check, but I don't follow. What do you mean to look at the unchecked boxes and 'subscribe' to it.
------------------------------
Mike Tamoush
------------------------------- MarkShnier__YouRight, ... so Automations are "gated' to not run at more than 10 per second. i can't explain why they stop at 4, but certainly if you are importing a lot at once it will exceed the Automation limit.
There are two choices. i can make a button for you to click which will slowly walk down all Time cards entries for which there is no Parent EE#-Project record and add that record. So the good thing about that is that it's pretty mindless to just click the button and let the script run while a progress bar crawl across and go on with other work on another scren - or sit and watch if you prefer.
Or else, you need to run that same Summary report of Time cards missing Parents (filtered where that Parent Exists] checkbox is unchecked) and then copy to another table (being the summary table).
My reference to a Subscription report is just to subscribe to that summary report to come 5 days a week, which is supposed to be blank, so if you do receive it in the morning you know that someone forgot to do that step.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
