Create Missing Timecards Between Two Dates

Question

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.&nbsp;
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------------------------------

markshnier__you · Answer

Can you clarify if you need one time card created for every day or is the time card for every week, per employee?&nbsp;
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 · Answer

Mark,
Thank you for stepping in here...
So, it's 1 timecard (parent record) per week. Our week ends on Sundays.&nbsp;
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 · Answer

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.&nbsp;

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

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.&nbsp;
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.&nbsp;
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.&nbsp;
------------------------------Chayce Duncan------------------------------

markshnier__you · Answer

If you want to run a Pipeline, which will catch up the whole year, you can load up and help her table with all of the Sundays so far in this year.

Then you can run a pipeline that will search a table of employees, and then, in a For Each loop you would search that helper table for all the Sundays and then in a For Each loop from the second search, you would then search whether or not that time card week record exists, and if it doesn't exist, create it.

once you have that catch-up working, you could change the helper table so that it always lists the last say month of Sundays by using a formula based on today, and then run that pipeline each week to do any catch ups, but saving the pipeline the needless work of checking the whole previous year to date.
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

charlesdulaney · Answer

Appreciate your feedback, between you and Mark, I am whiteboarding a solution that I think will work.------------------------------v/r,Chuck------------------------------

Forum Discussion

Create Missing Timecards Between Two Dates