salary review form and auto update

no problem,

Let me give you an OK solution and a better solution.

Here is the OK solution:

You have a relationship where 1 Employee (EE) has many salary reviews.

Make a summary of the maximum of the Salary Review [Record ID#]. Call it [Record ID# of most recent Salary review by record ID#]


Now make a reverse relationship where 1 salary review is related to many Employees. On the right side of the relationship will be that field [Record ID# of most recent Salary review by record ID#]. Then just do a "lookup" of the salary review and the effective date up to the EE record.



That will work fine if you ALWAYS enter the salary reviews in sequence so that the highest record IDis the most recent.



A better way is to do a summary field of the Maximum of the Effective Date of the salary increase. Lookuop that down to the salary review table.

Then do another summary field of the maximum record ID where the Effective date = that lookup of tyhe maximum effective date.

Then do that reverse relationship as described above. That will ensure that the record you are grabbing is the one with the most current effective date, and not the highest record ID.