1 Reply Latest reply on Aug 7, 2013 9:13 AM by philmodjunk

    indexed calculation field based on a related field permanently corrupts the database.

    bostonbuckeye

      Summary

      indexed calculation field based on a related field permanently corrupts the database.

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      windows 7

      Description of the issue

      I have a  "Mens Club YearPaidThru" field with several calculation fields based on the existence and value of “Mens Club YearPaidThru". One of those calculation fields is “Mens Club Member Name" that was indexed. That way I could do a search and find of only those people who are Mens Club members. Another Mens Club calculation fields determined how much was owed based on the value of the Mens Club YearPaidThru" field. I did the Mens Club search via a script with "Insert from Index" command and “Mens Club Member Name" as the indexed search parameter. Everything worked normally.

      However, I needed the value of "Mens Club YearPaidThru”  to come from an external spreadsheet and not be set within the database. So I created a related table with 3 fields - LastName, Firstname (both keys) and "YearPaidThru" with the value provided externally. I changed the original "Mens Club YearPaidThru"  field to be a calculation where the value came from the “YearPaidThru” in the related table.

      Actual result

      *BAD* What happened was the “Mens Club Member Name" could no longer be indexed and the "Insert from Index" in all my scripts failed (because “Mens Club Member Name" was not indexed.

      ** BUT ** what I didn’t expect was that all my other indexed fields with nothing to do with the “Mens Club Member Name" or "Mens Club YearPaidThru" field (like “Child Name Index”) could no longer be indexed (I got the error message on indexing errors due to related fields).

      That is what confused me. I expected “Mens Club Member Name" to not be indexed but not my other indexed calculation fields like “Child Name Index”. It appears that when you have an indexed calculation field that is indirectly based on a related field – then ALL indexed fields are rendered unindex-able. I tried to delete the related table and restore "Mens Club YearPaidThru" back to text field it originally was – but that didn’t work and my other – nonrelated  - indexed fields could also no longer be indexed.

      So it appears that if you have an indexed calculation field based on a related field – it permanently corrupts the database by corrupting other fields that were indexed.

      Exact text of any error message(s) that appear

      "Calculation Child Name Index could not be stored or indexed because it references a related field, summary field or an unstored calculation field or a field with global storage"

      Workaround

      none

        • 1. Re: indexed calculation field based on a related field permanently corrupts the database.
          philmodjunk

               What you are reporting should not be the case. (We'd have been deluged with issue reports about it many many months ago if it was and I would be one of those posting such a report) wink

               Either there is some detail not included in your report of the issue that is the cause of this trouble or your file was already damaged in some way prior to making this change.

               I recommend that your recover the file and then try making this change in the recovered copy--even if the recover process reports no problems found.

               Things to keep in mind about Recover:

               While Recover almost always detects and fully corrects any problems with your file...

                 
          1.           The recovered copy may behave differently even if recover reports "no problems found".
          2.      
          3.           Recover does not detect all problems
          4.      
          5.           Recover doesn't always fix all problems correctly
          6.      
          7.           Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.

                

               And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).