1 2 3 Previous Next 32 Replies Latest reply on Sep 22, 2015 2:36 AM by olesimonsen

    Another Survey question

    olesimonsen

      Title

      Another Survey question

      Post

      Hi,

      I have spend quite some time on the forum and the concept of creating a survey/grading using filemaker is beginning to sink in however I am still left with some questions that present themselves through my many attempts to set up the solution.

      My main source of inspiration has been this post:

      http://forums.filemaker.com/posts/beeff7fcd9

      However it does not fully cover our needs.

      A short explanation as to what we need in this solution.

      Overall the solution is a grading of new employees. The process is based on two interviews (Very similar in content but performed by two different interviewers) and one hands-on assessment.

      Interviewer and hand-on assessors assessments of one candidate has to be recorded and stored. Reports must be generated either all assessments, all assements in an area, all assessments for a candidate, alle assessments given by an assessor etc.

      So to sum up. One candidate will have 3 assessments:

      Two interviews with grading of different areas (Self awareness, leadership potential etc) with a grade 1-3 and a comment by the assessor. There are 7 different areas.

      One job hands-on assement with a grade 1-5 and a comment by the assessor. There are 6 different areas.

      My questions are:

      1. Will this structure suit our needs.

      2. How do I create the surveys/Assessments for the candidates on a separate layout

      3. Is there a way to make an administrators dashboard where new candidates, assessors and assessments can be created and statistics/reports can be viewed. To which table should this be linked?

      We would highly appreciate any help with this!

      Initially I have come up with a database structure like this:

      "Questions" are actually grading areas (Self awareness, leadership potential, task #1, 2, 3 in the hands-on assessment.

      Voila_Capture_2015-07-31_11-03-12_AM.png

        • 1. Re: Another Survey question
          philmodjunk

          This looks to be a reasonable adaptation of the original concept--though you are only showing the keys in this screen shot, not the other data fields needed to make this work..

          Your layout would be based on Responses. It can be a list view or form view layout with a set of records, each linked to a different question. You'd start by creating a new record in Candidates. You'd then select an Assessor and an Assessment. The necessary Assessment and assessor ID's can be initially selected in global fields on a layout based on Candidate. I am assuming that you would have a number of fields to fill out so it would make sense to start by creating and completing a new record on your Candidates layout with two popup menu formatted global fields for selecting the Assessor and Assessment needed. A script can then do this to set up the assessment for a given candidate:

          #field names that start with "g" have global storage specified and can be defined in any table in your database
          Set Variable [$CandidateID ; value: Candidates::__PKCandidateID]
          Go to layout ["Assessment" (Assessment) ]
          Enter Find Mode []---> clear the pause check box.
          Set Field [Assessment::__PK_assessmentID ; Globals::gAssessmentID ]
          Perform Find[]
          Set Variable [$QuestionIDList ; value: List ( Questions::__PKQuestionID ) ]
          Go To Layout ["Responses" ( Responses ) ]
          Enter Find Mode []
          Set Field [Responses::_FKCandidateID ; -1 //any value that will never match any candidate will do here ]
          Set Error Capture [on]
          Perform Find [] --> This is to make sure that you start with an empty set so that script ends with a set of records only for the specified assessment and candidate.
          Loop
              Set Variable [$K ; value: $K + 1 ]
              Exit Loop IF [$K > ValueCount ( $QuestionIDList ) ]
              New Record/Request
              Set Field [ Responses::_FKCandidateID ; $CandidateID ]
              Set Field [ Responses::_FKQuestionID ; GetValue ( $QuestionIDList ; $K ) ]
              Set Field [ Responses::_FKAssessorID ; Globals::gAssessorID ]
          End Loop

          This script leaves you on the Responses layout with a new set of Responses linked to the correct Question, assessor and candidate.

          • 2. Re: Another Survey question
            olesimonsen

            Thank you for a very fast response!

            Yes, it makes sense to start wit the creation of a new candidate and have the workflow towards creation of assessments start here.

            I did not think of the global fields so this is somewhat new to me. What you are saying is that on the candidates layout I have to select an assessor and an assessment and set a script to capture these values as global variables (session dependent) and store these in a table ("Globals") in a field named "gAssessorID" and "gAssessmentID" respectively?

            Can you share any details how I would set up the workflow for selecting assessor/assessment? Not all assessors can undertake all assessments. I plan to store this qualification in the Assessors table.

            As each candidate will have three assessment I suppose this action must be performed 3 times?

            The script seems a little intimidating but I have put it together after creating the missing fields and will try to get it working.

            This is by far the most advanced solution I have worked on so I apologize for any questions regarding the obvious ;-) 

            Thank you again for your help and advise!

            • 3. Re: Another Survey question
              philmodjunk

              What you are saying is that on the candidates layout I have to select an assessor and an assessment and set a script to capture these values as global variables (session dependent) and store these in a table ("Globals") in a field named "gAssessorID" and "gAssessmentID" respectively?

              Not quite. By placing global fields to gAssessorID and g AssessmentID directly on the Candidate layout, the user can select these values before performing a script to use those values to set up the Assessment questions. The only reason to use global fields here instead of variables is that these are fields you might format on a layout in order for the user to enter or select data that your script will then use in the context of either another record and/or another layout. If this is not the case for one of these ID's, then a variable can be used instead of a global field. (That's why a variable is used for the Question ID's.)

              Presumably, you would format your gAssessorID with a drop down list of Assessor ID's and names. The same would be done with gAssessmentID where you could select an assessment from a value list of Assessment ID's and names. Then, you click a button to run the script or you might use a script trigger set on the gAssessmentID field so that selecting an assessment starts the script.

              And you have a choice here. You can select an AssessmentID each time and run the script once for each assessment, (I have been assuming that the assessor might not be the same person for each assessment), or you can set up a script that simply performs the sample script shown three times, once with each assessment. Picking from a value list is a bit more flexible, you can select from different assessment options for different positions, but a loop that loops through all three assessments at the same time would be a bit faster.

              • 4. Re: Another Survey question
                olesimonsen

                Hi Phil

                The twist with the global fields is very nice. As I understand it this approach allow the selection of the Assessor and/or Assessment to be done on any layout as long as it is related to the relevant tables? This would allow for candidates to be created and stored in one file with their stored grades from the assessments and the actuaI grading in a different file containing only the tables used for data collection. 

                The most elegant solution would be to create all three assessments all at once. Actually we are assessing 12 candidates with 3 assessments each in every session we run, so a workflow with the creation of the candidates, a central selection of 12 candidates and assignment of assessors leading to creation of all assessments in one go would be fantastic but that must be coming in ver 2.0 ;-)

                Your advice has given me a very good push in the right direction. I will put some effort into this and maybe return with the questions that are likely to come up....

                • 5. Re: Another Survey question
                  philmodjunk

                  Global fields can be edited on any layout in your file period, but only within the same file.

                  What I've outlined can be part of a larger script that loops through the candidates and loops through the assessments to set all the Response records up for all candidates for all assessments.

                  • 6. Re: Another Survey question
                    olesimonsen

                    Hi Phil,

                    The alteration so far:
                     
                    In order to distinguish between the different assessment activities (interview1/interview2/Hands-on) I have included a new table:
                     
                    AssessmentSection > Assessment > AssessmentActivity
                     > AssessmentArea (See updated relationship graph below)
                     
                    AssessmentArea with 2 records (Interview and Hands-on)
                    Assessment Activity with 3 records (Interview #1, Interview #2, Hands-on)
                     
                    A table with globals is created with two global fields; “gAssessorID” and “gAssessmentActivity”. 
                     
                     These are presented on the Candidate layout in a pop-up. The values are from two value lists based on values from “AssessorID” and “AssessmentActivityID” respectively. (Will make these conditional through self-join tables from the global table, so that only assessors qualified for interviewing are displayed if interview 1 or 2 is selected in the Assessment Activity and hands-on assessors are displayed when hands on assessment is selected).
                    In the pop-up there is a button triggering the “create Assessment” script.
                     
                    The script is:
                     
                    Set Variable [$CandidateID ; value: Candidates::__PKCandidateID]
                    Go to layout ["Assessment" (Assessment) ]
                    Enter Find Mode []
                    Set Field [Assessment::__PK_assessmentID ; Globals::gAssessmentID ]
                    Perform Find[]
                    Set Variable [$QuestionIDList ; value: List ( Questions::__PKQuestionID ) ]
                    Go To Layout ["Responses" ( Responses ) ]
                    Enter Find Mode []
                    Set Field [Responses::_FKCandidateID ; -1  ]
                    Set Error Capture [on]
                    Perform Find []
                    Loop
                        Set Variable [$K ; value: $K + 1 ]
                        Exit Loop IF [$K > ValueCount ( $QuestionIDList ) ]
                        New Record/Request
                        Set Field [ Responses::_FKCandidateID ; $CandidateID ]
                        Set Field [ Responses::_FKQuestionID ; GetValue ( $QuestionIDList ; $K ) ]
                        Set Field [ Responses::_FKAssessorID ; Globals::gAssessorID ]
                    End Loop
                     
                    When the script fires it says that no records are found? 
                     
                    I might have a problem with the relationship between "Assessment activity" and "Assessment Area”. Assessment activity (Interview and Hands-on) can have 2 interviews but only one hands-on area, making it a one-to-one relationship? 
                     
                    I might be way off, but I think the original table layout did not cater for a report with all interview responses together but only responses from interview #1 assessments and responses from interview #2 seperately. Is this correct?
                     
                    If so are the tables and relationships correct?
                     
                     
                     
                     
                     
                    • 7. Re: Another Survey question
                      philmodjunk

                      When the script fires it says that no records are found? 

                      That suggests that either you have the wrong value in gAssessmentID or the wrong values in __pkAssessmentID.

                      If you have FileMaker Advanced, you should run this script in the debugger watching for problems and error codes while you step through the script. At the same time you can use the data viewer to check the values of fields and variables while also checking to see if key calculations are evaluating correctly.

                      I'm not totally convinced that you need those added tables and records as this would seem to be data I'd expect to find in the Assessment table. What does one record in Assessment represent?

                      I might be way off,...

                       You can get any number of different reports from a layout based on the Responses table. You can show results for a single assessment of a single candidate or show reports from multiple candidates and/or multiple assessments.

                      • 8. Re: Another Survey question
                        olesimonsen

                        Hi Phil,

                         
                        I think I have to admit I had this the wrong way round. The concept I failed to grasp is that this is all based on the responses. You are quite right that the two additional tables stating the activity are not needed and only ads to the confusion.
                         
                        Just to be absolutely clear the AssessmentID is a unique ID for either of the three assessment activities (Interview1, interview 2 and hands-on) and the “gAssessmentID" should get its values from this table/field.
                         
                        So now I have the tables back to the original layout and the script in place. 
                         
                        When the script is launched after selections in gAssessmentID and gAssessorID the layout actually changes to the response layout but no files are created. In the script debugger the script stops at the beginning of the loop with the error “(401) no records match the request”.
                         
                         
                        • 9. Re: Another Survey question
                          philmodjunk

                          And at the time that the script stops. What layout are you on? What value is in gAssessmentID?

                          You can use the data viewer to check the value of this field. You will be able to see what layout is current on the screen.

                          • 10. Re: Another Survey question
                            olesimonsen

                            Hi Phil,

                            Clearly there is something in this I don´t fully understand. Please bear with me...

                            The script stops when entering the loop. I guess I am on the Response layout at this time?

                            My problem is the gAssessmentID field as the data viewer indicates that no value is inserted into __PKAssessmentID.

                            I place the gAssessment field in a popup window on the Candidate layout together with an gAssessorID field. 

                            What values go into the gAssessmentID field so that the field AssessmentID can be populated with this value? 

                            (Script step: Set Field [Assessment::__PK_assessmentID ; Globals::gAssessmentID ])

                            And one last question which might be very silly indeed, but how many records exist in the Assessment Table? 

                            I suspect 3 ...

                            Field: __PKAssessmentID/Field: AssessmentName

                                           (1/AutoEnter)              Interview #1

                                           (2/AutoEnter)                Interview #2

                                           (3/AutoEnter)               Hands-on assessment

                            Is this correct?

                            • 11. Re: Another Survey question
                              olesimonsen

                              Seem to have the script running... sort of ;-)

                              Some problems....

                              1. If a new candidate record is selected on the candidate layout prior to running the script, the CandidateID and variable $Candidate are loaded correctly. If I rerun the script for the same candidate these fields are empty.

                              2. As the script moves from find to set field (__PK_AssessmentID) the value disappears from this field. It re-appears as the script moves to the next step, and disappears again as the next find is entered. I suppose this is ok as the field is only required to find the related questions in the first find (Creating the QuestionsIDlist)

                              3. As the script progresses it seems in the data viewer that the §questionIDList  is loaded with the correct questions from the selected assessment, but still when entering the LOOP step an error is displayed "(401) no records match the request".

                              4. Stepping through the "set field" steps on the responses layout the values are inserted correctly for candidateID, questionID and AssessorID.

                              5. On the last line "End loop" of the script pressing the "proceed button" in the script debugger moves the script back to the "Set variable $K; value: +1". Stepping through the the last part of the script again loads another Response but pointing to the same question as the previous. If the script is just left to run it loads and endless amount of identical responses records.

                              I think it might be a very trivial fault but I am having difficulties finding the bug...

                              • 12. Re: Another Survey question
                                philmodjunk
                                       
                                1. $Candidate is not a field and it will not retain a value once the script exits. Beyond that, you'll need to describe in more detail what you mean by "rerunning" the script and why you are doing that.
                                2.      
                                3. Are you watching the script in the debugger? It's normal for all non-global fields to go blank when you enter find mode. The data isn't gone it's just that your layout is in a different mode that cannot display it until you return to browse. If you manually enter find mode on a layout, you'll see the same change take place.
                                4.      
                                5. This variable should not be loaded with actual questions, but with the IDs that identify them. Then another step takes one value from this list and uses it as find criteria. So the fact that the variable is loaded with the correct list of ID's doesn't isn't all that has to happen for the find to work.
                                6.      
                                7. The problem is that you don't set the variable $K to the value +1. You set it to the expression $K + 1 so that you add 1 to the value with each iteration of the loop. Your current script keeps setting the variable to 1 each time.
                                • 13. Re: Another Survey question
                                  olesimonsen

                                  Hi again,

                                  Your help i really appreciated!

                                  Finally I got the script working. Thank you for the great patience!

                                  The re-running of the script refers to the fact that each candidate must go through three assessments and therefore the script has to be run three times for one candidate to produce three sets of responses for each assessment. Also this is up and running now.

                                  I realized there was a many-to-many relationship between assessments and questions as one assessment can have many questions but one question can also relate to more assessments (Some questions are common to all assessments). I have set up a join-table between the two with a AssessmentQuestionJoinID field and the two foreign key field linking to assessment and question table primary keys.

                                  I think the script need to be modified to accommodate for this?

                                  I Can create questions and through a portal on the questions layout create records in the AssessmentQuestionJoin table, the script does not find the response records for a specific assessment. Any ideas?

                                  I have an additional question which may have an obvious solution.

                                  Previously we had all the question with the answers in the same table, so it was no problem to have a single layout with each question/response on a seperate tab, so going through the assessment would just be a matter of going through the tabs.

                                  Now a number of response records are created through the script all relating to a specific question, in a specific assessment assessed by a specific assessor. I there any way to present each question with the response fields in form view? 

                                  • 14. Re: Another Survey question
                                    philmodjunk

                                    I think the script need to be modified to accommodate for this?

                                    Actually no. The List function can still reference the Questions table by "tunneling through" the new join table between assessments and questions. This will still produce the same list of Question ID's.

                                    I Can create questions and through a portal on the questions layout create records in the AssessmentQuestionJoin table, the script does not find the response records for a specific assessment. Any ideas?

                                    I don't understand what you are trying to do here. The script that we have been discussing would CREATE response records for each candidate so FINDING response records in this script does not make sense. Perhaps you are creating a different script for a different purpose here?

                                    Previously we had all the question with the answers in the same table

                                    No. Your relationship graph shows one table for questions and another for responses. This relationship should not have changed even though you now have a join table between assessments and questions.

                                    I there any way to present each question with the response fields in form view? 

                                    This should have been in place already and should not have been changed by making the relationship between questions and assessments a many to many relationship. You use a layout based on the responses table, but include fields from the related Questions table to display the question for which that response record will record an answer.

                                    1 2 3 Previous Next