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

    Combining Boolean Operators

    BrianWoods

      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:

       

          really

          just

          like

          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"

      AND

      (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.

      Override2.jpg

       

      Any guidance would be greatly appreciated.

        • 1. Re: Combining Boolean Operators
          TSPigeon

          BrianWoods:

           

          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!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Combining Boolean Operators
            David_McKee

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

             

            What I would do is something like this:

             

            if (

                 (Override  =  "On")

                  OR

                 ( (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
              arjen.evertse

              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
                RobWestergaard

                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
                  BrianWoods

                  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
                    RobWestergaard

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

                    • 7. Re: Combining Boolean Operators
                      philmodjunk

                      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

                      AND

                      (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

                      AND

                      (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
                        BrianWoods

                        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.

                         

                        Gracias!