Portal w/relational fields

[dschaal]

I probably should know the answer to this but it is escaping me right now.

My solution consists of:

Benefits Sheets

Personnel

Rates

Benefit Posting

On the Benefits Sheet, you have a blend of the payroll entry date; personnel date like name, etc., including the rates of benefits and a portal to Benefit Posting so that you can have a list for who received benefits in a given payroll. The payroll entry date is utilized to determine the benefit that each employee receives. On that date, I am eligible for so many vacation/sick hours. The calculations for this are on the Rates table which is connected by the Name of the Employee in the Personnel table to the Benefit Sheet.

All of that works well.

What I noticed was that my rate was not changing based on that payroll date. So, I wrote a script to force those fields to relookup their value based on the payroll date and the Rates table. That works.

What doesn't work is when I go through the year, changing the payroll date, the "relookup" makes all the records in the benefit sheet change not just the current portal row. For example:

Payroll Entry Date: 7-8-11 One of our employees worked for 2 years at that point getting xxx vacation hours and xxx sick hours.

Payroll Entry Date: 5-25-12 The same employee has passed her 3rd anniversary date; she now gets more xxx vacation hours and xxx sick hours.

The relookup works to change this on the Personnel table but it also changes it on all the previous rows in the portal on the Benefit Sheets.

My question is how do I get the portal rows to stay "frozen" with the old rate, but still get the new rate based on the payroll entry date?

What am I missing? Thanks in advance....

Donna

[dschaal]

Never mind, I figured it out!