Sequelink: multiple fields value in a query

[AnFrusch@pepp]

Hello all,

I'm using SQLserver 2008 with Visual studio and SSID.

I'm trying to retreive data from a Filemaker table via ODBC (sequelink) and put them into a MySQL table.

In mysql (and in all standard SQL compliant RDBMS) multiple fields doesn't exists, so I have 20 fileds which should receive 20 values from a multiple field with 20 repetitions.

I don't know (and I can't figure) how to select every single repetition from filemaker and update MySQL. I'm afraid that this is not possible.

Do you know which syntax to use to query Filemaker properly (if any)?

[mwarren2]

There is a hack for getting multiple fields out of Filemaker via sql. This consists in creating a calculated field which makes a comma-separated String from all the values in the multiple field. Then you get the new comma-separated field in sql.

This is how I created a comma-separated field of the 'ValutePerSitoAgenzie' multiple field for example (you might not need the isempty check)

GetRepetition ( ValutePerSitoAgenzie ; 1 ) & If ( not IsEmpty ( GetRepetition ( ValutePerSitoAgenzie ; 2 ) ) ; "," ; "" ) & GetRepetition ( ValutePerSitoAgenzie ; 2 )& If ( not IsEmpty ( GetRepetition ( ValutePerSitoAgenzie ; 3 ) ) ; "," ; "" ) & GetRepetition ( ValutePerSitoAgenzie ; 3 )& If ( not IsEmpty ( GetRepetition ( ValutePerSitoAgenzie ; 4 ) ) ; "," ; "" ) & GetRepetition ( ValutePerSitoAgenzie ; 4 )

[Wim Decorte]

Yowsie... I wouldn't like burdening my schema with a calc like that.

How about reversing the whole thing: make a new file that has a TO to the main FM file and a TO to the mySQL data table (using the FMS ESS feature). Now you can write your own parsing logic in a script and push data into mySQL without importing.

(will be slower than importing but you'll have more control). You can schedule this as an FMS server-side script schedule