Scenario: Looking to restrict the lookup values based on the active earn codes and based on salary vs hourly

Requirements: These steps assume that you own the UD Module. If you don’t own the UD Module, you could perform steps 2-8 with a SQL script.

Example Solution:

  1. Add a custom udActive field to the PR Earnings Code form and set all active codes to active. Make sure field type is bYN.
  2. Open UD User Defined Lookups form
  3. Create a new lookup by entering udFilteredEC in the Lookup name.
  4. In the title, enter Filtered Earnings Code
  5. In the from clause, enter PREC with (nolock)
  6. In the where clause, copy and paste the query text from SQL Query found below, modify as needed to use your earn codes and your custom field name.
  7. In the join clause add the following text INNER JOIN PREH on PREC.PRCo = PREH.PRCo
  8. On the details tab, enter the columns as shown in the image at the bottom of this post.
  9. Open the PR Timecard Entry form
  10. Select the EarnCode Field and Press F3 to launch the field properties.
  11. Open the Lookup tab of Field Properties.
  12. Add udFilteredEC to the lookup list with -1,10 as the parameters, the Active box Checked, and 0 as the load Seq #.
  13. Deactivate the standard lookup by unchecking the Active box.
  14. Test the lookup to make sure it functions as expected.
  15. If necessary, deactivate the user defined lookup and reactivate the standard lookup to restore default Vista behavior.

SQL Query

/** User Defined Lookup **/ 

/** Purpose: Used to Filter Earn Codes Available to Employee Based on Default Earn Code (Hourly or Salary). **/

/** Created by Constructive Tech Solutions 8/3/2018 **/

PREH.PRCo=? AND PREH.Employee = ?  --The allows the entry form to pass the PR Company and Employee number to the query
AND PREC.udActive = 'Y'  --udActive needs to be added to the PREC table.  If you only want to filter on Active/Inactive remove the remainder of this query.
AND 1 = CASE 
WHEN PREH.EarnCode = 1 AND PREC.EarnCode in (1, 6, 7, 30) THEN 1 --This line allows codes 1, 6, 7 & 30 when the employees default earn code is 1 (i.e. they can't select salary if they are hourly)
WHEN PREH.EarnCode = 4 AND PREC.EarnCode in (4,14, 15) THEN 1 --This line allows codes 4, 14 & 15 when the employees default earn code is 4 (i.e. they can't select houlry if they are salary)
ELSE 0 
END