3 Replies Latest reply on Jul 30, 2009 4:00 PM by philmodjunk

    master-detail relationship in fm10



      master-detail relationship in fm10


      hello one and all,


      i am trying to create a survey in fm.  the master table is for each survey, it contains when the survey was created and the name of the person being surveyed.  the detail table contains responses for each of the 40 questions for that survey created under the master.  the master-detailed is connected in a one-to-many relation in the Relationship Graph via a auto-serial field "Survey ID".  so at present i have 20 surveys/records in the master table and 40 answered questions in each survey so the detail has a total of (20 * 40) = 800 records.  there are three people being surveyed, so personA has 2 surveys, personB has 10, and personC has 8.  good.


      ok, i want to create a single report.  the Header contains the total survey, i.e., 20.  then three subsections, one for each person with their name and the number of surveys for that person, i.e., personA has 2 surveys.  then under that 40 lines, one line for each question in the survey with each line showing the count of responses, the average, and std.dev. for each question.


      that should be pretty straightforward, but i can't seem to get it to work using "SubSummary when SortedBy"  even though i sort the records appropriately.  i can get the header with 20 and then each subsection for persons, but it won't show the 40 averages.  or, the 40 averages broken down by the three persons and their individual totals, but not the entire total of 20 at the top in the header, the header only shows a total of 1.


      so, how do i negotiate fm to make this report the way i want it?  thank you in advance and have a great day.  lucas

        • 1. Re: master-detail relationship in fm10

          Did you base your layout table on the detail table or the master table. You need to specify the detail table in order for this to work.


          You need multiple sets of sub summary part for this to work. It sounds like you've set up most of this already. The trick is to make your "40 questions" section a sub-summary sorted by question and then delete the body part from your layout entirely.


          The statistical values you want can be set up as summary fields and placed in this newly added sub-summary part.

          • 2. Re: master-detail relationship in fm10

            yes, one of my "experiment" reports is exactly that, and i did figure out how to show the count of surveys (master table) for each person under their subheading by creating another occurrence on the graph and connecting it back to the original survey's (master table) occurrence via the persons field.  then under the subheading i reference the count field from survey 2 instead of survey 1.  it works, but i don't understand why that works. can you explain that to me please.


            and finally, i want to see the total number of surveys in total under the heading or very top of the report.  i can't seem to get the 20 count at the top there.  how do i do that?


            i come from a traditional client/server background using delphi and oracle/firebird.  this non-typing way of doing things is still foreign to me, but i am willing to learn to get my head around it.  thank you for your help.  lucas 

            • 3. Re: master-detail relationship in fm10

              "I want to see the total number of surveys in total under the heading or very top of the report. I can't seem to get the 20 count at the top there. How do i do that?"

              The 20 is the number of records in your master table, right? Define a "Count of" summary field in your main table that "counts" a field that is never empty. Place this count field in your header.


              The thing you have to stay constantly aware of in Filemaker is that you are three things to control what data appears in your report: The relationships, the found set and your current layout. When you define a summary field, place it in a sub-summary part and sort your records by the sub-summary's specified field, you group your current found set  or records (this is roughly the same as a "record set" linked to forms in some other database systems" by that value. This enables filemaker to use your summary fields to report subtotals for each group of records that normally appear listed below a sub-summary part. In this case, there is no data we need to display from each individual records, so we can delete the body and only report the sub-totals.


              Hope that makes sense.