1 2 3 Previous Next 33 Replies Latest reply on Jun 3, 2014 10:26 AM by philmodjunk

    Charting Trends With A Pie Chart For Safety Audit Database.

    CalebTurner

      Title

      Charting Trends With A Pie Chart For Safety Audit Database.

      Post

           Hi, 

           So I've created this Safety Audit Database that is very simple, as a matter of fact it is one table simple complete with two layouts for 2 devices, iPad and iPhone. The audit basically asks 12 questions that are answered by checking yes or no by using a checkbox set with two custom values, yes you guessed it, yes and no.  The fields that hold question data are labeled Question 1, Question 2, and so forth. The fields that the checkbox set info goes in to are labeled as Question 1 A, Question 2 A, etc...  Basically I need two charts, one pie chart that finds all the questions answered with a no and shows the top 5 by specifying two dates and finding the data for all audit records that occurred between those two dates, I do have a date field that is automatically populated once a record is created. The other is a bar graph that shows every no recorded for every question for every record.  I've botched every attempt I've made to accomplish this... PLEASE HELP!

      Screen_Shot_2014-05-09_at_8.45.52_AM.png

        • 1. Re: Charting Trends With A Pie Chart For Safety Audit Database.
          philmodjunk
               

                    The fields that hold question data are labeled Question 1, Question 2, and so forth.

               That is often not the best design approach for this type of data. Using a related table with one record for each question's response can be a more flexible way to record and manage this data.

               Without that type of "structure" to your data, it becomes very difficult to count how many times each question was answered yes or no and that then makes charting your data complex.

          • 2. Re: Charting Trends With A Pie Chart For Safety Audit Database.
            CalebTurner

                 Awesome, I'm obviously a filemaker newbie, so I've created a table named Audit Results, I have the question responses all setup within that table but as lookup fields, that will not do the trick will it? How do I get it to create a record for every question's response?

            • 3. Re: Charting Trends With A Pie Chart For Safety Audit Database.
              philmodjunk

                   I'm posting a link for to a more detailed description of this at the end of this message.

                   What you need are really 3 tables:

                   Audits-----<Audit_Question>----Questions

                   Audits::__pkAuditID = Audit_Question::_fkAuditID
                   Questions::__pkQuestionID = Audit_Question::_fkQuestionID

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

                   Your "yes/no" and comment fields would be defined in Audit_Question. Just one yes no field and one comment field needed in that table. The text of each Question would be entered into a text field in the Questions table--one record for each of your 12 questions. Should you need more questions in the future, you would simply add another record to this table and enter the question.

                   I almost missed how your layout is currently set up. Are you using a Slider with 12 panels to show the 12 questions? One problem with that is you will have to modify your layout design each time that you add or remove questions from your audit.

                   While I could set that up in a slider that does not need such design changes, it's not something I can really help a new user set up as it's pretty sophisticated--using more relationships, script triggers and a three panel slider where two of the panels are empty.

                   I suggest a simpler layout design--at least at first in order to get this working. You can then transition to a more sophisticated approach as you learn more about how to set this stuff up in FileMaker.

                   Put a one row portal with a scroll bar to Audit_Question on your layout. Place your Question field from Questions in this portal row along with the yes no field and the comment field. You can drag the resize handles to create a tall portal row and place the fields inside the portal row much like you show in your screen shot. Your touch users can flick the portal rows up and down to move from question to Question. There really won't be a need for the buttons for each question with this approach.

                   You can use this script to set up the needed records in Audit_Question. You'll want to run this script once each time you start a new Audit:

                   Set Variable [$QuestionList ; value: List ( Questions::__pkQuestionID ) ]
                   Set Variable [$AuditID ; Value: __pkAuditID ]
                   Freeze Window
                   Go to Layout ["Audit_Question" (Audit_Question)]
                   Loop
                      Set Variable [$K ; value: $K + 1 ]
                      Exit Loop If [ $K > ValueCount ( $QuestionList ) ]
                      New Record/Request
                      Set Field [Audit_Question::_fkAuditID ; $AuditID ]
                      Set Field [Audit_Question::_fkQuestionID ; GetValue ( $QuestionList ; $K ) ]
                   End Loop
                   Go To Layout [Original layout]

                   For more on Audits, Questionaires and Surveys: Need aid on generating a report from a survey layout.
                    

                    

              • 4. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                CalebTurner

                     Hi,

                     I have made the changes you recommended, I think, and have added a new audit_iPad layout. Will the script change that begins with every new audit change due to users mainly using iPads? Also, I made the _pkAuditID field and _pkQuestionID fields auto serial fields. Do I need to also change the _fkAuditId field and _fkQuestionID field to lookup fields or also set these as auto serials?

                     On another note, since this data needs to be based on dates, would I add a date field in the Audit_Question table for charting trends in the questions marked "no"

                • 5. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                  philmodjunk

                       I can't quite understand this question: "Will the script change that begins with every new audit change due to users mainly using iPads?"

                       Change what exactly?

                       Yes, you can add a date field to the Audit_Question record--the join table in this many to many relationship. You may want to set the date field to auto-enter either the creation date or the modification date so that each record has a date entered automatically.

                  • 6. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                    CalebTurner

                         Sorry about that, will the script you provided need to change if I have one layout for desktops and one layout for iPads?

                         Also for some reason when I'm inputting your script, when I try to input the set field script steps inside the Loop I get this response, "An operator here (e.g. +.-,*,....) is expected here.

                    • 7. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                      philmodjunk

                           With either layout, you need the script to create the records in the join table so that your user can record their responses in them. (In the thread I posted with more info on questionnaires and surveys, the same field is called "Responses" in the example design posted there.)

                           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: Charting Trends With A Pie Chart For Safety Audit Database.
                        CalebTurner

                             Maybe I've bitten off a little much to start off with, I've set up the script and when it runs the fields Date, Location, Name, etc become active in my Audit_Question Table,  but nothing shows in the portal... Could the fact that the fields _fkAuditID & _QuestionID are not setup to be auto serials such as their counterparts _pkAuditId and _pkQuestionID?

                        • 9. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                          philmodjunk

                               Best guess is that there are errors in your script. I suggest that you post the exact script that you have created. The picky details involved can be critical to correct script execution. wink

                               To post a script to the forum:

                                 
                          1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                          2.      
                          3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                          4.      
                          5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                          6.      
                          7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                          • 10. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                            CalebTurner

                                 Alright, quick walkthrough...

                                 I've created all three tables:  Audits--------<Audit_Question>--------Questions

                                 Audit Table includes the following fields: _pkAuditID setup as a number field with an Auto Enter Serial AD1

                                 Audit_Question Table includes: _fkAuditID, _fkQuestionID, Date (auto-enter) , User, Attendant Name, Location, yes or no field, comment

                                 Questions inlcudes: _pkQuestionID (auto-serial, Q1),  with 12 records, each the question text

                                  

                                 The main layout is linked to the audits table.

                                  

                                 I've inserted the portal with the _pkQuestionID and Question field as well as the yes_no field from the Audit_Question Table

                                 Outside of the portal is the User, Location, and Date field from the Audit_Question table.

                                 The script is as the picture describes:

                            • 11. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                              philmodjunk

                                   Apologies. There's an embarrassing mistake in my suggested script. The first step won't pull the needed list of Question ID's into the $QuestionList variable.

                                   Add one more field to Questions. Define it as a summary field to produce a "list of _pkQuestionID" and name it sIDList.

                                   Modify the first part of the script to script to be:

                                   Freeze Window
                                   Go To Related Record [Show only related records; From table: Questions; Using layout: "Questions" (Questions) ]
                                   Set Variable [$QuestionList ; value: Questions::sIDLIst ]
                                   Go to Layout [ Original Layout ]
                                   Set variable [$AuditID...

                                   Note: this is a new type of summary field introduced in FileMaker 13. If you are using an older version of FileMaker Pro, let me know as we'll need to use a different method to get the correct list of ID's into $QuestionList.

                              • 12. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                                CalebTurner

                                      I'm trying to set up the audit so that the 12 question audit is done by an auditor who is identified by the user field. The date, name of the safety attendant the audit is on, and the location of the audit is needed to show who about, where, and when these 12 questions were asked.  

                                • 13. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                                  philmodjunk

                                       Note that the other thread on surveys that I recommended uses more tables and relationships.

                                       Generally speaking, you wouldn't record the name of the auditor and the safety attendant in the Audit_Question table for the simple reason that you don't want to record this info over and over again for each question answered. But the current design does not have the needed additional table for recording this info.

                                       Depending on what a record in Audits represents, you may be able to define fields in Audits for recording:

                                       1) The auditor

                                       2) The attendant

                                       3) The date of the audit

                                  • 14. Re: Charting Trends With A Pie Chart For Safety Audit Database.
                                    CalebTurner

                                         I changed the script and for some reason I still cannot get it to work, I'm uploading pictures of the script and current setup.

                                         Audits>-----------<Audit_Question>-----------<Questions

                                         Audits Table :: _pkAuditID (Auto-Serial) ,Date, Location, User, Safety Attendant

                                         Audit_Question Table :: _fkAuditID, _fkQuestionID, yes_no, comment

                                         Questions Table :: _pkQuestionID (Auto-Serial) , Question, sListID (Summary)

                                         The next post will be a pic of the portal setup

                                          

                                    1 2 3 Previous Next