TABLE API : query over multiple tables

[Paul108]

Hi,

I'm using the table api to get to retreive some information.

Something like;

.../api/now/table/u_responsibilities?sysparm_query=u_related_to.company=dc01d2a0a0a280a00c2bd3065d88de

A part of the XML-output look like ;

<u_email>xxxxx@***.***</u_email>
<sys_mod_count>0</sys_mod_count>
<u_alternate_phone>+316125879784</u_alternate_phone>
<sys_updated_on>2017-10-25 14:37:40</sys_updated_on>
<sys_tags/>
<u_business_phone>+316125879784</u_business_phone>
<u_user>
 <link>
 https://kpn.service-now.com/api/now/table/sys_user/b3a69e360a0a280a008f134eb04de8ef
 </link>
 <value>b3a69e360a0a280a008f134eb04de8ef</value>
</u_user>

In basic this works fine. But I also wants the user_name which is stored in de sys_user table.

Off course I can get this info by queriing the displayed link

https://kpn.service-now.com/api/now/table/sys_user/b3a69e360a0a280a008f134eb04de8ef

but then I need an extra step.

What I want is to do this with one url !! But I cann't figured out how to achieve this

Regards,

Paul

[Pradeep Sharma]

Hello Paul,

You can append sysparm_display_value=true to the Table API to fetch the reference field display value i.e query should be something like.

.../api/now/table/u_responsibilities?sysparm_query=u_related_to.company=dc01d2a0a0a280a00c2bd3065d88de&sysparm_display_value=true

Thanks,

Pradeep Sharma

[Bonnie Cresswe2]

Hi

You can dot-walk to fields on related referenced records if you include sysparm_fields in your URL, e.g. u_user.user_name

The only downside is that if you introduce sysparm_fields into your URL you will need to specify all the fields you want to return, in a comma separated list, so let's say you want all of those fields that are shown in your output snippet your URL would look something like this:

.../api/now/table/u_responsibilities?sysparm_query=u_related_to.company%3Ddc01d2a0a0a280a00c2bd3065d88de&sysparm_fields=u_email,sys_mod_count,u_alternative_phonee,sys_updated_on,u_business_phone,u_user.user_name,value

Regards

Bonnie

PS - Please mark as Helpful or Answered if this has helped or answered.

[Phonsie Hevey1]

Thanks! This saved me lots of time trying to figure out how to write some complicated code in the client controller of a widget. Dot walking to the other table I need the data returned from worked great.

[Paul108]

Thanks for the respons.

I'll try both options