Time and status base alerts


#1

G’day everyone!

I’m hoping someone will be able to help with this scenario.

Here’s a bit of background:

My airtable is currently being used to take expressions of interest from people for a business trial.

I’ve chosen 20 of these people to do a 6-week trial so I can get everything I need for this venture in place.

I converted 20 of these people in my ‘registered interest’ table from the status from ‘interested in trial’ to ‘trial’.

I have then taken payment from these 20 people. I have a separate table that records payment via Stripe that is associated to each customer.

This payment has been set-up as a re-occurring payment that happens once per week for 6 weeks.

Each new payment is also written to the payment database (hopefully, I am only in the first week).

The delivery of the product is each Friday and the order I need to make from the supplier needs to occur on Wednesday (2 days prior).

After the 6 week trial, there will be reoccurring payments and also ad-hoc payments that are made.

I would like a way that I can display all the customers that have made payment since the last delivery so that I am able to get the correct numbers on Wednesday to make my supplier order.

I am thinking there are a couple of different ways I could go about doing this, but I’m looking for the most simple an effective. Firstly, I am thinking about a payment status field that I could change at the customer level that says paid/not paid. It could set to unpaid when a delivery is made and then paid when payment is collected for that user.
The other way I was thinking, was having a date check on the payment date vs delivery date and then alerting based on that. But that seems more difficult.

The end result I would like to have is a detail of everything that I need to order, the quantities and for whom.

Does anyone have a solution they could share?

Thanks so much!


#2

If I’m gathering correctly here, it seems you are saying your customers’ payments are automatically being entered into the Payments table – is that correct? Is this done through some integration? Webhooks?

Is each Payment associated with a Customer through linked records? Such that when you look at a Customer record, you will be able to see a link to each of their Payments?

If all that is the case, then you should be able to create a “Rollup” field in your Customers table that finds the most recent payment date (rollup on the Payments table, the Date field, and use the MAX() function to rollup). Then, you can create a view that filters to show only Customers whose most recent payment falls within a certain date range, relative to “today” or “one week ago” or something like that.

(in this screenshot, Field 5 is a “Rollup” field using the MAX() function on a date, as described above, and I have the “Filter” menu open):