Service Manager / Service Center Practitioners Forum

View Help Needed

David Nottingha
Valued Contributor.

View Help Needed

Hey I am trying to develop a veiw that shows me all changes in a certain state that have no tasks assigned to them. Any help provided will be appreciated.

Jacob Heubner
Acclaimed Contributor.

Re: View Help Needed



Well, the challenge is that what you're asking for sounds more like a report than a view... The purpose of a view is to return a list where the operator then needs to take a particular action on those tickets.  So there's not really the concept of a joined view with a kind of excludes idea that you're looking for.  


So, first, I'd say you probably want a report, rather than a view.


But, assuming you MUST do this, I think it can be done with a combination of the views and a ScriptLibrary script.


First, create a script library script that performs the query on the MAIN table you want in your view.  For example, you want all Change tickets in the Change Logging phase assigned to a particular assignment group.  Include in that script a second query that iterates through the list and searches for Task records.  So have a script like:


Script Name: trickyView

Script -

function getChangesWithNoTasks(){

var recList = new Array;

var changeRec = new SCFile("cm3r");

var getChange = changeRec.doSelect("open = true and current.phase=\"Change Logging\" and assign.dept=\"My group\"");

while (getChange==RC_SUCCESS){

var taskRec = new SCFile("cm3t");

var getTask = taskRec.doSelect("parent.change=\""+changeRec.number+"\"");

if (getTask != RC_SUCCESS){



getChange = changeRec.getNext()


return recList




THen, make the query in your View call that ScriptLibrary script.  


For example:

Query: number isin jscall("trickyView.getChangesWithNoTasks")


THen, when someone looks at the view, the ScriptLibrary script will be called, which will return a set of Change records.


Now, there is a limit to this - I forget the exact amount, but there's sizing limit for the number of items you can put in an "isin" type query, so I would NOT recommend this if you're doing a TRUE search on all change tickets ever opened, or querying changes that may have thousands of records in this phase.  


If need be, you can modify the ScriptLibrary script to take additional paramaters - like the users' assignment group or the operator's ID, or whatever, so that you can have some flexibility in the query.  But this should be enough to kind of get you started.

Super Contributor.

Re: View Help Needed

David, from Task Search it's possible to find de related changes because the cm3t table has the parent.change field but in the other way (from Change Search, cm3r related with cm3t) i don´t find any field like parent.task (for example), this situation is always confused for me :  fisical relation between tables  doesn´t ensure a virtual join.

Well, i've didn´t help you but sure somebody else could help.

Good questions will have Good answers
Vadim Gorda
Acclaimed Contributor.

Re: View Help Needed


Jacob such script in the view query wont strongly affect the productivity of the view?


 suppose that it will take way long to populate the records. Ofc it depends on actual number of changes and tasks in there , but it will increase while time is passing anyway.

Jacob Heubner
Acclaimed Contributor.

Re: View Help Needed

Vadim -

I think he'll hit the limit of items that can be returned in an 'isin' query before he hits a significant performance hit. It is, of course, slower than just querying the Change tickets themselves, because he does have to iterate through each change to check for Tasks; that's one of the reasons I suggested he do this via a report rather than a view.


The solution provided is in case he has no other options. I don't recommend this be done as a view, especially as it sounds like something that would be used for reporting purposes.


But, I was operating on the assumption that this HAS to be a view. And, I also stated in my response that this would NOT be something you'd want to run when you're doing a TRUE search on all change tickets.


But, I gave it a shot in my environment before I posted the solution. We have several thousand change tickets, but the query example I gave (tickets in a particular Change Phase assigned to a particular group) did not take an excessive amount of time to return the results of the query.

Vadim Gorda
Acclaimed Contributor.

Re: View Help Needed

Hello Jacob,

it was just a question like how normally will work that view, as you might have tried this on your system.


I saw that you were insisting on report and I agree with you on that.


All similar insane view request should be negotiated with the requester(I meat similar requests in my projects) and gently pushed to reporting :)


If we take away the fact that it is really task of reporting, then Jacobs solution can fit the view for 100%.

Jacob Heubner
Acclaimed Contributor.

Re: View Help Needed

I agree... The issue is that people want to create a view and then export to Excel or .csv file, and use the view as the source of their reports.


I keep having to remind our users that, if it's a complicated query, the end result of which is to reach out to some team, some leader, some manager to get them to perform an action in the tool, that it would be something better suited for a report.  Various reporting tools would be able to generate a list like that more easily, and SQL joins allow for those kinds of queries.


However, I do like making things work in HPSM - making the tool as functional as I can make it for users who need it.