How to make query using two tables in servcienow api

Phanikanth
Kilo Contributor

‎03-30-2022 10:09 PM

Hi Team,

I have an api  which will give the result based on number and I want to filter the query using "requested_for" filed(Name).

How can make condition that will fetch the  details based on "requested for",it was in diffrent table.

Ex:

1)https://****.service-now.com/api/now/table/sc_req_item?number=RITM0016087

Response:
            "u_requested_for": {
                "link": "https://****.service-now.com/api/now/table/sys_user/fbb0511fdb2c6f0034f1f77c29961979",
                "value": "fbb0511fdb2c6f0034f1f77c29961979"
            }
2)https://****.service-now.com/api/now/table/sys_user?name=Selina Forte
 
Response:
                "name": "Selina Forte",
              "employee_number": "C0653",
                "u_org_code": "D52230",
                 "gender": "",
0 Helpfuls
  • 1,493 Views
1 REPLY 1

Service_RNow
Mega Sage

‎03-30-2022 10:19 PM

Hi

,

You've likely found a solution by now but in case others might want options I managed to achieve a join of 2 tables by passing an encoded join query via sysparm_query.

As part of a data transfer I wanted to verify if a list of attachments had been uploaded, the problem was that the some of the attachments had duplicate file names but I knew which RITM record they should be associated with.

The resulting query would look like this:

GET https://<instance_name>.service-now.com/api/now/attachment

table_name=sc_req_item^file_name=<duplicated_file_name>^!JOINsys_attachment.table_sys_id=sc_req_item.sys_id!number=<RITM_number>

However, when applying a filter on both the sys_attachment table and the sc_req_item table, the response body is empty, but the header attribute 'x-total-count' indicates the number of records returned by the call. In my case that's all I needed to confirm the existence of an attachment.

The response body is returned as expected if only one of the tables is filtered e.g:

table_name=sc_req_item^file_name=<duplicated_file_name>^!JOINsys_attachment.table_sys_id=sc_req_item.sys_id

or

JOINsys_attachment.table_sys_id=sc_req_item.sys_id!number=<RITM_number>

The same issue doesn't seem to occur when using the table API and I was able to successfully perform the following call:

GET https://<instance_name>.service-now.com/api/now/table/sc_item_option

sys_idSTARTSWITH4^!JOINsc_item_option.sys_id=sc_item_option_mtom.sc_item_option!^JOINsc_item_option_mtom.request_item=
sc_req_item.sys_id!sys_idSTARTWITH4

Which returns records from the table storing variable values where there sys_id starts with '4' where the sys_id of the RITM they are associated with (via the MTOM table) also begins with '4'. Why you would want to do this, I don't know, but it shows what is possible.

 

Hope this helps!