What ? Apply filter/whereclause on lookup values
Why ? Easier to apply a condition on a list of values without a table domain.
Some Maximo fields like WORKORDER.WORKTYPE have a field level class and adding a table domain would add more complexity for applying a list where condition. Instead a custom lookups.xml configuration in application designer is simple approach to achieve it.
How ? Sample use case: Display items in Work Order plans tab that are in ACTIVE status or PENDOBS (Pending Obsolesce) status having current balance greater than zero.
Follow the below steps to filter values from a lookup
- Export the LOOKUPS.xml from Application Designer
- Open the xml file and add/modify new table section
- Add whereclause attribute on the table tag
<table id="activeitem" inputmode="readonly" selectmode="single"
whereclause="status='ACTIVE' or (status='PENDOBS' and exists (select 1 from inventory a, invbalances b
where a.itemnum = item.itemnum and a.itemsetid = item.itemsetid and a.itemsetid = b.itemsetid and a.location = b.location
and a.itemnum = b.itemnum and a.siteid = b.siteid and b.curbal > 0 and a.status in ( 'ACTIVE', 'PENDOBS')))">
- We can't use greater than and lesser than symbol directly in the lookup.xml, so replace them with equivalent characters.
- Condition in whereclause runs from Item table, so use item.itemnum inside the subquery
- Import the LOOKUPS.xml
- Link the custom lookup "activeitem" to the WOTRACK.xml application on the ITEM field under the Planned Materials section
References : limiting-lookups-using-whereclause
