Create Custom Lookups for Timecard Entry

The employee portal is designed to take advantage of the Vista lookup framework.

If you wish to customize the dropdown list in the portal, you have to make a Vista Custom lookup.

The steps below describe how to limit earn codes in the portal based on the standard earn code of an employee.

Note: Your portal will only be able to show the first two columns of any lookup.

Part 1: Create the Earn Code Lookup

  1. Open the form UD User Defined Lookups (User Database > Programs > UD User Definted Lookups)
  2. Create a new lookup named udFilteredEC
  3. Give the lookup a Title. In this example we use Filtered Earnings Codes
  4. Where Clause: PREH.PRCo=? AND PREH.Employee = ? AND 1 = CASE WHEN PREH.EarnCode = 1 AND PREC.EarnCode in (1,2,3) THEN 1 WHEN PREH.EarnCode = 4 AND PREC.EarnCode in (4,7,9,11,15,17) THEN 1 ELSE 0 END
  5. Fill in the Join Clause: INNER JOIN PREH on PREC.PRCo = PREH.PRCo

In the example above, the earn code 1 is for hourly employees. The statement that says WHEN PREH.Earncode = 1 is looking at the form PR Employee and is checking the default Earn Code. The second part of the statement AND PREC.EarnCode in (1,2,3) means that if the Employees Standard Earn Code = 1 then only show the earn codes 1,2,3 (Standard Time, Over Time, Double Time). In the example above, if you wanted to add Vacation Hourly (Earn Code 8) you would change the statement to read AND PREC.EarnCode in (1,2,3,8).

Part 2: Set Up the Lookup in PR Timecard Entry

  1. Open PR Timecard Entry in Vista (Payroll > Programs > PR Timecard Entry).
  2. Open a batch.
  3. Select the Earn Code field and select the Field Properties Button.
  4. Fill the grid out to look like the image below.
  5. In order for the lookup to show up in the portal, the Load Seq # must be set to 0. If you do not want this to be the default lookup in Vista, uncheck the Active checkbox. It will be active in the Portal, but not in Vista.

Part 3: Create the Phase Lookup

The steps below describe a lookup that only shows active labor phases on a job.
Note: You will need to adjust your cost type to match your labor cost type.
  • From
    • JCCH
  • Where
    • CostType = 1 and JCCH.JCCo=? and JCCH.Job=? and JCCH.ActiveYN = 'Y' and JCJP.ActiveYN = 'Y'
  • Join

    • Left Outer Join JCJP On JCCH.JCCo=JCJP.JCCo and JCCH.Job=JCJP.Job and JCCH.Phase=JCJP.Phase

  • Details
    • JCCH.Phase
    • JCJP.Description

Change the Cost Type to the cost type your company uses for labor. (E.g. in the above example CostType = 1 means that the Cost Type 1 is labor. If your labor cost type is 5 the statement would read CostType = 5.) Follow the steps in Part 4 to connect the lookup to the timecard.

Part 4: Set Up the Lookup in PR Timecard Entry

  1. Open PR Timecard Entry in Vista (Payroll > Programs > PR Timecard Entry).
  2. Open a batch.
  3. Select the Phase field and select the Field Properties Button.
  4. Fill the grid out to look like the image below.
  5. In order for the lookup to show up in the portal, the Load Seq # must be set to 0. If you do not want this to be the default lookup in Vista, uncheck the Active checkbox. It will be active in the Portal, but not in Vista.


For additional information on setting up custom lookups, please see Vista Help.