Absent Member.
Absent Member.
602 views

trying to figure out how to generate specific queries in expert search

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.

0 Likes
3 Replies
Absent Member.
Absent Member.

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.
0 Likes
Absent Member.
Absent Member.

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

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.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.