4 Replies Latest reply on Jun 11, 2010 1:21 PM by coartist

    counting duplicate records-and answer in a field in table view

    coartist

      Title

      counting duplicate records-and answer in a field in table view

      Post

      Hello,

      I have been trying to solve a problem which I thought would be simple and commonplace but I can't seem to find the answer. I have a database of about 16,000 records. The main purpose of it is to group items with similar features. These items which are similar are assigned a unique alpha numeric name. I am trying to create a calculation for a field which will count how many of each unique group and then have that field shown in a table view with the rest of a record's info. So that when I am looking at a particular record, I can immediately view how many other records are like it. So if one record is called AB12345, and there are 10 AB12345's in the entirety of 16,000 records, I want this field to say '10' so that I know that there are 10 of the same kind called AB12345. All I am able to do is get a gross total of 16,000 for this field. I have tried various 'case' and 'If' calculations, using other summary fields and I can't qualify the calculation to look in the entire database for the name of the active record I am looking at. Maybe I'm making this too difficult and the answer is plain and simple, but I could sure use some other eyes on this question.

      Thanks,

       

      Peter

       

        • 1. Re: counting duplicate records-and answer in a field in table view
          ryan

          In your Relationships Graph, create a self join relationship by duplicating your Table.

           

          Set the relationship so that the INDIVIDUAL item id ≠ to INDIVIDUAL item id  and GROUP id = GROUP id

           

          then create a calculation field using the following:

           

          Count ( ***RELATEDTABLE::ITEMID )

           

          ***Substitue with your actual table name an field name

          • 2. Re: counting duplicate records-and answer in a field in table view
            philmodjunk

            One solution is to use a summary report rather than a table view. You can group your records by this field by sorting them and if you place all your fields in a sub-summary part and removed the body layout part, you can get a table like list of records with one row for each type of record and a summary "count of" field can give your the number of such records in each group.

             

            You can also list your records in a table view and count the records with a self-join relationship and a calculation field that uses the "count" function.

             

            In Manage | Database | Relationships, click on your alpanumeric field, drag from it outside the table occurrence box and back to the same field. This creates a new table occurrence related to the first by your alphanumeric field.

             

            Table::AlphaField = Table 2::AlphaField

             

            Now you can define a calculation field such as Count ( Table 2::AlphaField ) to compute the total number of records with the same exact value in this field. This table view will still list all copies of each record (Don't know if that's what you want or not) but it will display your record counts as requested.

            • 3. Re: counting duplicate records-and answer in a field in table view
              coartist

              Many thanks Phil. And apologies for not providing useful info like version operation system, etc. Just to fulfill that, I'm on a Mac Pro (OS 105.7) using FM 11 Advanced. It is not currently on a server, but soon will be, along with FM 11 Server.

               

              Again thanks for the help. I really appreciate it. I'll try to get this code done by the end of the day and report back by Monday.

               

              Peter

              • 4. Re: counting duplicate records-and answer in a field in table view
                coartist

                Hey Phil,

                 

                It works perfectly! Again, many thanks! That saved me a lot of time. 

                 

                Peter