Exclude matching descriptions from Maximo List tab searches

Not equal is not the same as does not contain

This came up during an upgrade that moved Maximo from a SQL Server database with text search enabled to a DB2 database without text search. In the previous environment, text search interpreted != as "does not contain". Without text search, the Description filter instead produced an exact inequality comparison. The aim was to restore the exclusion behaviour users were accustomed to.

Enter !=%TEST% in a Description search field and Maximo can generate:

UPPER(description) != '%TEST%'

That excludes the literal value %TEST%. It does not exclude every description containing TEST, because wildcards have no special meaning in an inequality comparison.

What I wanted was to enter !=test and get:

UPPER(description) NOT LIKE '%TEST%'

UI Bean Scripting lets us change the Work Order Tracking List tab's Description search without modifying Maximo's Java classes. The implementation below was developed and tested on Maximo Manage 9.1 with DB2; the DB2 version was not recorded.

This deliberately changes the meaning of != for this field: it becomes a substring exclusion rather than an exact inequality. Make that behaviour clear to the people using the application.

Enabling UI Bean Scripting

Maximo supports automation scripts that intercept methods invoked on application and data beans.

To enable this functionality, configure the following system property:

mxe.script.allowbeanscript=1

Apply the property change as required by your environment.

UI Bean Scripting allows us to execute Jython functions when supported methods are invoked on Maximo's UI beans.

For this implementation, we need to intercept the ResultsBean responsible for the Work Order Tracking List tab.

Identifying the ResultsBean

Before creating the final script, I used an application bean script to identify the ResultsBean associated with Work Order Tracking.

Create an automation script named:

APPBEAN.WOTRACK

Enable Allow Invoking Script Functions.

Add the following Jython:

def initializeApp(ctx):
    app = ctx.getAppInstance()
 
    print("Application: " + str(app))
 
    resultsBean = app.getResultsBean()
 
    print("ResultsBean: " + str(resultsBean))
    print("Bean ID: " + str(resultsBean.getId()))

The output identified the ResultsBean:

Application: AppInstance [id: wotrack]
ResultsBean: psdi.webclient.system.beans.ResultsBean
Bean ID: results_showlist

This gives us the application and bean identifiers needed to configure a DataBean script.

Attach a script to the ResultsBean

Create an automation script named:

DATABEAN.WOTRACKSEARCH

Enable Allow Invoking Script Functions.

Add two script variables:

The script name itself is not important beyond the DATABEAN. prefix. The literal variables identify the application and bean to which the script is attached.

With this configuration, Maximo resolves the script against:

WOTRACK~results_showlist

During testing, the Maximo logs confirmed that methods including setvalue() and filterrows() were being intercepted.

For example:

invokeFunction=setvalue
beanid=results_showlist
beanapp=WOTRACK

And:

invokeFunction=filterrows
beanid=results_showlist
beanapp=WOTRACK

The filterrows() method is particularly useful because it executes when Maximo processes the List tab search.

Override the Description QBE condition

The useful interception point is filterrows(ctx), when Maximo processes the List tab search. From there, the ResultsBean exposes its MboSet and the current Query By Example (QBE) value:

mboSet = ctx.getAppInstance().getResultsBean().getMboSet()
qbe = mboSet.getQbe("DESCRIPTION")

Testing showed that both !=test and !=%test% produced inequality comparisons. Typing not like '%test%' into the field did not supply raw SQL either: Maximo treated it as search text.

The method that lets us change the generated predicate while keeping the displayed input is:

MboSet.setWhereQbe(String attribute, String qbe, String whereExpression)

In the tested implementation, the third argument is appended to the attribute expression. Passing NOT LIKE '%TEST%' therefore produced description NOT LIKE '%TEST%'. To keep the case-insensitive comparison, the working expression was:

mboSet.setWhereQbe(
    "DESCRIPTION",
    "!=test",
    "IS NOT NULL AND UPPER(DESCRIPTION) NOT LIKE '%TEST%'"
)

This produces:

