Send notification if two records have the same date value

[MetroBOS]

I'd like to set up an automation to send an alert when two records have the same value in a date field.

For example, Record A has a date of August 6.  Record B has a date of August 7.  Then the date of Record B changes to August 6 (same as Record A).

Can someone please tell me if this is possible, and if so, how?

[corb1]

I'd create a formula field that checks the two dates, and setup a view that is for this condition. The formula would be like:

IF(Date1col=Date2col,1,0)

Then filter the view to show records only when the formula field = 1.

Then setup an automation to trigger whenever a record enters that view, to send an email or slack.

[MetroBOS]

Would that work for all records though?  I'd need it to look at all records, not just two.  Sorry if I didn't make that clear in my original message.

Also, what would I substitute "Date1col=Date2col" with?  I want to compare the same field between all records and flag anything that falls on the same date.

[corb1]

Oh I misunderstood. You are saying that you’d want an alert whenever there’s more than one record with the same date. Correct?

in this case I’d instead having a separate table specifically for this date issue. You can use a roll up in that separate table pointing back to this original table.. I think?? Then the roll up formula to use would be arrayunique and count, to find something >1. Would need to research more…

[corb1 · Accepted solution]

It's a little cumbersome, but here is a solution. You have your primary table, with recordA and recordB, and that table has your date field. See "Table1" below.

Add a secondary table and prefill it with all possible dates as the primary key (you can do this quickly in Excel and then paste in Airtable). Then add a linked field and allow multiple links back to the other table. Add a Rollup field to the secondary table & use COUNTALL(values) as the formula in the linked field. Create a view on the secondary table that filters to where the rollup value is >1.

Then add two automations - the first is associated with Table1. The trigger should be when the datefield is updated, to update the linked field column with the same string (which will link if string matches the PK in other table).

The second automation is for your alert email in Table 2. If any record enters your >1 view, then fire an email.

Table1

Table2