14 Replies Latest reply on Jul 18, 2013 8:46 AM by philmodjunk

    Need help designing a survey database



      Need help designing a survey database


           Hi everyone,

           I am brand new to FMP, although have gone through the provided tutorials and training from FM, so am doing best I can to learn.  I have also searched extensively on the forum website to understand how to set up my database, and have a few key quesitons about how to structure my tables before I even begin layouts. 

           My task: design a database where several students (including myself) can enter the data from a survey that was completed on paper. I know I want a layout where a student can enter all the responses from each survey on one page, with either radion buttons or drop down menus to select the appropraite answers. The selected answers need to be linked to a numerical code which will be stored in a response table. because my responses will be exported to excel, then entered into a statistical software for detailed analysis. 

           Based on what I have gleaned from this forum, I have designed 5 tables, with the following fields/records as below:

           Table 1: Surveys (fields: surveyID, State, Date administered).  This has three feilds and 6 records (I have 6 survey versions)

           Table 2: Respondents (fields: _pkID#, coded by, date received, date entered).  4 fields, 700 records (I have 700 completed surveys)

           Table 3: Questions (fields: _pkQuestionID, _fkID#, Text).  3 fields, 100 records ( I have 100 questions)

           Table 4: Responses... This is where I am having difficulties. Based on other responses, I am thinking I need each record to be the question , and each field to be a unique respondents answer to that question - i.e. - 100 records, 700 fields.  This does not seem intuituve to me - it seems more intuitive to have the records be the individual survey taker, and the fields be each individual question - 700 rows, 100 columns.  Help? 

           Table 5: Code Book... again, I am confused here. Each question has multiple responses - like, dislike, neutral; agree, disagree, neutral; always, often, sometimes, rarely, never....etc. I need each of these words to correspond with a number that can be used in my statistical software. Do I make a single code book table with each question, and each possible reponse, with a corresponding number?  If I do this, I am realy not sure how to set up the recods and fields, because each question has a different set of responses. 

           Or do I need a separate table per question?  Seems that a separate table per question is easy, I don't really understand how to set up just one codebook that has each of the 100 questions and possible responses in it (with words and numbers for my menus), but an individual table per question seems silly - a lot of tables. 

           Any help is appreciated.  I really want to set this up correctly before I start my layouts.  This is a one-time use project for my advisor...




        • 1. Re: Need help designing a survey database

               Survey questions on these forums can be found with a Google "survey site:forums.filemaker.com"

          • 2. Re: Need help designing a survey database

                 The table and field names seem very similar to what I used in this thread: Need aid on generating a report from a survey layout.

                 Perhaps that is one of the threads that you found.

                 4) Responses: One record in this table would be one reponse to one question by one respondant. A record in this table links both to a specific question record and also to the specific respondent who recorded that answer to that question. Thus a record in Responses has an fk field linking it to the pk of a question record and also an fk field linking it to the pk of a respondent record.

            • 3. Re: Need help designing a survey database

                   The relationships would look like this:


                   Respondents::__pkRespondentID = Responses::_fkRespondentID
                   Questions::__pkQuestionID = Responses::_fkQuestionID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Need help designing a survey database


                     Thank you very much, that is exactly where my confusion was for the Responses table.  (Indeed - I have read through the responses to many of the survey questions on this forum, including your responses to the one linked - thank  you for that!)

                     Thus, I have a responses table with 3 fields (_pkANswer, _fkQuestionID, _fk_RespondentID). With 700 survey takers and 100 questions, I will end up with 70,000 records in this table.  Did I get that right?

                     I am still confused about defining the possible answers to the questions however, which perhaps you can help me with. Each question has a different set of possible answers (although none are multiple selections, one and only one must be selected for each question). Do I put all the possible answers as separate fields on the questions table?  If each record in the question table is a separate question, I would have many fields to list all possible answers, although each question would only have a few applicable responses. Or, can the possible answers be stored in one field? I do need a text answer and a numerical answer for each question - text for the coder to select when completing data entry, numerical to be recorded on the responses table for export. I was able to do this with just one question on a table, but with many questions with different responses, it seems cumbersome - probably because I am not setting it up correctly. 

                     Perhaps somebody has an example of a similar table that I could look at to help me uinderstand this set-up?

                     Thank you!!!!!!

                • 5. Re: Need help designing a survey database

                       You can format a single response field with a conditional value list. Both the radio button and pop up menu formats are intended for selecting single values from a value list. You can add a "respontype" or "CodeID" field to your code table where each record is one possible value of a set of responses for a given question.

                       The relationships might look like this:


                       Responses::_fkQuestionID = Questions::__pkQuestionID
                       Codes::_fkResponseTypeID = Codes::CodeGroupID    (The same group of possible answers may be specified for more than one question.)

                       Then you can define a value list of Response values from the Codes table occurrence starting from Questions.

                       If conditional value list is a new term, see these links:

                       There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                       The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                       Forum Tutorial: Custom Value List?

                       Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                       Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                       Hierarchical Conditional Value lists: Conditional Value List Question

                       Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                  • 6. Re: Need help designing a survey database

                         Thank you!  I will take some time to read through these helpful links, much appreciated!  I will follow-up as necessary...



                    • 7. Re: Need help designing a survey database

                           Hi Phil - 

                           I have read carefully through these articles and tried the excercises, and have been able to understand how they are working.  However, can't seem to translate this to what I need to do. I believe I have all of my tables set up correctly, and relationships. But I don't know how to make the next step - to design a data entry layout where I have a list of quesitons and only the appropriate values pop up, and than are entered into the appropriate place in my tables. 

                           For example, someone doing data entry shoudl be able to come to a layout that shows, all on one tab: 

                           Question 1: Have you ever... ? And then select from just the quesiton 1 values to select the response, then have this data be entered back into my tables. 

                           Question 2...etc. 

                           Then, upon creating a new record, the question prompts clear for a new respondent to be entered. It seems straignforward as a flat DB (I cn actually get it to work, at least for data entry), but I know a relational is what I need to be abel to make reports etc.

                           I thought this would be relatively straightforward given all the forum info provided, but I can't figure out how to make all of this work together. 

                           I was planning to create a new layout and put in a portal to the other tables, but I cant get the new layout right (what TO to base it on) or a portal to work (the related frields is messing me up). 

                           Sorry for the newbie questions - just a student tasked with workign with this new software and File Maker doesnt seem to offer support for DB development that I need. 

                           Any advice on how to proceed is appreciated!!



                      • 8. Re: Need help designing a survey database

                             Why reinvent the wheel?

                             I use Survey Monkey for online surveys. I download the survey results from Survey Monkey in Excel format. Then I open the Excel file in Filemaker.

                             After some initial testing, you can lock in your Filemaker database, and then build import scripts from the Excel downloads for any additional survey data that comes in.

                        • 9. Re: Need help designing a survey database

                               I see at least two issues in what you have just described:

                               1) Getting the right list of possible responses (Codes) associated with the correct question

                               2) Designing a layout that collects the respondant's answers in a user friendly and efficient fashion.

                               How much of 1) can you get to work? Can you format a field from the Reponses table with a radio button format and see different lists of possible responses controlled by what question ID is entered in the QuestionID field in the Responses record?

                          • 10. Re: Need help designing a survey database

                                 Hi Eqitec - thanks for your response.  I unfortunately do not have my data digitally - that is what I am traying to set up here - a way to enter data from paper to the DB.  However, any suggestions you have for how to store the data woudl be helpful!

                            • 11. Re: Need help designing a survey database


                                   I am attaching a screen shot of my code table.  Its not my final table, just entered a few values to see if I could get this to work.  I created the relationship of question ID through manage database, but don't know how to get the field in the responses table to only link to records under, for example, question ID 1. 

                              • 12. Re: Need help designing a survey database

                                     Phil - an update: 

                                     I am able to get a list of response options after a certain question is selected in my answers table.  However, it requires me to have the user select which question they are answering.  So - hooray - I have gotten that funciton to work using a relationship!

                                     I guess this beings me to the layout, which seems quite a bit more complicated, becuase I need a 'fixed' question number to display, with the associated drop down menu...

                                     I have tried a portal, but I can only create one answer per respondent this way, I do not know how to get the portal to create more than one new record for a response - I can only get one question and one answer to show up.  When I try to put in additional, it just changes the first record. Does that make sense?

                                • 13. Re: Need help designing a survey database

                                       Survey Monkey has a function for manually entering survey responses from paper submissions.

                                       I'm not selling anything here. I'm sure the other commercial online survey instruments are just as good. I just don't see spending so much time on custom-building one in Filemaker or any other app. I know how daunting that can be.

                                       Regarding your concern about radio-button answers, Survey Monkey has a lot of flexibility in reporting so those answers can be shown as the text or numeric equivalents as they are stated in the survey question.

                                       It would be great if DM would develop a generic survey solultion that could be tweaked by developers to meet special needs.

                                  • 14. Re: Need help designing a survey database

                                         equitec makes an excellent suggestion if it works for you.

                                         To continue what you've started here, the next step is for a respondent to choose or be presented with a specific survery--a specific list of questions. Typically this requires that a script loop through the set of questions records for that survey and generate a set of response records with the needed question and Respondent ID's entered via this script so that the respondent get's a set of response questions linked to them where each is linked to a different question that is part of that survey.

                                         Such a loop can be set up in one of two ways:

                                         a) Generate all the response records in a single "batch" operation, then the user steps through them to see and answer the questions.

                                         b) Generate the response record for the first question and present it. When the response is recorded, a script then creates the needed response record for the next question in the survey.

                                         Take your pick, from the user's point of view, there really isn't any noticeable difference between the two.