what is the validation on the column? You can get some information, simply by using the Manage database. While you cannot change External source validation, you can view what FileMaker says it is.
Is the column: text or number or ???
Is it really the commas (do other values go in correctly)?
Beverly, you may be onto something.
Actually the SQL statement will run one on MYSQL if i run it direct.
So it seems to be the ESS driver that is the issue.
The field type was "text", I changed it to "archer" but have the same issue.
I'm using the actualtech ODBC driver for OS X.
I wonder if it would be ok on windows, I may have to try this..
beverly is going to be your better resource, but FileMaker does only supports SELECT. You should be able to set the field with a new value in FileMaker. Some fields may not allow changes as they are defined in your MySQL database.
can you post screen shots of the dialog (auto-enter, validation) for the column (as FM sees it)?
Don't forget to press the Sync Button for the table in the Manage Database Dialog after you update it in MYSQL.
Ok found the issue.
This particular ODBC MySQL driver won't save text with apostrophes!
So really its a driver issue, I have raised a case with them.
I can save the same text in Mysql query directly so it is 100% driver issue.
thanks for the pointers everyone and especially to Beverly.
bigtom, SELECT only with ExecuteSQL() function. The IMPORT script step supports (non-ESS) SELECT and the Execute SQL (script step, note the space and spelling) supports (non-ESS) INSERT, UPDATE, DELETE.
malachydevlin had the correct methods, the driver was driving up the the wrong road.