Automating Statuses based on single selects

[Evan_McMahon]

Hi Everyone,

I am currently trying to figure out a way to do the following:

I have two tables. 1 is for projects and 1 is for project options.

Each project option is linked to the proper project and both have single selects for their status.

Essentially it is set up like this:

(Project Table)

Project Name | Status | Project Options | Final Approved Option

(Project Options Table)

Project Option Name | Linked Project | Status | ~ Various other information

The Status fields are single selects with various statuses…i.e.

Research
Cutting
Sent for Approval
Final Approved
Paid

My goal is to do two things.

#1 Have the Project Table automatically update status based on the Project Options linked.
#2 Have the Final Approved Option link to the record that matches the proper criteria.

Given that several options will be made for each Project, there will also need to be a hierarchy where a project option that is further along the process negates ones that haven’t progressed as far.

Any ideas on how to achieve this?

[Vivid-Squid]

Hi @Evan_McMahon ,
This should be fairly simple. If a Project has more than one Project Option and each Option has a different Status, what would the Status of the Project be?

Do Project Option Status have a “weight” to them?

[Evan_McMahon]

Yes exactly.

As the status is updated and the project progresses each consecutive status should out weigh the previous status.

[Vivid-Squid]

Hi @Evan_McMahon
I think I have a way to do this, there may be better options but this works.

First create a new table called Status, Create 5 row and put the 5 status options in, then create another column in that table called Weight, number them 1-5.

Then in Project options, change the single select to Link to the new Status and Lookup the Weight

In the Projects table add a column that is a Roll up of the Weight using Max(values)

Now create an Automation with conditional options for each weight to Update the Project status