Integration

Okay, a step in the right direction. Got a view and can see the view in FM.

Two issues:

1:

SELECT
DATE_FORMAT(FROM_UNIXTIME(submit_time), ‘%b %e, %Y %l:%i %p’) AS Submitted,
MAX(IF(field_name=’first_name’, field_value, NULL )) AS ‘first_name’,
MAX(IF(field_name=’last_name’, field_value, NULL )) AS ‘last_name’,
MAX(IF(field_name=’email’, field_value, NULL )) AS ’email’,
MAX(IF(field_name=’cell’, field_value, NULL )) AS ‘cell’
MAX(IF(field_name=’website’, field_value, NULL )) AS ‘website’
MAX(IF(field_name=’service_provided’, field_value, NULL )) AS ‘service_provided’
MAX(IF(field_name=’address1′, field_value, NULL )) AS ‘address1’
MAX(IF(field_name=’adress2′, field_value, NULL )) AS ‘address2’
MAX(IF(field_name=’city’, field_value, NULL )) AS ‘city’
MAX(IF(field_name=’state’, field_value, NULL )) AS ‘state’
MAX(IF(field_name=’zip’, field_value, NULL )) AS ‘zip’
FROM wp_cf7dbplugin_submits
WHERE
form_name = ‘Individual Membership Form’
GROUP BY submit_time
ORDER BY submit_time DESC

This is my query but it errs out on line 7: website and even if I remove that, errs out subsequently on the next ones. What am I doing wrong? I’m not a SQL developer. 🙂

2. FileMaker cannot connect to a view or table where there is no primary key. I used the submit_time for now, but I would prefer a primary key. Any ideas here?

Thanks,
Agi

OMG, that’s embarrassing.

I actually added an ID field that gets a random ID in FM, so that problem is solved.

I cannot connect the table because I don’t get all the data in one record. FileMaker works like Excel: one row is one record. Field names are “column” headers.

Okay, so now I have this:

SELECT
DATE_FORMAT(FROM_UNIXTIME(submit_time), ‘%b %e, %Y %l:%i %p’) AS Submitted,
MAX(IF(field_name=’first_name’, field_value, NULL )) AS ‘first_name’,
MAX(IF(field_name=’last_name’, field_value, NULL )) AS ‘last_name’,
MAX(IF(field_name=’email’, field_value, NULL )) AS ’email’,
MAX(IF(field_name=’cell’, field_value, NULL )) AS ‘cell’,
MAX(IF(field_name=’website’, field_value, NULL )) AS ‘website’,
MAX(IF(field_name=’service_provided’, field_value, NULL )) AS ‘service_provided’,
MAX(IF(field_name=’address1′, field_value, NULL )) AS ‘address1’,
MAX(IF(field_name=’adress2′, field_value, NULL )) AS ‘address2’,
MAX(IF(field_name=’city’, field_value, NULL )) AS ‘city’,
MAX(IF(field_name=’state’, field_value, NULL )) AS ‘state’,
MAX(IF(field_name=’zip’, field_value, NULL )) AS ‘zip’
FROM wp_cf7dbplugin_submits
WHERE
form_name = ‘Individual Membership Form’
OR
form_name = ‘Student Membership Form’
GROUP BY submit_time
ORDER BY submit_time DESC

AND IT WORKS!!!

Thank you. 🙂

I have 4 forms that I need to get the data into one table/view into.