Cost Code Lookup for Equipment Expense Example

Create a custom lookup that limits cost codes for Equipment expense items based on GL account overrides on the EM Department.

The following is just one example of how to write a custom lookup. If you use this lookup example, when a user enters an Equipment expense item in the portal, the Cost Code dropdown will show only the cost codes associated with the override GL account assigned to the EM Department.

You must be a System Admin to complete the following task.

  1. In the web portal, select Admin > Manage Lookups.
  2. In Select a Lookup, select Add New Lookup.
  3. In the SQL Choices Override box, enter the following:
    select Cast(kEMCC.KeyID as varchar(10)) ChoiceShortName, kEMCC.CostCode + ' - ' + kEMCC.Description as ChoiceLongName
from kEMDO
INNER JOIN kEMEM on kEMDO.EMCo = kEMEM.EMCo and kEMDO.Department = kEMEM.Department
INNER JOIN kEMCC on kEMDO.EMGroup = kEMCC.EMGroup and kEMDO.CostCode = kEMCC.CostCode
WHERE kEMEM.Equipment = @Equipment

    Note: Changes made on the Manage Lookups page are saved automatically.
  4. Select Evaluate Lookup to test the new lookup.
    If any errors are found, an error message will display at the bottom of the page. If no errors are found, no messages will display.
  5. Select the Active check box at the top of the page.
  6. Assign the new lookup to the portal setting Cost Code Override Lookup.