3

Is there a way to search/join multiple tables within one URI/call using PowerShell?

I'm trying to query the task table for any open TASK items and 'GET' back certain field values. The problem is that there are custom fields on the TASK item which are linked to other tables not on the TASK record/TASK table.

URI
$task = "https://instance.service-now.com/api/now/table/sc_task"

Adding filters to the URI
$task = "https://instance.service-now.com/api/now/table/sc_task?sysparm_query=yadadada"

..but I'm looking to be able to join multiple tables so I can "dot walk" to other tables other than sc_task.

I've been having a lot of trouble so any help will be much appreciated!

Thank you!

user2086204
  • 107
  • 1
  • 3
  • 10
  • The ServiceNow Table API docs doesn't mention any such functionality, so I don't think there is a way, no – Mathias R. Jessen May 02 '16 at 18:03
  • I looked and saw nothing either Mathias. I'm hoping someone will know a way on how to do this in an outside of the box fashion. Possibly something like making an SQL call from powershell. – user2086204 May 02 '16 at 18:07
  • 1
    "making an SQL call" is not going to help you - it's a REST API, not a directly exposed SQL server. Your only option is to retrieve all records from one table first, the other table separately and then implement the join logic client side – Mathias R. Jessen May 02 '16 at 18:10

1 Answers1

4

Depends on what direction your join is (target parent, reference child vs target child, reference parent)

If you're dot-walking through reference fields on the target table, you can just do it in the query like assigned_to.first_name=bob, and it will automatically join sc_task to sys_user on the assigned_to reference field.

If you're not actually dot-walking, but rather looking to walk down into child records like if you were querying the problem table, but wanted to join a Related List like related incidents where the child incident table has a reference field named problem_id pointing back up to your target problem table, you could create a Database view joining problem to incident on incident.problem_id = problem.sys_id, and name it something like problem_incidents. Then, you can just treat that Database View like a table (as far as the REST API is concerned) like https://instance.service-now.com/api/now/table/problem_incidents

Joey
  • 2,901
  • 21
  • 22