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.
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.