4 Replies Latest reply on Nov 29, 2010 1:28 AM by NormanStock

    Counting values

    NormanStock

      Title

      Counting values

      Post

      Forgive the possibly very simple question - but I am not an experienced user of FileMaker (despite using it for years). I have a database which has a field which only contains values from a value list. What I would like to do is display how many of each value in that value list the whole database contains. For example (and this is not describing my database but the principle is the same and hopefully easy to understand) if the database was of customers and their orders with a field that was the customers name (from a value list) and each record was of an order they had placed, what I am trying to obtain is a list of the customers (the names in the value list) and the number of orders that they have placed - if you like a league table of customers based on the number of orders placed.

      Thanks in advance for any help you can give

      Norman

        • 1. Re: Counting values
          philmodjunk

          A Creating Filemaker Pro summary reports--Tutorial can be set up to do this. A related table can also be used.

          Using a summary report, you can set up a list view layout with no body part. Instead add a Sub Summary part, when sorted by customer to the layout and place customer name field in this part. A "count of" summary field can be defined and placed in this same part to report the number of records for each customer. Your records must be sorted by customer in order for this report to properly display the info.

          In FileMaker 10 and newer, this report can be viewed in browse mode. In older versions, you have to switch to preview mode or print it.

          If your list needs to be sorted by count, you'd need a related table of info where you have one record for each client. A calculation field using the count function (Count ( OrdersTable::OrderID ) ) can then compute the total orders per client. This approach will allow you to sort by count and can include the names of clients have not placed any orders.

          • 2. Re: Counting values
            NormanStock

            Many thanks for this - have implemented the first part and got that working fine. Will have a play with creating the table so as to be able to sort the results by count over the next few days.

            • 3. Re: Counting values
              philmodjunk

              If you need option 2, then you don't have to create a summary report layout at all. They are two different approaches that have nearly, but not quite, the same result.

              • 4. Re: Counting values
                NormanStock

                Cheers - I tried the first option first as it was the easiest. Now I have also tried the second and got that working - the second is the mist useful as it allows me to sort them, but both gave me valuable experience in using FileMaker. Many thanks again.