8 Replies Latest reply on Mar 30, 2015 12:40 PM by blewvelvet

    search for empty fields not working



      search for empty fields not working


      In a FM 10 table with 504 records, I imported data into two (previously empty) fields, updating 387 records.  When I search with * in either of the imported fields, I find the 387 records.  However, when I search with = to find the empty fields (the set of records which did NOT get filled by the import), the find is unsuccessful -- no records found. Why would this be?  How do I find these records?




        • 1. Re: search for empty fields not working
             Perhaps the field's indexing is corrupted. Trying turning indexing off on the two fields and click OK to close Manage | Database. Then re-open Manage | Database and turn indexing back on. (Double click the field definition and then click the storage tab.)
          • 2. Re: search for empty fields not working
               I followed your instructions, but still no luck.  Any other ideas?  Are there any text fields which an "empty" search would not work on?  I've never encountered this before.
            • 3. Re: search for empty fields not working

              What's the field type and is it consistant with the type of data you have imported?


              I'm wondering if you imported text into a number field.


              What do you see when you Show all records and then sort by one of these fields?

              • 4. Re: search for empty fields not working

                Both are text fields.  One field records yes/no (whether they completed a survey), and the other records the email address used for the survey.


                When I try the sorts on either field, it shows the records with entries in the fields first, then the records with empty fields.  So the records are there, but they aren't found via a = search.


                The yes/no which I imported into had a value list on it (radio button yes/no), but I also pasted a copy of the same field in the record without the value list, in order to do the * and = searches.  The * search works, but the = one does not.  



                • 5. Re: search for empty fields not working

                  If you are sorting in ascending order (the default) the records with empty fields should sort to be ahead of the records with fields that contain data. That suggests the fields do contain data--just data that you can't see on your layout. Perhaps there are invisible characters in the field like a carriage return. Format the fields as edit boxes and click in them. If there's a carriage return the field will pop out to show that it contains more than one line of text. Try double clicking the field and see if one or more "spaces" of your field highlight to show that you've selected them.


                  A final test you can do:

                  Create a calculation field that uses Length(yourField). If the field is empty, it'll return 0 or be blank. If the field contains data, you'll see a number counting the number of characters.


                  BTW: changing the value list settings for a field will not change what data is stored into the field. A check box or radio button value list can hide data stored in the field that is not match one of the value lists. THus removing the value list format (change it back to edit box) is a good idea and you'll need to do this to check for the presence of invisible data.



                  • 6. Re: search for empty fields not working

                    Hi Phil

                    I've been having this issue - my Constrain FoundSet - Omit records field - TOTAL AMOUNT DUE FIELD Criteria "="

                    I did the test calculation field to find characters and it found "1" character in supposedly all blank fields that might reference a value.

                    The TOTAL AMOUNT DUE FIELD is a total calcluation of other calculations.....as soon as I remove one of the preset values I have..(say a monthly common charge for a vendor).....the "1" disappears. 

                    So how do we OMIT what should be an no value (0) field...but apparently has invisible data attached to it?

                    I tried (instead of FIELD Criteria "=")....I put criteria "< 0"...but that did not work either.

                    How do we gain control of the real calculated value (hidden or shown) and omit those in a constrain found set?


                    --after thought - What if I used the character length calculation and omit records that have a character length equal to and less than 1?

                    • 7. Re: search for empty fields not working

                      That may have worked!


                      • 8. Re: search for empty fields not working

                        Even though I know I didn't wait for response...thinking it through and leaving those notes here is really helpful when you come back to it.

                        Re-reading something through...it just clicks sometimes.

                        "Character Length" boogey...good catch!!!