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 this file, 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.