11 Replies Latest reply on Aug 12, 2015 8:38 AM by Annette

    Conditional Formatting - PatternCount?

    Annette

      Title

      Conditional Formatting - PatternCount?

      Post

       Hi, trying to get a conditional formatting to work but no matter how i write the function it isn't working.  I have two fields: ReferralTo OtherReferral ReferralTo is a checkbox field.  In the value list one of the options is Other (please specify). What I am trying to do is if Referral to has other selected (along with any of the additional options), and there is nothing written in the OtherReferral field for it to change color.  I started with this and edited it in various ways to no avail: PatternCount ( CSD_ClientForms 2::PC_ReferralTo ; "Other (please specify)"  and  IsEmpty ( CSD_ClientForms 2::PC_OtherReferral )) The field doesn't change color ever, regardless of being empty or the other fields selections/being blank. What did I do wrong?

        • 1. Re: Conditional Formatting - PatternCount?
          philmodjunk

          Is "Other" an actual value listed in the defined value list or did you select the "other" option in the Inspector?

          If you used the Inspector Setting, there is no "other" value being entered into the field. This option opens a dialog where the user can enter this data directly into the check box field rather than your OtherReferral field.

          • 2. Re: Conditional Formatting - PatternCount?
            Annette

            Hi.  "Other (please specify)" is actually a value in the value list on that field. 

            • 3. Re: Conditional Formatting - PatternCount?
              philmodjunk

              Make sure that your field is of type text. And if "Other" is not part of the text of any of the values in the value list, try this expression:

              PatternCount ( CSD_ClientForms 2::PC_ReferralTo ; "Other")

              That reduces the change that there is some small difference in your quoted text that is keeping it from matching to the specific value.

              That said, I generally use filtervalues instead of Patterncount as it's possible for patterncount to fail to work with some values and value lists. IF you had both "stop" and "Stopping" as values in the same value list, patterncount could not be used to detect if "Stop" and not "stopping" was selected where filtervalues can still be used:

              Not IsEmpty ( FilterValues ( CheckBoxFieldHere ; ValueHere ) )

              will be true if the value of "ValueHere" is found in the list of values created by clicking check boxes in the Check box field.

              But I suspect that you either have a field of type number or your quoted text isn't entered quite correctly.

              • 4. Re: Conditional Formatting - PatternCount?
                Annette

                Hey.  I have checked.  All my fields are set to text type.  And I copied and pasted the exact value from the value list into the conditional formatting to ensure it was correct.  That being said, I also  tried it with just "Other" thinking maybe the peice in ( ) was messing with it and it still doesn't work. 

                 

                I tried your suggestion above using filter values...that is a bit more successful, except, the  field I want the condition on is white when empty and shades it grey when I enter text into it, and I want it to do the opposite.  I tried making it just isempty as opposed to not isempty but no luck.  What am I missing, why does something so basic seem such a pain? lol

                • 5. Re: Conditional Formatting - PatternCount?
                  Annette

                  I'm just going to break it down again because most likely I'm not clear on what I want it to do.  Basically, what I'm tryign to do is if:

                  Field one is blank OR has anything except "Other" checked, Field 2 to remain filled white.

                  if:

                  Field one has "Other" checked, Field 2 to fill grey IF it is blank

                  If:

                  Field one has "Other" checked, Field 2 to remain filled white if it has texted entered in it.

                  • 6. Re: Conditional Formatting - PatternCount?
                    philmodjunk

                    Specify a White Fill color for field 2 just using the inspector as the "default" color

                    Field one has "Other" checked, Field 2 to fill grey IF it is blank

                    Create a conditional format expression on Field 2:
                    PatternCount ( Field 1 ; "Other" ) and IsEmpty ( self )
                    Specify the grey fill color.

                    • 7. Re: Conditional Formatting - PatternCount?
                      Annette

                      Back to square one...lol.  that's what I was doing from the start which wasn't working and brought me here. 

                      • 8. Re: Conditional Formatting - PatternCount?
                        philmodjunk

                        Compare your file to this one. (hope you have at least FMP 12) https://dl.dropboxusercontent.com/u/78737945/OtherCheckBoxDemo.fmp12

                        The left hand example reproduces what you have specified and works. The right hand example uses the same fields, but uses hide object when to set this up the way I would do it.

                        • 9. Re: Conditional Formatting - PatternCount?
                          Annette

                          Thanks very much.  I'm using version 14.  I'm starting to wonder if it's a bug with 14 because I had exactly that and it doesn't work.  It never occurred to me to use the hide option.  Might just go with that although it doesn't demand the attenntion the changing field color does. 

                          • 10. Re: Conditional Formatting - PatternCount?
                            philmodjunk

                            The file that I shared with you works identically in FileMaker 14. FileMaker 12, 13, 14 all use the same file format so this should work the same with all 3 versions. Users with older versions--that use a different file format would not have been able to open this file.

                            • 11. Re: Conditional Formatting - PatternCount?
                              Annette

                              I know, I was able to open it and see what you did fine.  I was just saying, I wonder if I have found a glitch in V14 that is making the conditional value not work in this instance because there is no reason it shouldn't.  I've done exactly what you've done in your example file.  I just tried it in a previous version (v11) and it worked fine using the same exact formula.  Soooo that explains it, I've been going crazy for two days for a glitch!!