Hello John,

Your formula specifies the specific rate you wish to look up. In your example, you are looking up the rate for a PAY ID that matches exactly "REG" as you specified in your lookup formula.  If you wish to Lookup the rates for the other Pay IDs you listed, you would need to substitute those in your formula exactly as you've listed them.  Does each employee have more than one of the four Pay IDs on their Employee record?  If each employee has only one of the four Pay IDs on their record, you could use a formula with the MAX function and lookup the rates for all four pay IDs for each formula, with the MAX functioning returning the result of only the Pay ID that is populated with data on the Employee Record.  

Perhaps the best option would be to reference the Pay ID stored on the "Entry Info" tab of the Employee record, and use that variable in the formula you're use, rather than hard-coding in the "REG" that you have in the formula.

You might also consider looking up the Units and Amount from the Check Pay record, rather than the employee record, and divide the Amount by the Units to calculate the rate. The key fields for that record are Employee, Period End Date, Check Sequence, Pay ID.  

You may need to get more sophisticated in your formula.  It would be helpful if you gave an example of the ultimate calculation result you're trying to achieve.

Art Minds
Independent Senior Consultant

Each of the employees would only have one of the Pay IDs listed. I am trying to pull hourly rate to use in a formula to calculate Life/LTD and STD fringes. There may be additional Pay IDs for expense or other reimbursements that I do not want to include.

John,

If you have the Pay ID field populated on the Employee record, Entry Info tab, then you should use that field in your formula to specify the Pay ID Amount you want to look up from the Employee Pay record. Remember that a Lookup formula requires you to specifically target the record containing the information you need.

In this case, your formula would look like this:

LOOKUP(Amount (PR Employee Pay), Employee (PR Check), 1, Pay ID (PR Employee))

You will also need to incorporate an IF condition in your formula if the Pay ID is a salary amount, such as SAL to then divide the Amount by 40 rather than just returning the Amount.

Art Minds
Independent Senior Consultant

John,

Here's the formula that will divide by 40 if the Pay ID is a "Salary" pay (identified by the check box on the Pay ID setup screen):

IF(LOOKUP(Salary (PR Pay), Pay ID (PR Employee)), LOOKUP(Amount (PR Employee Pay), Employee (PR Check), 1, Pay ID (PR Employee)) / 40, LOOKUP(Amount (PR Employee Pay), Employee (PR Check), 1, Pay ID (PR Employee)))

Art Minds
Independent Senior Consultant

Hey Art,

This is great.  I have an additional question regarding this.  The formula provided worked for employees that had their pay setup in their employee file (Employee Setup-Pays).  However, we have some employees who are paid from a Pay Rate Table.  They are paid based on the union and classification they are setup as.  I can't seem to get their pay rate to pull.  Do you have any ideas regarding this?

The sort order I need on the report I am using this for is set to PR-Checks, employee, check order.

Thanks!    

John, your original question was posted over a year ago; you're really testing my memory! Let's start from the beginning with a clear explanation of the use of the formula. After reading your initial statement of the problem, you need a formula that will be used during check processing to calculate amounts that will be recorded as a fringe amount for each specific check? Is that correct or close to correct?  In other words, this formula is not calculating an amount on already printed PR checks?  If my assumptions correctly state the situation, do you also need to allocate this fringe to the job, department, union, etc on the original time entry that triggers the calculation of the fringe? 

Hi Art,

I think you were reading the old post and responding to the original.  My question is about pulling an employee rate from a pay rate table.  I tried your answer above and it worked perfectly for an employee who has their pay setup in their employee file.  However, we have employees that have their pay rate based off of a pay rate table, so their rate is not hard-coded in their employee setup.  It is based on their union ID, which then pulls the corresponding pay rate from the pay rate table.  I can't seem to get that rate to show up.  Any ideas?

Thanks.

Hi Kristen,

I apologize for misunderstanding your need.  

Are you trying to create a fringe calculation to be created on a check during check processing to ultimately be posted to the Fringe accumulators for each employee?  Are you attempting to calculate a "cash fringe amount" to pay the employee when the actual fringes fall short of a mandated certified pay rate? If so, search the Knowledgeable for "Formula to calculate cash fringe" and you will see multiple solutions.

Or are you attempting to produce a report after the fact, looking at historical payroll check data and incorporating multiple pay periods over a specified time period? 

Art Minds
Independent Senior Consultant

Hi Kristen,

I just replied, but it just occurred to me that you may not be following up on John's original post, but may have jumped in to this thread with a completely different request.

To answer your basic question directly, to lookup a rate from a Pay Rate Table requires specifying the Rate Table Key to obtain the correct rate that matches your input. 

In the Knowledgeable search for Article ID:93928 Pay Rate Table Lookup Formula.

Art Minds
Independent Senior Consultant

Hi Kristen,

I just replied, but it just occurred to me that you may not be following up on John's original post, but may have jumped in to this thread with a completely different request.

To answer your basic question directly, to lookup a rate from a Pay Rate Table requires specifying the Rate Table Key to obtain the correct rate that matches your input. 

In the Knowledgeable search for Article ID:93928 Pay Rate Table Lookup Formula.

Art Minds
Independent Senior Consultant