Discussions

Expand all | Collapse all

Summing like fields

  • 1.  Summing like fields

    Posted 20 days ago
    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
    ------------------------------


  • 2.  RE: Summing like fields

    Posted 20 days ago
    Mike,

    What is the business use for summing the Time Card Data?   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  vs projects for trends in time card generation, you really need to use a different tool for that sort of multidimensional analysis.  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 Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 3.  RE: Summing like fields

    Posted 18 days ago
    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
    ------------------------------



  • 4.  RE: Summing like fields

    Posted 20 days ago
    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
    ------------------------------



  • 5.  RE: Summing like fields

    Posted 17 days ago
    Edited by Mike Tamoush 17 days ago
    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
    ------------------------------



  • 6.  RE: Summing like fields

    Posted 17 days ago
    Ok, post back if you are stuck

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 7.  RE: Summing like fields

    Posted 17 days ago
    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
    ------------------------------



  • 8.  RE: Summing like fields

    Posted 17 days ago
    Right, ... 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
    ------------------------------



  • 9.  RE: Summing like fields

    Posted 17 days ago
    I did the copy paste option for now. That worked great! Thanks!

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