9 Replies Latest reply on Jan 14, 2011 12:10 PM by RyanFishy

    Help with summary of 'similar' fields

    RyanFishy

      Title

      Help with summary of 'similar' fields

      Post

      I'm working FM Pro 11

      We have a single-table database with about 10k records.  There is a non-unique field called 'Accession Number.' We have about 5k different accession numbers.  I would like to place into a layout a 'summary' that would count how many times the current record's 'Accession Number' field's value appears in the database across all records.  This would mean that each record would display a count of how many other records shared its 'Accession Number'

      Thanks for any help with this simple question, still trying to wrap my head around relational databases.

      -Ryan

        • 1. Re: Help with summary of 'similar' fields
          philmodjunk
          1. Create a new layout.
          2. While still in layout mode, double click the Body part label to change it from a Body part to a sub summary when sorted by "Accession Number".
          3. Place a summary field defined to compute the "count of" Accession Number in side this sub summary part. Also add the accession number field to this part.
          4. Return to browse mode, select list view and sort your records by Accession Number.
          5. You shoul see one row for each Accession Number value with a count of the total records with each value.

           

          • 2. Re: Help with summary of 'similar' fields
            RyanFishy

            Thanks for the quick reply Phil,

            I'm sorry if I was a bit unclear, I don't want to create a new layout.  The objective here is to make this info accessible from an existing layout in browse mode.   I would like to insert into an existing layout a field (portal?) that would count how many other entries share this entries 'accession number'.

            Thanks

            • 3. Re: Help with summary of 'similar' fields
              philmodjunk

              A portal will work, but you'll need a new related table with one record for each accession number and a pair of new relationships to it.

              YourTable::AccessionNumber X AccessionNumber::AccessionNumber

              AccessionNumber::AccessionNumber = YourTable 2::AccessionNumber.

              YourTable 2 is a new table occurrence of YourTable (Use your current table's name in place of "yourtable".)

              Count ( YourTable 2::AccessionNumber ) defined in the new AccessionNumber table will give you your count.

              The relationship with the X operater instead of = will enable you to have a portal of all accession number records where you can put the calclulation field in each portal row to show your counts.

              • 4. Re: Help with summary of 'similar' fields
                RyanFishy

                Hey Phil,

                Thanks for the answer. I've got it to work.  I did however need to change the relationship between 'YourTable:AccessionNumber' and 'AccessionTable::AccessionNumber' from a "X" to an "=". 

                I then put in a portal to 'AccessionTable:AccessionCount' from the layout of 'YourTable'

                Now the layout shows 1 number for each entry, the count of how many entries share its 'accession number', just how i wanted.

                Thanks, big help

                -Ryan

                • 5. Re: Help with summary of 'similar' fields
                  RyanFishy

                  Actually, sorry I have 1 more issue.

                  I was able to population the new table 'AccessionTable' with one entry for each 'accession number' manually by simply importing a list of all the current accession numbers.  But now it will fail to count new accession numbers that are added subsequently.

                  Is there a way to keep the list of 'accession numbers' in 'AcccessionTable' up to date automatically? So that I would not have to manually add to the list of accession numbers in 'AccessionTable' each time we use a new 'accession number'?

                  Thanks

                  • 6. Re: Help with summary of 'similar' fields
                    philmodjunk

                    Hmm, I'm not sure how you got it to work with = instead of X in the relationship for this portal...

                    With =, you should only see one accession number in the portal so there's some detail I am missing here.

                    Wouldn't your import load the accessionNumber table with multiple copies of each number? (Perhaps you used a Unique Values setting to filter out the duplicates here.)

                    It's possible to use a script trigger to add a new record to this table if it's accession number doesn't match any record in the accession number table.

                    • 7. Re: Help with summary of 'similar' fields
                      RyanFishy

                      Thanks for the tip on using a script to add a new record, I should be able to get that to work.

                      I did an import of unique accession numbers, so no duplicates came across.

                      As for the portal, only showing one 'accessionCount' at a time is what I was looking for.  The portal is from a layout of 'Yourtable' as is showing records from 'AccessionTable::AccessionCount', not sorted or filtered.

                      • 8. Re: Help with summary of 'similar' fields
                        philmodjunk

                        So if you are on a layout for a record with Accession number 1234, you only want to see the count for Accession Number 1234?

                        In that case, you don't need the extra table at all.

                        Just use

                        YourTable::AccessionNumber = YourTable 2::AccessionNumber

                        and Count (YourTable 2::AccessionNumber)

                        will count the number of records with the same accession number as the current record.

                        • 9. Re: Help with summary of 'similar' fields
                          RyanFishy

                          Works well, knew it could be simple.

                          Thanks