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!!
