1 2 Previous Next 17 Replies Latest reply on Sep 1, 2016 10:32 AM by LaRettaK

    How to write formula


      I want to hide a field and label whenever a condition is met. I cannot figure out how to construct the formula for a field which contains a checkbox set value list. Let's say the two values are "Clay" and "Lead". I want to hide unless either of two conditions exist: Either "Lead" is selected, or both "Clay" and "Lead" are selected.

        • 1. Re: How to write formula

          Let (

          _field = TheCheckboxField;


          Case (

          PatternCount ( _field ; "Clay" ) > 0 AND PatternCount ( _field ; "Lead" ) > 0 ; do_this_and_exit_case ;

          PatternCount ( _field ; "Lead" ) > 0 ; do_this_and_exit_case ;




          • 2. Re: How to write formula

            You could also try:


            IsEmpty ( FilterValues ( yourField ; "Clay¶Lead" ) )


            ... which will hide if neither values exist in the multiline checkbox field.

            • 3. Re: How to write formula
              David Moyer


              you might want to know that you don't need an If statement.  The conditional formatting is just looking for a False/True value (i.e. a zero or a non-zero result).  The following two calculations are equivalent:

              If ( a > b and c > d ; 1; 0 )

              a > b and c > d

              • 4. Re: How to write formula

                +1. PatternCount can be a little dangerous in this usage, as "Clay" and "Clay Pigeon" will both return a positive result - but they're not the same value.

                • 5. Re: How to write formula

                  +1 on Mike's response. FilterValues is a better choice as it avoids such "false positive" results.

                  • 6. Re: How to write formula
                    David Moyer

                    Because the result of checking boxes is a list (and because of the logic in your original question, all that's needed is to know if Lead is selected),



                        padList = "¶" & $list & "¶";

                        padValue = "¶Lead¶"


                    PatternCount ( padList; padValue ) > 0


                    This custom function might also be useful:

                    Custom Function:  ValueListIndex



                    FilterValues looks like a much easier way to do what you want.  (But my function can still be useful,)

                    • 7. Re: How to write formula

                      Or you can use


                      Not Isempty ( FilterValues ( $List ; "Lead" ) )


                      I use this so often that I created a custom function InList ( List ; sublist ) to save typing.

                      • 8. Re: How to write formula

                        I guess I don't see the problem at all with using the calculation I presented.  It is far simpler, allows for multiple items in either list and works just fine.  AND, it hides when none of the values exist.


                        Phil, not IsEmpty() will hide the field if any are checked.  Also, FilterValues ( smallList ; bigList ) is faster that FilterValues ( bigList ; smallList ).


                        And to others in this thread ... It is, after all, the purpose of FilterValues(). :-)

                        • 9. Re: How to write formula
                          David Moyer


                          • 10. Re: How to write formula

                            There is nothing wrong with the previous example as long as you include the returns as shown, just explaining in detail what I meant earlier.

                            Phil, not IsEmpty() will hide the field if any are checked.

                            Don't follow what you mean there. The whole idea in the original question was to know if a value was selected and the expression returns true.


                            I suppose the order of the parameters given might make a difference (this is new information to me), but given the relatively short lists of values involved with most check box fields, it isn't likely to make a noticeable difference.


                            Of course if you apply the concept over massive amounts of data or with a lot of iterations, then it definitely becomes something to pay attention to.

                            • 11. Re: How to write formula

                              Whether speed issues apply in this particular case or not does not change the principle.  Run some tests and it will prove what I say about which is fastest.    Every nanosecond counts and we should ALWAYS design with optimum speed in mind.


                              The reason I mentioned not IsEmpty() is that the question was to HIDE the field and label if it did not contain the values specified.  I just didn't want stevaroni to think your suggestion would hide the field as he requested.


                              Of course I needed to include the carriage return - they are two different values.  I am showing that it takes into account the entire string within a value in a list ... not just part of a string.  It is used to compare two lists or values; thus the carriage return.  I have nothing against creating a custom function from it but really, it seems simpler just to learn the principle.  It is just as fast to type the formula I provided as it is to type a custom function of that same thing, no?


                              Nice to see you around, Phil.

                              • 12. Re: How to write formula
                                David Moyer

                                I disagree with the "ALWAYS design with the optimum speed" concept.

                                To me, there's a balance between time-to-launch and time-to-optimize.  How many nanoseconds would it take to justify an extra hour of development time to an employer? (... a rhetorical question, of course)

                                • 13. Re: How to write formula

                                  Rhetorical question deserves a rhetorical response.


                                  If one learns the principles ( working at perfecting their craft ) then it does not take longer to build optimized. In fact, when one focuses on elegant concise design, it takes less work, as they learn to apply those principles.


                                  Here's the bottom line, not-at-all-rhetorially ...


                                  When given two calculations which achieve the exact same result, choosing the slower solution is ludicrous.  When someone identifies one method as faster than another, I ALWAYS appreciate the information.

                                  • 14. Re: How to write formula

                                    By the way David, I'm not suggesting that we can always succeed at optimum design (can anyone ever?) -  only that we aim for it and welcome opportunities to improve.  I do not think it right to dis information when one provides a more streamlined approach or calculation just because a person MAY have a small set of records.  It was information for all reading these forums for general consumption.


                                    Who knows stevaroni's record count, number of values in the field or number of words they will eventually be adding?  Even if small now, tables grow and solutions change.   When the file begins to run like a dawg and sink like a stone, they will have wished they paid attention to those nanoseconds so casually discarded early in a file's life.


                                    As it was explained to me once, by a very great person, "One side-effect of getting better all the time is that you cannot be as good now as you will be six months later.  If you want results now, you gotta work with what you have now."


                                    So you are correct that I should not have used the word 'always' (one never should I know) without clarifying ... optimization is a goal, not a destination.  So I will change my sentence to, "We should always strive for optimum design, to the best of our ability while remaining open to improved methods." 

                                    1 2 Previous Next