ALTER TABLE mytable CHANGE oldFieldName newFieldName

[Rewolfer]

Hello,

I'm experimenting with the Sql-Runner-Plugin to manage the structure of FileMaker databases via SQL. With an data separation model I'd like to change field names from the layout file via script. I successfully created/ dropped fields and set new default values etc. But what I need most is to change field names (column names) via SQL.

I tried

ALTER TABLE "tableName" MODIFY "oldFieldName" "newFieldName" varchar ALTER TABLE "tableName" CHANGE "oldFieldName" "newFieldName" varchar ALTER TABLE "tableName" RENAME "oldFieldName" TO "newFieldName"

All I get is an "There is an error in the syntax"-error. The ODBC-manual only lists create, drop, drop index on, alter default...

Does anyone know if this possible with FileMaker at all?

Working on Windows7 with FileMaker Server 11.03.

Thanks for any reply,

Rewolfer.

[Wim Decorte]

I've had no success either in my trials so I don't think it is supported. I also did not have success with ADD and DROP tables.

[Rewolfer]

That's sad to hear. It would have made my life a lot easier since I have to rename a myriad of fields of several old solutions that need to be re-imported into the new separation model solution (critical data). Does anyone know of a scripted way to rename fields in FM. Plugins? Script hosted?

Thanks for the reply,

Rewolfer.

[Rewolfer]

I contacted the makers of SQL Runner and they wouldn't know of a way to rename fields with SQL.

Well: one more thing on my internal wish list coming true in... let's guess: 2018 ;-)

Greetings to all FileMaker enthusiasts,

Rewolfer.

[DanShockley]

You can rename a bunch of fields using some kind of UI Scripting, either with AppleScript or with QuicKeys (or some other interface-scripting macro utility for Mac or Windows). Certainly not the most elegant way to do it, but it works.

The typical flow I've used to do this is to open the Manage Database window, go to the field list for the table, then click on the row of the first field. Then, the macro does this:

1. Hit Tab to change interface focus to the field name text box.

2. Keyboard command-C to copy the current name.

3. Perform some operation on that name (modify it according to a formula to prepend/append some text, choose the new name from some pre-defined matching list).

4. Type/paste in the new name.

5. Click the Change button.

6. Hit Shift-Tab to move focus back to the field list.

7. Hit Down-Arrow to go to the next field.

Then repeat.

A very important note: If you are renaming fields, you want the "View by:" sort order to be "creation order" so that the field doesn't move after the macro hits Change, throwing off where you are in the list.

Also note that your macro could skip steps 4 and 5 if the field meets a test in step 3 indicating that it shouldn't be renamed.

Like I said, not the most elegant way to do it, but functional. I certainly wouldn't use this in a completely automatic way. In other words, I think I'd stick around and watch the macro do the work, and start the process manually. I've even had the macro just run steps 1-7, with a keyboard combo (usually a function key) that I have to press for each field. Simple way to avoid some issue causing it to do something unexpected.

Another UI Scripting caveat: Your computer can throw up other dialogs from outside FileMaker that throw this off. Another reason not to run something like this without watching. But, again, if you have more than 20 or so fields to rename, this can save a LOT of time.