1 Reply Latest reply on Jun 2, 2015 11:02 AM by philmodjunk

    Survey with multiple table imports questions



      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. 




        • 1. Re: Survey with multiple table imports questions

          You may find that you need more tables into which to parse your imported data. The structure you have described makes for easy import into FileMaker but will make any kind of tabulation or analysis of the responses difficult. For a look out how a system of related tables might be used to manage surveys, see: Need aid on generating a report from a survey layout.

          As to your script, the right relationship might make the generation of new participation records fairly straightforward.

          You can link occurrences of Surveys and Participants like this:

          Surveys::eMailAddress = Participants::eMailAddress And
          Surveys::DOB = Participants::DOB

          You can enable "Allow creation of records via this relationship for Participants and then your script to generate new participants records could be this simple:

          #immediately after importing the new records:
          Go to Record/Request/Page [first]
              Set Field [Participants::emailAddress ; Surveys::eMailAddress ]
             Go to record/request/Page [next ; exit after last ]
          End Loop

          To shorten the amount of looping, you could perform a find for all Surveys records that do not have a related Participants record.

          Note that this works from a different relationship than one by a Participant ID. You will need both and this can be managed by using multiple Tutorial: What are Table Occurrences? when setting up your relationships.