I have a parent FM record with a multikey text field of child IDs. I would like to use gtrr from this parent to the children. The children are MySQL records in an ESS table and the primary key is a number field. The gtrr fails.

On p.15 of the ESS FM11 Tech Brief, it states that multikeys can be used to match to ESS child records.

Why is this failing?

What is the error code?

Right after the gtrr is attempted, I receive FM Error 1408, and a dialog that says:

ODBC Error:[MySQL][ODBC 5.1 Driver][mysqld-5.1.41-3ubuntu12.10]You have

an error in your SQL syntax; check the manual that corresponds to your MySQL

server version for the right syntax to use near " at line 1

Thanks for the help, Ideal,

Barbara

Mmmmmm... gobble-de-gook to me. Can you test each key combination independently and then in combinations?

Do you think it may be one of the IDs is not valid? It may be the case, but highly unlikely.

So, are you saying that it is definitely possible to gtrr FM to ESS via a multikey? That would be reassuring to know. I'll try a three ID multikey test with know "good" values.

I'm no expert here, and have only done a single field match before now. According to p15 it should work, but scrutinise your data - particularly trailing returns in the child.

Maybe a workaround to use a looping script to perform successive finds and EXTEND the found set?

Are you using a multi-VALUE or multi-PREDICATE match?

Not a multi-predicate, just a multi-key. We're talking about 2500 IDs. I've thought of throwing each ID into a global and gtrr, working my way thru the entire list, but that's plan B.

Are you're mixing data types? The SQL key field is number, but of course you are building a list of TEXT (numbers) to match.

Simplyfy the test and see if you can make 2 separate NUMBER fields in FM match the single SQL key field. Alternatively target a different TEXT field in the SQL table.

Is there a failure on the first line of the key? That at one time was a source of disruption.

Steven

Hi Steven,

Thanks for chiming in. Well, the error says, "to your MySQL server version for the right syntax to use near " at line 1" You're hinting at something here. Can you elaborate?

Barbara

Steven?

Well, apparently the cause of the failure is the limit of 4K on a sql statement. I'll need to rewrite this processing the list one at a time, I suppose.