7 Replies Latest reply on Oct 17, 2011 11:17 AM by MikeEdwards

    Count Fuction



      Count Fuction


       Can the count calculation be used to give numbers for a particular value for a field utilizing a value list?

      For istance count of # or records where the value = ordered in a staus field?

        • 1. Re: Count Fuction

          Not as settings you specify in the calculation. There are ways to get such a count using the count function or by using a "count of" summary field but it needs some "help" to make it work and the specifics depend on what kind of report you want to see.

          Here's one simple example, but I freely admit that this option may not be what you need, you'll have to tell us more...

          You can use a "Count of" summary field in a sub Summary part "when sorted by Status". If you then sort your records by Status, you'll see a count for each value that can appear in the status field. The individual records can be included in the layout or hidden so that all you see is a count for each value in the status field.

          • 2. Re: Count Fuction

             My idea was to create several calc or summary fields which would display the count of the value I wanted.

            • 3. Re: Count Fuction

              I need a more detailed description of how you want your layout to look.

              Let's say that status field has three possible values: Quoted, Ordered, or Shipped and you want the count of each.

              a report that looks like this:

              Quoted:   300
              Ordered:  400
              Shipped:  900

              Can be set up with a single "count of" summary field on a layout based on the table (Invoices?) where you have defined this status field.

              You create a layout based on Invoices
              You remove the body part and replace it with a sub summary part. Specify that this sub summary part be "when sorted by" the status field.
              Put the status field and the summary field inside this sub summary part.
              Sort your records by the status field and you'll get the above report.

              Presumably, you'll want to limit this report to records from a specific date range and/or other criteria. You can use such criteria in a find to pull up just the records you want included in this count. Just be sure to sort them by the Status field or you'll get a blank screen. (If you have FileMaker 9 or older you must preview this report in order to see it.)

              • 4. Re: Count Fuction

                 This is to be along side a chart in a basic layout and examines all records within the table. I don't think a sub summary will work. How about get found count?

                • 5. Re: Count Fuction

                  Get Found count gives you a single count of the current found set. Not impossible with scripting but I think we can do better.

                  Take the same summary field and put it inside a one row portal. Use a portal filter expression to limit the records to those matching each category. The relationship you use for the portals will depend on the structure of your database.

                  Here's one possibility:

                  LayoutTableOccurrence::anyfield X PortalTableOccurrence::anyfield

                  The table occurrences on both sides of this relationship can refer to the same table or different tables and the X operator will match any record in LayoutTableOccurrence with all records in PortalTableOccurrence.

                  With this set up, the portal filter expression must do all the needed filtering to restrict the count to just the records needed. This can include other criteria than just the status if you don't want to count every record in the table with a specified status.

                  Other relationships can also be used here.

                  • 6. Re: Count Fuction

                    Do you have FileMaker Pro Advanced? A custom function can be used to accomplish this. One I developed, called TypeSumField() comes to mind. Googling that should find it for you.

                    • 7. Re: Count Fuction

                       Do not have advanced.