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).
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.
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!