Skip to main content

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 eBacklog | 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

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 oTasks] 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 eInitiatives] 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!

Reply


Terms & Conditions