Advanced Lookup w/ Multi-Record Evaluation

Topic Labels: Formulas
1707 4
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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

4 Replies 4

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

Screenshot 2019-07-25 at 21.16.22.png

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”:

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

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

Screenshot 2019-07-25 at 21.20.18.png

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


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


4 - Data Explorer
4 - Data Explorer

Thank you both for your suggestions. Very much appreciated!