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
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.
Hi Don, sorry for the slow response. Heavy travel week! So I could convert to something like this. However, today I do not create a "payroll record". I just run a report off of the team members table. 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,
Having Payroll History is valuable for lots of reasons. 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.
