1 2 Previous Next 15 Replies Latest reply on Jan 23, 2012 2:57 PM by philmodjunk

    Need aid on generating a report from a survey layout.

    DanFlores

      Title

      Need aid on generating a report from a survey layout.

      Post

      First time using FM pro and I've hit a snag. This is what I've been tasked with:

      Translate the paper version of our survey to a FM database.

      Set up layout for the survey.

      Generate a report that tallies the number of times a given choice ( 1-5 ) is chosen for each question.

      i.e Section A Question 1:  5 "1s',  3 "2s" , 4 "3s" etc.

      I am also tasked with collecting all comments from each section ( there are 4 sections ) and displaying them in the report one after another. 

      i.e. Section B Comments: It was ok, I think it needs this, poorly implemented ...etc

      I've hit a road block as how to generate the report with the tallies and the comments. I have a field for each Question and set up radio buttons for the 1-5 choices, just not sure how to tally them.

      I have no clue about gathering the comments, I was thinking from a programming stand point of searching the comment fields then sort and separating with commas in a new record, but not knowable enough of FM pro to implement that.

      Any and all advice is welcome.

        • 1. Re: Need aid on generating a report from a survey layout.
          philmodjunk

          Before working with layouts, you need to structure your tables and relationships to support the survey process. If you put Survey in the search the forum box at top right on this screen, I think you'll pull up several discussions of the tables and relationships used to produce a survey.

          You'll need to consider some basic issues:

          Is this a one time only project or will new surveys be taken in the future?

          How will each person taking this survey record th e answers to be entered into your database? Via hard copy documents? A web site? a FileMaker Pro layout to be filled out at a computer?

          Generally, you need a table of questions where each question is a separate answer record that you then link to a table of responses where each record is one person's response to a single question on a survey. This allows you to pull up a group of responses from one person to see the survey results for that one person or you can pull up all the responses for a given survey, sort them by Question and get a summary report that tallies each recorded response for each question. Essay type answers can be grouped by question for your report in the same way.

          • 2. Re: Need aid on generating a report from a survey layout.
            DanFlores

            I think my problem was in how I structured it. I started with only one table that gathered what i needed from the user: Name, date, course for review, then the Questions and comments.

            We are switching from the paper version to this digital one so we will be using it more then once. Hopefully we will have a link on our website that goes to survey layout. 

            The survey itself is pretty simple in nature. Each question uses a likert scale of 1-5 along with space for comments. In turn the report my boss wants will only show the tally of the respones as well as the comments.

            I think i get what your saying, however i'm confused with "each question is a seprarte answer that you then link to a table of responses" Do you mean that the fields in the Question table will consist of every response for each question like:

            Question 1 response 1 | Question 1 response 2 etc OR 

            Question 1 | Question 2 etc THEN link those to same fields in the response table? 

            • 3. Re: Need aid on generating a report from a survey layout.
              philmodjunk

              Apologies for bad typing there. I've edited my last post. That sentence should read "each question is a separate record..."

              Generally, you set up this type of table structure:

              Respondants------<Responses>-------Questions>-------Surveys    (----< means "one to many")

              A single record in respondants represents one person responding to your survey. You put your name, date, course for review, etc. fields there. Then link it to responses by a unique serial number field defined in respondants:

              Respondants::__pk_RespID = Responses::_fk_RespID     (pk means "primary key" and fk means "foreign key".)

              Each response to a question is a separate record in that table. The radio button field with your Likert scale and your comment field for comments is defined in this table. (Assuming you have one comment field for every question, not a single comment field for the entire survey...)

              Each question to ask in your survey is a separate record with it's own serial ID in the Questions table to produce this relationship:

              Questions::__pk_QuestID = Responses::_fk_QuestID

              A Text field in the Questions table is used to store the survey question being asked. If your survey asks each respondant 5 questions, this table will have 5 records for that survey. (It's not needed for this project, but it's possible to add a field in Questions that lists the possible answers for that specific questions so a conditional value list can use this field and present the user with different values on every question.)

              You may or may not need a Surveys table. Adding one allows you to process more than one survey--each with it's own group of related records in the Questions table.

              With the above structure, you can set up a portal to responses on the respondants layout. The Question field from the related Questions table can be added to this portal so that the respondant can read the question and select a response in the portal for each question.

              Alternatively, a list layout based on the Responses layout with related fields from both Respondants, and Questions added to the layout can be used to administer the survey questions.

              Either way, a script would be used to create one matching record in responses for each question, linked by RespID to the Respondants record each time a person starts to take the survey.

              To see how such a table of responses can be used to produce your report, see this tutorial on summary reports (The lineItems table in the tutorial is similar to the responses table described here.):Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Need aid on generating a report from a survey layout.
                DanFlores

                Alright i've redone my structure as per your advice, just a few more questions and i think i'll be done with this project. 

                I have my Questions table with a single Questions field with 12 records ( 3 for section A plus a comment, 1 for Sect B plus comment, 2 for sect C plus comment, 1 for D plus comment, then lastly a final comment ).

                I have my Response table with three fields, ( they asked me to go from 1-5 to 1-3 responses for each question) response 1, 2, 3 plus fields for the comments for each section and the final comment. 

                As I'm recreating the layout for the survey i hit a bump. I used the portals suggestion in a respondence based layout, and read over the tutorial for a summary reports you did, and I wondering if i'm doing anything wrong. 

                I know i'm to link questions to response via QuestID but how do i do that when no response records exist til the user begins the survey? Also within the layout do i use the question field within the layout to display the questions or simply a text field.

                Lastly I am to upload this survey to a website on our network, I figured i would have to create a submit button to gather the data from the given survey and have that data setup into a report form then emailed to my boss. That sound about right? 

                • 5. Re: Need aid on generating a report from a survey layout.
                  philmodjunk

                  I know i'm to link questions to response via QuestID but how do i do that when no response records exist til the user begins the survey? Also within the layout do i use the question field within the layout to display the questions or simply a text field.

                  You'll need to use a script to generate one response record for each question record for the given respondant just as they are ready to take the survey.

                  A script like this should work:

                  #Script is run from a layout based on Respondants
                  Set Variable [$RespondantID ; value: Respondants::__pk_RespondantID ]
                  Go To Layout [Questions]
                  Show All Records
                  Sort Records [Restore; no dialog] //sort the questions in the order they need to be listed.
                  Go to Record/Request/Page [first]
                  Loop
                     Set Variable [$QuestionID ; value: __pk_QuestionID ]
                     Go To Layout [Responses]
                     New Record/Request
                     Set Field [Responses::_fk_RespondantID ; $ResponadantID ]
                     Set Field [Responses::_fk_QuestionID ; $QuestionID ]
                     Go To Layout [Questions]
                     Go To Record/Request/Page [next ; exit after last]
                  End Loop
                  Go To Layout [original layout]

                  I figured i would have to create a submit button to gather the data from the given survey and have that data setup into a report form then emailed to my boss.

                  I'd run such a script from a Filemaker client and save the report it sets up as a PDF. The email can then attach the PDF.

                  Here's a link on saving as PDF that may help: Found Sets to PDF with unique file names

                  • 6. Re: Need aid on generating a report from a survey layout.
                    DanFlores

                      Set Field [Responses::_fk_RespondantID ; $ResponadantID ]
                      Set Field [Responses::_fk_QuestionID ; $QuestionID ]

                    This section of the script, is it suppose to set the numbers in responses tables based off of respondanID and QuestionsID respectfully? 

                    is the second part of the script  " ; $RespondantID ] " supppose to be contained in the repetition entry? I wonder because when i run it, it creates the 12 records i need but fk respondant and questions fields are left blank. 

                     

                     


                    • 7. Re: Need aid on generating a report from a survey layout.
                      philmodjunk

                      This section of the script, is it suppose to set the numbers in responses tables based off of respondanID and QuestionsID respectfully?

                      yes

                      is the second part of the script  " ; $RespondantID ] " supppose to be contained in the repetition entry?

                      No

                      When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Need aid on generating a report from a survey layout.
                        DanFlores

                        Great got the script up and running properly. What i found was now i have the 12 records for each response for each questions, however each response field in the layout displays only the first record.

                        I then tried to do the layout as a list layout, and i found that if i keep the respondance fields in the header and the single questions and response fields in the body parts when viewed in the list view each question and response are easly displayed as I scroll down.

                        My new issue is of the 12 question 7 are of the 1,2,3 radio responses and 5 are comment entries so the list layout won't work for what i need due to how i need the survey to be displayed, which would be something like; Q1 Q2 Q3 Comment / Q4 Comment / Q5 Q6 Comment / Q7 Comment / Final Comment. 

                        Or would none of this matter if i made another table for comments? 

                        • 9. Re: Need aid on generating a report from a survey layout.
                          philmodjunk

                          My new issue is of the 12 question 7 are of the 1,2,3 radio responses and 5 are comment entries.

                          If you present one question to the user at a time with a button to take them to the next question, that button's script can change layouts to the layout appropriate for the type of response to be entered by the user. You can use a field in the questions table to indicate the response format.

                          • 10. Re: Need aid on generating a report from a survey layout.
                            DanFlores

                            Well i think we've come full circle now on the project, i'm back to how to make a report with a tallie of responses, i've found your comments here

                            Count or summary of Drop Down Fields and it looks simliar to your summery report tutorial, and both are helpful, but now i just need to figure out how to apply them to my report format. Also would i need to make a report layout ahead of time and use a button script to insert formatting before i convert it to pdf and have it emailed, or can the whole process ( generate the report -> tally -> convert -> email to boss ) be done within on long script? 

                            • 11. Re: Need aid on generating a report from a survey layout.
                              philmodjunk

                              One script can do it all, provided you have the layout you need already designed to support the report format required.

                              • 12. Re: Need aid on generating a report from a survey layout.
                                DanFlores

                                How would this lay out be setup? simliar to how you setup the summery report in your tutorial or slightly different? 

                                • 13. Re: Need aid on generating a report from a survey layout.
                                  philmodjunk

                                  The tutorial sets up more than one layout depending one where in the tutorial you are referring to.

                                  The basic concept and structure is the same, however. You have sub summary parts that sort by a value common to seach group of records (The response values) with summary fields in the sub summary parts to display the needed sub totals. You could have groups broken down into smaller groups if you need that. (I think the tutorial demos that idea). The other key detail is whether or not you want to see data from the individual groups or just the sub totals. The tutorial shows you how to get either report depending on whether or not you keep the body layout part or not.

                                  Once you have a layout that correctly displays the data the way you want to see if (provided you sort the data correctly to group it), you can then script a find and sort to pull up the desired report on your report layout.

                                  • 14. Re: Need aid on generating a report from a survey layout.
                                    DanFlores

                                    In my find and sort script, one of my find requirements would be the course reviewed. Is it possible to use set variable to capture the current survey's entry for course reviewed with the script, in order to use it in the find mode? Also how would look like in scripting?

                                    1 2 Previous Next