- Create a new layout.
- 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".
- 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.
- Return to browse mode, select list view and sort your records by Accession Number.
- You shoul see one row for each Accession Number value with a count of the total records with each value.
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'.
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.
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
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'?
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.
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.
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.
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.
Works well, knew it could be simple.