6 Replies Latest reply on Feb 7, 2014 6:27 PM by AshleyRoti

    Database to hold multiple surveys



      Database to hold multiple surveys


           Hi everyone,

           I am new to Filemaker and have run into a snag on my current project. I'm working on a database to hold multiple (34) surveys. The database is just an electronic version of the paper surveys, so I don't need to worry about respondents and their answers or things like that.


           My problem is that some of the questions are multiple part questions and I can't get the multiple question parts and their respective answer options to all display on the same layout. Here is an example of one of the questions with its parts and answer options:

           1. For each of the following, please mark whether or not you plan to talk to your health care provider about it as a way to manage your knee pain.

           Over-the-counter pain medicine                      [ ] Yes  [ ] No  [ ] Already doing

           Prescription pain medicine                              [ ] Yes  [ ] No  [ ] Already doing

           Physical therapy                                                  [ ] Yes  [ ] No  [ ] Already doing

           A walking aid, like a cane or brace                  [ ] Yes  [ ] No  [ ] Already doing

           Knee surgery                                                       [ ] Yes  [ ] No


           I've tried re-working this database so many different ways! It's a mess right now, but the tables I have currently are:

           Surveys [fields: __pk_survey_id, survey_name, survey_kind]

           Sections [fields: __pk_section_id, section_name, _fk_survey_id]

           Questions [fields: question_id, question_name, question_kind, question_text, _fk_section_id, _fk_answeroption_id, grid_id]

           AnswerOptions [fields: __pk_answeroption_id, answeroption_name, answeroption_text]

           Grid Questions [fields: grid_id, gquestion1_name, gquestion1_text, gquestion1_ansopt, gquestion2_name, gquestion2_text, gquestion2_ansopt ...] FYI, "ansopt" is short for answer option and each "ansopt" field is a fk for the __pk_answeroption_id field.


           I originally had the grid questions in the Questions table with the normal ones- is it best to have it separate or should I move that information back? 

           As a side note, this database, once complete, will not need to be added to.. Not sure if that would alter which method would be the best way to get this done.


           Any ideas on this? I cannot seem to find a solution!

           Thank you!!!!

        • 1. Re: Database to hold multiple surveys

               I am a bit confused on your request..  So you have all these surveys.. but yet you are not keeping the info?

               What is the purpose in doing the survey then?

               Also, what do you mean by multiple part questions?    What are you trying to do with the questions specifically?

               Do you have these questions in a table or just on a layout?

               Is can there be multiple answers to the questions?  ie.. select all that apply?

          • 2. Re: Database to hold multiple surveys

                 We are keeping the info in separate databases depending on which site submitted them.

                 The purpose of this database is so we have a catalog of the surveys available, with no respondent data attached.

                 I provided an example of a multiple part question in my original post, but to provide another example of a multiple part question would be:


                 1) When was the last time you visited each of the following stores?

                 a) Whole Foods    [ ] Within the last 3 months  [ ] Within the last year  [ ] Never

                 b) Trader Joe's    [ ] Within the last 3 months  [ ] Within the last year  [ ] Never

                 c) Market Basket    [ ] Within the last 3 months  [ ] Within the last year  [ ] Never


                 ^There is one main question being asked-- "when did you last go here"- but you must answer that main question regarding multiple subjects--the different grocery stores. I hope that clarifies a bit more. 


                 The questions are in a table, but I cannot seem to produce a layout that will show the question, it's grid questions (parts a,b, and c in the above question example) and the answer options for each of the grid questions.

                 There can be one answer selected for each of the grid questions (a, b, and c will each have their own option selected, but only one each).


                 I hope that helps and doesn't make this more confusing... Let me know if you need further clarification. Thanks for the speedy reply!

            • 3. Re: Database to hold multiple surveys

                   There are other ways.. but is this what your are referring too?




              • 4. Re: Database to hold multiple surveys

                     Yes! That is, essentially, how I want the layout to look... However: 

                     1. Not all subquestions will have the same answer options (or even the same number of answer options), so using custom values would not work here. 

                     2. I'd like to have the answer options remain on their own table, if at all possible. It seems to me it would be easier to organize everything, as well as perform searches (if needed) if I could have them on their own.  There are 1500 different questions, with only 134 different sets of answer options so, if possible, I'd rather define the different answer option choices via an answeroption_id fk field in the Questions table... At least in my head that makes more sense- I am quite obviously not a pro with FileMaker.


                • 5. Re: Database to hold multiple surveys

                       Here is the same thing just pulling from a table...



                       You would just have to link your questions to the correct responses...


                  • 6. Re: Database to hold multiple surveys

                         The issue with that layout is that when you set up the conditional value list, you selected "show all records" from the field.. For my database to work with that set-up, I would need a new field for each set of answer options (of which there are 134), which seems cumbersome. My initial thought was to link the answer option_id field of each subquestion to the answer option_id field of a set of answer options, but then I would need a separate value list for each subquestion, all listed on the same layout, which doesn't seem possible. Also, if it was possible, I couldn't use it with the way the subquestions are set up in the portal on the database you referred to in the last comment (or could I?)