Advanced Lookup w/ Multi-Record Evaluation

[Alex_Stefanacci]

I’m hoping someone can help here… I have a base with two tables (Initiatives and Tasks). Each Initiative is linked to one or more Tasks and each Task has a State field (single-select [Backlog | In Progress | Complete]). What I would like to do is create a separate State field on the Initiative record that is driven by the State of the related Tasks. Something like this…

• If all Task State values are “Complete” then make the Initiative State = “Complete”
• If one or more of the Task State values is not "Complete then make the Initiative State = “Not Complete”

Any suggestions would be greatly appreciated

[JonathanBowen]

Hi @Alex_Stefanacci - this can be done with a lookup and a formula. In your tasks table you’ve got something like this:

In your Initiatives table you can lookup all of the current states of the tasks associated with each initiative:

The “initiative state” field is a formula which shows the “summary state”:

IF(
  OR(
    FIND('Backlog', State & ''), 
    FIND('In progress', State & '')
  ), 
  'Not Complete', 
  'Complete'
)

However, beware the task with no state as this will give you a false initiative state:

You might want to put an alert column on a task with no state as a reminder to set it to something.

JB

[JonathanBowen]

A couple of other comments:

• you can’t just check for the presence of state before you do the IF() formula as there might be a number of tasks and all but one of them have a “complete” state, the other task having no state at all, so this would still give an incorrect result.
• In the formula State is stringified using concatenation:

State & ''

This is done as the State Lookup is an array and you cannot do a FIND() on array - it must be a string.

[Justin_Barrett]

Here’s a variation on @JonathanBowen’s suggestion. In the [Tasks] table, I added a formula field that displays an X if the state is anything except “Complete”. This includes tasks with no state chosen.

In the [Initiatives] table, I roll up that {Marker} field instead of the states, using ARRAYJOIN(values, "") to pack everything together. My formula for the {Initiative State} becomes fairly short:

IF(Markers & "", "Not Complete", "Complete")

[Alex_Stefanacci]

Thank you both for your suggestions. Very much appreciated!