Looking for a way to report records that fall within a certain pay period

nickboom · ‎Jan 27, 2023

I have a database where I manage the personal time off (PTO) for a large team. Each record has the employee's Name, Start Date, and End Date (as well as manager's approval, notes, etc). These PTO requests can rage from a single day (where start and end date would be the same) to multiple weeks. I need to produce a report for our payroll department of all of the records that fall within a certain pre-defined pay period which runs Sunday - Saturday for each week. I am also looking for a way to automate this or make it error proof.

A filter can easily be created to capture the records that apply to a given pay period:

Start Date, is on or before, the last day of the pay period
~AND~
End Date, is on or after, the first day of the pay period

This will find any entry that has one or more days within the pay period.

The brute force way to do this would be to manually update the filter definition each week, export a CSV, and send it along - but I am hoping there is a smarter way.

I was hoping that I could define an automation that would run each week at midnight, Sunday morning, create a CSV and email that to payroll - but I cannot find a way for the generation of a CSV to be automated.

Next, I tried to build an Interface / Shared View that payroll could use, but would require the user not to make a mistake in defining the filter each week to get the correct report, and I think it would be easy to mess that up. What I would love to have in this case would be a drop-down list with all of the filters for the pay periods pre-defined.

I found this post on the forum with a clever idea on how to solve something similar if the number of filters is relatively small - but having 52 buttons (one for each week of the year) would be really kludgy.

Hoping someone out there has another good idea I could use to solve this. Thanks!

bruceconsulting · ‎Jan 31, 2023

Hi Nick,

If you use this formula in a new field:

DATETIME_DIFF(NOW(),{Start Date},'days')

It will return the number of days between today and the date in the Start Date field.

You can then create a view filtered for all records in that field that are <= 7. On Sunday morning, this view will contain all your records for the past week. You can create an automation that runs every Sunday morning to create a Google Doc from that view if you are running Pro or above. Here's the link on how to do that: https://support.airtable.com/docs/using-document-automator

If you have any questions or need further assistance, feel free to schedule some time with me. Here is my Calendly link: https://calendly.com/d/x5c-662-55q/30-minute-meeting

Jody

nickboom · ‎Jan 31, 2023

Hi Jody, thanks for the reply. I came up with a similar solution by creating a calculated field and named it "Applies to Last Week?". I then created a shared view where I filtered to only show records with the resulting value of "YES". This works for payroll to have a concise list to work with during the week following the pay period - but if they need to look back in time they still have to manually create a somewhat confusing and easy to mess up custom filter.

Still interested to hear any ideas for something like a dropdown "list of weeks" (or pre-programed multiple filter) type functionality that I could build into an interface - but the google doc export is a good idea that I had not thought of and might be a good solution. Thanks!

The custom field needed to be more complex than a comparison to the start date because each record has a duration (start and end date) and thus the algorithm needs to check if the record started, ended, or completely encompassed the last week.

Here is the code for the custom filter that I wrote:

IF(
  OR(
    AND(
      {Start Date} >= DATEADD(DATETIME_PARSE(WEEKNUM(NOW()),'w'),-7,'day'),
      {Start Date} <= DATEADD(DATETIME_PARSE(WEEKNUM(NOW()),'w'),-1,'day')
      ),

    AND(
      {End Date} >= DATEADD(DATETIME_PARSE(WEEKNUM(NOW()),'w'),-7,'day'),
      {End Date} <= DATEADD(DATETIME_PARSE(WEEKNUM(NOW()),'w'),-1,'day')
      ),

    AND(
      {Start Date} < DATEADD(DATETIME_PARSE(WEEKNUM(NOW()),'w'),-7,'day'),
      {End Date} > DATEADD(DATETIME_PARSE(WEEKNUM(NOW()),'w'),-1)
	  )

	),
  
  "Yes", "" 
)

pressGO_design · ‎Jan 31, 2023

Not sure if this is crazy enough to work or just crazy. There's a table of Employee PTO entries with a WEEKNUM() formula that calculates the Start/End Dates/Times for the PTO, a concatenation that grabs the start and end pay periods and a field linked to the pay periods table. There's also a form that you use to create new PTO records.

There's another table with all the pay periods in it, with start dates (no times). There's an automation

that updates the Pay Periods field in the PTO record and links it to the pay periods table when the PTO form is submitted.

The interface has a record picker of the pay periods (right now I only have 7) and when you pick the pay period you see which employees were out.

Read only link to the interface and the base is here: https://airtable.com/invite/l?inviteId=invgtzB0rmatDzHAP&inviteToken=4787e738fb7f5d43653f0ecb66c2a76...

nickboom · ‎Feb 02, 2023

