Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Automations Community
Solved
Jump to Solution
1049 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Kai_Muscher
6 - Interface Innovator
6 - Interface Innovator

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.

Bildschirmfoto 2024-03-23 um 17.14.33.png

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

 

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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 

See Solution in Thread

5 Replies 5
ScottWorld
18 - Pluto
18 - Pluto

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
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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.

Bildschirmfoto 2024-03-23 um 22.08.15.png

SOLVED! 🙂

K

 

Congratulations! Glad I could help! 😃

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