How to build a complex query using javascript

I need to retrieve a list of interactions in a new view that will show all interactions with related incidents that have a problem.status of "Pending Project".

I know how to do this by querying the database directly and I know how to do a basic select in javascript in Service Manager but the query is the part that confuses me. It doesn't appear to be standard SQL. The query I would like to duplicate in javascript is the following:

SELECT *
FROM INCIDENTSM1
WHERE CATEGORY <> 'purchase request' and OPEN <> 'Closed' and INCIDENT_ID in
(select SOURCE from SCRELATIONM1
WHERE DEPEND in
(SELECT "NUMBER"
FROM PROBSUMMARYM1
WHERE problem_status = 'Pending Project')
)

Any help would be appreciated. If you also know of a better way to perform this action then that would be appreciated too. Thank you.
Parents
  • You cannot replicate that SQL statement exactly.

    But you can do something that will return the interactions you need, just without the im id's in the actual list. Users would have to enter the interaction and look on related records tab.

    In the view you put something like this in the query:

    jscall("scriptlibrary.function")

    in the javascript library you write your function. This function can be as complex as you like as a result it should return a string like this:

    incident.id isin {"SD001","SD002"}

    of course you can combine with other parameters in the view but if you are writing a javascript already then it migth as well do the main part of the select.
Reply
  • You cannot replicate that SQL statement exactly.

    But you can do something that will return the interactions you need, just without the im id's in the actual list. Users would have to enter the interaction and look on related records tab.

    In the view you put something like this in the query:

    jscall("scriptlibrary.function")

    in the javascript library you write your function. This function can be as complex as you like as a result it should return a string like this:

    incident.id isin {"SD001","SD002"}

    of course you can combine with other parameters in the view but if you are writing a javascript already then it migth as well do the main part of the select.
Children
No Data