5 Replies Latest reply on Aug 19, 2011 2:00 PM by AreliLopez

    Unique Value Function

    AreliLopez

      Title

      Unique Value Function

      Post

      I am trying to find the unique values of a field called Part Number. In this field, there are many part numbers that repeat, so I want to output the unique part numbers found in Part Number field. Then I want to find the number of times each unique part number repeats in the field. I have looked online and found the UniqueValues(values) function that does the first thing I am looking for. However I don't find the function in the "Specify Calculation"..is there a way to implement this function? I am using FileMaker Pro 11.

        • 1. Re: Unique Value Function
          philmodjunk

          I believe that you have found reference to a custom function that you would have use FileMaker Advanced in order to add it to your database before you could use it. There is, however, no need for that function to get what you want here:

          Here's a simple set up that will produce a list of your part numbers. Each part number will be listed once with the number of records with that part number listed on each such line in the report.

          Open Manage | database | Fields. Select your table of Part Numbers in the table drop down and add a new summary field, sCount, defined to compute the "count of" your Part Number field.

          Next, enter layout mode create a new layout based on your table of part numbers. This can be a simple blank layout to start.

          Double click the body part label to bring up part setup. Change this body part to a "Sub Summary when sorted by" your part number field.

          Return to the layout and add the part number field and sCount to this sub summary layout part. Position them to make a single row of fields just below the boundary between this part and the header. Do not allow either field to touch this boundary. Now drag up on the Boundary that separates this part from the footer to resize it so that it is just large enought to contain your fields. (If you have a part name or description field, you can add this field too if you want.)

          Now return to browse mode, select Show All Records and sort your records by your Part Number field. Select View as List and you'll see a list of all your part numbers with just one row for every part number.

          • 2. Re: Unique Value Function
            AreliLopez

            Thank you very much! That workedSmile

            • 3. Re: Unique Value Function
              AreliLopez

              One more question, is there a way to have the count for the part numbers in the table view format, so that for every record I could have the same number appear in a field?..like for example:

              Part Number    Count

              PN123                1

              PN234                3

              PN234                3

              PN234                3

              PN456                2

              PN456                2

               

              where Part Number and Count are fields in the Table view

              • 4. Re: Unique Value Function
                philmodjunk

                Use the method I suggested in your original post in Report and Issue.

                If you define a self join relationship between this table and itself, you can click the modify button to add the summary field from the related table occurrence and it will report the part counts.

                • 5. Re: Unique Value Function
                  AreliLopez

                  Oh! Now is clear to me. Thank you again.