Advanced Lookup w/ Multi-Record Evaluation

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

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.

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")

Thank you both for your suggestions. Very much appreciated!