Trying to use Table REST API to query variable values for a request

[kcwong]

Based on the information shared in this blog: 

https://www.supernow-blog.com/blog/querying-related-tables.html

I'm trying to grab a specific variable value for a Request. What I have so far: 
URL: https://{{baseURL}}/api/now/v1/table/sc_item_option_mtom
For sysparm_query, I'm using: 
request_item=[Request's sys_id]
^JOINsc_item_option_mtom.sc_item_option=sc_item_option.sys_id
^JOINsc_item_option.item_option_new=item_option_new.sys_id!name=[name of variable]
For sysparm_fields: 
request_item,sc_item_option.sys_id,sc_item_option.value,sc_item_option.item_option_new.name,sc_item_option.item_option_new.reference

But it doesn't work if the last part, !name=[xxx] is included. 
Without specifying name, I will get a list of all variable/value for that Request. With name, I get 0 result. 

That syntax should be valid according to the blog (I keep the left table different for the two JOINs), and my own testing using a single JOIN. 

How can I make it work for two JOINs? 

[Tanushree Maiti]

Hi @kcwong 

Instead of this long query, you can create a database view and fetch the details from there.

• Navigate to System Definition -> Database Views -> New

Order	Table Name	Variable Prefix	Where Clause	Purpose
100	Variable Ownership (sc_item_option_mtom)	mtom		Maps the specific variable value to its parent item.
200	Options (sc_item_option)	opts	opts.sys_id = mtom.sc_item_option	Stores the individual variable values.
300	Requested Item (sc_req_item)	ritm	ritm.sys_id = mtom.request_item	References the main Requested Item (RITM) record.

Refer: Two ways (Database View and Variable config) to create Catalog Item variable report

Report on Item Variables in the Service Catalog with a Database View

Reporting on Catalog item variables

[kcwong]

Can I query the created Database View with REST API? I need the Variable values via REST API, as I have a script for a lot of other stuff, the variables are only a tiny part of it. 

[Tanushree Maiti]

Hi @kcwong ,

Only Get operation will work on database view table. Which is your requirement.

Refer this post for API : https://www.servicenow.com/community/developer-forum/using-database-view-in-rest-apis/m-p/2168944