3 Replies Latest reply on Mar 9, 2013 7:46 AM by philmodjunk

    Count Specific Values in a Field



      Count Specific Values in a Field


           This is driving me nuts! I have a field called Risk Level. This field has 5 values in a dropdown list: 1, 2, 3, 4 and N/A. I want to count the number of Risk Level 1's, 2's, 3's, 4's and N/A's INDIVIDUALLY. How do I do that?

        • 1. Re: Count Specific Values in a Field

               Let me double check what you want:

               You have a table with one Risk Level field in each record formatted as a drop down list.

               This field stores a single value, 1, 2, 3, 4 or N/A

               If so, create a layout based on this table. Define a summary field as the "count of" some field in this same table that is never empty of data.

               Make this a list view layout and add a sub summary layout part "When Sorted by" the risk level field.

               Put the risk level field and the summary field in this new layout part.

               Remove the body layout part unless you want to list all the individual records in your report.

               Perform a find or Show All records to get the records you want for your report.

               Sort them by the Risk Level field to group them by Risk Level.

               The sub summary layout part will not be visible unless you sort your records by the "when sorted by" field specified for it.

               This is one way to get the counts that you want. There are other options that can be used to meet different layout requirements.

               Of special interest is that FMP 12 users can get such a count in a single calculation field using ExecuteSQL().

          • 2. Re: Count Specific Values in a Field

                 That was extremely helpful and worked! Thank you!

                 I'd like to do the following to the report and could sure use your expertise and extremely helpful guidance:

                 1. Add a grand total count to the field that is never empty of data (Assessor Name).

                 2. I'd like to also show the percentage of Risk Level 1's, 2's, 3's, 4's and N/A's as a function of the grand total.

                 3. I'd like to add a chart with Risk Level 1, 2, 3, 4 and N/A as X and count as Y. When I try to add a chart to the sub summary layout part when sorted by the risk level, I get a separate chart for each of the risk levels. I only want one. This is baffling to me.

                 Thank you very much for your help. Your guidance is such a nice tutorial!!!

                 I'm not sure if you have time, but if you do, I also want to create reports where the risk levels are sorted by the person who assigned them (Assessor Name). I'd like to apply the same visuals as listed above to these reports as well.

                 Could I also create a portal to show these summaries on other layouts in my db?

                 Thanks again!


            • 3. Re: Count Specific Values in a Field

                   1. Take a copy of the same summary field and put it in the header, the footer or a trailing/leading grand summary part

                   2. I think the fraction of total summary field can be used for that, just use data formatting in the inspector to display the decimal value as a percent.

                   3. You would not put this chart in the sub summary part, but if you put it in the header or footer, you can use the summary field for Y and specify the "found set, groups of records when sorted" data source option to get a chart where each plotted point is a subtotal corresponding to one of your sub summary parts.

                   For your summary totals in sub summary parts, you can sort your records by assessor and by the risk level. You can get different reports just by whether you sort by assesor first, then risk level or vice versa.

                   For charts that have bars for each risk level for each assessor, things get a lot more complex. Previous experiments with sorted found sets and charting while helping others here revealed that only sorts on the first field specified in the sort order group the records for the chart. To sort on two fields, we had to carefully construct a calculation field that combined the values from both fields in such a way that sorting on that field correctly grouped the records the same as a two field sort.

                   You may be able to get the right results with something like:

                   Assessor & " " & RiskLevel