How do you track who hasn’t submitted their weekly time cards?
Say you have employee contacts and they are supposed to submit time cards for their activities each week. You need to know when someone has missed a time card or if someone has not logged their time. How can you measure this if they haven’t done it? The answer resides in knowing what should be there and measuring if it isn’t.
In this example, we need to know the window of time that we are interested and calculate the number of weeks we expect time cards. We start with a start date and compare it to today. We take the start date and calculate the first day of the week and then we take today’s date and calculate the last day of this week so we get a spread of days and add 1. Then we can divide by a 7-day week to get the number of weeks for which we need time cards.
# of Expected Time Cards to Current Week:
(ToDays( [Last Day of Current Week] - [First Day of Start Week])+1)/7
Next we need to count the number of time cards that have been satisfactorily completed. What does that look like? Is it only by the existence of a record? Probably not because when you peel it back you discover you actually want to know if there are any hours recorded on that time card. If there are no hours, then it is an indicator that the card has not been completed. What about if the person is on vacation? We need a way to indicate that this particular time card is exempt. So we might create a summary field that tells us the number of time cards with hours greater than 0 or if they have been approved to be exempt. If you try to create a summary field to do this, you will discover you can’t create a summary field with an OR condition. We have to create a formula checkbox field on the time card that monitors whether the hours are greater than 0 or if the Exempt field is checked. It is a “Valid Week” formula checkbox field. Then we can create a summary field with this checkbox as the condition.
[Hours]0 or [Exempt]
Now back at the contact record, we have the total expected weeks and we can compare it to the total valid time card weeks, and if they don’t match we have a way of identifying those contacts that need attention.
Lastly we create a report that shows us all the contact employees where the number of missing valid time cards is greater than 0. We can put this report on a dashboard or we can set up a subscription report sent to all employees that is filtered to only show me “my” contact record where I am the user and where the number of missing valid cards is greater than 0. This use of a filtered report tied to “my” user field is a very powerful way to distribute notices to the offending contact users. You can set the subscription report to be sent out to your employees every day until they update their time cards. When they actually do update their time cards, there will be nothing in the report and consequently the email subscriptions will stop for that employee. Very powerful!
Here is a video describing the “ABC Missing Time Cards” example application.
Existing customers may get the app here: ABC Missing Time Cards
New users: Click here to start your free 30-day trial with this app from the QuickBase App Exchange: ABC Missing Time Cards
We cover topics like this in our customization webinars held three times weekly. Check out a complete listing of webinar dates and times at https://www.quickbase.com/webinars.