5 Replies Latest reply on Jul 9, 2009 5:30 AM by thezed

    Total of current salaries



      Total of current salaries


      Hi, I have an issue with a calculation I want to make. Let me explain.


      I have a table 'Staff' with all the employees. Under each Record I have a DateJoined and a DateLeft (empty if currently employed). I also have a MonthlySalary field and a LastIncreaseDate and LastIncreaseAmount.


      What I am trying to do on a Summary layout is to have the total salary paid per employee, and grand total, for the Current date. Where I get a bit confused is that there are a lot of parameters:

      - if the employee joined after Jan 1st,

      - if the employee left during the year

      - if there is a salary increase during the year


      All help will be welcomed!


        • 1. Re: Total of current salaries
             I'm afraid that I think you will need to provide more details on exactly what you want on your report and how these "parameters" are involved with the report.
          • 2. Re: Total of current salaries

            Well, basically I want to create a Summary report of the Salaries, that would give me the total amount paid per employee at the date I run it. Something that would look like the following:


            EmployeeName     DateJoined     TotalSalary (paid during this fiscal year up to today)       DateLeft (if he/she left)     

            Marc                   01/05/09        12,000

            Emily                  01/08/08        15,000



            I have no issues with the first 2 and last fields, but I am not sure how to calculate the TotalSalary.

            - The first case is an employee who joined before the beginning of the year, in which case TotalSalary will be the number of months x MonthlySalary.

            - Second case, an employee who joined during the year, where TotalSalary will be the difference of months between now and DateJoined x MonthlySalary

            - Third case (which is actually combined with the first two in some instances) , a Salary increase, in which case the TotalSalary will be the number of month between beginning of the year and LastIncreaseDate x MonthlySalary - LastIncreaseAmount + the number of month between LastIncreaseDate and now x MonthlySalary.



            I hope the above is clear. I'm new with FileMaker and I get a bit confused as I am not sure if I need a script to calculate the TotalSalary per Employee or if I can get away with some clever Calculation and Summary fields.


            Thanks for your help.


            • 3. Re: Total of current salaries

              You need to tell us how you record:


              Starting salary.

              Date and amount of salary changes.


              There are a numbe of different ways to record that and we need to know how you've set it up in order to advise you.

              • 4. Re: Total of current salaries
                   I'd suggest creating another table that has an entry for each time an employee is paid, with their name, date paid, and amount paid.  Then relate that table back to your current table, that has 1 record per employee, and you can easily calculate the amount paid for any time period.
                • 5. Re: Total of current salaries

                  Phil, as I mentioned in my first post I have a MonthlySalary field and a LastIncreaseDate and LastIncreaseAmount in my Staff table. But I think I found the solution, thanks to etripoli. I'll do it through another table that relates to my Staff table.


                  Thanks all of you who took the time to help me!