Software Subscription Directory and Renewal Alerts

Ben_Frederick · ‎Dec 28, 2018

I’m trying to create a table that houses all the information about the software subscriptions that I have.

Monthly Cost
Renewal Period (Monthly, Yearly, etc)
Number of User/Licenses
Next Renewal Date
Renewal Reminders

I’m running into trouble with Next Renewal Date and reminders. I’ve put in the start date of the subscription, which I use in a formula along with the period to calculate the first renewal date. But I want to calculate the next referral date in the future and have it update each time that referral date passes. How can I achieve this?

Formula: DATEADD({Start Date},{Period (Months)},‘months’)

Kamille_Parks · ‎Dec 28, 2018

You need to split “Tools, Trainings, & Services” into two tables:

One table called [Services] or something similar, where you list every type of tool, training, and service you provide.

Here is where you’d input the price of the service and any other static data that doesn’t change after each service period.
Add a Rollup field pointing to your new [Subscription Periods] table, described below, using the MAX(values) aggregation to get the latest start date and the latest calculated renewal date. This will update automatically every time you add a new “season”

Another table called [Subscription Periods] or something similar, where you input every “run” of each service offered. (fyi, this table is what is often referred to as a “junction table”)

In [Subscription Periods] you’d make a Link to Another Record field that points to [Services] to identify the type of each season
Here you’d move your {User/Licences}, {Start Date}, {Renewal Date} fields. Now you won’t lose data on each period whenever you need to add a new one.

Ben_Frederick · ‎Dec 28, 2018

Thanks for the detailed response! I’m a little new to Airtable, so I’m going to have to take my time to work through this solution. But I’m assuming there’s no way to make this work by keeping it all in 1 table?

Ben_Frederick · ‎Dec 28, 2018

I think I’ve figured out how to do this all in one table!

I basically created a field that calculates the amount of time between Today and the start date of the subscription in number of months. If that number of months is greater than the period of the subscription, then it adds that number to the start date to adjust for any prior renewal dates having past. If the difference of months between the start date and today is less than the renewal period, then it only calculates the first renewal date.

Renewal Date
Formula:
IF({Calculation for Renewal Date}>{Period (Months)}, DATEADD(DATEADD({Start Date}, {Period (Months)}, ‘months’), {Calculation for Renewal Date}, ‘months’), DATEADD({Start Date}, {Period (Months)}, ‘months’))

Calculation for Renewal Date
Formula:
(DATETIME_DIFF(TODAY(),{Start Date}, ‘months’))

Now, does anyone have any idea how I can:

Remove the time from the renewal date.
Remove the NaN from 2 fields when empty
Remove the Error from 2 fields when empty

Ben_Frederick · ‎Dec 28, 2018

After testing out the above solution a few different ways, it all seems to be working right EXCEPT if the period is 12 or 24 months and it’s after the first renewal date.

In this situation it’s calculating the renewal dates to be earlier than they should be… Can’t figure out what I’m missing.

Ben_Frederick · ‎Dec 28, 2018

How could I setup this formula?

IF ({start date} + {period months}) is less than Today, THEN add {Period Months} until ({start date} + {period months}) is greater than Today.

Is this type of formula even possible or is it circular?

Kamille_Parks · ‎Dec 28, 2018

Try this:
IF(IS_BEFORE(DATEADD({start date},{period months}, 'month'),TODAY()),DATEADD({start date},(ROUNDDOWN(DATETIME_DIFF({start date},TODAY(),'months') / period,0)+1)*{period months},'month'))

Ben_Frederick · ‎Dec 28, 2018

Thanks @Kamille_Parks :slightly_smiling_face:

I had to update a couple of referenced fields in the code before it would save it, so now the code looks like this:

IF(IS_BEFORE(DATEADD({Start Date},{Period (Months)}, ‘month’),TODAY()),DATEADD({Start Date},(ROUNDDOWN(DATETIME_DIFF({Start Date},TODAY(),‘months’) / {Period (Months)},0)+1)*{Period (Months)},‘month’))

Here are the results when plugging in this formula. A couple items have the same renewal date as the start date, some have renewal dates that are 2 month instead of 1 month ahead, and one has a renewal date in 2013.

Kamille_Parks · ‎Dec 28, 2018

Try:

IF(IS_BEFORE(DATEADD({Start Date},{Period (Months)},'month'),TODAY()),DATETIME_FORMAT(DATEADD({Start Date},ROUNDDOWN(DATETIME_DIFF(TODAY(), {Start Date},'month')/{Period (Months)},0)*{Period (Months)},'month'),"M/D/YYYY"))

Ben_Frederick · ‎Dec 28, 2018

Here’s the results for that one.