4 Replies Latest reply on May 28, 2015 7:53 AM by VeronNovosad

    Count a Specific Text in a Select Field



      Count a Specific Text in a Select Field


      I am trying to figure out how to the number of times a specific text is used in a field. I have viewed practically every other posting that is similar to my problem and still cannot get anything to work.

      I have a field called Category in the table Resources.  Under Category there is a value list containing People; Equipment; Vehicle; Vessel and Other.  I have created the fields called Total People, Total Equipment, etc. and want  to have a calculation/summary that will provide me with the number of times People is entered in the Category field.

      I have tried the following Case; PatternCount; ValueCount; ExecuteSQL; self-join relationship and I only get three results: A count of 1 in every Total field; a blank Total field; or a count of 0.

      Obviously I am missing something and am getting frustrated trying to figure this out.

      Help is definitely appreciated.

        • 1. Re: Count a Specific Text in a Select Field

          If you have tried all that and didn't get to work, you are probably missing more than one thing as several of those should have worked.

          First a question that must be answered before I can go further: Is the category field limited to a single value per field or is it possible to select multiple categories in the same field?

          If you have multiple values in the same field and want a count over all the records in your table or all the records in a found set, this get's a lot messier than it would be if you limited the field to a single value and used a related table in place of that single field if you need to be able to select multiple values.

          • 2. Re: Count a Specific Text in a Select Field

            Thanks for the quick response.

            Only one value per field, i.e. People or Vehicle never both together.

            What has plagued me is that when I had used Case; PatternCount; ValueCount; ExecuteSQL; self-join relationship not only would I get a count for what was in the field, but I would also get a count for a value that was never entered in the field.

            The Category field is linked with a value list for a pulldown menu, would this interfere/

            • 3. Re: Count a Specific Text in a Select Field

              Then you have multiple options that work. Which is best is not a question that I can answer as I do not know enough about your requirements to make a specific recommendation.

              Here's one of the simpler methods:

              Define a "count of" Summary field, named sCategoryCount in your table.

              Set up a list view layout based on the same table.

              Add a sub summary layout part, "When sorted by Category". Put sCategoryCount (and some layout text to label it) into this sub summary layout part. Perform a find or show all records to find the records you want to use or your report and then use sort records to sort them by the Category field. This sort arranges the records in groups of records with exactly the same value in Category.

              At this point, you should get one sub summary layout part with a list of the records that are members of that category listed either just below or just above it. If you remove the body layout part and just keep the sub summary layout part, you will get one row of data per category with a count of the number of records in each such group.

              For an example of an ExecuteSQL query that can list all categories and their counts in a single large, multiple row fields (which will look a bit like a portal but with no borders around the fields), see this thread for an example: FMP 12 Tip: Summary Recaps (Portal Subtotals)

              Other methods are also possible.

              • 4. Re: Count a Specific Text in a Select Field

                Thanks, I will give this a go.