If formula for multiple text choice fields

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
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?



Photo of Ryan Whitener

Ryan Whitener

  • 170 Points 100 badge 2x thumb
  • hopeful

Posted 2 months ago

  • 0
  • 1
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





Photo of Ryan Whitener

Ryan Whitener

  • 170 Points 100 badge 2x thumb
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 )


Photo of Evan Martinez

Evan Martinez, Community Manager

  • 12,338 Points 10k badge 2x thumb
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. 
Ryan, sorry try this

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

Ryan Whitener

  • 170 Points 100 badge 2x thumb
Just finished it Thanks Evan and QB Coach.
well done,

Your next IF will be way easier for you