4 Replies Latest reply on Jun 20, 2013 9:34 AM by philmodjunk

    Calculate from Current Context?

    doronkipper

      Title

      Calculate from Current Context?

      Post

           I'm designing a filemaker database for a study involving scuba divers.  I've attached an example of the data entry layout and relationship structure.

           Ideally, I'd like to be able to enter data for all Divers on a team from one page as seen on the left.  The Divers might reconfigure into a new team throughout the study, so that is why the data entry layout is at the top level table: groups.

           On a given Groups record, I would like to define the Diver, and then enter log information for each member of the team in different portals.  

           I structured the Divelog relationships as instanced versions of a single Divelog table because I would like the data from ALL Divelog Records to be recorded onto a single Divelog table that is exportable for later analysis in Excel.  I would like to create a calculation field that autofills info into the Divelog Table for any fields that live at the Diver and Group Level.

           The problem I'm running into....at the Divelog level, I created a calculation field to fill in the grandparent Team Name.  It only works for the first Diver Table instance because the calculation is starting from the context of Divelog 1.    You can see the group name missing for Anna Coral on the right side of the screenshot.

           Is there a way to calculate from the current instance?  So fields on Divelog 2 would calculate from the context of the Divelog 2 Instance?   

           Any suggestions on how I could better structure my tables would be much appreciated.  Thanks!

      LayoutExample.png

        • 1. Re: Calculate from Current Context?
          doronkipper

               Also uploading an example of the calculation for the field on Divelog

          • 2. Re: Calculate from Current Context?
            philmodjunk

                 I'd recommend a very different data model for managing your groups:

                 DiveLog>------Divers-----<Diver_Group>-------Groups

                 Divers::_pkID_Divers = Diver_Group::_fkID_Divers
                 Groups::_pkID_Group = Diver_Group::_fkID_Group

                 For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

            • 3. Re: Calculate from Current Context?
              doronkipper

                   Hi PhilModJunk,

                       Thanks for the recommendation.   I implimented the notation into my table structure as a test, but I'm at a loss for how to set up the data entry layout to mimic the one from the my first screenshot (on the left) using this structure.

                   The intended data entry process:  New Record, create a group name, identify the Divers, start logging dives.

                   My brain might just not be working on this, but how do you establish the join relationship for 2 or more divers from a single layout?

                    

                    

                    

                    

              • 4. Re: Calculate from Current Context?
                philmodjunk

                     how do you establish the join relationship for 2 or more divers from a single layout?

                     You use a portal. A portal to Diver_Group on a Groups layout can be used to select each diver for your group.

                     That get's you to the fiirst few parts of your work flow. The challenge then becomes setting up a means of logging data in the DiveLog for each diver. I can think of a number of ways to set that up, but not being a diver myself, I'm not fully clear on which method would be best.

                     One option would be to set up a fields from an occurrence of DiveLog that Matches to Groups by a field in Groups. Clicking a row in the portal to Diver_Group performs a script that sets the field to the value of the Diver's ID. That would use a relationship such as:

                     Groups----<DiveLog|SelectedDiver

                     Groups::SelectedDiverID = DiveLog|SelectedDiver::_fkID_Diver AND
                     Groups::pkID_Group = DiveLog|SelectedDiver::_fkID_Group

                     Select "allow creation..." for DiveLog|SelectedDiver in the above relationship.

                     A button in the portal would then use this script to set up the match fields for logging the dive:

                     Set Field [Groups::SelectedDIverID ; Diver_Group::_fkID_Diver]
                     Commit Records