8 Replies Latest reply on Apr 13, 2017 7:20 AM by mergatroid_1

    Integrating multiple related records into a portal (nesting problem)

    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.

       

      Thanks,

      Andrew

        • 1. Re: Integrating multiple related records into a portal (nesting problem)
          philmodjunk

          I don't see the problem that you see. Seems like you need these tables/relationshps:

           

          Researcher----<Visits------<Collection_Usage>-------Collections

           

          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.

          • 2. Re: Integrating multiple related records into a portal (nesting problem)
            philmodjunk

            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.

            • 3. Re: Integrating multiple related records into a portal (nesting problem)
              jbrown

              Hello.

              Here's an article about this specific instance, from our good ol' friend Kevin Frank:

              https://filemakerhacks.com/2014/08/25/fm-13-popovers-in-portals/

              '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.

              • 4. Re: Integrating multiple related records into a portal (nesting problem)
                philmodjunk

                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.

                • 5. Re: Integrating multiple related records into a portal (nesting problem)
                  mergatroid_1

                  Great - thanks both for your quick and thoughtful replies. They're tremendously helpful.

                  • 6. Re: Integrating multiple related records into a portal (nesting problem)
                    mergatroid_1

                    Hi Phil,

                    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.

                     

                    Researcher----<Visits------<Collection_Usage>-------Collections

                     

                    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

                    • 7. Re: Integrating multiple related records into a portal (nesting problem)
                      philmodjunk

                      "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.

                      • 8. Re: Integrating multiple related records into a portal (nesting problem)
                        mergatroid_1

                        Hi Phil -

                        Got it - thanks so much again for your help.

                        Andrew