Survey with multiple table imports questions
I'm fairly new to FMP and am attempting to build a database for my company that revolves around online questionnaire responses. The data will come in daily from our online questionnaire in the form of an excel data file (.csv) - we'll need to import that into our FMP database.
At the moment I'm looking into a 3 table approach: Surveys >-----Participants-----< Communication
The survey table needs to:
-Take imports of new survey data (we're hoping to use scripts to import only new data each day as the .csv file will hold all cumulative data up to that export, obviously including what's already been imported to the database - duplicates wont be acceptable)
-Create new records in the Participants table when imports occur (scripts again?) to coincide with new records created in the surveys table
The Participants table needs to:
-Contain a UIN for each participant (thinking this seems super easy as a serial number)
-Link to the Communications and Surveys table to show recent communications and survey answers
-Be able to show multiple surveys for a single participant (we know from a previous survey that some people will fill out more than one...) through a portal or related fields
-Not have duplicate participants caused by data imports in which people have filled out the survey multiple times.
The Communication table needs to:
-link up to the participant UIN
-keep a record of outgoing and incoming communications
-have a static notes field
The data imports (.csv) will come in the form of about 20 columns of questions and each row will be a different participant. While we'll have names and dates of birth as part of the survey data we'll want to use email as a primary way to link the survey table to the participants table seeing as there may be more than one John Doe, but very unlikely more than one John Doe with the email JohnDoe@yahoo.com.
We'll need a script that creates a new record in the participant table when a survey comes in with a new (unique) email address. So i suppose this script would have to loop through all the entries in the survey table and check the emails (and Date of birth?) against current entries in the participant table, and then add the ones it finds to be unique as new entries to the participant table... that seems incredibly complicated, I'm hoping someone has a much cleaner way to do this!
TLDR: Multiple surveys may link to single participant, multiple communication entries for single participant. Need quick way to create new entries in participant table when importing survey results as by the end we'll have 10,000+ surveys we're thinking. Need help creating relationships.
Any help is appreciated.