Calculating a Subscription’s Expiration Date

UPDATE Sep 2, 2014
The prototype file was kindly modified by erolst. It’s much better now. Thank you erolst.

I posed a question on FileMaker’s developer forum today, and received a great response (thank you erolst), which I prototyped.

Report the status of a user’s subscription.

Background / Use Case
A mobile file is free on FM Go. Paid subscriptions are offered on the hosted file. The hosted file allows the user to sync the mobile file and share data with other users. Three types of subscriptions are offered: one month, six months, or twelve months. Users can purchase any type of subscription at any time.

Sample Data
The hosted file includes a [User] table and a [UserLog] table. The latter records user “actions” and corresponding time stamp. For example:

timestamp | action | monthsPurchased | c_expirationDate
2012-08-28 17:33:46 | Paid one month. | 1 | 2012-09-28
2012-08-28 17:53:22 | Paid six months. | 6 | 2013-02-28
2013-08-28 23:31:36 | Paid six months. | 6 | 2014-02-28
2013-08-28 23:34:14 | Paid twelve months. | 12 | 2014-08-28

Commentary on Sample Data
For the sake of defining some terms, and also for the sake of using plain language…
Line 1 – the user “subscribes”
Line 2 – the user “renews” subscription
Line 3 – the user “resubscribes”
Line 4 – the user “renews” subscription

Notice the calculated result in line 2? The expiration date is wrong because it fails to take into account the user’s prior purchase. The correct result should be 2013-03-28.

Line 3 is correct because the user’s subscription lapsed before she re-subscribed.

Line 4 is also wrong for the same reason as line 2. The correct result for the expiration date should be 2015-02-28

The existing calculation incorrectly adds the [monthsPurchased] to the [timeStamp] without accounting for prior [monthsPurchased]. Renewals are consistently wrong.

Am I overthinking this? I mean, it’s not exactly a unique use case. Maybe I don’t need to bother with expiration dates in the [UserLog]. Maybe the calculation should reside in the parent [User] table. I’ll need one there anyway to report the user status: Are they paid up or not?

I just think it’s cleaner to see a — correct — expiration date in the log.

Download the prototype here.