Formula Help

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

I'm new at this and trying to create a formula that will update the status field in parent record from a status field(s) in child record(s). Certain status' will only need one record changed to change the parent record, where as for 2 status, all child records will need to have that status to change the parent record. 

Essentially, when all child record status are marked as either Not Started or Completed, I want the parent record status field to say Not Started or Completed respectively. However, if one or more of the child record status changes to In Progress (with the rest staying Not Started) I need the parent record to be updated to In Progress. And also if any of the child records change to Completed, I want to keep the parent record status field as In Progress. 

This is what I've build so far:

If(Contains([Project Title], "do not delete"),"Template",

 

If([# of Workflow Steps]>0 and [# of Open Workflow Steps]>0,"In Progress",

 

If([# of Workflow Steps]>0 and [# of Open Workflow Steps]>0,"Not Started",

 

If([# of Workflow Steps]>0 and [# of Open Workflow Steps]=0,"Completed")))


The result I'm getting is that when the child records are set to Not Started, the parent record status says Completed. When one or more child records is set to In Progress, the parent record status says In Progress (so that works). When the child records are set to Completed, the parent record status says Not Started.

Any assistance for this newby would be appreciated. Thank you!
Photo of Kelly Kiernan

Kelly Kiernan

  • 120 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
It might be best if you separated out the different options into their own summary fields.

[# of Workflow Steps]
[# of Workflow Steps - Not Started]
[# of Workflow Steps - In Progress]
[# of Workflow Steps - Completed]

Then your formula logic will be easier to manage:

If(
[# of Workflow Steps]=[# of Workflow Steps - Not Started], "Not Started",
[# of Workflow Steps]=[# of Workflow Steps - In Progress], "In Progress",
[# of Workflow Steps]=[# of Workflow Steps - Completed], "Completed",
[# of Workflow Steps - In Progress]>0, "In Progress", 
"Not Started")


Matthew Neil - Product Specialist
(Edited)