1 Reply Latest reply on Dec 1, 2010 9:19 AM by philmodjunk

    Count Records With Criteria Calculation

    ChrisAustin

      Title

      Count Records With Criteria Calculation

      Post

      Hello. As a feature of my database I would like users to be able to see at a glance the number of products we supply each customer. In the hope of achieving this I have made a table with 2 fields in addition to the large products database. 1 field for the customer name (e.g. Wilkonsons) and 1 field for the number of products we supply them (e.g. 12). In plain english the Number of Products field would say:

      Search the customer field in the main products database (e.g. for Tesco) and count the number of records that are returned. Use this number as the value.

      Hope that made sense.

      Many Thanks,

      Chris.

        • 1. Re: Count Records With Criteria Calculation
          philmodjunk

          Instead of matching by customer names, you should match by a serial Id type customer number. What if you get two customers with the same name or a customer changes their name?

          If you establish this relationship between your tables:

          Table1::CustomerID = Table2::CustomerID

          Then you can define this calculation field in Table1: Count ( Table2::CustomerID ) to compute a count of all the related records in table 2 for the matching record in table 1.

          There are also ways to create a summary report that groups your products sold by customer and uses a summary "count of" field to report just the total product records. This approach does not require an added table, but also will not list customers for whom you did not supply any products at all.

          If you want to try the summary report method, this link may help you get started:  Creating Filemaker Pro summary reports--Tutorial