Title
Linked table via ODBC (ext ds) field validation removal or read only layout?
Post
We are using filemaker to link to an extwernal database (sql 2005 and mysql) using external datasource odbc. This is all working fine.
We have a few databases set up as front ends, with some filemaker tables as decoration tables to our linked tables. I have the relationship graphs all setup to a primary key. I have the SQL linked tables as read only for the odbc login, and the local filemaker table in the filemaker database should be the only modifiable data.
I make a layout for the linked sql table and add a portal for the local filemaker table with the relationship that allows creation of the filemaker related records (since the related record creation works now and copies the related key field to the related record). When I create the filemaker table record, the linked sql layout field validations fire and says some field requires a value (it is read only anyways) and do I want to fix it. Clicking no and reverting the linked table record also trashes the filemaker record creation. So I try to make 3 records in the filemaker table, but since the sql tables layout validation fails, all 3 linked records are ignored (transaction like roll back).
Do I have to turn off all the data validations and required field attributes on the linked table for this to work? Can I make a layout readonly and get rid of this issue or should I create a script to copy the related key to a variable, fo to a filemaker layout for the local filemkaer table that I want to make new records and edit them directly and then script step back to the sql linked table?
This issue went away for awhile, but has now come back. I think because we started doing the pretty layout switch with script variables (like what used to have to be done before the automatic related field copy worked correctly) for the decoration table record creation in filemaker. The decoration field is just a comments record for now with 1 field being the foreign key to the linked table.
This was more or less caused by sql allowing empty strings in text fields and not counting them as null. The fields are non nullable in sql. Fielmaker then validates the text fields when empty as null-ish and triggered the validation messages. Since this was a view I presented to the filemaker layout it was not updateable (as well as usning a readonly user account for the dsn setup).
The work around is to go through the field definition for the linked table and remove "required" field validations from such fields. Of course the other solution sould be to not have blank text or allow nulls, but I am working on a cached table from db2 which allows this situation (cached in sql server from DB2 and viewing linked in FMP) otherwise I would just allow the null records.