PR formula to lookup employee rate

SUGGESTED

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")

  • 0

    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

  • 0 in reply to Art Minds

    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.

  • 0 in reply to John M Jankowski
    SUGGESTED

    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), 1Pay 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

  • 0 in reply to John M Jankowski
    SUGGESTED

    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

  • 0 in reply to Art Minds

    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!    

  • 0 in reply to Kristen C

    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? 

  • 0 in reply to Art Minds

    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.

  • 0 in reply to Kristen C

    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

  • 0 in reply to Kristen C
    SUGGESTED

    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

  • 0 in reply to Kristen C
    SUGGESTED

    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

  • 0 in reply to Art Minds

    Thank you Art.  Ok, yeah I thought about doing a division formula but couldn't figure it out because of overtime and employees working different hours, but now that you say it it does seem a lot easier if I can base it off of pay ID.    

    Can you help me out with what that formula would be?  I can't seem to get it right.  The current sort order is PR-Checks, print in employee, check order.  

    Thanks so much! 

    Kristen

  • 0 in reply to Kristen C

    Kirsten, the report would need to be designed using the PR - Check Pay record as the primary record, sorted by employee, p/e date, check seq.  The report would list each Check Pay detail record for each employee by p/e date and check seq.  You could include totals at that level.  You might want to contact your  Sage Timberline consultant if Report Design is not your strength.  If you don't have a working relationship with a consultant, please contact me offline by email. 

    Attached below are some screen captures of the starting point for the report design using the sample data in the Timberline Construction Company. You would then build your formula to calculate your rate using a conditional "IF" statement to determine the division based on the Pay Type (Regular Pay would just divide Amount by the Units, Overtime pay type would divide the Amount by the Units and then further divide by the OT factor specified on the Pay ID record if you need to calculate the underlying base pay rate on which the OT is calculated.

    Because you mentioned your Pay Rate tables, you may need to calculate the rates based on each job or other lower level detail than just the Check Pay record. In that case, you would need to drive the report from the PR - Time record, not from the PR - Check Pay record.

    Hopefully this gives you some direction in the design of your report. For further support, you should probably engage the help of a consultant, either your regular consultant or contact me offline via email.

    Art Minds
    Independent Consultant