Scenario: If you have a custom lookup that returns blank when a field isn’t setup, you might want to tell the user to use another list.
Requirements: These steps assume that you own the UD Module. If you don’t own the UD Module, you could perform steps 1-4 with a SQL script.
Example Solution: This is for a Specific Use case that includes a UD Field, but can be applied to other use cases as needed.
- Open UD User Defined Lookups form
- Create a New Lookup named udSMCustomerDivision
- Title the Lookup SM Customer Division
- In the From Clause and the following text SMWorkOrder
- In the Where Clause, add the following text SMWorkOrder.SMCo = ? AND SMWorkOrder.WorkOrder = ?
- In the Join Clause and the Join Statement Below. The statement on line 15 can be edited as desired.
- Open the SM Work Order form.
- Select the Division Field and Press F3 to launch the field properties.
- Open the Lookup tab of Field Properties.
- Add udSMCustomerDivision to the lookup list with -1,212 as the parameters, the Active box Checked, and 0 as the load Seq #.
- 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.
Join Clause Code
INNER JOIN (select WorkOrder , SMDivision.SMCo , SMDivision.Division , SMDivision.Description FROM SMCustomer INNER JOIN SMWorkOrder ON SMCustomer.SMCo = SMWorkOrder.SMCo AND SMCustomer.CustGroup = SMWorkOrder.CustGroup AND SMCustomer.Customer = SMWorkOrder.Customer INNER JOIN SMDivision ON SMCustomer.SMCo = SMDivision.SMCo AND SMCustomer.udDivision = SMDivision.Division UNION ALL SELECT WorkOrder , SMCo , 'N/A' as Division , 'Division not set on this Customer; select from master list' as Description FROM SMWorkOrder wo WHERE (SELECT SMDivision.Division FROM SMCustomer INNER JOIN SMWorkOrder ON SMCustomer.SMCo = SMWorkOrder.SMCo AND SMCustomer.CustGroup = SMWorkOrder.CustGroup AND SMCustomer.Customer = SMWorkOrder.Customer INNER JOIN SMDivision ON SMCustomer.SMCo = SMDivision.SMCo AND SMCustomer.udDivision = SMDivision.Division WHERE SMWorkOrder.SMCo = wo.SMCo and SMWorkOrder.WorkOrder = wo.WorkOrder) IS NULL ) t1 on SMWorkOrder.SMCo = t1.SMCo AND SMWorkOrder.WorkOrder = t1.WorkOrder