Query multiple tables with Servicenow Rest API and Powershell

Question

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!

Answer 1

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