9 Replies Latest reply on Oct 20, 2016 5:21 AM by jeremyb

    Find says 'blank' calculation fields aren't blank.  Any advice or fixes?

    jeremyb

      For 5 seemingly random records (of over a million) I have a Calculation Field that appears blank in Table View and downloads to excel blank but is "found" when search on that field is for "*". Any clue why? The calculation is set to only return different text strings if conditions are met, and the field type is TEXT.  I've only started having this problem this week while database and the calculation field have been around for 1.5 years (though always tinkering with the calculation and the database).  Any help is welcome!

      Thanks, Jeremy

        • 1. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
          TomHays

          If it is a stored calculation, the index on the field may have become corrupt.

           

          Turn off indexing in the field definition.

          Go back to Browse Mode.

          Turn on indexing in the field definition.

          FileMaker will rebuild the index anew.

           

          If it isn't a stored calculation, then look more carefully at the field.  For example you can create another calculation field that is Length(TheFirstField).

          Then examine the value of the second field for the cases that you think are blank.

          Similarly you can examine Code(TheFirstField).

           

          -Tom

          • 2. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
            David Moyer

            Hi,

            the field could contain spaces - which would be invisible, but found with *.

            So, if your calculation field could produce at least a single space without any other data, it would be found.  To test, start by looking at your calculation to determine what should show in your field.

            If what should show is not as described above, then you might be experiencing an indexing problem.

            • 3. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
              jeremyb

              It is not an indexed/stored calculation field.  And I can't find anything that would return a space, nor is it downloading to excel with a space.  I am checking out the Length and Code functions applied to those records now.

              • 4. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
                jeremyb

                I created a Calculation field that is:

                Code( Field )&" is the code and the length is "& Length ( Field)

                and for those 5 records this calculation also returns blank/nothing, not even the text "is the code and the length is ".  For the other records it returns things like this, "6500080000320011000111000320010100100001050011700071001100011400117001160010100082000320011100078 is the code and the length is 20"

                • 5. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
                  coherentkris

                  maybe this thread will provide a clue

                  Unstored Calculations Find Fails

                   

                  if your calc is Code( Field )&" is the code and the length is "& Length ( Field) then reset the index for Field and trigger the calc by changing something in Field. Let us know the results.

                   

                  you may have to go down the road of running recover and checking out the logs

                  • 6. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
                    richardsrussell

                    I often chalk things like this up to the nefarious operation of cosmic rays. All kidding aside, I find it's usually a better use of my time to just find a fix than figure out what caused the problem in the first place. I think TomHays had the right idea when he advised unindexing and then reindexing the field. (That's kind of like the standard hardware advice to turn it off and then turn it back on again.) I'll pass along a similar suggestion on a larger scale. Close the file but leave the program open. Go to File > Recover and have FMP rebuild your problem file from scratch. (It's a pretty speedy operation, depending mainly on file size.) If the original file was called "XYZ.fmp12", the rebuilt one will be called "XYZ Recovered.fmp12", and you should store it in the same folder as the original XYZ file, so it's operating out of the same environment. See if you have the same problem in the recovered file. If it's gone away, trash the original and take the word "Recovered" out of the name of the rebuilt one.

                     

                    Good luck! Inexplicable errors are 2nd only to intermittent ones in terms of exasperation levels.

                    • 7. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
                      David Moyer

                      The fields that the calculation references may need to be re-indexed.

                      The easiest way to attempt to re-index a field is to go to that field definition > Storage tab ...

                      - turn indexing off by selecting None

                      - close out of field and table definitions

                      - go back to the field definition and turn indexing back on, as you had it set before

                      • 8. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
                        TomHays

                        Assuming that the database itself is not corrupt, you can continue searching for other benign causes or finding workarounds.  You might discover a new clue in the process.

                         

                        (1) Specify in the calculation options that it should evaluate even if the fields are empty, i.e. uncheck the box for "Do not evaluate if all reference fields are empty."  This shouldn't make a difference, but it might.

                         

                        (2) Search the field for empty using just == in the field.

                         

                        (3) Search the field for @* instead of just *.

                         

                        (4) Eliminate the possibility that the search is being contaminated by script triggers interfering with the search.

                        Create a new layout based on the table containing the fields.

                        Drag the odd field onto the layout.

                        Search using this plain layout.

                         

                        -Tom

                        • 9. Re: Find says 'blank' calculation fields aren't blank.  Any advice or fixes?
                          jeremyb

                          Thanks to all!  While I expect some of this will come in handy in the future, I downloaded the solution from the server and discovered it worked fine on my local machine.  I was later told that this was a bug in filemaker server 15.0.1 that was fixed.  Our IT team ran the update last night and now it works on the server too.  Thanks again for all the helpful advice! -Jeremy

                           

                          FileMaker Server 15.0.2 bug fixes

                           

                          Addressed an issue performing find requests for unstored calculation fields in hosted solutions

                          1 of 1 people found this helpful