trying to figure out how to generate specific queries in expert search - Micro Focus Community

lwvirden · ‎2012-01-31

We are running hpsm 7.11 on windows 2003 servers.

I want to create and save a query that does the following:

select all interaction records that have an assignment field that is not equal to null and has a value that is not one of the names in the assignment table.

We found this bug in the escalation wizard that stores a user's input of the escalation assignment group in the interaction record even when the value is not one of the valid assignment groups. The wizard tells the user that what they typed is wrong and gives them a list to choose from... then stores both the wrong answer and the right answer in the record.

We want to be able to generate a qbe of those so that someone could go through and correct the records. After the corrections, reports will be more accurate.

The problem appears to be that the assignment column appears twice in the dbdict for interactions. One is an array, and the other is a character string. I need to construct a query that makes use of the character string definition, but I don't know how to do that when they both have the same name.

Does anyone know how I could go about constructing the query that I need?

Thank you.

Piku · ‎2012-01-31

Hi,
Not having the answer but as you mentioned that "assignment column appears twice" it is not so.Actually assignment is array type field which can hold the character type elements.Appearing consecutively of assignment in dbdict is way to represent the array type in SM table.
and to build a query you have to use JS or RAD script and need to call in query.

hth,

____________________________________
Assign Kudo, if found post useful and mark it accepted if solves the issue.

lwvirden · ‎2012-01-31

Thanks for explaining those lines in dbdict. Does anyone have an example of the building of a query using javascript or RAD code?

-m- · ‎2012-02-01

The following query was successfully tested against a 9.21 system running on Oracle:

not assignment isin $G.assignment

The above query should skip empty assignment fields, but here is the additional syntax just in case:

not null(assignment) and not assignment isin $G.assignment

Note: $G.assignment is a global variable built by the Assignment Groups globallist record.