FileMaker 15 - ODBC queries to SQL server Error

Discussion created by NickFarnan on Mar 27, 2017

I have been working on an integration the past couple weeks with FileMaker 15 and a SQL server system.


It seems as though FileMaker when it queries an ODBC set up connection, it forcefully adds a comma (,) to values larger than 999. But since the field it is querying is set as a strict int field, it comes back with an error because there shouldn't be a comma in there.


So my set up was:


-Add a ODBC external source.

-Add tables from the SQL server to relationship graph (we are just using shadow tables, nothing fancy). Adding a table with this issue works fine and pretends to be working.

-Go to layout based on the table you added and once you navigate to a record that has an int field that is over 999, then you get an error showing in each field saying an invalid character ',' in a field. But it will still let you look at the records that don't go above 999 which shocked me.


Just to be clear, the shadow field is set as a strict number field in FileMaker and on the SQL server side there are NO commas in the fields I'm mentioning. We did a SQL trace when I try loading one of those records and it shows right in the query FileMaker made to SQL server that it put a comma in any number field over 999.


I will attach a screenshot of the layout when it comes across this issue.



Thought I would run this by, we got a way around it by getting the SQL people to pretty much have to change any of the int fields that will go above 999 into varchars, luckily the fields didn't really NEED to be ints but I can see this being a serious issue for other people.