1 Reply Latest reply on Oct 8, 2010 2:36 PM by philmodjunk

    Count of occurneces within a join table



      Count of occurneces within a join table


      I have successfully designed and completed a join table named "ProdCust" that links products and customers.  On my Customers layout, I have a portal for the ProdCust join table where I can add products to a specific customer's file.  In a different table called "ItemDetail", I have data that is specific to each customer's product that shows enhancements (each enhancement a distinct record) made to that base product per customer.

      Ii would like to make my ProdCust join table on the Customer layout show the count of ItemDetail records there are for each specific Customer and product (in other words, for every Product noted in the join table for that Customer, how many ItemDetail records there are for that combination).  Is this possible? What is the best way of doing this?  I thought ading a calculated field in the ProdCust join table would work but I couldn't figure out the calculation.

        • 1. Re: Count of occurneces within a join table

          First, you need table Occurences setup in Manage | Database | Relationships that look like this:


          (You also have a link from ProdCust to Products, but that isn't a relevant relationship here.)

          That gives you a relationship between ProdCust and ProductDetails that looks like this:
          ProdCust::ProductID = ProductDetails::ProductID

          Then you can put Count ( ProductDetails::ProductID ) in ProdCust to report the number of related detail records.

          Note: I originally thought you needed: Customers----<ProdCust>---Products---<ProductDetails, but then I noticed that these details were specific to a given product and customer.