export url with a join query

[Peter Tattlebau]

Hello,

I currently have a url that works:

https://uname:pwd@my.service-now.com/sys_user_list.do?CSV&sysparm_query=active=true^u_faculty=true^O...

My goal is to export a url that has both a department column as well as a column that includes only department names that have an & in them.

I know how to create this kind of query in mysql but have not had any luck searching the documentation to create this kind of join query for an export url.

In addition to the join, my confusion is how to label this column in the params portion of the query.

I have tried the query below using '^OR123department123=Gaston' to see if that would also search for department names that had Gaston in them but that did not work.

https://uname:pwd@my.service-now.com/sys_user_list.do?CSV&sysparm_query=active=true^u_faculty=true^O...

Any assistance pointing me in the right direction would be super greatly appreciated.

Thanks!

Peter T

[Tai Vu]

Hi @Peter Tattlebau 

The sysparm_query is received an encoded query, so basically you can apply your filter in the list. Then right click to copy the query like below.

Now in your url export, just paste the query to the sysparm_query for exporting.

https://<instance_name>/sys_user_list.do?sysparm_query=department=221db0edc611228401760aec06c9d929^title=Administrative Assistant^NQdepartment=221f3db5c6112284009f4becd3039cc9^title=Director&CSV

Cheers,

Tai Vu

[Peter Tattlebau]

Thanks Tai,

Unless I am wrong, it does not look like the filters can accomplish what I need.

What I need to do is replicate the department column so that it appears twice in the query,

Basically, I need the filter to replicate this query below

SELECT id, 
       CASE WHEN department like "%&%" or department like "%'%"
            THEN department 
            END department1,
       CASE WHEN department not like "%&%" or department not like "%'%"
            THEN department 
            END department2
FROM `departments_table`
HAVING department1 IS NOT NULL OR department2 IS NOT NULL

Thanks again,

Peter T

[Peter Tattlebau]

Rats, I realize I hit send too soon.  Is there a way to accomplish a query like this in ServiceNow?