0

I'm trying to produce a list of Projects (from a custom table called "pm_project") and the users who are working on those projects.

Using an API Explorer I can get a list of Projects (below is the example of my API and data).

API: https://my-instance.service-now.com/api/now/table/pm_project?sysparm_fields=number%2Cu_resource_list&sysparm_limit=1

Data:

{
   "result":[
      {
         "assigned_users":"7961b907db9253045fbdf1fabf9619d4,55617907db9253045fbdf1fabf9619d2,c4c46419dba6d7045fbdf1fabf9619b5",
         "project_number":"11216"
      }
   ]
}

And using a separate API (see below example of my API and the data), I can get the list of users from "Sys_User" table.

API: https://my-instance.service-now.com/api/now/table/sys_user?sysparm_query=&sysparm_fields=sys_id%2Cemail

Data:

{
   "result":[
      {
         "sys_id":"7961b907db9253045fbdf1fabf9619d4",
         "email":"test_user1@my-site.com"
      },
      {
         "sys_id":"55617907db9253045fbdf1fabf9619d2",
         "email":"test_user2@my-site.com"
      },
      {
         "sys_id":"c4c46419dba6d7045fbdf1fabf9619b5",
         "email":""
      }
   ]
}

Then, I'm performing joins between data from "pm_project" and "sys_user" outside of ServiceNow to assign "emails" instead of "sys_id" to the projects and produce the final list like one below:

{
   "result":[
      {
         "assigned_users":"test_user1@my-site.com,test_user2@my-site.com,",
         "project_number":"11216"
      }
   ]
}

Is there a way (may be Scripted REST API) where I can do this join inside ServiceNow itself? And also, I don't want to include users with empty "email" from "sys_user" in my final result. Can someone please advise!

Victor McIntyre
  • 93
  • 1
  • 10

1 Answers1

2

I though this might be possible using a Database View (https://docs.servicenow.com/bundle/paris-platform-administration/page/use/reporting/concept/c_DatabaseViews.html), but it appears that you are using a Glide List to hold the list of users. Database views are rather limited, and there is no way to perform a join using a Glide List.

Your only option would be to create a Scripted REST API, which would be completely custom JavaScript. You can do anything you want in a Scripted REST API. You can return any data that you want and filter the data anyway you want.

giles3
  • 465
  • 2
  • 9