It would help to know your FM version. Can a person be eligible for more than one Activity?
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.
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.
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.
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
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.