1 2 Previous Next 25 Replies Latest reply on Nov 28, 2016 6:21 AM by David Moyer

    Q&A Radio Box Selection To Generate Text Report

    AnthonyTesoriero

      I am trying to create a solution that allows users to answer simple yes no questions and based upon the answer have Filemaker generate a text based report.

       

      Example:

      Case ( LIC_included = "No" ; "WARNING: You must include a copy of your business license." ; LIC_included = "Yes" ; "√ A copy of your license is included." ; "" )

       

      So Far I've been successful generating a text report using a calculation but I recognize this is not a scaleable solution

       

      Example:

       

      LIC_included_a  & ¶ & ¶  & LIC_Name address_a & ¶ & ¶  &LIC_Validity dates_a & ¶ & ¶  & VIN_Picture_a & ¶ & ¶  & VIN_Picture is clear_a & ¶ & ¶  & VIN_Picture is permanent_a & ¶ & ¶  & BILL_Picture_a & ¶ & ¶  & BILL_Biz n LIC Match_a & ¶ & ¶  & BILL_VIN match_a & ¶ & ¶  & BILL_Service date visable_a & ¶ & ¶  & BILL_Service date n LIC valid_a & ¶ & ¶  & BILL_Customer_a & ¶ & ¶  & BILL_Details_a & ¶ & ¶  & BILL_Totaled_a & ¶ & ¶  & BILL_Includes Storage_a & ¶ & ¶  & BILL_Authorized_a

       

      I need advice on how I can more easily edit the Yes or No answers rather than have to edit every case calculation

       

      An a better way to generate the text report so that it will better format itself to look more cohesive and natural flowing.  Meaning currently if the answer is Yes it takes up 4 lines, no is 1 line no answer will leave a bing blank space in the middle of the report.

       

      Any help you can provide is appreciated.

        • 1. Re: Q&A Radio Box Selection To Generate Text Report
          erolst

          As so often, the answer is to use related records, rather than a set of fields, i.e. one record per question. Makes reporting and analyzing your data much easier, e.g. when using a list layout to display a . . well, list of answers.

           

          To learn more about this approach, search this forum for 'questionnaire'; this requirement comes up frequently.

          • 2. Re: Q&A Radio Box Selection To Generate Text Report
            David Moyer

            Hi,

            I'm not sure what you mean by a natural flowing format.  It might be that some of your fields are empty and you end up with a bunch of blank lines.  If so, I can think of several things right off to help.  Most simply would be to look at the List function.  If that doesn't work, then I can recommend a couple of custom functions I use for getting rid of white space.

            • 3. Re: Q&A Radio Box Selection To Generate Text Report
              AnthonyTesoriero

              I have searched for questionnaire but unfortunately haven't been able to decipher the answer from the threads that I have read. Could you provide a simple diagram or explanation.

               

              I recognize you're pointing me toward related records but don't understand how to form the correct relationship to accomplish the task. I really want it simple, everything I have read is actually appears more complex with multiple choices, contact tables, different question styles etc. I don't need any of those things. Just 10 yes or no questions and 3 possible answers (yes, no, N/A) from the 10 questions that generate a text report.

              • 4. Re: Q&A Radio Box Selection To Generate Text Report
                AnthonyTesoriero

                I will look at the list function. Thank you for the tip.

                Yes I have white space from empty lines that I am trying to eliminate.

                • 5. Re: Q&A Radio Box Selection To Generate Text Report
                  AnthonyTesoriero

                  I have read the thread: Create a FileMaker Pro survey and Need aid on generating a report from a survey layout.

                  and while I have probably learned more from @philmodjunk than any book or video I have ever watched, I'm simply stumped on how to create this simple structure... I think I have been away too long and my brain is freezing up on creating these relationships. Any additional help you can provide would be appreciated.

                  • 6. Re: Q&A Radio Box Selection To Generate Text Report
                    David Moyer

                    I see you included double-spacing in your posted equation.  List will reduce your data to single-spacing.  If you need double-spacing (without voids), you can use:

                    Substitute( List (Text1; Text2; Text3 ); "¶"; "¶¶" )

                    • 7. Re: Q&A Radio Box Selection To Generate Text Report
                      erolst

                      You can make this as complex as you want, but also very simple.

                       

                      A questionnaire table, with, say, an applicant ID (the person), and a related Answers table, where each record holds the question and the answer. So

                       

                      Questionnaire --< Answers

                       

                      Write a script that creates the desired number of Answer records; it could also fill in the question field from an existing list.

                       

                      This setup will come in handy not only for presentation, but also if you ever need to analyze the answers, and while it takes more preparation to implement than a bunch of fields, that effort will pay off nicely.

                      • 8. Re: Q&A Radio Box Selection To Generate Text Report
                        AnthonyTesoriero

                        Relationships.pngQuestions.pngReport.pngCars.pngInspection.pngScript.png

                         

                        OK, Following your leads and the thread: Need aid on generating a report from a survey layout. I restructured the database.

                        I have a table for

                        Cars (PK-Car ID, Description) - That table is linked to:

                        Inspection (FK-Car-ID, PK Inspection ID, FK-Questions ID, Responses)  - Linked to

                        Questions (PK-Question ID, Question, Yes, No, Option, Notes, Response, Check) - The Yes and No are text fields that include responses that will be added to a formatted report generated after they are through with the inspection.)

                        I also have a table for Inspection Report (FK-Inspection ID, Report) but frankly I don't know if I really need this separate table to accomplish what I want to do.

                         

                        First problem is, using script created based on philmodjunk Need aid on generating a report from a survey layout. thread, the questions are generated in a portal on the car record of a new inspection, but I need to make sure the script can't be run more than once after it has been run the first time, otherwise it will keep creating records of the exact same questions.

                         

                        Second problem is I cannot figure out how to create the report now, I tried the list function but can't figure out how to get list the responses in a format from the Question table since the field is singular but the answers are multiple based upon the multiple questions asked.

                        • 9. Re: Q&A Radio Box Selection To Generate Text Report
                          AnthonyTesoriero

                          3rd Problem- After I create a new "car" record, then run the script to insert the questions into the portal it inserts the questions already pre filled with the previous answers... I need the radio buttons to be reset to empty.  I'm guessing this might be because I have the Response filed on the same table as the question... Any ideas would be helpful...

                          • 10. Re: Q&A Radio Box Selection To Generate Text Report
                            erolst

                            AnthonyTesoriero wrote:

                            I also have a table for Inspection Report (FK-Inspection ID, Report) but frankly I don't know if I really need this separate table to accomplish what I want to do..

                            Nope, you don't. The inspection record is the 'framework' for the questions; combined they form the Inspection.

                             

                            AnthonyTesoriero wrote:.

                            Second problem is I cannot figure out how to create the report now, I tried the list function but can't figure out how to get list the responses in a format from the Question table since the field is singular but the answers are multiple based upon the multiple questions asked.

                            You can use a list layout based on question to print out a report, aka 'inspection form'. Since you have a fixed number of questions, you could even use a portal on an Inspection layout. btw, the possible answers yes, no and n/a are mutually exclusive - why not use a single answer field?

                             

                            Before you ask further questions, note that you have one important thing way backwards: an inspection has many question, not vice versa. So you need to remove the quest_fk from the Inspection table, and instead add an InspectionFK to the Questions table.

                            • 11. Re: Q&A Radio Box Selection To Generate Text Report
                              AnthonyTesoriero

                              OK I deleted the Report table, and will work on creating the report based on the Inspection table.

                               

                              I also figured out that I had been using the option field from the wrong table which was leading to the script creating the questions pre filled with responses.

                               

                              I had to think for a second but understand what you meant about the yes/no/na being mutually exclusive and if I just use a Yes or don't check it I can get the same results... Good catch.

                               

                              I'll now review the relationships and key fields.

                               

                              Thank you for the help

                              • 12. Re: Q&A Radio Box Selection To Generate Text Report
                                AnthonyTesoriero

                                I reviewed the relationship, key field and made the changes you suggested, however it disrupted my create questions scrip and I couldn't get it to work. If you don't mind could you review the pictures of my script and the relationships and tell me what I'm doing wrong???

                                • 13. Re: Q&A Radio Box Selection To Generate Text Report
                                  erolst

                                  Ok, as to your script (if you refer to the one from your post with the multiple screenshots), it was built on the wrong structure. Also, if you have a hierarchy of relationships, there is (only rarely) a need to store "grandparent" IDs in the "grandchildren" records. What you want to do is this:

                                   

                                  # [ from a Car layout ]

                                  Set Variable [ $carID ; Car::id ]

                                  Go to Layout [ Inspection (Inspection) ]

                                  New Record/Request

                                  Set Field [ Inspection::id_car ; $carID ]

                                  Set Variable [ $inspectionID ; Inspection::id ]

                                  Go to Layout [ Question (Question) ]

                                  Loop

                                    Exit Loop if [ Let ( $i = $i + 1 ; $i > 10 ) ]

                                    # [ a "magic value" which are usually frowned upon in programming ... ]

                                    New Record/Request

                                    Set Field [ Question::id_inspection ; $inspectionID )

                                  End Loop

                                  Go to Layout ( Inspection (Inspection) ]

                                   

                                  where you could see the 10 newly created question records of this inspection in a portal.

                                   

                                  Only question that remains is how to get the actual question text into the Question records.

                                   

                                  One strategy is to set up a "template" table (you may or may may not have seen that in all those other posts re questionnaires). That's very scalable and structured (eg store different lists for different circumstances) ...

                                   

                                  ... but if you need something easier to get going: store the questions as a return-delimited list in a field, put that into a variable, and within the loop, use the loop variable $i to pull out the respective line:

                                   

                                  # [ from a Car layout ]

                                  Set Variable [ $questionList ; Globals::questions ]

                                  Set Variable [ $carID ; Car::id ]

                                  Go to Layout [ Inspection (Inspection) ]

                                  New Record/Request

                                  Set Field [ Inspection::id_car ; $carID ]

                                  Set Variable [ $inspectionID ; Inspection::id ]

                                  Go to Layout [ Question (Question) ]

                                  Set Variable [ $questionCount ; ValueCount ( $questionList ) ]

                                  Loop

                                    Exit Loop if [ Let ( $i = $i + 1 ; $i > $questionCount ) ]

                                    # [ to accommodate lists of any size ]

                                    New Record/Request

                                    Set Field [ Question::id_inspection ; $inspectionID )

                                    Set Field [ Question::text ; GetValue ( $questionList ; $i ) ]

                                  End Loop

                                  Go to Layout ( Inspection (Inspection) ]

                                  • 14. Re: Q&A Radio Box Selection To Generate Text Report
                                    AnthonyTesoriero

                                    I really appreciate the help you're trying to provide but I'm not getting anywhere. Now with what I think is the  the proper restructuring of the database I cannot get the questions to set on the layout. I'm really getting frustrated. I also do not understand this portion of the script itself.

                                    Exit Loop if [ Let ( $i = $i + 1 ; $i > 10 ) ]

                                      # [ a "magic value" which are usually frowned upon in programming ... ]

                                    Once this script was implemented and initiated it created tens of thousands of new question records before I was able to stop it. I've messed with it 20 ways to Sunday but no success.

                                     

                                    Is there a way for me to post the file to be critiqued properly. Perhaps someone car find a simple error that I've made that for some reason I'm missing?

                                    1 2 Previous Next