Discussions

Expand all | Collapse all

If formula for multiple text choice fields

  • 1.  If formula for multiple text choice fields

    Posted 05-14-2019 18:30
    I am struggling setting up a "status" formula. All fields are Multi choice text fields, here is the breakdown:

    Fields (Possible status)
    1. [Submitter's Status] (Started, Submitted, Re-Submitted)
    2. [Confined Space] (Yes, No)
    3. [Excavation] (Yes, No)
    4. [Energized Equipment] (Yes, No)
    5. [Energized Follow-Up] (Null, LOTO, Energized, Diagnostics)
    6. [LOTO Complex] (Null, Yes, No)
    7. [Safety Manager Status](Null, Approved, Rejected)
    8. [Project Superintendent Form Status] (Null, Approved, Rejected)
    9. [Project Manager Form Status] (Null, Approved, Rejected)


    So I am just really looking for that first string so I can manipulate the answers to come up with the possible returns of "Started", "Safety Manager to Review", "Project Superintendent to Review", Project Manager to Review"

    Something like this...

    If(
    ([Submitter's Status]="Started"and [Confined Space]="Yes" and [Safety Manager Status]=Null, "Safety Manager to Review")
    (([Submitter's Status]="Started"and [Confined Space]="Yes" and [Safety Manager Status]="Approved", "PTP Approved")
    ([Submitter's Status]="Started"and [Excavation]="Yes" and [Safety Manager Status]=Null, "Safety Manager to Review")

    Or do I have to involve every field on each string?





  • 2.  RE: If formula for multiple text choice fields

    Posted 05-14-2019 18:33
    The Quick Base IF will evaluate the logic in sequence and stop at the first true statement.  Note that to test a text field as being blank, you may not compare to Null.  You need to do it like I have below





    If(
    [Submitter's Status]="Started"and [Confined Space]="Yes" and Trim([Safety Manager Status]" = "", "Safety Manager to Review",


    [Submitter's Status]="Started"and [Confined Space]="Yes" and [Safety Manager Status]="Approved", "PTP Approved",

    [Submitter's Status]="Started"and [Excavation]="Yes" and Trim([Safety Manager Status]" = "",  "Safety Manager to Review",


    etc







  • 3.  RE: If formula for multiple text choice fields

    Posted 05-15-2019 12:44
    So just copying and pasting in the first string, I am coming up with the follow error based on the BOLD text

    If(
    [Submitter's Status]="Started"and [Confined Space]="Yes" and Trim([Safety Manager Status]"="", "Safety Manager to Review")


    Expecting )




  • 4.  RE: If formula for multiple text choice fields

    Posted 05-15-2019 13:37
    Hi Ryan,

    It looks like you just need to change the " that comes after  [Safety Manager Status] to a ) to close out the function.

    Trim([Safety Manager Status])=""

    That was probably just a typo. 


  • 5.  RE: If formula for multiple text choice fields

    Posted 05-15-2019 19:35
    Ryan, sorry try this

    If(
    [Submitter's Status] = "Started"
    and [Confined Space]="Yes"
    and Trim([Safety Manager Status]) = "", "Safety Manager to Review")


  • 6.  RE: If formula for multiple text choice fields

    Posted 05-15-2019 20:13
    Just finished it Thanks Evan and QB Coach.


  • 7.  RE: If formula for multiple text choice fields

    Posted 05-15-2019 21:47
    well done,

    Your next IF will be way easier for you