8 Replies Latest reply on Jun 14, 2017 3:13 PM by BrianWoods

    Combining Boolean Operators


      Hi, I am trying to write a solution that strives for better communication by encouraging the user to avoid certain words.


      It seems like it'd be a relatively simple formula that addresses a couple of conditions that will likely require combining the AND/OR operators.


      Rather than having annoying error messages reappear, conditional formatting should be an innocuous approach.


      At any rate, the first condition is whether one of the "undesirable" words has been entered.  There are dozens of words, but here's a small set:





          kind of

          used to


      The second condition is whether the user has chosen to override the conditional formatting. Might seem odd to have an override option. But trust me, it needs to be there.  At any rate, the override checkbox would have a single choice "On". With the checkbox on, any appropriate conditional formatting should disappear.  My best crack at it would be:


      Override  ≠  "On"


      (PatternCount (Self; " like ")  ≥  1 or

      PatternCount (Self; " really ")  ≥  1 or

      PatternCount (Self; " just ")  ≥  1 or

      PatternCount (Self; " kind of ")  ≥  1 or

      PatternCount (Self; " used to ")  ≥  1)


      I've set the conditional formatting to turn the fill to a soft orange color when any of the above words are used.

      As a test using the word "like" I've inserted a composite screenshot below showing the four possibilities (and the results).


      You can see that I'm getting the conditional formatting even with the override checked.



      Any guidance would be greatly appreciated.

        • 1. Re: Combining Boolean Operators



          Thank you for your post!


          I'm going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!



          FileMaker, Inc.

          • 2. Re: Combining Boolean Operators

            When you have an override, it can sometimes get tricky.


            What I would do is something like this:


            if (

                 (Override  =  "On")


                 ( (PatternCount (Self; " like ")  = 0) and ... )


                0 /* don't show warning */


               1 /* show warning */





            Hope this helps!

            1 of 1 people found this helpful
            • 3. Re: Combining Boolean Operators

              When I need to do a check for many possible conditions I usually prefer to make my calculation return simply 0 or 1.


              Your case can be done in one Case statement:


              Case (

                       Override = "ON" ; 0 ;

                       Patterncount ( Self ; "like" ; 1 ;

                       Patterncount ( Self ; "really" ; 1 ;

                       //all other conditions

                       0 )

              1 of 1 people found this helpful
              • 4. Re: Combining Boolean Operators

                I would guess that the value in the Override field for the example record where you're getting an unexpected result is not exactly equal to "On". Change the field to be a text box instead of a checkbox set to see. Look for a leading or trailing carriage return or other whitespace.

                • 5. Re: Combining Boolean Operators

                  Hi Rob,


                  Thanks for the suggestion.  I changed the field into a standard text box

                  which revealed a different value.


                  However when I entered the proper values (i.e. "off" and "on"), the

                  conditional formatting didn't change.


                  On Wed, Jun 14, 2017 at 1:45 PM, RobWestergaard <noreply@filemaker.com>

                  • 6. Re: Combining Boolean Operators

                    Clear out the contents of the Override field completely before entering new values.

                    • 7. Re: Combining Boolean Operators

                      Make sure that it's a text field. Make sure that the text exactly matches. Case (capitalization) doesn't matter, but any extra spaces, returns, other text in the field and the override won't be "on" even when it looks like it should be.


                      You may want to change the override field to a field of type number and set up a single value check box with 1 entered when selected and removed if a second click clears the check box. You then make the field narrow or increase the padding to keep the 1 from showing and put "on" next to it as layout text.


                      This would change your original expression to:


                      Not Override


                      (PatternCount (Self; " like ")  ≥  1 or

                      PatternCount (Self; " really ")  ≥  1 or

                      PatternCount (Self; " just ")  ≥  1 or

                      PatternCount (Self; " kind of ")  ≥  1 or

                      PatternCount (Self; " used to ")  ≥  1)


                      If you choose, you could simplify to:


                      Not Override


                      (PatternCount (Self; " like ")  or

                      PatternCount (Self; " really ")  or

                      PatternCount (Self; " just ")   or

                      PatternCount (Self; " kind of ")  or

                      PatternCount (Self; " used to ") )


                      But making that change should not alter the results that you get.


                      Note also that if one of these words/phrases is the first or last text in the field, the specified space before or after the text will not be there and the fill color won't change. Punctuation can also prevent the conditional format from changing the color.

                      • 8. Re: Combining Boolean Operators

                        Thank you all for your input.


                        Admittedly my coding skills are intermediate at best.


                        So I'll go with Phi'ls suggestion which mirrors mine the closest.