Time and status base alerts

[Parkigrocer_you]

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!

[Jeremy_Oglesby]

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):

[Dave_Harrison]

Hi @Jeremy_Oglesby - sorry for the late reply.

Yes, that is correct. Payments are being done via Zapier. I use moonclerk on a squarespace website to capture recurring payments. These are then added to the payments table.

Yes, each payment is associated to a customer through linked records. If I look at a customer table, I can see multiple payments link to customers account. And in the payments table, each payment is associated with a customer record.

My date field registers the payment in the format 2018-09-18T07:59:05Z. I thought this was giving me a bad return value, so I created another field for the date and used a formula field with DATETIME_FORMAT({Checkout date}, ‘DD/MM/YYYY’) to reformat the date.

For some reason, the returned value in my customer table is still 0.

Help!

[Jeremy_Oglesby]

When you say

Dave_Harrison:

My date field registers the payment in the format 2018-09-18T07:59:05Z

I am confused by this - when you say “registers”, do you mean it “accepts” it from Zapier in that format? Or do you mean it is “displaying” the date like that? If it is “displaying” the date like that (ie, that is what you see in the field), then it can’t possibly be a “Date” field as you call it. Airtable’s “Date” fields always display friendly formatted dates. If it is displaying as 2018-09-18T07:59:05Z, then it must be something other than a “Date” field - perhaps a “Formula” field or a “Single line text” field?

I’m also confused when you say you

Dave_Harrison:

created another field for the date and used a formula field with DATETIME_FORMAT({Checkout date}, ‘DD/MM/YYYY’) to reformat the date

Does this mean you created two fields here, another “Date” field (“for the date”) and a formula field, or just a formula field that you intend to display a date? Is {Checkout date} a “Date” field, or is it a “Formula” field that is formatted to display a date?

[Dave_Harrison]

Sorry, to clarify:

Point 1:
I have a field called ‘date’. It is simply a text field that is given the date value in the format of 2018-09-18T07:59:05Z from moonclerk/zapier.

Point 2:
Yes, I created a second field that reformats 2018-09-18T07:59:05Z into dd/mm/yyyy. I did this hoping the roll-up would work, but obviously not!

I’m gathering that the calculation you gave only works on a field defined as a date type. I just did a quick test where I created a date field and just copied the values across and the roll-up worked.

I guess the thing I need to do now is figure out how to create that date field upon record creation. Not sure if I should do that in the zap or in airtable. Is there a way to create the date field from the zapier formatted date?

[Jeremy_Oglesby]

Airtable should be capable of taking the date passed by Zapier, in the format Zapier is passing it, and enter that into a “Date” field (that is, a field of the type “Date” rather than of the type “Single line text”). I would try changing the field that you have labeled as “Date” into a proper “Date” type field, and test the input from Zapier to see if Airtable properly handles the format being passed.

[Parkigrocer_you]

Thanks so much @Jeremy_Oglesby! Once again you’ve saved the day.
Thank goodness for people like you in the Airtable community!

Is there any way to automate emails or sms from the blocks that we use?