Calculations in portal window that reference current record
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.