2 Replies Latest reply on Feb 13, 2013 7:06 AM by itraining

    How to fix corrupt Value List (field index) in FMP12?

    itraining

      G'day everybody

       

      Question:

      How do I repair a corrupt index on a field in FMP12?

       

      Background:

      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.

       

       

       

      Michael Richards

      Brisbane (Australia)

        • 1. Re: How to fix corrupt Value List (field index) in FMP12?
          timwhisenant

          Hi Michael,

           

          Have you tried this variation?

           

          File>manage>field definitions>storage> Set indexing to “none” & uncheck “automatically create indexes as needed”

           

          Close file

           

          Reopen file

           

          Return to field definitions and set indexing to  “all”

           

          Go to a layout where the field resides and perform a search with criteria in that field and FM will rebuild the indexes

           

           

           

          This is the prescribed way to re-build indexes. To view the index you can do this. In browse mode go to a layout where the field resides, enter the field, right click, insert, from index and you will get a scrollable list of the index values.

           

           

           

          HTH,

           

          Tim

          • 2. Re: How to fix corrupt Value List (field index) in FMP12?
            itraining

            Hi Tim

             

            Thanks for the suggestion.

            I followed the steps but alas the value list still only showed approximately the first 100 entries and they were malformed (missing the Company and/or Suburb part).

             

            However, scrolling to the bottom of the value list I noticed a tiny "ellipsis" (screenshot inserted below).

            Clicking the "ellipsis" expanded the value list and showed the remaining 6,900 values.

             

            In hindsight, it looks like the original issue was not indexing but a truncated value list and the malformed entries are poor data entry in early records.

             

            @Tim (or anybody else who wants to chime in)

            Is the "ellipsis" to expand a truncated value list a new feature in FMPv12 or is it an existing feature I have missed in previous versions?

             

            Thanks in advance.

             

             

            Michael Richards

            Brisbane (Australia)

             

            Value list ellipsis (show remaining values).png

            PS to insert the image, I was able to select the image but the "Insert Image" button would not activate in Safari on Mac OS X 10.8. The .png file extension was set to "hide" in The Finder. Making the .png externsion visible in Get Info activated the "insert Image" button.