Integrating multiple related records into a portal (nesting problem)

Discussion created by mergatroid_1 on Apr 12, 2017
Latest reply on Apr 13, 2017 by mergatroid_1

I have been hired to build a database and am running into a problem involving nesting portal information, which I realize is not possible in Filemaker. Here is the scenario:


The database is being built to track usage statistics for a university's archive and special collections. The client wants to be able to keep tabs on certain information for each instance that a researcher uses a collection including how the request was made, date of request, time spent with collection, etc. So far so good. I have all of that working fine, displayed on a layout from the researcher table in a portal from a related table called researcher_interactions.


However, a researcher may use more than one collection per visit and the client wants a way to keep track of the amount of time spent with each collection in order to perform a calculation in the collections table that will show the cumulative use of each collection over time. Because there is no way to know (and no limit to) how many collections a researcher will use on a given visit, I want another related table where each collection and usage time could be recorded. However, I need those collection usage statistics to be related to each specific visit. The only way that I have been able to conceptualize this is to think of putting the collection usage table in a portal in the researcher visit portal. Obviously that doesn't work. So I'm wondering how I would go about structuring the database so that each researcher visit could capture as many collections used, given that that can't be a fixed number. Any help would be greatly appreciated.