Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Comparing a complete set of dates with an incomplete set and outputting the dates that are missing

Solved
Jump to Solution
610 8
cancel
Showing results for 
Search instead for 
Did you mean: 
dmagyears
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi folks,

I've got employees that submit their timecard for every Friday of the year, so roughly 52 records for each employee all with the same date interval between them (1/3/25, 1/10/25, etc.). 

Many employees forget to submit a timecard for several weeks out of the year, so I would like to create a view/interface where each employee can see the weeks for which they forgot. 

I imagine it would be something where AT would compare the weeks/dates they should have inputted versus what they actually did input, but only displaying the difference between them, i.e. the missing dates. 

Is this doable in AT?

Many thanks in advance.

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Assuming you want the end result to be an Interface where each timecard the employee has missed is available for them to update, then the quickest way would be to just write a script for this really

If a script isn't an option, you could try:
1. Creating a new table where you create one record per week of the year per employee (i.e. if you have 10 employees you'd end up with 520 records), where each record represented a single week and a single year
  - Let's call this table 'Logs'
2. Make the primary field of this table a combination of the date interval and employee name, e.g. "1/3/25 - Jerry"
3. In your main data table (let's call this 'Timecards'), create a linked field to 'Logs'
4. In Timecards, create a formula field that outputs the same value as step 2, i.e. "1/3/25 - Jerry"
5. In Timecards, click the field header of the formula field from step 4, and then hit CTRL/CMD+C
6. In Timecards, click the field header of the linked field to 'Logs', and then hit CTRL/CMD+V
  - This should link everything up appropriately
7. In Logs, any record that doesn't have a populated linked field will represent a missed week for that Employee

Screenshot 2025-01-15 at 9.10.31 AM.png

Screenshot 2025-01-15 at 9.10.34 AM.png

Link to base

 

See Solution in Thread

8 Replies 8
Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

How do the timecards come in? Are they form submissions or are they PDFs or something of that sort?

They come in via Airtable forms.  They track percentages of time worked on different projects that particular week.

And do the timecards ask them to input the time period that the card is covering, or do you track that based on when the record is created (ie. it is assumed that a timecard always. covers the current week at the time of submission)?

I have a pre-loaded drop down of date options in a single-select field that they choose from.

TheTimeSavingCo
18 - Pluto
18 - Pluto

Assuming you want the end result to be an Interface where each timecard the employee has missed is available for them to update, then the quickest way would be to just write a script for this really

If a script isn't an option, you could try:
1. Creating a new table where you create one record per week of the year per employee (i.e. if you have 10 employees you'd end up with 520 records), where each record represented a single week and a single year
  - Let's call this table 'Logs'
2. Make the primary field of this table a combination of the date interval and employee name, e.g. "1/3/25 - Jerry"
3. In your main data table (let's call this 'Timecards'), create a linked field to 'Logs'
4. In Timecards, create a formula field that outputs the same value as step 2, i.e. "1/3/25 - Jerry"
5. In Timecards, click the field header of the formula field from step 4, and then hit CTRL/CMD+C
6. In Timecards, click the field header of the linked field to 'Logs', and then hit CTRL/CMD+V
  - This should link everything up appropriately
7. In Logs, any record that doesn't have a populated linked field will represent a missed week for that Employee

Screenshot 2025-01-15 at 9.10.31 AM.png

Screenshot 2025-01-15 at 9.10.34 AM.png

Link to base

 

Thanks so much, I will try this.  Scripting, at the moment, is way above my head.  Where do you suggest I go for scripting and advanced formula training?

Thanks, Kenneth, for your inquiries.

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

No problem!

For scripting training, you'd need to learn Javascript. There are many free courses for beginners online, any would do. Here's one 3.5 hour series on Youtube that I found helpful. That said, I had prior experience with scripting, and was just looking to brush up on Javascript's syntax, specifically.

Learning Javascript is just part of the equation. You'll have to learn how Airtable uses Javascript (what functions are available and how does it relate to Airtable's data structure). Luckily, Airtable offers it's API documentation from within the scripting editor for both extensions and automations.

After getting the Javascript basics down, I would recommend you try to create simple projects for yourself, even if they emulate functions that you can do without scripts (ie. update records that match a given criteria). As your comfort grows, try out more complex projects. Check the community board for topics (both solved and unsolved) involving scripting and see if you can achieve the desired functions.