What is the relationship between the two tables (what fields is it joined on)
You can sort the relationship such that the latest record is always the first record in the relationship
There isnt really a need then to update the salary field in the table because placing the related field (from this relationship) will result in displaying only the latest value.
If you want the information stored in the other table then you will have to set the field via script because calculations on related fields are unstored.
You can trigger t he script based off save data in the particular field in the portal record.
If its not required to store the info then the calculation of the field is simply the related fiields value from the sorted relationship.
...In my Employee Table I have a field salary....and I have also Portal from my Salary Table with a field Position, Date Hired, and Salary.
Hi thong, the business rules are unclear. It would seem that an Employee salary may be determined by the standard position salary upon hire but incremental annual increases and merit increases would be based upon an individual employee. Therefore, two employees in same position would have different salaries. Is this your business rule?
If so, it would seem best if the tables were a bit rearranged as:
DOB ... etc
Status (active, medical leave, leave of absence, terminated etc)
SalaryID (auto-enter incremental serial, FM generated and meaningless)
EmployeeID (use pop-up menu with value list based upon all Employee records
ActionDate (would start with date of hire and then each time salary was adjusted, a record were be created)
Position (a lookup from Employee at the time of a new Salary Record (may change manually by Manager if position title changes. Positon is best written to your salary table so you can document the employee's position at the specific Activity Date. It should be lookup so it is kept static for historical purpose.
ActionType (New Hire, Annual Increase, Merit raise, Position Change etc)
These two tables should be joined on EmployeeID. This means that one employee can have many salary records (activities).
What I want to happen is if I will update my salary table in my portal, it will also automatically update the latest salary of an employee in my Employee Table.
When you update your salary table (by adding a new record), that Employee will simply KNOW the information if you do one of the following (listed in order of my personal preference which might not be someone elses):
1) If vs. 11, place a 1-row portal from salary on your Employee layout and create the filter as: Last ( Salary::SalaryRate)
2) You can sort your relationship descending on the Salary table as descending on Salary and then just place the Salary::SalaryRate field on your layout.
3) Create a calcualtion in your Employee table which is: Last ( Salary::SalaryRate )
With vs. 11, we can save adding calculations and use filtered portals instead. So what I am suggesting is that you do NOT store that Employee salary in two locations (or two different tables or files). If you store it in two places, you will have to fuss with keeping it in synch which sounds easy but can break on you.
The only time we need to write data in two places is if the data, at the time the action happened, needs to store (for historical record). Example: Shipments ... you ship to customer and you store their current address as static data. If you don't write static for this type of information and they move in the future, their new address would appear in the Invoice. This could cause you problems for audit tracking. So, as business rule, if data might change in future, write it static otherwise just place your related fields on the layout (if you want first related, ie, Hire Date in this case) or last related (if you sort the relationship) or filter a portal to give you exactly what you want or create an unstored calculation to display the results.
You would therefore never need to store the Position within the Employee record either.