8 Replies Latest reply on Aug 20, 2012 2:22 PM by disabled_JackRodgers

    Count number of specific values

    ultranix

      Title

      Count number of specific values

      Post

      Hey, I have a field, in which only 3 status values are available (determined by drop-down list): Active, Cancelled, Done.

      So how can i count the total number of each status values?

      The way i know adds too many fields - 6, i.e. 2 fields per each status (one for extracting status, another for summary).

      Is there a way to trim the number of additional fields to 3? i.e. 1 field for each status count?

        • 1. Re: Count number of specific values
          philmodjunk

          A lot depends on your layout design.

          A simple "count of" summary field, a sub summary part and sorting your records by the status field can show the totals.

          A portal can list the counts for each status using relationships and/or portal filters.

          If you have FileMaker 12, you can use the Execute SQL function to get these counts.

          It also depends on two other details:

          Whether you need to use these totals in subsequent calculations or just to display them.

          Whether this count is for all records in your table or a user specified subset of them.

          • 2. Re: Count number of specific values
            ultranix

            It's a list view with no sub summary parts, and i'm not intended to get any.

            I just want to have a top row, in which i could have it basically appearing in the exact same order:

            ____________________________________

            Active: 4 | Cancelled: 2 | Done: 5

            [And then it's the list of prizes]

            ____________________________________

            yes, it's only for display purposes.

            It may be all records or specified subset, depending either all records are shown, or just specific found set.

            I don't have Filemaker 12, it's way too slow in comparison to Filemaker 11, and new features doesn't convince me to go for slower...

            • 3. Re: Count number of specific values
              philmodjunk

              Take a look at "option 2" in this link: Sum_Calculation based on condition You can set up 3 one row portals to show a single horizontal row of your three counts.

              It may be all records or specified subset, depending either all records are shown, or just specific found set.

              You'll have to explain that in more detail. The above suggestion can be made to work, but you may need to include additional details in either the relationship or the portal filter expression to get the results that you want.

              • 4. Re: Count number of specific values
                ultranix

                That option 2 thing with 3 one row portals worked. thanks,

                as for found set - if i enter find mode and find only those prizes that are with status "Active", it would then display "0" for both cancelled and done prizes, as no prizes of that status are in the current found set, because all of the prizes are active. i mean the total feature would update dynamically, depending on the records that are shown.

                • 5. Re: Count number of specific values
                  philmodjunk

                  The filtered portal method totals records in your table via a relationship--not the records in your current found set as does a summary field. You may want to use your separate summarized calculation fields that you were trying to avoid using unless you can come up with a way to match the current found set to a set of related records for your one row portals or for a filter expression to filter out all but the same set of records

                  Much depends on your interface design as to whether such a relationship or portal filter can be devised that works for you.

                  Here's an example:

                  Say you enter dates in two global date fields and then a script performs a find to pull up all records for that date range. A portal filter can use the same dates in the same two global fields to filter out all records not in that date range and then your totals are based on the same records as shown in your found set...

                  • 6. Re: Count number of specific values
                    ultranix

                    true, but if i add sub summary by status. it would then (visually, list view) look like this:

                    Total: 6

                       Active: 4 [sub-summary]

                        Active 1 [body]

                        Active 2

                        Active 3

                        Active 4

                       Done: 2 [sub-summary]

                        Done 1 [body]

                        Done 2

                     

                    While I want to view it like this:

                     

                    Total: 6 | Active: 4 | Cancelled: 0 | Done: 2

                       Active 1 [body]

                       Active 2

                       Active 3

                       Active 4

                       Done 1

                       Done 2

                    • 7. Re: Count number of specific values
                      philmodjunk

                      Not necessarily.

                      If you have three calculation fields:

                      cActive: Status = "Acitve"
                      cCancelled: status = "Cancelled"
                      cDone: status = "done"

                      and 3 summary fields:

                      sActiveCount: total of cActive
                      sCancelledCount: total of cCancelled
                      sDoneCount: total of cDone

                      You could place sActiveCount, sCancelledCount, and sDoneCount into a header or leading grand summary to get the same result.

                      I'm not trying to sell you on that approach, just trying to layout the two options side by side so you can evaluate the pros and cons for both.

                      • 8. Re: Count number of specific values

                        Interesting and typical...

                        Please allow me to comment with tongue in cheek...

                        Here is a thread where someone wants to save three fields in a record on a hard disk with 500 Gigabytes of space and spends an hour or so typing the information, etc.

                        So, two people spend four hours analyzing a problem that need not be a problem since 5 mintues of work would have solved the problem three days ago.

                        I am acutely aware of the quandry this thread poses having joyfully jumped onto the bandwagon and....

                        Why did the chicken cross the road. Because the road was there.