6 Replies Latest reply on May 5, 2016 12:09 PM by briancrockett

    Find on unstored calc field producing strange results

    gordonkil

      Title

      Find on unstored calc field producing strange results

      Post

           I have an unstored calc field, see below, which checks if two fields are the same and shows 'x' if they are not. Since upgrading to Filemaker Advanced 12.0v4 from v3, performing a find on this field gives bizarre results, eg when I search for 'x' it returns results where the field is actually blank.

           The fields involved in the calc are both in the same table. Stock_Level is a calc field based on related records and is hence not indexable.

           The file is not damaged and verifies.

           If I take a copy of this file from backup and perform the find on that un-hosted file, the find returns the correct results, using both v4 and v3, which leads me to suspect there is an issue with FileMaker Server.

           I'm using Server Advanced 12.0.2.232 and FileMaker Pro Advanced 12.0v4. I'm aware there is an upgrade to Server available but cannot find any reference to this issue. Anyone have any idea?

           this is the calc field:-

            

           If ( 
           Stock_Level ≠ Stock_Level_index;
           "X";
           ""
            )

            

        • 1. Re: Find on unstored calc field producing strange results
          philmodjunk

               You can never be sure that a file is not damaged. You can only confirm that it passes a consistency check (verifies on backup is also a consistency check) and/or that recover reports no problems found. THe file could still be damaged and the issue could be one that those checks do not detect. The fact that your back up copy works strongly indicates that there is a problem with your file.

               An issue that they cannot detect and which could explain the behavior you are seeing is that you have a corrupted index or two, But you've also indicated that this is an unstored calc. From the syntax given, there's really no need to make this calculation unstored and finds on it will be faster if it is stored, but maybe one of these fields is from a related table and you left out that syntax? If one of the two fields is from a related table, there are several other issues that you could check.

               I suggest using recover to test yoru file in the following ways:

               Do a recover on a copy of your file and test the recovered copy to see if finds work as expected even though you report that recover does not report finding any issues. If the recovered copy works for you, try the following less drastic version:

               If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:

                 
          1.           With the file closed, select Recover from the File Menu.
          2.      
          3.           Select "Use advanced Options"
          4.      
          5.           Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
          6.      
          7.           The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.

                

               Test this recovered copy and see if it also works. While I do not generally recommend using a recovered file if there is a back up available. One were only the indexes were rebuilt with advanced recovery options is my one exception to that rule.

               If all else fails, save a clone of your back up copy that works and import all of your data from your current file into the backup, updating serial number settings as needed.

          • 2. Re: Find on unstored calc field producing strange results
            gordonkil

                  

                 Thanks for your response, please see below:-

            "You can never be sure that a file is not damaged. You can only confirm that it passes a consistency check (verifies on backup is also a consistency check) and/or that recover reports no problems found. THe file could still be damaged and the issue could be one that those checks do not detect."

                 Yes that is true, I should have said that it verifies ok, not that it wasn't damaged.

            "The fact that your back up copy works strongly indicates that there is a problem with your file."

                 The backup copy only works when not hosted on the server.

            "An issue that they cannot detect and which could explain the behavior you are seeing is that you have a corrupted index or two, But you've also indicated that this is an unstored calc. From the syntax given, there's really no need to make this calculation unstored and finds on it will be faster if it is stored, but maybe one of these fields is from a related table and you left out that syntax? If one of the two fields is from a related table, there are several other issues that you could check."

                 I did state:-

                 'Stock_Level is a calc field based on related records and is hence not indexable.'

                 so the calc can't be indexed. I'm aware that searching is much faster on an indexed field. What are the the other issues you mention?

            "I suggest using recover to test yoru file in the following ways:......"

                 Thanks for that, I'll give it a go and report back. It does sound like a corrupted index.

            • 3. Re: Find on unstored calc field producing strange results
              gordonkil

                   Update - Both a hard recover and just rebuilding theindexes completed without error. Both copies of the file work ok when not hosted on the server. Both copies fail in the same way when hosted on Server Advanced 12.0.2.232. So to my mind two possibilities remain:-

                   1. The file is damaged in a way that Recover does not recognise.

                   2. There is a bug in FileMaker Server or the server installation is somehow corrupted.

                   Next step I'll try is to upgrade system software to latest version.

              • 4. Re: Find on unstored calc field producing strange results
                philmodjunk

                     You never explained why the calculation is unstored. Is this a a calculation field that references a field in a related table?

                     There are issues to check on a relationship if this is the case--but nothing I can think of is an exact match to this situation. If the relationship, for example, is based on an unstored calculation field, a find that specifies criteria in the related table's fields can trigger an error message even though the relationship works, but you are not getting an error message. Likewise, some users get confused over results when they reference fields in a related table in a find where the related record is from the "many" side of a relationship, but this also does not seem to be the case here.

                     If there is a relationship referenced in this calculation, is a match field used in this relationship a global field? There is a known bug that pertains to finds and is server specific that then might apply. I initially did not consider that a possibility as the syntax of your original calculation example did not indicate that you were referencing a related table.

                • 5. Re: Find on unstored calc field producing strange results
                  gordonkil

                        

                  "You never explained why the calculation is unstored. Is this a a calculation field that references a field in a related table?"

                       Thanks for your response. I have in fact stated (twice) that:-

                       'Stock_Level is a calc field based on related records and is hence not indexable.'

                       Stock_level being one of the fields in the calc in question. For further clarification:-

                        

                       Stock_Level_Check (the field in question) = 

                       If ( 

                       Stock_Level ≠ Stock_Level_index;

                       "X";

                       ""

                        )

                        

                       Stock_Level = 

                       Stock_Total_at_Locations - Stock_Total_Confirmed_UnProcessed + 0

                        

                       Stock_Total_at_Locations = 

                       Sum ( vSL_vSI::Stock_Level )

                        

                       Stock_Total_Confirmed_UnProcessed = 

                       Sum (vOI_vSI::Confirmed_UnProcessed_Despatch_Quantity)

                        

                       Stock_Level_index is an indexed, non-global number field

                       All relationships involved use only indexed, non-global key fields. Some of the fields in related tables are unstored.

                  "There are issues to check on a relationship if this is the case--but nothing I can think of is an exact match to this situation. If the relationship, for example, is based on an unstored calculation field, a find that specifies criteria in the related table's fields can trigger an error message even though the relationship works, but you are not getting an error message. Likewise, some users get confused over results when they reference fields in a related table in a find where the related record is from the "many" side of a relationship, but this also does not seem to be the case here."

                       As you say, I don't think this applies in this case.

                  "If there is a relationship referenced in this calculation, is a match field used in this relationship a global field? There is a known bug that pertains to finds and is server specific that then might apply. I initially did not consider that a possibility as the syntax of your original calculation example did not indicate that you were referencing a related table."

                       see above

                        

                        

                  • 6. Re: Find on unstored calc field producing strange results
                    briancrockett

                    Did you ever get a resolution to this issue?

                     

                    I created an unstored calculation that references related fields in a file on my local drive. Last night I copied that field to the online version of the file on the FM Server. It appears to display the correct data but when I try searching or sorting by this field it fails. Searching comes back as "No Records Match This Find Criteria" and sorting just doesn't work. The records do not sort properly and there is no error message.

                     

                    Found my own solution. I had the calculation set as number instead of text. No wonder I was getting odd behaviour.

                     

                    Never Mind!