1 Reply Latest reply on Jul 2, 2015 4:55 PM by WilliamThurmes

    Calculation - Latest Salary



      Calculation - Latest Salary


      Hello everyone

      I'm using FMPA 14 / OS X 10.10.4

      I'm trying to figure out, how to get a calculation working, based on our customers salary.

      John Doe - Salary X - 01.01.2015

      John Doe - Salary Y - 01.06.2015

      John Doe - Salary Z- 31.12.2015

      I need a calculation, which takes basis of the latest salary, although not future dates.

      In this example, John Doe's salary Y 01.06.2015, would be the one to calculate atm.

      As soon as we hit 31.12.2015, the calculation should take basis of salary Z.


      Now furthermore, we do get salary information based on monthly salary, which I would like to spare my colleagues the trouble to calculate manually - which leaves mistakes.

      Is it possible, again with a calculation, to calculate a yearly salary to monthly and vice versa, by using 2 different field, Field 1 - Yearly Salary / Field 2 - Monthly Salary.

      My colleagues then type - depending on the salary information they get - yearly or monthly - in the desired field - the other field, which is not populated, then get's populated with the same salary, but then specified either yearly or monthly?

      Best regards


      P.S. Yell if you need more info.

        • 1. Re: Calculation - Latest Salary

          I suppose there are several ways to do this, including using an eSQL query to get the latest date of a salary, but my preferred method is this:

          Salaries should be in a separate table, which should include the date they begin and end on (So Salary X started Jan 1, ended May 31)(let's call this Salary). From what you wrote above, a monthly salary would make the most sense here, if your pay period is monthly. Yearly salary is 6 months at Salary X and 6 months at Salary Y, or maybe 3 months at X, 6 at Y, 3 at Z, or maybe 12 at Z.

          In the other table, the one your colleagues are typing into (let's call this Working), have a date field; Have Working::Date >= Salary::BeginDate AND Working::Date <= Salary::EndDate. Then you can look up the salary for the given date.

          Now you have to decide exactly what kind of answer your customers want, or make it flexible enough that they can define what they want. You need at least two inputs from your customer: Monthly or Yearly, and Starting date. Let's say that the answer is yearly, starting from March 1, 2015. You could easily write a script that contains a loop, setting Date to March 1, get that month's salary and add it to a variable; setting Date to April 1, get that month's salary and add it to the variable, etc, to Feb 1 2016. Then you output that salary information to the customer, either by setting a field or by using a Custom Dialog.