AnsweredAssumed Answered

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

Question asked by bostonbuckeye on Aug 6, 2013
Latest reply on Aug 7, 2013 by philmodjunk

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

Outcomes