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.
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.
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:
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 = 1{
"id": recordID,
fields: {
"Last Charge": ""
}
}]
await subscriptionTable.updateRecordsAsync(updates1);
// Updating Last Charge Date With New Date
var updates2 = 2{
"id": recordID,
fields: {
"Last Charge": newDate
}
}]
console.log(updates2)
await subscriptionTable.updateRecordsAsync(updates2);
Let me know if that worked for you!!
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:
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!
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!
