Date recurring on a yearly basis every year?

[1ikigai]

Hi fellow Airtablers!

Could anyone suggest how can I make the ''Next charge'' date field recurring on a yearly basis every year if the toggle in ''Frequency'' is set to ''Yearly''? 
This dilemma is driving me nuts 😞

Right now, I just have a formula in that field:

DATEADD({Latest charge}, 1, 'years')

But it's not ideal because I would need to manually update ''Latest Charge'' field with the previous year every year.
Ideally, I'd love to solve this with a formula because I'd have lots of entries, and it may be a bit cumbersome to create automation for each and every entry.

But if automation to update the latest charge is absolutely needed, what is the best way to go about it/set it up?

[jwag]

Hi,

I've coincidentally just answered this in another post, so here's a link to that:

https://community.airtable.com/t5/automations/recurring-reminder-auto-update-date-fields/td-p/168564

You've done most of the work already, so it really is just a case of setting up an automation to move the Next Charge to the Latest Charge field when your trigger is triggered, whatever that might be (eg When record matches conditions -> When Latest Charge - is - today)

This will run for every record in that table automatically, when triggered (in my example, simply when the Latest Charge date is today), so the number of entries shouldn't matter, unless I've misunderstood.

[1ikigai]

ohhhh this might be it!

I loved your IF statements solution for doing annual/monthly thingy, but for some reason getting an error for that formula.
Do you think something must be changed in it or maybe I'm doing something wrong?

My formula looks like this:

IF (Frequency} = "Yearly",

DATEADD({Latest charge}, +1, 'years'),

IF(

{frequency} = "Monthly",

DATEADD({Latest charge}, +1, 'months'),

""

)

)

But I'm getting this error:

[Arthur_Tutt · Accepted solution]

Hey @1ikigai , if you're still having issues here's an alternate approach. I recorded a Loom video to explain how this automation works (watch it first).

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]

You're just missing a bracket around the first Frequency field:

IF({Frequency} = "Yearly",

DATEADD({Latest charge}, 1, 'years'),

IF(

{frequency} = "Monthly",

DATEADD({Latest charge}, 1, 'months'),

""

)

)

And actually I think I made a mistake, in that you don't need the "+1" in the dateadd formula., just "1", so I've removed that.

Give that a try!

[1ikigai]

Guys, thank you so, so much!

Really appreciate your help.

@Arthur_Tutt special thanks for going beyond with the explanation. Deleting and adding  script workaround is smart!