Skip to main content
Solved

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

  • January 14, 2025
  • 8 replies
  • 50 views
dmagyears
Forum|alt.badge.img+6

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.

Best answer by TheTimeSavingCo

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

Link to base

 

8 replies

Kenneth_Raghuna
Forum|alt.badge.img+20

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

dmagyears
Forum|alt.badge.img+6
  • dmagyears
  • Author
  • New Participant
  • January 14, 2025

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.

Kenneth_Raghuna
Forum|alt.badge.img+20

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)?

dmagyears
dmagyears
Forum|alt.badge.img+6
  • dmagyears
  • Author
  • New Participant
  • January 15, 2025

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
Forum|alt.badge.img+31

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

Link to base

 
Adam, Airtable Consultant - thetimesaving.company
dmagyears
dmagyears
Forum|alt.badge.img+6
  • dmagyears
  • Author
  • New Participant
  • January 16, 2025

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

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?

dmagyears
Forum|alt.badge.img+6
  • dmagyears
  • Author
  • New Participant
  • January 16, 2025

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)?


Thanks, Kenneth, for your inquiries.

Kenneth_Raghuna
Forum|alt.badge.img+20

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.

Terms & ConditionsAccessibility statement