Re: Recurring Automation with Due Dates

Jeremy_Van_Caul · ‎Feb 16, 2022

I’m confused at why this is so difficult to figure out.

I want a record created automatically once a month in a table via automation with start and due dates filled out.

You can’t add formulas to automation fields so that’s a no go.

My workaround is that I’ve created a ‘creation date’ field that is filled in by the automation’s ‘expected trigger time’. That create date field is then used to fill in a start date and due date formula field using the DATEADD function with some math.

My problem with this method is that I’m not able to change dates independent of each other if I need some flexibility and the only way to adjust dates is by adjusting the ‘creation date’ date field.

Having due dates populate based on the date the automation was fired off seems simple enough but I can’t really figure out the best way to do this.

Any input would be great!

Ben_Young1 · ‎Feb 18, 2022

Welcome in @Jeremy_Van_Caulart!

I had a bit of fun solutioning this one, however, it is totally possible that there’s some context that I’m missing from your post.
Please let me know if there’s something I did not understand correctly or anything you would like me to tweak.

The first thing I did was create two date fields.
The Start Date & the End Date.
These will function as your start and due date fields.

The next thing I did was create three new fields:

Expected Creation (Date Field)
Initial Start Date (Formula Field)
Initial End Date (Formula Field)

Next, I built my first automation.

This is the automation for the once-monthly record creation that is at the core of your use case.
The mappings are simple. Just a fill-in for the project name (though you could opt to just leave this blank if you need to define the new project’s name).

You’ll notice that I followed your method of mapping the expected trigger time to the new Expected Creation field that I created.

Once the record is created, and the Expected Creation date field is no longer empty, the two formula fields will populate.


IF(
    {Expected Creation},
    DATEADD(
        {Expected Creation}, 5, 'days'
    )
)

This formula (in my example), takes the expected creation date and adds five days to it as a default.
This, of course, can be changed to reflect how many days you actually want to add in your particular use.

The second formula then references that formula field value to calculate its own returned value.

IF(
    {Initial Start Date},
    DATEADD(
        {Initial Start Date}, 15, 'days')
    )
)

The end result as of now looks like this:

Expected Creation Date = Automation Trigger
Initial Start Date = Expected + 5 Days
Initial End Date = Start Date + 15 Days

This means that the default end date is 20 days from the expected trigger time.
Again, you can configure this however you want.

Editing the Default Dates

Now that we have all of our required values present and calculated in the record, we can then trigger our second (and last) automation.
I’ll explain why we need two in a bit.

We’ll select a record condition trigger.
Our trigger is contingent on the two formula fields returning true.

Our only action will be a record update that takes the values of the formula fields and maps them to the Start and End date fields.

Final Results

Now, all you need to do to clean it up is hide the formula fields and the expected trigger time field.

Some Extras (if you want them)

I’m recently becoming a fan of using formulas in primary fields, and I really liked how clean it looked while solutioning your post.

If you’re curious, here’s what it all looks like:

All it does is create filler and flags if any key data is missing from any crucial fields.
Just me screwing around.

IF(
    {Project},
    {Project} & "\n",
    "⚠ Missing Name!" & "\n"
)
&
IF(
    AND(
        {Start Date},
        {End Date}
    ),
    "Start: " & 
    DATETIME_FORMAT(
        {Start Date}, 'l'
    )
    & "\n" &
    "Goal: " &
    DATETIME_FORMAT(
        {End Date}, 'l'
    ),
    "⚠ Missing Project Dates"
)

Again, let me know if I completely missing something, or if you need any additional help!

Edit:

I realized that I forgot to explain why you need two automations (at least I think you do).

Airtable can be a bit weird about its timing when it comes to automation action timings, as well as how quickly formula fields reflect changes to dependent field values.

While ideally we would attach the update record action from the second automation, onto the end of the first automation, it wouldn’t be reliable for us to use, as the update record only completes its intended job once the two date formula fields are populated.
(Another set of things that can be unpredictable in timing.)

Since Airtable does not have a Delay automation action, in order to support strong data integrity, it’s much safer for us to use a second automation that only fires once the required fields are populated and ready to continue the flow we need them to.