pressGo - Appreciate the efforts there, your base gave me a few new ideas. Thanks!

It does not exactly solve the need that I have however, for two reasons. First - it only picks the start and end weeks, but there could be cases that span 3+ weeks. But I believe that could be fixed with a more complex formula in the "Concatenate Pay Period" field that somehow solves for all weeks concatenated.

The bigger problem in my case however, is that the form submission cannot drive the action. In our workflow, a manager needs to approve before it is active, and also there is a workflow for canceling the PTO (where the record remains in the base, it is just flagged as canceled).

Your automation however is close to what I would call a triggered conditional link field (conditional, automatic linking is something I really whish Airtable could do and would absolutely enable a solution here). I had tried to do something similar with the trigger happening at a scheduled interval, so that it would just be updated once per week for the report. I cannot figure out how to do this however - the action options available with the scheduled time trigger both do not make sense to me, and do not appear to be able to do what you do in your form-submission-triggered automation.

pressGO_design · ‎Feb 03, 2023

I have 100% given up trying to use the quote function - please bear with the code snippet format.

It does not exactly solve the need that I have however, for two reasons.   First - it only picks the start and end weeks, but there could be cases that span 3+ weeks.  But I believe that could be fixed with a more complex formula in the "Concatenate Pay Period" field that somehow solves for all weeks concatenated.

IF(

{End Date Week Num}-{Start Date Week Num}=1,

CONCATENATE({Start Date Week Num}, ", ", {End Date Week Num}

),

IF(

{End Date Week Num}-{Start Date Week Num}=2,

CONCATENATE({Start Date Week Num}, ", ", {Start Date Week Num}+1, ", ", {End Date Week Num}

),

IF(

{End Date Week Num}-{Start Date Week Num}=3,

CONCATENATE({Start Date Week Num}, ", ", {Start Date Week Num}+1, ", ",{Start Date Week Num}+2, ", ", {End Date Week Num}

),

IF(

{End Date Week Num}-{Start Date Week Num}=4,

CONCATENATE({Start Date Week Num}, ", ", {Start Date Week Num}+1, ", ", {Start Date Week Num}+2, ", ", {Start Date Week Num}+3, ", ", {End Date Week Num}

),

{Start Date Week Num}

))))

I'm sure there's something more elegant than this, but it does do the trick. Also, if {End Date Week Num}-{Start Date Week Num}=5, I legit want to come work for the company you are doing this for.

The bigger problem in my case however, is that the form submission cannot drive the action.  In our workflow, a manager needs to approve before it is active, and also there is a workflow for canceling the PTO (where the record remains in the base, it is just flagged as canceled).

OK. So I would suggest creating an automation triggered by the manager's approval, which updates each PTO request as it is approved, rather than doing that once a week. Then you can create an automation that runs once a week that first finds the records* from the previous pay period (which could be a view in your pay periods table) and then inserts those records as a grid into an email that goes to the payroll people.

*whenever you run an automation at a scheduled time, you have to tell Airtable to find the records you want to do the thing with before you say "this is the thing I want you to do."

Fingers crossed that this makes sense. Also, edited for spacing. Edit#2: editing for spacing doesn't work. Grrr.

nickboom · ‎Feb 03, 2023

..Then you can create an automation that runs once a week that first finds the records* from the previous pay period..

I think this is the part that is most confusing to me - I can't figure out a way to drive such an automation off of a scheduled time trigger. The options available to me don't make any sense. In the screen grab that I posted above, in the "Choose Data" section, the only options are:

Actual Trigger Time
Expected Trigger Time
Next Trigger Time

If there is a way to edit a record like this using the scheduled time trigger, it is escaping me. If I can get this to work, I would simply run an automation on every record in the base as a specified interval.

pressGO_design · ‎Feb 03, 2023

You need to add "Find Records" action to the action box.

nickboom · ‎Feb 03, 2023

Ah - thanks again, this was a breakthrough for me in understanding how to set up this type of automation. I am currently experimenting with the emailed list option to see how that goes.

I was excited for a moment because I thought this might enable the type of bulk update that I needed to truly create a dynamic look-up field (that at least dynamically updates an entire table at a regular interval). But alas, still not possible in Airtable.

Per the Airtable support documentation:

pressGO_design · ‎Feb 03, 2023

Right. You need to divide and conquer: first update records individually as they’re approved so that they’re visible immediately in the interface and then you email them to your accounting people. This requires two automations and another table.

Is it a perfect solution? No. If you have a lot of employees and requests and other automations running in your base and you’re not on an Enterprise plan, it’s 100% conceivable that you would blow through your automations by mid-month. It adds another table (and more records) onto a base that might be bumping up against some hard limits (and another table that people can go into and mess up). You have to pick your poison.