1 Reply Latest reply on Apr 13, 2012 12:34 PM by philmodjunk

    Trying to Analyze a Subset of Related Records

      Title

      Trying to Analyze a Subset of Related Records

      Post

      So I have a Customers table and a Products table. Customers can have many Products.

       

      I am trying to create a found set of customers that own one product, and then see what their ownership numbers are like for all the remaining products.

       

      Any ideas? I am ALWAYS running into issues where my summary fields or counting fields are coming up with the total count of customers in the found set, not counting customers within for each individual title. Or they are counting just the first record in a a related field instead of going through all of them.

      I have an OK grasp of SQL and relational db design, but I feel like I'm just not grasping something with FMPro that is keeping me at 90% here, which is unfortunately not close enough!

       

      Can anyone recommend any good very technical books or websites or documentation to read? 


      Thanks,


      Jason

        • 1. Re: Trying to Analyze a Subset of Related Records
          philmodjunk

          I am ALWAYS running into issues where my summary fields or counting fields are coming up with the total count of customers in the found set,

          That's what summary fields are supposed to do if referenced from a layout based on the same table where the field is defined.

          Try this approach and see if it works for you.

          a) perform a find on the customers table for all customers with a related record for the specified product.

          b) use go to related records with the "match found set" option to pull up all related product records for all customers found in step a.

          c) pull this up on a summary report layout where summary fields and sub summary parts produced the desired statistics. Be sure to sort your records to be consistant with the sub summary parts specified for your report.