1 2 Previous Next 16 Replies Latest reply on Jun 22, 2016 7:11 AM by PSI

    Find * does not work as described

    PSI

      Generally when I find for an empty field I just use an equal sign. We were working on a find routine, which I did for another system where I put a few global fields at the top of a list view. Then in the script set a variable a calculation like this.

      Case ( not IsEmpty ( WD.TS::zG_DistrictID ) ; WD.TS::zG_DistrictID ; "*" )

       

      It worked find in that system because all of the fields will always have a value. Not so in the other system.

       

      However, In find mode operator dropdown shows this...

      I read zero to mean empty but it doesn't work that way. I think it should say one or more characters.

       

      John

        • 1. Re: Find * does not work as described
          StephenWonfor

          John

           

          I note that a search for "*" with an OMIT will find empty fields.

           

          Stephen

           

          "An empty canvas is full." ~Robert Rauschenberg

          • 2. Re: Find * does not work as described
            PSI

            But for my routine I was trying to simplify the script because there could be any combination of 4 fields they might search on. So the variable will contain a value or * which works great when the fields in question always have a value.

            • 3. Re: Find * does not work as described
              alquimby

              Consider that a field in a record may look empty, but may have 1 or more spaces. Find * is this field will include that record in the Found Count of those fields having values. Find = in this same field will not include that record with the spaces with those that are truly empty. You might need to run a Trim on the fields in question in Replace Field Contents.

               

              Find = in 2 fields in the same find request returns only those records that have no values in both fields, not either field.

              • 4. Re: Find * does not work as described
                user19752

                It seems "zero or more characters" but "in word or index". (So if the field is empty, there is no data to match with criteria)

                Finding ** matches to one character, or *John matches to John, so it is clear that not "one or more".

                 

                In Japanese index, ~* works as "find any" that also match to empty field, but it doesn't work for other languages.

                • 5. Re: Find * does not work as described
                  greatgrey

                  Null ("") and zero (0) are not the same thing.

                  • 6. Re: Find * does not work as described
                    PSI

                    I guess I am being a bit to literal. I do realize the difference between null and zero. However, zero 0 is a character as are any values entered. So when I read ( * zero or more characters ) I expect zero characters to mean no characters or null.

                     

                    Clearly that's not the case. I think it should be ( * one or more characters )

                     

                     

                    .

                    • 7. Re: Find * does not work as described
                      Mike_Mitchell

                      No, because as user19752 points out, searching for "*John" will return any record with just John. So, there are zero characters in front of the word, but it still returns.

                       

                      If you put nothing else but the asterisk in the field, then it won't return empty fields. If it did, it would just find everything, every time. Not very useful.

                      • 8. Re: Find * does not work as described
                        PSI

                        Now I see what is meant by ( * zero or more characters). However, Zero only pertains to a search this isn't * only

                         

                        Test with 4 records.

                        John

                        AJohn

                        (space) John

                        _John

                         

                        "*John" finds John with zero or more characters preceding "John". However, I think it would be useful to enter asterisk only to find null and not null values.

                         

                        For instance I setup a search feature for a client using list view with global fields in the header. They fill out one or more fields then click a button to perform the search.

                        The resulting script would have numerous else if statements for all the possible combinations of the 4 search fields ( I handle the dates a bit differently).

                         

                        So instead I did this...

                        In this circumstance all the fields will always have a value so it worked. But if can see where the Asterisk alone returning all records null or not would be useful...when not all of the fields have values.

                        • 9. Re: Find * does not work as described
                          greatgrey

                          It returns any non null set. Which is one or more characters but, when combined with other data makes it zero or more characters.

                          • 10. Re: Find * does not work as described
                            DavidJondreau

                            Yeah, it's confusing.

                            • 11. Re: Find * does not work as described
                              jormond

                              Because it's a wildcard, it can still represent zero characters. What happens when you enter find mode enter a field, type nothing, and hit return? There is no search operator.

                               

                              = says search for exactly this ( meaning if nothing, then search for empty )

                              * says search for existing data with zero or more characters in addition to any other text.

                               

                              *a* finds:

                              • ball
                              • all
                              • bat
                              • baltimore
                              • atlanta
                              • a
                              • etc

                               

                              Telling FileMaker to search for "*" is the same as doing a countless number of searches. But since "" ( empty ) isn't a valid search, it doesn't perform that one.

                               

                              greatgrey wrote:

                               

                              It is returns any non null set. Which is one or more characters but, when combined with other data makes it zero or more characters.

                              • 12. Re: Find * does not work as described
                                keywords

                                You are wrong Joshua; "*a" will not find "etc"—just kidding!

                                 

                                Seriously though, I have always found the "zero or more" descriptor a bit confusing, so thanks to greatgrey for putting it thus: "when combined with other data makes it zero or more characters." That's the key.

                                • 13. Re: Find * does not work as described
                                  user19752

                                  I agree there are confusing in finding of FM...

                                   

                                  For number, help says "#3 finds 53 and 43 but not 3" correctly, but "#3 finds also 03". It seems using # makes the finding unindexed, and the searched value is not text representation of the number value, value of "as entered" aka GetAsText(numberField).

                                   

                                  And, this is not written in help, "A1## finds 100 and A100 and B100 etc. but A##0 finds A100 but not 100 nor B100"

                                  I can't explain this behavior, only can say "don't use such values in number field".

                                  • 14. Re: Find * does not work as described
                                    keywords

                                    I don't find those examples confusing at all. The # is a wildcard which represents "any character", but not "no character"—hence it will find 03, but it won't find 3; that makes sense to me, even if mathematically the two numbers have the same value.

                                    PS. you are wrong: A1## will NOT find B100

                                    1 2 Previous Next