I need a notification once all records with same reference have reached the same status

[Kai_Muscher]

Hello, Everyone!

I am desperately looking for a solution for something where I thought that it's easy to do. But somehow this is beyond my thinking. 🙂

The situation:
I have a table, grouped by a reference ("EXA"). Several records with same reference ("EXA") go through different "status" until it's done. The "status" gets updated slice by slice for the records with same reference. So far so good.

What do I want to achieve:
once all records under same reference have reached the same status (for e.g. "DELIVERED to project site") I want to get an alert. Best would be an automation which sends an email saying that all is done for EXA# 24DE535529298955B6.

I hope the solution is not too easy, otherwise I would be frustrated by my own skills. 🙂

Kai

[ScottWorld · Accepted solution]

You're right, there's not really a super-easy way of evaluating a group of records in Airtable.

Here are 2 different ways of doing it:

1. If you want to evaluate a group of records at the grid level, all of those records would need to be linked to the SAME RECORD in another table. Then, from that other record in that other table, you could use 2 count fields and a formula field to give you the results you're looking for. One of your count fields would count ALL the records, and then the other one would count only the DELIVERED records. Then, you would create a formula that compares whether those 2 count fields have the same value. If so, you could trigger your automation based on that formula field.

2. If you want to evaluate a group of records in an automation, you could perform 2 "Find Records" actions in a row. One would search for all the records in the group, and then other one would search for the records that are DELIVERED. Then, you would create a conditional that compares the "length" of both find results.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

[Kai_Muscher]

Hi, Scott!

Your ideas sound great (as always)! 🙂 I was hoping to avoid another junction table. Let me try with an automation including 2 x find record actions…

K

[Kai_Muscher]

Okay, with help of a junction table, at the end it's easier than I thought:

I created a junction table for the references ("EXA") and did 2 rollup's. One counting the total records, and a second one that meet certain conditions (counting what is not "DELIVERED"). If difference is 0/zero then everything should be done and an automation for notification can be triggered.

SOLVED! 🙂

K

[ScottWorld]

Congratulations! Glad I could help! 😃

[Kai_Muscher]

After another glas of wine I reduced to only one rollup field and tweaked the rollup aggregation formula to read:

IF(AND(ARRAYUNIQUE(values) = 'DELIVERED', COUNTA(values) = COUNTALL(values)), 'COMPLETED', 'still in progress')

Works great as well!  😁 🍷

K