Help

Orders & Subscriptions Question

329 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Awais
4 - Data Explorer
4 - Data Explorer

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. 

 

 

1 Reply 1
TheTimeSavingCo
16 - Uranus
16 - Uranus

Hm, sorry, I hope someone else can help you with your issue, but I'm curious why you don't just create a new record per subscription every time instead of updating a single subscription though? Seems like it would solve the problem you're facing now