How do I repair a corrupt index on a field in FMP12?
Last weekend I converted a (new) client from FMS11/FMP11 to FMS12/FMP12.
This was only my 3rd conversion and you will be pleased to know it converted successfully and smoothly.
Monday morning, a user encountered a glitch with a drop-down value list.
The value list summarises the data in a calculation (text) field that concatenates 3 text fields (Company - Suburb - ContactPerson).
The value list should list about 7,000 records but only lists about 100 records.
In addition, the "Company - Suburb" beginning of each value in the list is missing, only the - ContactPerson is displayed.
Troubleshooting so far:
Attempt #1: deactivate/reactive indexing [FAIL]
In Manage Database for the Company, Suburb and ContactPerson field options, under Storage, I set indexing to None and deselected the "Automatically create indexes as needed" checkbox, then OK'd the changes.
The indexes still exist because a Find and Insert From Index... were both still possible.
Next I revisited Manage Database, left indexing set to None and selected the "Automatically create indexes as needed" checkbox.
Attempt #2: changed indexing Default Language [FAIL]
In Manage Database for the Company, Suburb and ContactPerson field options, under Storage, I set indexing to Minimal and selected the "Automatically create indexes as needed" checkbox and switched the Default Language from English to Unicode. The indexes still exist and appear to be unchanged because a Find and Insert From Index... were both still possible.
Attempt #3: change field type [FAIL]
Change the CompanySuburbContactPerson calculation field to text field with an auto-enter calculation.
Performed a Replace... across all records to re-enter the Company - Suburb - ContactPerson concatenation calculation.
Attempt #4 - #10: eclectic illogical permutations of #1, #2 and #3 above [FAIL FAIL FAIL FAIL FAIL FAIL FAIL]
Frustration level, vulgarity and frequency of expletives rising.
Tried all sorts of combinations of Indexing (None, Minimal, All) and "Automatically create indexes as needed" checkbox and changing the CompanySuburbContactPerson calculation field to auto-enter text field and back again.
Attempt #11: export/import data [UNKNOWN]
I am yet to complete this step but I was going to create a new UniqueID field and populate it with the Get(UUID) function, export the fields and re-import as a "matching" import.
Unfortunately I encountered duplicate UUID values in the data which threw me off guard.
Bye for now.