Maximo Filter data using LOOKUPS whereclause tag

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 &gt; 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.

         greater than  (>) -->  &gt;      lesser than (<) -->  &lt;

• 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

Maximo server host name for hyperlink in BIRT report

What?  get the server hostname of the environment where the BIRT report is running.

Why? In a few scenarios, we need to form a URL in hyperlink property to point it to Maximo record or any other integrated application specific to environment.

Maximo BIRT framework don’t support implicit variable :HOSTNAME. This variable is handy on getting the server host name inside Maximo applications like communication template.

How? In order to achieve it inside BIRT, we need to run the sql query in MAXPROPVALUE table from the system property mxe.hostname on the beforeOpen method of the dataSet; it can also be from any instance or custom system property.

Store the hostname in a Global variable - hostname to use them inside the report.

Use the variable in a cell hyperlink property to point to specific Maximo host name

"https://" + reportContext.getPersistentGlobalVariable ("hostname") + "/maximo/ui/login?event=loadapp&amp;value=asset&amp;additionalevent=sqlwhere"

+"&amp;additionaleventvalue=assetid = (select assetid from asset where siteid = '"+row["siteid"]+"' and assetnum = '"+row["assetnum"]+"')"

report file - hostname_hyperlink_design_file

Courtesy: Madhavan