Search code examples
indexingfilemaker

Drop all Indexes on a FileMaker Pro Table

We have inherited a table with over 500 fields in it and almost all of them are indexed. We are trying to access this table via ODBC but cannot due to the huge number of indexes.

We are looking for help on how to iterate all the fields, see if it is indexed and drop the index if it is.

We have stumbled around for hours and are making no progress, so hoping someone can help!

We are open to plugins or other solutions that can help, or just some suggestions for how to do it via FileMaker Pro scripts.

Thank you in advance!

Ben

PS We are on FileMaker Pro 17 for Windows Here is what we have tried: We tried to use Execute SQL with the DROP INDEX function, but it seemed to be needed for each field and each field requires a new Exectute SQL statement, which means over 500 statements. This is also a problem, because not all fields have an index, so ones that don't thrown an error, which seems to stop the script running.

UPDATE: Thanks to @AndreasT, I was able to get this working! I have bolded the items that were the key.

STEPS

  1. Create a view with the fields I wanted
  2. Write a script (see below) to get a variable of the field
  3. Loop over the set of fields, dropping the field using Calculated SQL

Easy peezy! Thanks again, @AndreasT! You are a golden god!

NOTE: __BENTEST is a Layout with all the fields from the table I need to work on.

Set Error Capture [ On ] Set Variable [ $fieldlist; Value:FieldNames ( Get(FileName); “__BENTEST”) ] Set Variable [ $fieldcount; Value:ValueCount($fieldlist) ] Loop Set Variable [ $counter; Value:$counter + 1 ] Set Variable [ $fieldname; Value:GetValue($fieldlist;$counter) ] Execute SQL [ ODBC Data Source: “FM_Constituents”; Calculated SQL Text: “DROP INDEX on Constituents.” & $fieldname ] [ No dialog ] Exit Loop If [ $counter = $fieldcount ] End Loop

Solution

  • There is no functionality in the script engine or calculation engine to affect the database schema. You can however create or drop the index from a field when using the database as an ODBC source. You could get a list of column (field) names and iterate over these and performing the DROP INDEX statement on each field.

    More on page 22 of this document: FileMaker 16 SQL Reference

    EDIT: You can also try using a plugin and running it from within a calculation in a looping script. This one seems to support the DROP Index statement. myFMBUTLER DoSQL plugin

    There may be other plugins that also will do this.

    EDIT 2: Something like this should do the trick, but the underlying issue of not being able to access the file over ODBC because of too many indexes seems strange and should be investigated. The file could be damaged and you could try to do a recover and test on the recovered version.

    Here is the principle of the looping script.

    Create a new layout and add all needed fields to it. Drag and drop from the Field picker is an easy way to do that. You can sort by field type and only drag the text and number fields if you want.

    In your script, Set error capture to On.
Set a varible $fieldlist to FieldNames ( Get(FileName );"Your new layout name").
Now you have your list of fields/columns.
Set a $fieldcount variable to count the fields ValueCount($fieldlist)
Set a $counter variable to 1
Start a loop
Get the first column name by setting a variable to GetValue($fieldlist;$counter)
Do the SQL to DROP INDEX on this column
Increment the $counter
Exit loop when $counter > $fieldcount
End loop

    What Sam says in the comments is true, so disable Quickfind on relevant layouts or you will probably have your indexes back for any indexable field on that layout. And he is right that there is no way to prevent the creation of new indexes programatically, it has to be done manually.