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
Leave A Comment