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.
- Add a custom udActive field to the PR Earnings Code form and set all active codes to active. Make sure field type is bYN.
- Open UD User Defined Lookups form
- Create a new lookup by entering udFilteredEC in the Lookup name.
- In the title, enter Filtered Earnings Code
- In the from clause, enter PREC with (nolock)
- 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.
- In the join clause add the following text INNER JOIN PREH on PREC.PRCo = PREH.PRCo
- On the details tab, enter the columns as shown in the image at the bottom of this post.
- Open the PR Timecard Entry form
- Select the EarnCode Field and Press F3 to launch the field properties.
- Open the Lookup tab of Field Properties.
- Add udFilteredEC to the lookup list with -1,10 as the parameters, the Active box Checked, and 0 as the load Seq #.
- Deactivate the standard lookup by unchecking the Active box.
- Test the lookup to make sure it functions as expected.
- If necessary, deactivate the user defined lookup and reactivate the standard lookup to restore default Vista behavior.
/** 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