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.
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?
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::__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 ]
Go to Layout ["Audit_Question" (Audit_Question)]
Set Variable [$K ; value: $K + 1 ]
Exit Loop If [ $K > ValueCount ( $QuestionList ) ]
Set Field [Audit_Question::_fkAuditID ; $AuditID ]
Set Field [Audit_Question::_fkQuestionID ; GetValue ( $QuestionList ; $K ) ]
Go To Layout [Original layout]
For more on Audits, Questionaires and Surveys: Need aid on generating a report from a survey layout.
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"
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.
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.
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.
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?
To post a script to the forum:
- You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
- 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.)
- If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
- 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.
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:
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:
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.
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.
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
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 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