Discussions

Expand all | Collapse all

Formula Help

  • 1.  Formula Help

    Posted 03-28-2018 14:48

    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!


  • 2.  RE: Formula Help

    Posted 03-28-2018 17:41
    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 Specialistmneil@mcftech.com Phone: 440-201-6050 ext. 476" width="300px">