8 Replies Latest reply on Aug 21, 2011 8:07 AM by LaRetta_1

    How to get the number of repetitions of a record in a field?

    AreliLopez

      Summary

      How to get the number of repetitions of a record in a field?

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      mac OS X 10.6.8

      Description of the issue

      I am trying to write a script calculation, where I can calculate or get the number of times a record repeats in a particular field and output this number along with the content of the record in the field. I want to automate this task for every record in the field that appears at least once. How can I go about it?

      Steps to reproduce the problem

      I have tried using some of the Get () functions, but I can't a function for what I am looking for.

        • 1. Re: How to get the number of repetitions of a record in a field?
          philmodjunk

          can you given an example of what you want to do with actual names and values?

          There seems to be a problem with the way you are using your terms here. A record does not "occur" in fields and a record can store many values in many fields defined in that record's table. Thus "along with the content of the reocrd in that field" is particulary confusing here...

          • 2. Re: How to get the number of repetitions of a record in a field?
            AreliLopez

            Sorry for the confusion. 

            I am building a database where I have a field called Part Number. In that field, many of the contents/part numbers repeat, so I want to find the number of repetitions for every part number and be able to store the result with the part number and the corresponding number of repetitions. 

            • 3. Re: How to get the number of repetitions of a record in a field?
              philmodjunk

              If I read this right, you want to know how many records in your table have the same part number as the current record.

              Define a self join relationship linking this table to itself by part number:

              PartsTable::Part Number = SamePartNumber::Part Number

              Create SamePartNumber in manage | Database | fields by selecting PartsTable and clicking the button with two green plus signs. Then link this new occurrence of your table to the original by the part number field. (You can double click the new occurrence to bring up a dialog where you can change it's name.)

              Then you have two options that produce identical results:

              Define a summary field in this table sPartCount as the count of your parts number field and place sPartCount from SamePartNumber on your PartsTable based layout.

              Define a calcualtion if Part Number as Count ( SamePartNumber::Part Number ) and place this field on your layout.

              PS. You have been useing the Report an Issue section of this forum--which is intended as the place to report possible bugs with FileMaker software. Had you posted this question to the FM Pro Forum (See tab at top of screen), you might have gotten quicker responses and from more people.

              • 4. Re: How to get the number of repetitions of a record in a field?
                AreliLopez

                Yes thats what I am looking for.

                From what you said, I understand how to do the self join relationship, but what I am confused about is the calculation. Doing the summary option, I get the count   for the total number of records in the table. Maybe I am not doing correctly since I want to find the count for part numbers that repeats in the table and not the count of all the part numbers.

                I'm sorry, I didn't notice that. I will post my next questions to the other forum.

                I appreciate your help

                • 5. Re: How to get the number of repetitions of a record in a field?
                  philmodjunk

                  The summary field cannot be from Parts Table, it must be added from SamePartNumber to get a count of just the matching part number records.

                  Use the field tool to add a field to the layout, but select SamePartNumber from the drop down, then select the summary field from the list of fields.

                  I see I also used some confusing on the alternate method:

                  Define a new field named cSamePartCount of type calculation. Enter Count ( SamePartNumber::PartNumber ) as its calculation. Place cSamePartNumber on your layout to display the count of records with the same part number.

                  • 6. Re: How to get the number of repetitions of a record in a field?
                    AreliLopez

                    I don't think I understand how to get the summary field from the SamePartNumber Table. The only option I have from the "Table:" drop menu in manage database dialog is "Table" I don't have the option to select "SamePartNumber" as the table. 

                    • 7. Re: How to get the number of repetitions of a record in a field?
                      philmodjunk

                      The two tables need to be linked in Manage | Database | Relationships in a relationship.

                      • 8. Re: How to get the number of repetitions of a record in a field?
                        LaRetta_1

                        "so I want to find the number of repetitions for every part number and be able to store the result with the part number and the corresponding number of repetitions. "

                        Are you using repetitions?  If so, it is suggested that you change your structure.  You probably mean 'count' of each part but I wanted to clarify.