description IS NOT NULL
AND UPPER(DESCRIPTION) NOT LIKE '%TEST%'

IS NOT NULL completes the predicate after the automatically inserted attribute name, allowing the additional UPPER(DESCRIPTION) comparison. Null descriptions are excluded. Passing UPPER(DESCRIPTION) as the first argument did not work: that argument expects the Maximo attribute name.

The working script

Replace the diagnostic code in DATABEAN.WOTRACKSEARCH with:

def filterrows(ctx):
    """
    Extends the standard Maximo List tab search behaviour.
 
    For DESCRIPTION, a search beginning with != excludes
    records containing the supplied text, ignoring case.
 
    Example:
        !=test
 
    Generates:
        DESCRIPTION IS NOT NULL
        AND UPPER(DESCRIPTION) NOT LIKE '%TEST%'
 
    Other search expressions retain their standard behaviour.
    """
 
    # Retrieve the ResultsBean's underlying MboSet.
    mboSet = ctx.getAppInstance().getResultsBean().getMboSet()
 
    # Get the current Description search expression.
    qbe = mboSet.getQbe("DESCRIPTION")
 
    # Only process searches beginning with !=.
    if not qbe or not qbe.startswith("!="):
        return
 
    # Remove the operator and surrounding whitespace.
    searchText = qbe[2:].strip()
 
    # Ignore empty search expressions.
    if not searchText:
        return
 
    # Escape SQL quotes and normalise the search value.
    searchText = searchText.upper().replace("'", "''")
 
    # Construct the case-insensitive NOT LIKE expression.
    #
    # setWhereQbe() automatically prefixes the SQL with
    # the attribute name, so IS NOT NULL provides a valid
    # predicate before the additional UPPER() condition.
    whereExpression = (
        "IS NOT NULL AND UPPER(DESCRIPTION) NOT LIKE '%"
        + searchText
        + "%'"
    )
 
    # Override the SQL associated with the Description QBE.
    # The original search expression remains visible.
    mboSet.setWhereQbe(
        "DESCRIPTION",
        qbe,
        whereExpression
    )

The original QBE value stays visible in the search field. Inputs without the != prefix, and an empty exclusion value, return without applying this override.

What the test showed

Searching for !=cbtest generated this WHERE clause in the test environment:

(
    (woclass = 'WORKORDER' OR woclass = 'ACTIVITY')
    AND description IS NOT NULL
    AND UPPER(DESCRIPTION) NOT LIKE '%CBTEST%'
    AND historyflag = 0
    AND istask = 0
    AND siteid = 'BEDFORD'
)

The existing application restrictions remained, including the test environment's BEDFORD site condition. The visible QBE value remained !=cbtest, and clearing the field successfully removed the custom filter.

Input behaviour to check before wider use

The script doubles single quotes before embedding the value in the SQL literal. It retains SQL LIKE wildcard behaviour: % can match any sequence and _ can match one character. It does not turn those characters into literal text. The surrounding % characters make an ordinary value a substring pattern.

Test apostrophes, wildcard input, mixed case, null descriptions, clearing the filter, and switching from an exclusion to a normal search in your own environment. The supplied results establish the tested exclusion and clearing behaviour; they do not establish every input combination or database platform. Leading-wildcard searches with UPPER() also deserve a performance check against representative data.

Why the Where Clause dialog can lag behind

The working override runs in filterrows(), when the search executes. Entering !=test and immediately opening Advanced Search > Where Clause can therefore show the standard expression before the custom search has run.

I also investigated the ResultsBean's setvalue() event. Accessing the current UI event through UIContext exposed its target and value, and event.setValue(...) successfully changed the resulting QBE. That alone still used Maximo's normal QBE parser.

Applying setWhereQbe() from setvalue() produced the custom condition in server logs, but the Where Clause dialog still showed the original expression. Whether later processing overwrites the condition or the dialog builds its content separately remains unresolved. For this implementation, keep the working override in filterrows().

I Bend Maximo. Sometimes "not equal" needs a little more explanation.