Formula checkbox field that uses a Contains operator to match the value in a text string derived from a List-User Field with values in a text field with logged entries

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have an app that manages employee time off requests.

Among other fields, I have 3 that i'm trying to use to solve for.
- Choose Approvers (List - User field)
- Approvers to Text (Formula - text field that converts the Choose Approvers field to a text list of FirstName LastName separated by ; )
         (e.g. "Jane Smith; John Doe")
- Manager Approvals (Text field with logged entries that populates when approvers click a button to show "Approve" or "Deny" with their name logged)
      (e.g. "[JUL-08-16 Jane Smith] Approved
      [JUL-08-16 John Doe] Approved")

I'm trying to create a Formula - checkbox field to check if the names in the [Approvers to Text] field are contained in the [Manager Approvals] field.

Tried this:
If(Contains([Manager Approvals],[Approvers to Text]),1)

but it didn't work. Ideas?
Photo of Jared Leavitt

Jared Leavitt

  • 10 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Try thisvar text ManagerName = Trim(NotLeft(Left(Right([Manager Approvals],"["),"]"),9)); // this should parse out the name only
If(Contains([Approvers to Text], $ManagerName),true)
Photo of Jared Leavitt

Jared Leavitt

  • 10 Points
Mark, doesn't seem to be working correctly. It should only check the box if all the names in Choose Approvers field match the ones in Manager Approvals field and it's not performing consistently.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Can you give me an example telling me what is in the field

[Approvers to Text]
 and what is in the field
([Manager Approvals]
Photo of Jared Leavitt

Jared Leavitt

  • 10 Points
- Approvers to Text (Formula - text field that converts the Choose Approvers field to a text list of FirstName LastName separated by ; )
         (e.g. "Jane Smith; John Doe")

- Manager Approvals (Text field with logged entries that populates when approvers click a button to show "Approve" or "Deny" with their name logged)
      (e.g. "[JUL-08-16 Jane Smith] Approved
      [JUL-08-16 John Doe] Approved")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
So in this example the list of Authorized approvers were "Jane Smith; John Doe", and the actual person who approved was Jane Smith.  

The formula should have checked the box.  Are you saying that it did not check the box?

Can you also confirm that the field which is logging changes is set to Append with new entries at the bottom, and is not set to Prepend new entries at the top?  I made the assumption that you were using the default Append and not "Prepend"
Photo of Jared Leavitt

Jared Leavitt

  • 10 Points
In the example above, both Jane and Joe approved and it should be checked. It's set to show new entries at the bottom. I have requests that have no entries in the Manager Approvals field that are checked AND ones that don't have all the necessary managers (sometimes there can be more than 2) that have approved that are checked AND ones that have all 3 managers approval and are not checked.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
I think that I did not understand your original question, and then you did not give me a real example of your data when I asked for an example.  The example you gave only had one Manager Approving.  I did not realize that it was meant to capture the audit trail of multiple approvers.

Your actual requirement as I now understand it, is in fact much more complicated than I thought.  You are saying that you have a list of user names which NEED to Approve and they are already in one string.

Then you have a Log Changes field which is recording who HAS approved.

You want the checkbox to be checked if you have all the required approvals.  

so here goes another try, it is not tested so hopefully no syntax errors

var text ReqApproverOne =    Trim(Part([Approvers to Text],1,";"));
var text ReqApproverTwo =    Trim(Part([Approvers to Text],2,";"));
var text ReqApproverThree = Trim(Part([Approvers to Text],3,";"));
var text ReqApproverFour =   Trim(Part([Approvers to Text],4,";"));
var text ReqApproveFive =     Trim(Part([Approvers to Text],5,";"));


$ReqApproverOne = "" or Contains([Manager Approvals], $ReqApproverOne)
and
$ReqApproverTwo =  "" or Contains([Manager Approvals], $ReqApproverTwo)
and
$ReqApproverThree =  "" or Contains([Manager Approvals], $ReqApproverThree)
and
$ReqApproverFour =  "" or Contains([Manager Approvals], $ReqApproverFour)
and
$ReqApproverFive =  "" or Contains([Manager Approvals], $ReqApproverFive)
Photo of Jared Leavitt

Jared Leavitt

  • 10 Points
Mark, yes, your assessment of the problem is now correct...thank you... however it's still not working correctly (it is still checking the box when it shouldn't - e.g. when all managers in the Approvers field are contained in the Approvals field). Might it be missing the If operator?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Sorry, is there a typo in the above comment.

"e.g. when all managers in the Approvers field are contained in the Approvals field)".
Photo of Jared Leavitt

Jared Leavitt

  • 10 Points
Mark, what i'm trying to say, is that the formula is still checking the box when the conditions i've laid out are not met.

It should only check the box if all the names in the [Approvers to Text] field are contained in the [Manager Approvals] field.

Right now, the [Approval Match] field with your formula above is checking the box in a couple situations:
1) Some in the [Manager Approvers]/[Approvers to Text] fields have approved and others have not.
2) No (0) Manager Approvers have approved.

It is also correctly *not* checking the box when not all the [Manager Approvers]/[Approvers to Text] have approved.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Can you contact me off line via the information in my profile.  I will need to debug this in your app or a Copy of your app.
Photo of Ryan

Ryan

  • 0 Points
Could you share your formula for [Manager Approvals]? That would be helpful as I build out an approval process in one of my apps. Thanks!