FMP Equivalent for this MySQL Query

[Kelly O.]

Good afternoon!

I'm working on getting our FMP database talking to our MySQL database using ODBC. We've got the connection working just fine, but the data is in the wrong shape - MySQL has everything we need stored in a table with three columns (uid, fid, value). We can get that to populate our FMP table, but what we'd really like is to be able to have FMP parse that data into a more horizontal/useful shape, i.e. with the user IDs as rows, the field IDs as columns and the values filled in accordingly.

We talked to our web guy, and he put together a mySQL query that he thinks should do the job. Can anyone here take a look and tell me if there's a way to get FMP to do the same job?

Thanks so much!

Kelly O.

SELECT

u.name as 'Username',

GROUP_CONCAT(if(pf.name = 'profile_First', pv.value, NULL)) as 'First Name',

GROUP_CONCAT(if(pf.name = 'profile_Last', pv.value, NULL)) as 'Last Name',

GROUP_CONCAT(if(pf.name = 'profile_Sphere_of_interest', pv.value, NULL)) as 'Sphere of Interest',

GROUP_CONCAT(if(pf.name = 'profile_Ethnicity', pv.value, NULL)) as 'Ethnicity',

GROUP_CONCAT(if(pf.name = 'profile_PhD_Thesis_Title', pv.value, NULL)) as 'Thesis Title',

GROUP_CONCAT(if(pf.name = 'profile_PhD_Year', pv.value, NULL)) as 'PhD Year'

FROM users u INNER JOIN profile_values pv ON u.uid = pv.uid INNER JOIN profile_fields pf ON pv.fid = pf.fid GROUP BY u.name

[Matthew F]

I'm not fluent in SQL but it seems like you're fortunate that it has a GROUP function. Obviously you could do the same thing in Filemaker using a script to allocate data from your table into a new table with the appropriately named fields. Your script would loop over each record and use the 'Set Field By Name' with Specify Target Field = fid, and Calculated result = value.

One limitation is that your script will not be able create new fields for you if you encounter new field IDs in the future. As far as I know, scripts are still unable to create new fields and set field names. However, you can easily add new fields manually in Table view before running your script.