Forum Discussion

CharlesDulaney's avatar
CharlesDulaney
Qrew Member
9 months ago

Create Missing Timecards Between Two Dates

I can't see the forest for the trees on this one,

Anyway, we have a requirement to strictly maintain timecards on a weekly basis for several reasons, however, compliance is the crux of all timecard requirements.

Each timecard (parent table) is created by the user, also the timecard owner. Once created, they manage charge codes (child table).

I can discern the number of missing timecards for the current year through Today() with a basic formula (# required timecards) - (# initiated or completed) = missing timecards.

I want my weekly routine in pipelines to create missing week-ending timecards against each employee. 

I have 70 plus pipelines, so I'm sure I can build the brunt of the requirement, but I cannot figure out how to squeeze out the missing week-ending dates for each missing timecard for the current year through today.



------------------------------
Thank you so much,
Chuck
------------------------------

9 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    9 months ago

    Can you clarify if you need one time card created for every day or is the time card for every week, per employee? 

    Also, how far back do you want to go to create these missing time cards. Are you just trying to make sure the current week is up-to-date or you're trying to fix the whole years worth of history!



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • CharlesDulaney's avatar
      CharlesDulaney
      Qrew Member
      9 months ago

      Mark,

      Thank you for stepping in here...

      So, it's 1 timecard (parent record) per week. Our week ends on Sundays. 

      I need to create any missing timecards for the current year.

      I figured that I need to start with existing timecards of the current year, from here I can summarize active or closed timecards. If active or closed, it's not missing. I can now summarize the total for the current year and determine how many are missing.

      What's difficult, is determining which timecards are missing and create them. Once I create them, I could generate notification to users and leadership thus cracking down on those who are neglecting their responsibilities.



      ------------------------------
      v/r,
      Chuck
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        9 months ago

        There are elegant solutions that you could try for this - but it comes down to the goal with uncovering what is missing and will this be a one time thing? From reading your note - it sounds like going forward you plan to use a pipeline to create the missing ones so maybe this is only a question of getting the gaps. 

        In regards to backfilling, you could do a couple things: 

        1. Just do it in Excel and then build an import to load historicals. You can just index your users with each week of the year and see if they have a timecard for that week and then use that to make an upload. This assumes that the backfill is only a one time thing. 
        2. Make a summary report of your users and group by week to count the number of timecards. If the user has one it will show (1), blanks/null values means its missing. There is no direct action but its a simple way to see what's missing and where and then import the blanks or send it out to users to fill in whats missing. 
        3. This is the most complex but solves the short and long term: Create a table for 'Employee Weeks' - the table would be a join between a user and each week in the user. You would make the 'key' field a string concatenation of email + week (so like jdoe@email.com-1/1/23). You can then make a relationship to your timecards where you join the user and week as a formula field so that they link up automatically. Using your basic summary fields you can check if the employee has as time card or not and report and send out emails accordingly to those missing. You would pre-load this table with an import of your employees and whatever time period you want to check, and since you can chart out each week until the end of time you can import several years worth of these records all at once to check historically or into the future. 


        ------------------------------
        Chayce Duncan
        ------------------------------