Context:
I have three tables. Customers, subscriptions, and orders.
A customer can have multiple subscriptions, and a subscription can have multiple orders. Every time a customer fills out the order form, it is created in the order table. So a 1-month subscription will have 12 orders for the year.
I have columns in my subscription table that track the expiry date and formulate the days remaining for the subscription. This way, I can automate email reminders as the subscription is set to expire.
What I'm trying to automate:
When I complete an order. I want to automatically update the expiry date for the subscription.
This is my thought process thus far.
Scenario 1: The subscription has already expired.
The customer orders a 3-month subscription. I activate the subscription and mark the checkbox complete on the table. I timestamp when the order was completed / subscription was activated.
The subscriptions can be 1,3,6 or 12 months.
I need to check if the date the order was completed on was after the expiry date. If the completion date is after the expiry date, I need to update the expiry date to {CompletionDate} + 1,3,6, or 12 months.
Example:
A subscription has expired for 1 month. The customer places an order for a 12-month subscription and it is completed the same day. The new expiry date should be {Completion Date} + 365
Scenario 2: Subscription is still active
If the order date is before the expiry date, the subscription is still active. In this case, I need to add 1,3,6, or 12 months to the expiry date.
Example:
The subscription has 7 days remaining and the customer places an order for 1 month's subscription. I completed the order the NEXT day. The new expiry date should have 36 days remaining.
{ExpiryDate[6]} + 30
Another problem:
As a subscription can have multiple orders, I need to make sure that only the data from the last order is being used to calculate the new expiry date.
Thanks, in advance.
