Summing like fields

Question

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 time card includes many fields, but for our purposes the important ones are:

Job Number
Time Card Date (will be the end of a week)
Employee ID

If I go to a project, all time cards associated with that project appear. However, as expected on any given project, one Employee may have multiple time cards. I want to get a total hours for that employee. I can sort and group, which shows me the total, but I want it in a field that I can manipulate and use in future scenarios.

All I can think of is utilizing summary fields, but I think I would need to create a summary field for each employee, and have another summary table. Any easier options?

Secondary question: It seems with all these time cards, my table will end up in a couple years with hundreds of thousands of entries. Is there a way to avoid this? Or is that the nature of databases?

------------------------------
Mike Tamoush
------------------------------

donlarson · Answer

Mike,What is the business use for summing the Time Card Data?&nbsp; &nbsp;Is the summation about the project or about the Employee?
If you are trying to monitor or learn about the Employees you only need a single summary field in the Employee Table.If you are trying to slice and dice employees&nbsp; vs projects for trends in time card generation, you really need to use a different tool for that sort of multidimensional analysis.&nbsp; Something like Tableau or Power BI will let you write SQL queries to give you a large array of values that are not dedicated fields in Quick Base.------------------------------Don LarsonPaasporterWestlake OH------------------------------

markshnier__you · Answer

Mike, 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
------------------------------

michaeltamoush · Answer

Don,In this particular case the business use is to generate a pay app to an entity (a prime contractor in this case). Our invoice includes a line item that shows how many total hours a particular employee worked on a certain project. Right now, my data import gives me the total hours per time card, per project. I do not have access to change the data I get, so I need to combine all the time cards on a certain project, for a certain employee, into a single total hours for that employee so that I can utilize that number on an exact form plus document.------------------------------Mike Tamoush------------------------------

michaeltamoush · Answer

Mark,[EDIT: 2.22pm PST: One sec. I'm onto something]&nbsp;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.&nbsp; Make relationships to pull in the project name and the company name.&nbsp; 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?&nbsp; And then initialize that summary table?------------------------------Mike Tamoush------------------------------

markshnier__you · Answer

Ok, post back if you are stuck------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

Forum Discussion

Summing like fields

Related Content

Snapshot Fields

Find and Replace Field Values

Proxy fields

Consolidate Field Types

Pipelines Jinja Sum