2 Replies Latest reply on Feb 17, 2012 9:26 AM by philmee95

    Linked table via ODBC (ext ds) field validation removal or read only layout?

    philmee95
    philmee95 Dec 13, 2011 11:19 AM

      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.

      • 173 Views
      • Tags:
        • 1. Re: Linked table via ODBC (ext ds) field validation removal or read only layout?
          philmee95
          philmee95 Dec 28, 2011 9:06 AM (in response to philmee95)

           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.

          • 2. Re: Linked table via ODBC (ext ds) field validation removal or read only layout?
            philmee95
            philmee95 Feb 17, 2012 9:26 AM (in response to philmee95)

             I forgot to update this post. Filemaker sees blank fileds as null, which is ok. However, the linked tables from an external datasource see otherwise and there lies the problem. The schema design for the external data is not mine. I am pulling info from a DB2 database hosted in another state from a 3rd party and caching it in SQL server prior to loading into a data warehouse.

            The DB2 schema specifies that no fields are nullable, and all the crummy data from their 2nd normal table is padded so the field is not null or nothing, but is a blank value of a bunch of spaces. My cached table in SQL server 2005 (designed using only an SSIS import to set the table up) also HAD all non nullable fields and I trimmed the padded data after caching it.

            So SQL server and DB2 see this empty string as not null. When linked to filemaker as an external datasource, the auto field creation sets required and non null on each of these fields. Changing them in the manage database table field in filemaker was a fix until the schema was synced and then all the changes were lost (250 fields wide of terrible db design).

            I tried views as well as the table link and both results were the same. The simple fix since the uber source tables design (in db2) is not under my control was to make my SQL cached table (a database table I pull the db2 info into so filemaker can play and I can further process the data) was to make all the SQL columns nullable and then re-sync the filemaker table fields.

            Hope this helps somebody with the field cannot be empty error with linked external datasource tables. Really cool feature that we played with in MS access and were glad to see it in FMP.