Determining current record per employee (payroll compensation history)

Question

Hi everyone, thanks in advance for the help.
I have a "Team Members" Table which is a table of all of my employees.&nbsp; It is a one to many relationship with a "Compensation" table where I enter all of the compensation for each employee.
On the Team Members table there is a report called "Payroll Sheet" that will be used for bi-weekly payroll.&nbsp; It prints out the team members name, annual salary, bi-weekly salary, which is used for payroll entry. Currently, I am taking the largest salary for each team member on that sheet.

However, two of the owners have decided to reduce their salary.&nbsp; So now I do not need the highest salary but I need the most recent (effective date salary).&nbsp; But I cannot just take the highest effective date as it needs to be before the payroll date.&nbsp; This way if we decide to give someone a raise in a month we can enter it now but it will not populate on the report until after that payroll date.

So my thoughts are to create a formula checkbox on the compensation table that will be marked check for each employee's salary to use.&nbsp; I have this code in there so far

//To be the most current salary the following conditions must be met:
//&nbsp; Salary must be the most recent effective date that is before the payroll date per employee
&nbsp;
If([Effective Date]&lt;=[Payroll Date],true,false)

What I cannot figure out is how to limit this to (1) checked entry per Team Member and how to take only the most recent effective date.&nbsp;&nbsp;

Any thoughts?------------------------------Ivan Weiss------------------------------

donlarson · Answer

Ivan,
This should do it.

When you create the Pay Roll record, fire a Pipeline that searches for the Salary History record for the Related Team Member where

Pay Roll Date OAF Start Date AND&nbsp;
Pay Roll Date OBF Stop Date

Now you just use the Salary from the Compensation table as a Look Up field for all your calculations.------------------------------Don Larson------------------------------

ivanweiss · Answer

Hi Don, sorry for the slow response.&nbsp; Heavy travel week!&nbsp; So I could convert to something like this.&nbsp; However, today I do not create a "payroll record". I just run a report off of the team members table.&nbsp; I am not sure I have the need to actually record the payroll as a record although perhaps it would be an interesting idea as if I made the lookup/summary fields snapshots I guess it would record what was paid in that instance.

Thoughts on doing it without that payroll table or you think that is the best implementation?------------------------------Ivan Weiss------------------------------

donlarson · Answer

Ivan,

Having Payroll History is valuable for lots of reasons.&nbsp; However here is a way to do this without a payroll table.

Make a Formula Check Box field on the Compensation table that will go to true in the Payroll Period
Then summarize the Record ID of that Compensation record on the Team Members table.

Then make another relationship between Compensation and Teams where the Summary field is the Reference Field

Then make Salary a Look Up field in Teams.
That should always give you the salary for the current period.

The formula check box logic can get tricky for these types of questions.

------------------------------Don Larson------------------------------

Forum Discussion

Determining current record per employee (payroll compensation history)