PR formula to lookup employee rate

Question

We are trying to create a formula that will provide the hourly rate for an employee that can then be used in fringe calculation formulas. I can use a basic LOOKUP if there are only 2 Pay IDs. We have 4 different Pay IDs under two Pay Groups that we need to pull the rate from. Anyone know how to create a formula that can lookup one of the following Pay IDs? 
 
 REG1 
 REG2 
 SAL 
 EST 
 
 Here was what I had used but it only pulls in rate for "REG" Pay ID. 
 LOOKUP(Amount[PR Employee Pay], Employee [PR Check],1,"REG")

Art Minds · Answer

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

Art Minds · Answer

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

Art Minds · Answer

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

Art Minds · Answer

Hi Kristen, 
 To elaborate on looking up rates in a rate table, you need to identify the Rate Table that you wish to use for the lookup, and your Lookup formula must provide the necessary components included in the Rate Table Key if the lookup is to find a matching rate. As you know, you can setup multiple Rate Tables, and each table may have different fields used to determine the rate. When the system uses a rate table during check processing to retrieve a rate, it always returns the highest rate. To create a report after the fact to obtain a rate, you would have to use the MAX function and lookup all possible matching rates in each Rate Table, and the MAX function would return the highest rate. 
 Have you considered pulling historical information for prior payroll checks from the Check Pay record, which includes the Pay ID, Units (Hours) and Amount. A simple division formula can calculate the rate by dividing Amount by Units. 
 Art Minds Independent Consultant

Sage Construction & Real Estate

PR formula to lookup employee rate

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING