3 Replies Latest reply on Aug 11, 2009 7:09 PM by comment_1

    how to go about this relationship to get a specific report?

    lucas_1

      Title

      how to go about this relationship to get a specific report?

      Post

      hello one and all,

       

      ok, let describe the environment first.  i have a survey table and a response table where the survey contains the name and the response is the answers to the 40 questions for the person of that survey.  example, 20 surveys, so there are 800 answers in the response table.  both connected in the relationship graph via the unique key of SurveyID.

       

      another relation, a traits table connected to a many-to-many intermediate occurrence (called TQ) to the question table via the unique keys TraitID and QuestionID.  TQ also has a weight field so that each trait can have a set of questions, each question assigned its own weight for that particular trait.  i think you can begin to see where this is going, here is the table group for what i am trying to do:

       

      relation graph 

       

      the point, to create a report of traits that shows the weighted average of each trait for each person surveyed.  put another way, have a subsummary part for each person in the survey, and under that, another subsummary part to show each of the 5 traits and then the weighted average and std.dev. next to each trait.  simple enough in theory.

       

      i can't get it to work. if i have the layout set to TQ, i get the traits part right, but not the persons.  if i have the layout set to survey responses_ATTR, i get the persons correct through the surveys_ATTR_PERSONS connection, but then the traits don't show and connect properly.  that is just trying to get filemaker to setup the parts (subsummary sections) correctly, not to mention the final step of properly calculating the weighted averages based on the multiple questions from the responses as setup in the TQ table.

       

      so, please, guide me.  i am not sure how to proceed to connect it all properly.  thank you in advance and have a great day.  lucas 

        • 1. Re: how to go about this relationship to get a specific report?
          Ender
             I think you'd have to base the sub-summary report in a layout based on the Survey Responses table.  But in order for the TQ data to show correctly, I think the relationship should be a one-to-many from TQ to Survey Responses.  You could fix that by adding a unique key to TQ and use that instead of QuestionID, or add the TraitID to Survey Responses and add it to the relationship.
          • 2. Re: how to go about this relationship to get a specific report?
            lucas_1
              

            i think i understand why you are suggesting to create a one-to-many relation between TQ and survey responses.  but frankly, i can not see either suggestion to implement that working correctly.

             

            i think the way to make it happen is to create a calculation table that stores the base calculations for each person surveyed based on a culmination of both the traits, the weights within, and the responses from the surveys.  a script can be written that is run and populates this calc table every time the layout is entered.  unless someone comes up with a relation way of doing it, this script to table method is the only way i see it working correctly.

             

            lucas 

            • 3. Re: how to go about this relationship to get a specific report?
              comment_1
                

              lucas wrote:
              have a subsummary part for each person in the survey, and under that, another subsummary part to show each of the 5 traits

              I agree with Ender. The way you have it, each response "knows" which question was answered - but not which one of the question's traits. So it's not possible to summarize the responses by trait.