2 Replies Latest reply on May 5, 2010 4:02 PM by LaRetta_1

    Portal

    thong127

      Title

      Portal

      Post

      Hi to everyone,

       

      Please help me figure out how to do this... I have a two (2) Tables (Employee Table & Salary Table)...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.

       

      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. Kindly help me figure out if there is any calculation needed in my Salary field in my employee Table.

       

      Thank you very much.

       

      thong

        • 1. Re: Portal
          aammondd

          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.

           

           

          • 2. Re: Portal
            LaRetta_1

            thongLoreto wrote:

            ...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.

             

             

            thong


             

            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:

             

            Employee Table:

            EmployeeID

            Employee Name

            DOB ... etc

            Status (active, medical leave, leave of absence, terminated etc)

             

            Salary Table:

            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)

            Salary rate

             

            These two tables should be joined on EmployeeID.  This means that one employee can have many salary records (activities).

             


            thongLoreto wrote:

             

            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.