Help

Recurring reminder: auto update date fields?

Topic Labels: Automations
463 4
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Terranova
5 - Automation Enthusiast
5 - Automation Enthusiast

I like this reminder feature I tapped into sometime last year.  I am using it to monitor subscriptions based on their "Expiry Date".

Screen Shot 2023-10-30 at 12.14.36 PM.png

The triggers have worked so far, but I just noticed that obviously the Expiry date needs to be updated each time the date passes so that the reminder is triggered yearly/monthly recurrently, until I set the subscription to "Disabled".  What is the best way to do this?

Sorry if this has been covered.

Many thanks 

4 Replies 4
jwag
6 - Interface Innovator
6 - Interface Innovator

Hi David,

What I might do is have a formula field called "Next Expiry Date".

If the expiry dates simply need to increase by a year each time, the formula would be:

 

dateadd({Expiry},+1,'years')

 

or if the expiry dates might vary, you will need a single select field called something like "Expiry frequency", with your options of "Monthly", "Annual", etc.

Your formula field would then say:

 

IF(
  {Expiry update frequency} = "Annual",
  DATEADD({Expiry}, +1, 'years'),
  IF(
    {Expiry update frequency} = "Monthly",
    DATEADD({Expiry}, +1, 'months'),
    ""
  )
)

 

Make sure your formula field is set to be formatted as a date.

Once you've done that, you will need an automation with a trigger of "When a record matches conditions", where the conditions are:

Expiry - is - today (or maybe - is within - the last week) (or Reminder - is not empty (both would work))
AND
Subscription - does not contain - Disabled

And an action of:
Update record
Select your Table, Record ID and Field (Expiry). Press the cog next to Expiry and select Dynamic, then press the + and select your new "Next expiry date" field.

I haven't tested this but that's how I would go about it. Hope that helps!

Cheers

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @David_Terranova , just to add to what @jwag was saying, I think we're thinking along the same lines, but wanted to add more detail. I recorded a Loom video to explain how this automation works.

Here's the table overview:

Screenshot 2023-10-30 174254.png

I use 3 dates here to keep track of time: Initial Charge, Last Charge, and Next Charge. 

Initial Charge is when the first date a charge occurred.

For last Last Charge, I set it to a copy/past of Initial Charge to start, and then the automation takes over to update it. And 

And Next Charge is the frequency (weekly, monthly, yearly) added to Last Charge. 

### Remember to update all Table / Field / Variable names to match YOURS ###

Formula for Next Charge:

IF(
  AND(
    {Last Charge},
    {Renewal Frequency} = 'Weekly'
  ),
  DATEADD({Last Charge}, 1, 'week'),
  IF(
    AND(
      {Last Charge},
      {Renewal Frequency} = 'Monthly'
    ),
    DATEADD({Last Charge}, 1, 'month'),
    IF(
      AND(
        {Last Charge},
        {Renewal Frequency} = 'Yearly'
      ),
      DATEADD({Last Charge}, 1, 'year')
    )
  )
)

Screenshot 2023-10-30 174732.png

Trigger Reminder indicates when the automation is ready to run again, by displaying "Run Trigger"

Formula for Trigger Reminder:

IF(
  AND(
    TODAY() >= {Next Charge},
    {Last Charge}
  ),

  "Run Trigger"
)

Arthur_Tutt_0-1698702558071.png

 

The Automation is two parts. The first is the Trigger which is set to 'When record matches condition', and the condition is 'when Trigger Reminder contains Run Trigger'

Screenshot 2023-10-30 174947.png

 

And the 2nd is the script. The Script requires 3 input variables: recordID, lastCharge and renewalFrequency

Screenshot 2023-10-30 175114.png

 And here's the script (remember to update variable names to match yours)

// Select Table
var subscriptionTable = base.getTable("Subscription Renewal");

// Select Input Values
var inputConfig = input.config()
var lastCharge = inputConfig.lastCharge;
var lastChargeDate = new Date(lastCharge)
var recordID = inputConfig.recordID;
var renewalFrequency = inputConfig.renewalFrequency;
var newDate;

// Selecting Weekly, Monthly or Yearly Renewal
if (renewalFrequency == 'Weekly') {
   newDate = new Date(lastChargeDate.setDate(lastChargeDate.getDate()+7));
} else if (renewalFrequency == 'Monthly') {
   newDate = new Date(lastChargeDate.setMonth(lastChargeDate.getMonth()+1));
} else if (renewalFrequency == 'Yearly') {
   newDate = new Date(lastChargeDate.setFullYear(lastChargeDate.getFullYear()+1));
}

// Deleting Existing Last Charge Data (To Allow Automation To Retrigger)
var updates1 = [{
  "id": recordID,
        fields: {
            "Last Charge": ""
        }
}]

await subscriptionTable.updateRecordsAsync(updates1);

// Updating Last Charge Date With New Date
var updates2 = [{
  "id": recordID,
        fields: {
            "Last Charge": newDate
        }
}]
console.log(updates2)

await subscriptionTable.updateRecordsAsync(updates2);

 

Let me know if that worked for you!! 

 

jwag
6 - Interface Innovator
6 - Interface Innovator

Much more robust approach Arthur, thanks for adding on!

David_Terranova
5 - Automation Enthusiast
5 - Automation Enthusiast

ahh amazing, thanks very much to the both of you!

I will try this out and report back. Bit slammed at the mo, just wanted to say hi and thanks.