Time and status base alerts

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

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!

When you say

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

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?

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?

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.

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?