2 Replies Latest reply on Oct 15, 2014 4:29 PM by BruceRobertson

    Summary Count (uniques?)



      Summary Count (uniques?)


      FMP 12, Server 12, running on a Windows 7 environment.

      Data base has:

      One table is DRF records (a project document). The DRF number is a serial number issued by FMP with uniqueness enforced.

      Another table is for production items, which fall under project numbers (DRF numbers). The DRF number is entered into a field in every item under that DRF, and some fields are either placed onto layouts in the Items table, or looked up from the DRF by the Item. One other field in each Item is the OPCO (operating company owning the project – tightly controlled by a drop down list to support relationships).

      I have a summary report layout showing projects and items (in the Items table), sorted by Operating Company, Project Number (as entered in the Item record), and, in the body, the code numbers of the items.

      I have a summary count field in the sub summary part, showing the number of items in each DRF, and a grand total at the end. All good so far.

      I have been asked to show a count of the DRF numbers (how many unique DRFs represented), which display in the sub-summary part which is sorted by this DRF Number. I can’t figure out how to show the number of unique DRF numbers, the field always returns either 1, or the total number of items. I have tried creating the field in the both tables, and placing it on various parts, but can’t get it to work. I have a hunch that if I took out the body, it might work, but I need the body to be in the report.

      I’m not real experienced in reports, so quite possible I’m overlooking something. I hope I have explained this well enough.

      Any help out there?

        • 1. Re: Summary Count (uniques?)


          To bad that you are using FileMaker 12, a new summary field in FileMaker 13 would give you yet another option that is quite simple for getting this count.

          With 12, your options for this count are to use either

          ExecuteSQL: A new way to count unique values in FileMaker 12

          Or a rather bizarre, long standing method called "Sum the reciprocal": How to count the number of unique occurences in field.

          For those curious about how this might be easier in FileMaker 13, there's a new "list of" summary field that would produce a return separated list of all the DRF numbers in your found set. A calculation field can copy that value so that the calculation field can be used as a match field in a relationship to a new occurrence of DRF records. A count function or summary field that then counts the related DRF Records via this new occurrence would return your count. All without using SQL or Sum the reciprocal.

          • 2. Re: Summary Count (uniques?)

            The file does not seem to really be available using the 4Shared service. In addition, beginning to use the Facebook method also linked there  reveals that it will download your Facebook contact list. Really? You expect us to do that just to download an example file??