Recurring reminder: auto update date fields?

[David_Terranova]

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

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 

[jwag]

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]

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:

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

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

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'

 

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

 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]

Much more robust approach Arthur, thanks for adding on!

[David_Terranova]

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.