Look up from a related SQL database not working but reverse lookup works (FM12 & server)
I have two tables in my database, one is a table on an SQL server. (appears in italics in the Manage Database tables tab) Both tables have a primary key but neither has forign keys for every recoerd. (the data comes from different companies)
Both tables should be related by a part number (alphnumeric) but unfortunately because some parts have -/, and spaces in them the direct relationship part number to part number often fails.
In filmaker I have added a calculation field to both tables using
Filter (Upper (Part Number ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890" )
if I use the relationship to lookup a field in the SQL database it fails (nothing returned) however if I do the lookup in the oposite direction it works. ie I start with the filtered field in the SQL database Table (unstored Calculation) and look up a field in the filemaker table it works everytime.
If I use find to search for the filtered value in either table it is found and appears identical
There is a second relationship between the two tables but this does not involve the calculated field on the SQL table. Some of the records in the SQL database have the forign key manualy enterd into a field if I define a relationship between my tables primary key and this field that may contain the forign key it works and I can get the primay key for the SQL database.
So I know that the look up should function but it appears that I cannot use a calculated value on the SQL data table as the forign key.
Am I doing something wrong or is this a limitation within filemaker