3 Replies Latest reply on Jul 9, 2015 5:01 AM by JeffTomb

    Look up and match function?



      Look up and match function?


      I'm so new to this I don't even know the correct terms to search for...

      I have two tables in my database. The first is called "Salary Schedule" and has the following information:

      Step       Column       Salary       Start Date     End Date

      A                1                $50,000      7/1/15           6/30/15

      A                2                $53,000      7/1/15           6/30/15      ....and so on.

      My second table is "Employees". It also has the fields "Step", "Column", "Start Date", and "End Date". I would like a calculation (function? script?) that will automatically give me the salary based upon the date, step, and column information entered into the "Employees" table.



        • 1. Re: Look up and match function?

          This appears to be a salary increase schedule based on years of service, is that correct?  In your example you have steps with the same date range, was that just for example or can steps have the same date range. Also, are your tables related, and if so how?

          • 2. Re: Look up and match function?

            It's a just an example. Basically, we have a salary schedule for our employees that can change year-to-year. The letters go from A-D and the numbers are from 1-14. I put the start and end dates in in order to capture historical salaries.

            At the moment, the tables are related by employee ID number.


            • 3. Re: Look up and match function?


              I am still unclear on exactly what your trying to do but I can offer two solutions which might work based on my current understanding of your situation.

              1. If you have two tables, EMPLOYEES and SALARY and they are related by the employee id field, then you could us a portal in the employee layout to display the related salary records. This would give you the ability to show any of the salary fields in the portal and you could sort the portal by start date to show the most current salary at the top or bottom of the portal based on the sort order (ascending or descending)
              3. If your only looking to see the current salary for an employee in the employee layout there is another method which could be used, assuming that in the employee table you keep the Step and Column fields reflecting the current status of the employee.  To do this you could create another table occurrence of the SALARY Table (call it SALARY_LOOKUP for example) and relate it to your employee file using the employee Id, step, and column fields.  In the employee layout you would insert the salary field from the SALARY_LOOKUP table.  This would display the salary which currently matches the employee's Step and Column.  You could also add the start date and end date field from the SALARY_LOOKUP table to display the dates.

              Not sure this does what you want but, both solutions are dynamic, and would automatically reflect any changes.  If this doesn't help post again.