Update status based on status of linked records

[Ben_Crouch]

Hello,

I have a project table and a milestones table. I want to update the status of my project record to “complete” when all the statuses of the linked milestones are also “complete”

i have a roll up field which pulls back the unique array of the milestones, so I have all the milestone statuses comma separated as a text string, but I can’t figure out the formula to look at that field and determine if all the milestones are showing complete or not 

[Dimitris_Goudis · Accepted solution]

Hey @Ben_Crouch, 

You can do this in 2 ways choose the best fit for you: 

Option 1: 
Use the formula REGEX_MATCH() to get if not done status options included in your rollup. 

Option 2:
Instead, the rollup field adds 2 Counter fields. The first one will count all milestones, and the second only those with status = complete. When those 2 numbers are equal, then your Project can be flagged "Completed"

If you want to calculate the percentage of completion per project, then option 2 is a better option. 

Thanks
Dimitris Goudis

[Mike_AutomaticN]

Hey @Ben_Crouch!

Dimitris solution might work. However the most straight forward solution I can see is:
1. Create a rollup field of Milestone Statuses on your Projects table. You can call it "Milestones Status (unique)" 
2. Use the following formula within such Rollup:

ARRAYJOIN(ARRAYUNIQUE(values))

3. The formula will return a string of unique values found as Status on your Milestones table.
4. Create an automation that will get triggered "When a record matches conditions"
5. Select your Projects table for your trigger, and conditions should be "When rollup "Milestones Status (unique)"  is... "Complete""
6. Create an Update Record action on the automation which should update on the Projects table, the record which triggered the automation (you'll have to map the RecordID).
7. Select field Status, to have it marked as Complete

See the automation I'm using on a different project! (My phases table would replace your Projects table).

If you need some help, I'd be happy to hop on a 5 min call to help you out.

Mike, Consultant @ Automatic Nation

[Ben_Crouch]

Thanks so much!