Match fields. A word to the Not so Wise (like me)

[Rick Whitelaw]

I got bit yesterday and today by a basic issue. I was using the Data Viewer to write and test ExecuteSQL. The context was an old part of a solution I had started in 2006. I was having trouble with JOIN. I checked syntax and could find no errors but constantly had "?" returned. I knew the problem was related to the JOIN because the queries worked fine without it. Well, I'm embarrassed to admit the problem was this. The parent table had an auto-enter serial PK type number. All the child tables had an FK field but the type, for some reason, was Text. FM allows this as a match! Of course SQL does not. After changing all the child table FK fields to type Number everything was fine. Multiple joins we're a breeze. Why is FM so loose about field types in a relationship? This situation ought to produce an error in my opinion, or simply not work.

[LaRetta]

Rick said, "This situation ought to produce an error in my opinion, or simply not work. "

Hi Rick, I hear what you are saying. However, there are situations where one would naturally mix data type such as a multiline of child ID numbers ( thus text ) on the left side to child ID ( as number ) on the right and in fact mixing of data type can be handy in non-equijoins as well.

FM is in no position to interpret our intentions; we may have several relationships off a parent ( some mixed data-type and some not ) and it simply must be allowed. Neither would I want FM to warn me every time I attempted to change a data type - if I change a parent and there are 10 relationships off that parent, would you want 10 warnings to sound off?

And what about all the existing solutions already built which would break?

Your idea is good but if FM executed such a system then I think we all would throw a fit.

[Rick Whitelaw]

LaRetta,

After thinking about this I agree. I got caught by, many years ago, not having a reason to foresee using something like SQL which has stricter requirements. If a parent table had multi line ids (text) then the FK match field in the child would have to be text as well for the relationship to be used in ExecuteSQL, right?

[LaRetta]

But ExecuteSQL() does not use relationships. Can you provide an example file of a situation where this might apply?

I would be happy to help you work through some options. :-)

I am fairly new to ExecuteSQL() and I've not used JOIN yet and I am on iPad for next few hours so I cannot test. But I'm willing to work on it when I get back. It would seem that we could wrap with GetAsNumber() if it requires data match but of course your point was to warn of possible issue before it causes same problem for others.

I appreciate you speaking up.

[Rick Whitelaw]

No need for a file. If you want to set up a JOIN between a parent table (auto enter serial number type as PK) with a child table (FK saved as text) the JOIN will fail. Change the child table FK to type number and the JOIN succeeds.

 

Rick.

[Rick Whitelaw]

SQL Test.fmp12.zip

 

LaRetta,

 

Here's a little file that shows what I mean. Filemaker accepts the match field mixed type. SQL doesn't.

 

Rick.

[mr_vodka]

FileMaker's loose data typing can be helpful sometimes but when dealing with any external sources, odbc, etc it is a big PIA. Personally for me, It would be nice if FileMaker were more script on the data types.