9 Replies Latest reply on Aug 10, 2017 9:15 AM by philmodjunk

    How to populate a questionnaire automatically from certain conditions

    brenseec

      Hi there,

       

      NOOB here to filemaker (I have some SQL and Access and other coding experience) and loving it so far.

       

      I’m currently building an inspection tool for inspectors in our company, One thing I’m struggling at the moment is to populate any given inspection/questionnaire with the relevant questions to the inspection.

       

      I have a master question list table in the format below

      Question no, Question, lift type (check box list to choose from)

       

      Basically, when the inspector is on site he/she will enter what type of lift they are inspecting by ticking a checkbox, this should then populate the relevant question on the inspection form.

       

      Any ideas how can do this efficiently, I can do it a clunky way by conditional formatting but means I have to manually type the questions instead of inserting it form my question table.

        • 1. Re: How to populate a questionnaire automatically from certain conditions
          philmodjunk

          Each question should be a record in a master list table of questions.

          Each answer should be a record in a related table linked to the relevant question.

          Pulling up the correct set of questions, or one question at a time as the user completes the preceding question can be just a case of pulling up the correct record for answering the next question as determined by the type of equipment being inspected.

           

          That's a very general description because much depends on your full data model, which is not something that you have shared with us.

           

          There are a number of discussions here that describe data models for a questionnaire or survey that you might search out to learn more.

          • 2. Re: How to populate a questionnaire automatically from certain conditions
            brenseec

            Thanks philmodjunk,

             

            Yep I have each question as a record in a master list and I have a table of answers (there can be more that 1 answer),

            I will share more info tomorrow. I'm about to finish work now (Irish time).

             

            I will have a further look through the forum for some relate questions, this looks like a resource, thanks again for your response

            • 3. Re: How to populate a questionnaire automatically from certain conditions
              jackrodgersjr

              A simple method would be to add a field in your record that displays as a checkbox so the inspector can check one or more items. The checkbox is formed by a value list you define.

               

              Value List (good for a limited number of choices):

              Valve

              Pipe

              Fitting

               

              On your Form (I've found 50 or so choices manageable):

              _ Valve

              X Pipe

              X Fitting

               

              Or if you are only going to select one, you could use a popup list based on your value list.

               

              This of course has its limit as the number of items grows.

               

              New Table of Values

               

              Using a new table offers the abiliity to add at any time an unlimited number of items, often a preferable technique.

               

              Then you could display this list in a portal, popup window as a list, etc. The user could scroll through and find and click on one or more items. A script would let you use that click to insert the value as an additional value in a field or as a new related record allowing an unlimited number of values for that record.

               

              This new table also allows the use of categories for these items so you can use a sorted report to display the choices.

              • 4. Re: How to populate a questionnaire automatically from certain conditions
                realgrouchy

                A technique that I use, which may not be the most elegant, is to have a script called "Form Layout Triggers" peppered with a bunch of instances of:

                 

                If ( Get ( ScriptParameter ) = "Field1" and IsEmpty ( Table1::Field2 ) )

                   Set Field ( Table1::Field2 ; "calculation based on response for field1" )

                Else If ( Get ( ScriptParameter ) = "Field2" and IsEmpty ( Table1::Field3 ) )

                   Set Field ( Table1::Field3 ; "calculation based on response for field2" )

                Else If ( ...etc. )

                End If

                 

                And then on each field in the layout, I set a script trigger for OnObjectModify, to run that script with the script parameter being the field name. That way it's all in one script (so you can see how the different field responses interact all in one place) but each instance only triggers one aspect of the script.

                 

                This probably won't work with your data model of separate fields for each question/answer, but I thought I'd offer it as a suggestion.

                 

                - RG>

                • 5. Re: How to populate a questionnaire automatically from certain conditions
                  brenseec

                  Hi thanks for you reply,

                  So here is a simplified version of what I'm working on

                  3.png

                  Below to this is the question table which I will have pre populated with questions, this are the questions I want to insert into the survey depending on what boxes are ticked by the inspector

                  2.png

                   

                  As you can see below the inspector ticks the box and the the question should pop in, how can I do this. I'm a bit stuck

                  1.png

                  • 6. Re: How to populate a questionnaire automatically from certain conditions
                    philmodjunk

                    The relationships shown in your screen shot do not look like they will work. You'd need something more like this:

                    Surveys----<answers>------Questions

                    Survey::SurveyNo = Answers::SurveyNo

                    Questions::QuestionNo = Answers::QuestionNo

                     

                    And I think you'll need at least one more table to control which questions are associated with a given lift type, which would link a table of lifts to your table of questions.

                    • 7. Re: How to populate a questionnaire automatically from certain conditions
                      realgrouchy

                      There's something not quite right with your relationships. What you're trying to do is filter the questions so only the ones that apply to that lift type are shown.

                       

                      You have Survey No as an auto-enter serial field, so customer 1 will have Survey No 1, customer 2 will have Survey No 2, etc.


                      But you also have a relationship where Survey No = Question No. This means that Customer 1 will only see Question 1, Customer 2 will only see Question 2, etc.

                       

                      Instead you need to specify the customer's Lift Type, and then have a relationship that only shows Questions that apply to that lift type. Then you can use a portal to show those questions.

                       

                      I've attached a modified version of your database that does this (I changed "No" to "ID" in the field definitions, and "Survey" to "Customer" to be more clear that that table is one per customer)

                       

                      I believe your Answers table is for the answers that the customer gave for that question, as opposed to using it to configure the list of possible answers to that question. The records in the Answers table need to have a Customer ID so that it only shows an answer if it was given by that customer. I'm not sure how to set up that relationship in a way that displays the information clearly, but hopefully this puts you a step in the right direction.

                       

                      Good luck!

                       

                      - RG>

                      1 of 1 people found this helpful
                      • 8. Re: How to populate a questionnaire automatically from certain conditions
                        brenseec

                        Brilliant realgrouchy, this is pretty much what I'm looking to do.

                        What I plan on doing is having standard answers for the question being asked.

                         

                        The idea been that there will be no ambiguity from the inspectors report/survey.

                        I just put in the yes/no on an answers table for demo purposes

                        • 9. Re: How to populate a questionnaire automatically from certain conditions
                          philmodjunk

                          If you have standard answers, these would either be in a field in your questions table or in a separate table so that you have one table for the standard answers and one table for the actual responses recorded. I also assumed that your answers table would be the responses recorded for each question.