I don't see the problem that you see. Seems like you need these tables/relationshps:
Researcher::__pkResearcherID = Vists::_fkResearcherID
Visits::__pkVisitID = Collection_Usage::_fkVisitID
Collections::__pkCollectionID = Collection_Usage::_fkCollectionID
Collection_Usage is a join table that links a given visit to a given collection. Fields in this table can record other details about that particular use of a given collection such as the elapsed time (or time start, time end with calculated time...) The key detail is that you can create as many records in Collection_Usage as you need for a given researcher's visit to the collections. You create one record per visit per collection so if your researcher visits once and works with 20 different collections, you create 20 different records in Collection_Usage.
A portal to Collection_Usage can be placed on Visits to record the needed info on each collection worked with on a given visit.
Note that a portal to collection_Usage can also be placed on the Researcher layout to show a list of all uses of all collections over all visits. This portal could be filtered to show just the uses on a given visit or just the times that a selected collection was worked with.
You could also use Collection_Usage as the basis of a layout used for reports that can include data from all the other tables that I've shown here.
I just re-read your post. It doesn't change what I said in my last post, but here's a way to have a "usage" portal on a Researcher layout.
Researcher::gSelectedVisit = Collection_Usage|Selected::_fkVisitID
Collection_Usage|Selected::_fkCollectionID = Collections|Selected::__pkCollectionID
gSelectedVisit can be a global field.
A script on the Researcher layout can select and assign a visit ID to gSelectedVisit. This might be done via a button in the portal row of a portal to Visits on the same layout. A portal to Collection_Usage|Selected can then be used to view/record all needed info for a given visit.
Here's an article about this specific instance, from our good ol' friend Kevin Frank:
'Tis using a popover (hidden) and such to replicate what you're looking for. The structure is setup, I believe, as Phil describes, and this gives you a UI for the data entry point.
You could simply, as Phil suggests, have the 2nd portal on the layout that holds a researcher's usage, but this article describes a nice UX.
I agree with you Jeremy. I was "keeping it simple", by not mentioning that option in my last post, but placing the second portal in a popover located outside of the first popover (so that you don't break the rule of "no portals nested inside of portals"), but opened via script from a conventional button located in the portal row of the visits portal would work quite well.
This only shows usage info when the popover is open, so there can be pros/cons to that approach best evaluated by mergatroid_1 as to whether that is a good UI design or not.
Great - thanks both for your quick and thoughtful replies. They're tremendously helpful.
I have the relationships set up according to your first reply and am now able to associate as many collections as needed from a portal on a Researcher Visits layout.
Thanks for the help. Now I am having some difficulty getting the calculations to work correctly. I need to be able to keep a running total of how many hours each collection is used (both by staff and by the researchers) and I'm having trouble setting up the calculation.
The hours are recorded in the Visits table, but I need to sum them in the Collections table. I have two calculation fields in the Collections table, Staff Hours, and Researcher Hours. Here's how I currently have the calculation set up for the Staff Hours field. Collection Title is always a unique value.
If ( Collections::Collection Title = Researcher Visit Collection Usage::Collection Title ; Sum ( Researcher Visits::Staff Hours ) )
Unfortunately I'm not getting any result in the total staff hours field on the collections table. Can you please help point me in the right direction?
Thanks so much
"The hours are recorded in the Visits table, but I need to sum them in the Collections table."
This data should be recorded and summed in the collection usage table, not collections, not visits. A summary report layout based on the collection usage table can list each collection once with a total for researchers and a total for Staff. (I would treat Staff as a different type of researcher.)
A field in the usage table can refer to data from the related visit record to get the amount of time, but this assumes that all selected collections were "used" for the same amount of time during a given visit and that seems unlikely to me. Fields from collections can also be added as needed to this report.
Hi Phil -
Got it - thanks so much again for your help.