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.

  1. Open UD User Defined Lookups form
  2. Create a New Lookup named udSMCustomerDivision
  3. Title the Lookup SM Customer Division
  4. In the From Clause and the following text SMWorkOrder
  5. In the Where Clause, add the following text SMWorkOrder.SMCo = ? AND SMWorkOrder.WorkOrder = ?
  6. In the Join Clause and the Join Statement Below. The statement on line 15 can be edited as desired.
  7. Open the SM Work Order form.
  8. Select the Division Field and Press F3 to launch the field properties.
  9. Open the Lookup tab of Field Properties.
  10. Add udSMCustomerDivision to the lookup list with -1,212 as the parameters, the Active box Checked, and 0 as the load Seq #.
  11. Test the lookup to make sure it functions as expected.
  12. 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