9 Replies Latest reply on Jun 15, 2015 7:48 AM by crow

    Searching empty or populated fields not working.

    crow

      Title

      Searching empty or populated fields not working.

      Post

      I have a field that either has numbers in it or it does not. I know this as I have looked through the records. If I search for "=" I get all the records empty and populated. If I search for * I get no records. = should give me just the records that are empty and * should give me the records that are populated. But it's not doing that. What am I doing wrong? The control style is edit box and input method is automatic, this is FMP 13.

      I should add It looks like the field itself is a calculation, specifically "count". My search is going into a script ultimately, and when I look through the records I either see nothing or numbers 1 and above.

        • 1. Re: Searching empty or populated fields not working.
          raybaudi

          "If I search for "=" I get all the records empty and populated."

          I suspect that none of them are empty... may be there are hidden chars.
          Try to create a calculation field with calc:

          Code ( YourField )

          All empty fields will have that field empty

          • 2. Re: Searching empty or populated fields not working.
            raybaudi

            On second thought,after re-readingyourmessageI have to acknowledgethatinany caseis not anormal behavior.
            You couldtry to forcethere-indexingof thefield.
            If it is acalculated field,whichis the calculation?

            • 3. Re: Searching empty or populated fields not working.
              crow

              The calculation is:

              Count ( table::field )

              • 4. Re: Searching empty or populated fields not working.
                philmodjunk

                And what is the result type specified for the calculation?

                Also, on your layout, is this calculation a field from the same table occurrence as your layout or from a related table occurrence?

                Is this a manual search or via a script?

                Are there any script triggers specified to run scripts in Layout Setup?

                • 5. Re: Searching empty or populated fields not working.
                  crow

                  And what is the result type specified for the calculation?

                  The field formate is General and the field is either empty or has a number 1 or higher. The calculation result is Number.

                  Also, on your layout, is this calculation a field from the same table occurrence as your layout or from a related table occurrence?

                  Same table as layout

                  Is this a manual search or via a script?

                  So far just manual, once I get it working then I'll make it via a script.

                  Are there any script triggers specified to run scripts in Layout Setup?

                  I see script triggers listed for this layout but none of them are checked.

                  Thanks for helping with this. I'm beginning to wonder when the field is empty it's really got a zero under the hood and I need to search for zero to find the empty records?

                  • 6. Re: Searching empty or populated fields not working.
                    philmodjunk

                    Count ( relatedTable::FIeld )

                    should return a null (empty) result if there are no related records in RelatedTable for a given record in the parent table.

                    It will return a 0 result if there are related records, but Field is empty in all of them. But in my test in FileMaker 13, the field initially appeared empty until I changed modes or did something similar to force the layout to update. (Commit records didn't update the value to zero)

                    So there might, for a brief moment after editing a related field to clear it, where count might not show a 0 but = then will not find it.

                    • 7. Re: Searching empty or populated fields not working.
                      crow

                      Not sure I'm following you...If I search (View-> Find Mode -> click in field) with "=" I get all records, empty and populated. If I search for "*" or even "#" or "@" I get no records found. Even if I search for the number "1" which I know records have when browsing I get no records found. Also to be clear the layout and table are together, the field is project:: filecount the layout is on project. It's not a table outside this layout. So is this calculation only done when I'm viewing a record and since I'm in find mode all fields are empty? If so why wouldn't it just populate with calculation outcome. Thanks for your help! I'm hoping I can get this working correctly soon.

                      • 8. Re: Searching empty or populated fields not working.
                        philmodjunk

                        What you describe does not sound possible. It sounds as though you are not putting the search operator into the correct field.

                        Here's a download link to a demo file that I set up. I can use = on the calculation fields using count and get the expected resuls:

                        https://dl.dropboxusercontent.com/u/78737945/CountTest.fmp12

                        Perhaps by comparing it to yours, you'll spot what's different that explains the results that you are getting.

                        • 9. Re: Searching empty or populated fields not working.
                          crow

                          Thanks for putting this together. What you provided works as expected with = and *. I agree what I'm seeing doe not sound possible but it does it consistently. I saw a few differences in the data inspector and made those on yours but it still worked as expected.  I'll spend more time on this later today to see if I can find any differences that may cause this. Unfortunately the person who originally did this is no longer with the company, I've worked with FMP for 10 years off and on and I'm baffled.