5 Replies Latest reply on Oct 1, 2010 8:17 AM by Jorkau

    Calculations in portal window that reference current record

    Jorkau

      Title

      Calculations in portal window that reference current record

      Post

      Hi there.  Been struggling with this for a few hours and hope someone can help.

      I have two tables:  PeopleTable and EligibleTable.  

      The people table has a list of people and their ages.  The Eligible table is simply a list of activities with three fields:  ActivityName, MinimumAge (must be at least this old to participate in activity), MaximumAge (must not be older than this to participate)

      When browsing records in the PeopleTable I want the portal window to give me information on the eligibility for the person in the current record to take part in each activity (all of which are listed in the portal window).  So, the portal window's first column is EligibleTable::ActivityName (this works).  The second and third columns are EligibleTable::MinimumAge and ::MaximumAge (these work too).  What I really want to do is have a column that does a calculation on the person in the current record's current age along with the minimum age for that activity.  For example:

      PeopleTable::PersonAge - EligibleTable::MinimumAge    

      This would help me to know how far off someone is from being the minimum age for the activity.  Also, a column that simply says whether the person is currently eligible. 

      I have tried creating calculation fields in the PeopleTable that reference the ActivityTable, but it seems to base the calculation only on the age parameters in the first record in the activity table.  So, I tried putting the calculation in the ActivityTable but then I had the opposite problem... it only used the current age of the first record in the PersonTable. 

      The tables currently have a X relationship as I want to see all records from the EligibleTable when examining the its portal window from within the PeopleTable.  The activities and age parameters sometimes change but this table is not intended to be changed by any participant data.  

      Thanks!

      Jordan

        • 1. Re: Calculations in portal window that reference current record
          LaRetta_1

          It would help to know your FM version.  Can a person be eligible for more than one Activity?

          • 2. Re: Calculations in portal window that reference current record
            Jorkau

            Hi there,

            I have FMP 11.  

            Yes, a person can be eligible for more than one activity.  Similarly, more than one person can obviously do one activity.

            Thanks,

            Jordy

            • 3. Re: Calculations in portal window that reference current record
              philmodjunk

              The problem here is that x operator. Since any PeopleTable record links to any EligibleTable record, there's no way for a calculation field to refer to the current PeopleTable record.

              Let's add a new relationship just for linking the all the EligibleTable records to the current PeopleTable record.

              PeopleTable----x---EligibleTable----SelectedPerson

              EligibleTable::gPeopleID = SelectedPerson::PeopleID  (SelectedPerson is a 2nd table occurrence of PeopleTable.)

              gPeopleID should be defined as a global number field in the EligibleTable. PeopleID should be defined in the PeopleTable as an auto-entered serial number. To create SelectedPerson, go to Manage | Database | Relationships, click PeopleTable to select it, click the button with two green plus signs to create a new table occurrence with the same data source table and then double click the new box in order to change it's name from PeopleTable 2 to SelectedPerson.

              Now use the ONRecordLoad script trigger to perform this one line script:

              Set Field [EligibleTable::gPeopleID ; PeopleTable::PeopleID]

              Now you can define calculation fields in EligibleTable that refer to fields in SelectedPerson in order to do what you need. You may also want to use conditional formatting to highlight eligible activities or even "nearly eligible" actitivities.

              Also, since you have FileMaker 11, you can also set up a portal filter if you want to keep certain activities from displaying in the portal such as all records where the difference between the person's age and the minimum age is greater than 5 years.

              • 4. Re: Calculations in portal window that reference current record
                LaRetta_1

                Actually, there is no need for calculation or script trigger ... conditional formatting touching the first portal row can handle it all by declaring a variable (your calculation) for the row itself.  And it (conditional formatting calculations) are much faster (and less flashy) than any calculation and do not clutter field definitions with unnecessary calculations.  

                It works because conditional formats must update when displayed and they display in stacking order and are evaluated (in portal example) in order of portal row draw (when and if displayed).  Notice that, in record two, knitting and coin collecting don't calculate until they are scrolled to in the portal (they are the last two). 

                Download example here:  http://www.4shared.com/file/e3w1Sqvf/PortalCalcs.html

                • 5. Re: Calculations in portal window that reference current record
                  Jorkau

                  Thanks to both of you.  I tried both solutions and they worked.  The latter one seemed a bit less complicated, especially since the script seemed to not always be triggered when I thought it should be.  

                  Now that that part is accomplished I am running into another related problem.  Feedback from my co-workers indicated that the list of eligible activities should not include activities that the participant has already done.  I have a SessionTable that is related to both the PeopleTable (via PeopleID) and EligibleTable (via EligibleName).  SessionTable has also has fields for the date/time of the session etc. 

                  I am having trouble configuring the portal I discussed before so that it can take into account if say John has already done Basket Weaving.

                  Additionally, if you are wondering... no, a person cannot take part in the same activity more than once.

                  Any help is greatly appreciated.

                  JK