14 Replies Latest reply on Feb 1, 2013 6:32 AM by ErikWegweiser

    Way around limitation in use of variable in scripted find?

    Order_from_Chaos

      I have been excited to adapt some of my scripts to use the newly supported use of variables in the find script step. One issue that I've run into is that if I set a variable to a value of NULL with "", it becomes undefined. If this value is then used in a find script step, it is not expanded so that FileMaker states that no records exist for this query since the search term becomes "$VariableName". I run into this issue when I want to search by multiple criteria and some of them may vary well be empty. Is there a way to define a variable as having no value but not be undefined so that FileMaker will still expand the variable in the appropriate search field?

       

      Brett

        • 1. Re: Way around limitation in use of variable in scripted find?
          Mike_Mitchell

          Well, one thing that comes to mind right off the top of my head is to test the variable in your script. If it's empty, then set the field in the Find request to null instead of to the value of the variable. Example:

           

          If ( $variableName = "" )

            Set Field [ fieldName ; "" ]

          Else

            Set Field [ fieldName ; $variableName ]

          End If

           

          Or, you can put the conditional inside the Set Field command:

           

          Set Field [ fieldName ; Case ( isEmpty ( $variableName ) ; "" ; $variableName ) ]

           

          Would that work?

           

          Mike

          • 2. Re: Way around limitation in use of variable in scripted find?
            Order_from_Chaos

            The interesting this is, if I use a Set Field step after entering find mode, the variables are processed as you would expect and null values are accepted. The place this breaks down is if you use variables within the Perform Find script step with variables in the Specify Find Requests. FileMaker states on their help site that this is expected behavior. If a variable is undefined in a Perform Find script step, then the variable is not expanded and is returned in the search field as $variableName. For my use, I'm using about five variables at a time in the same script step and it works perfectly unless any one of these is NULL and therefore undefined. Error trapping for each one is more work than just doing it the old way that you mentioned above. It would be nice if there were a way for the variable set to "" or NULL were to simply return NULL as is the case in the Set Field step. Despite FileMaker's documentation that this is expected, I think it is not consistent and a great deal like the way merge fields using variables used to be returned but after some complaints was changed to return null values. Hopefully, I'm missing something and someone knows of a work around. If this performed as "I" would expect, it condenses several script steps into one - a much nicer way to go.

             

            Brett

            • 3. Re: Way around limitation in use of variable in scripted find?
              Mike_Mitchell

              Oh, okay. I see what you're trying to do. No, there appears to be no way around this. See previous thread in the archive:

               

              https://fmdev.filemaker.com/message/43617#43617

               

              Best I can tell you is error trap the set of variables ahead of the find. Sorry.

               

              Mike

              1 of 1 people found this helpful
              • 4. Re: Way around limitation in use of variable in scripted find?
                jormond

                Brett,

                Just for information's sake, the reason it works when you use the Enter Find Mode...Set Field approach has to do with how FileMaker handles the Set Field when there is no valid value given.  The behavior is the same if you try to set a field to a value from a related record that doesn't exist.  Set Field sets the value to null ( "" ).  Practically any calculation that results in an invalid value or no value will clear a field using Set Field.

                 

                My guess is that the Perform Find [Restore] step isn't actually performing an action, and the "Find" action to find a blank value is the equals sign ( "=" ).  That may be why a variable doesn't work.  If you set your variable to "=" when your variable doesn't have a value, it should then work as excepted.

                • 5. Re: Way around limitation in use of variable in scripted find?
                  RayCologon

                  Hi Brett,

                   

                  There is a relatively straightforward way around this issue.

                   

                  In your script, where each variable is being defined, append a trailing space. That way, if the rest of the expression resolves to null, the variable will still be defined, with its content being a space. And, since enclosing spaces are ignored in Find criteria, the finds will be performed correctly ignoring the trailing spaces (on find values that are not null) and ignoring field criteria that are null (now containing only a space).

                   

                  It's a simple (albeit slightly obscure) solution, and works in all cases I'm aware of, including fields indexed with regional, Default or Unicode index types.

                   

                  Regards,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 7. Re: Way around limitation in use of variable in scripted find?
                    Order_from_Chaos

                    Thanks Ray.

                     

                    I'll give this a try. Still seems like this is an inconsistent way for the variable to behave but glad there is a way around it. BTW, I found your book FileMaker Pro 10 Bible very helpful. Thanks for chiming in. I may be pushing the Perform Find script step too hard but seemed like this also allowed me to find based on related tables not on the layout as well. I'll try it and see.

                     

                    Brett

                    • 8. Re: Way around limitation in use of variable in scripted find?
                      Mike_Mitchell

                      Brett -

                       

                      Set Field will work if the field isn't on the layout, including related fields. Just FYI.

                       

                      Mike

                      • 10. Re: Way around limitation in use of variable in scripted find?
                        mrwatson-gbs

                        Hi,

                         

                        I can't get FileMaker to insert the variable name into search field. Which script step are you using to achieve that?

                         

                        The problem (at least the problem that I can see here) doesn't have anything to do with FileMaker variables, but with how FileMaker searches for empty fields.

                         

                        If you are searching a field for the users input, and the user wants to find all records with an empty field, you have to process the input anyhow in order to make a valid search criteria using the "=" sign.

                         

                        The following script step inserts an equals sign if the searchterm is empty, so that empty fields will be found.

                         

                        Set Field[ theFieldIWantToSearch ; Case( not IsEmpty( $Searchterm ) ; $Searchterm ; "=" )]

                         

                        Hope this helps

                         

                        Russell Watson

                        • 11. Re: Way around limitation in use of variable in scripted find?
                          RayCologon

                          mrwatson-gbs wrote:

                          I can't get FileMaker to insert the variable name into search field. Which script step are you using to achieve that?

                           

                          Hi Russell,

                           

                          The feature is new in FileMaker Pro v11 - which allows you to specify a named variable (either $local or $$global) as the search criterion within stored find requests for the Perform Find[ ] script step. When you do this, provided the variable you entered has a value when the step runs, the value of the variable will be used as the find criterion.

                           

                          mrwatson-gbs wrote:

                          The problem (at least the problem that I can see here) doesn't have anything to do with FileMaker variables, but with how FileMaker searches for empty fields.

                           

                          If you are searching a field for the users input, and the user wants to find all records with an empty field, you have to process the input anyhow in order to make a valid search criteria using the "=" sign.

                           

                          The problem described by Brett in the original post was that when the variables' value was null he wanted the criteria to be null (not "=" but actually null). However when the variable is null it's undefined and FileMaker instead (and somewhat idiosyncratically, one might say) uses the name of the variable as the search criterion.

                           

                          If Brett was wanting to search for records with empty values when the corresponding field criterion returns null, then some fancy footwork with the = find operator would be in order (in which case it could readily be worked into the original expression where the variable was defined). But that's not what was asked for here.

                           

                          Regards,

                          Ray

                          ------------------------------------------------

                          R J Cologon, Ph.D.

                          FileMaker Certified Developer

                          Author, FileMaker Pro 10 Bible

                          NightWing Enterprises, Melbourne, Australia

                          http://www.nightwingenterprises.com

                          ------------------------------------------------

                          • 12. Re: Way around limitation in use of variable in scripted find?
                            Order_from_Chaos

                            Russell,

                             

                            Agree using "=" is what is needed to find based on an empty field but I am more concerned with consistency in how the variable behaves. If a variable is null, I want null. This was also an issue in how variables used as merge data on layouts originally behaved until FileMaker corrected it with an update. Ray pointed out a work around but to me this is still inconsistent behavior that should be corrected.

                             

                            In contrast, if I use my variable that is set to null in a Set Field step, the value inserted is Null, not the variable name which to me is the expected behavior.

                             

                            Brett

                            • 13. Re: Way around limitation in use of variable in scripted find?
                              sporobolus

                              on 2011-11-29 07:05 order_from_chaos wrote

                              Agree using "=" is what is needed to find based on an empty field but I am more concerned with consistency in how the variable behaves. If a variable is null, I want null. This was also an issue in how variables used as merge data on layouts originally behaved until FileMaker corrected it with an update. Ray pointed out a work around but to me this is still inconsistent behavior that should be corrected.

                               

                              it is confusing, i agree, but consistent with how FileMaker works, given how

                              the variable search has overloaded the semantics of the stored Find requests

                              without an explicit denotation "this is a variable"

                               

                              when you look at it, you know that it's a variable, but when the interpreter

                              looks at it, it must decide whether it's a variable or just text that has the

                              form of a variable name; the interpreter decides by checking to see whether a

                              variable of that name exists; the benefit of this (which might seem minor) is

                              that we can save searches for text values that look like variable names

                               

                               

                              In contrast, if I use my variable that is set to null in a Set Field step, the value inserted is Null, not the variable name which to me is the expected behavior.

                               

                              in that case there is no ambiguity — the interpreter can tell that the variable

                              is not a text value, so it retrieves the value of the (non-existent) variable

                              • 14. Re: Way around limitation in use of variable in scripted find?
                                ErikWegweiser

                                OK, I'll admit I just got bitten by this one pretty good. I never realized that if a variable used in a scripted search contains no value, the resulting search criteria is not null, but as stated above, is instead the actual name of the variable. This detail is almost buried in Knowledge Base Answer 7590 (even with a few typos).

                                 

                                Although my opinion is this is not consistent with expectations in version 11, I must concede it sort-of makes sense, since FM currently has no way to tell if scripted criteria is a variable or just static criteria that begins with "$" — other than to first assume it is a variable only when that variable contains a value.

                                 

                                Here's my variation on how this behavior affected me adversely: In my scripted search routine, I have two criteria, such as the "static" [Category contains "Client"] and optional variable [Rating contains $rating_find]. If $rating_find is null, it does not mean I want to search for records where Rating is empty (=), but rather indicates I'm not specifying any criteria for that field at all. However, when FileMaker interprets the null variable as "undefined," instead of inserting a null value in the criteria, it interprets and inserts the literal, "$rating_find" that bollixes up the expected search result.

                                 

                                Also notable is the KB 7590 information that discusses how validation is disabled for variables used as search criteria. This can be demonstrated, for example, if you load $criteria with an asterisk ( * ) and perform a search on a field defined to be numeric values only. The scripted search will work perfectly, finding all records where that field is not null. This search would not not be possible manually, as the asterisk (unfortunately) can not be used as search criteria in a field defined with that particular restriction. To prove that, first run the script, then immediately attempt to manually modify the last find, without making any changes. Suddenly the asterisk—that does appear in the field—fails validation before the search can be executed.

                                 

                                I think there's no search operator that can be used to find "not null" in numeric-only fields.

                                 

                                While there is a search operator ( == ) that specifies only "empty" and a search operator ( * ) that specifies only "not empty text," as far as I know, there is none that could be used in any situation to find anything, null or otherwise, as if there was no criteria specified that field.

                                 

                                Thoughts?