Variable Table inquiry (sc_item_option & sc_item_option_mtom)

[Matt Campbell1]

Hello, I was able to figure out how to query from the sc_item_option & sc_item_option_mtom

But the results show the SYS_ID with the answers to the variable. not the actual question if that makes sense. 

Is their a way to query these tables or variables without using the sysid? I believe the sys_id is randomly generated.

Here is an output from the query. 

RITM RITM0049524
RITM sys_id: 8ff2c7c4db0393805f7f72e9af961973
Option items: 71
Sys ID: 03f2c7c4db0393805f7f72e9af96196f
Value:
Please provide the billing cost centre number
cost_center

Please provide the billing cost centre number

Sys ID: 0bf2c7c4db0393805f7f72e9af96196f
Value:
Please provide more information regarding the existing device.
ExistingCorporateMobileDeviceDetails

Please provide more information regarding the existing device.

Sys ID: c7f207c4db0393805f7f72e9af9619ed
Value: Matthieu
First Name
first_name

First Name

Sys ID: 8ff2c7c4db0393805f7f72e9af96197d
Value: Yes

Sys ID: 8ff2c7c4db0393805f7f72e9af961980
Value: No

Sys ID: 0bf2c7c4db0393805f7f72e9af96196b
Value:
End Date
EndDate

End Date

Sys ID: c3f207c4db0393805f7f72e9af9619ed
Value:
User Information
a_start

User Information

Sys ID: 83f2c7c4db0393805f7f72e9af961980
Value: Yes

Thanks,

Matt

[Mark Stanger]

I'm not sure what your query looks like, but generally, you can just add 'getDisplayValue()' to get to the friendly name of a reference field rather than a sys_id.

So, if I were retrieving the name of a user record in a reference field, for example, I could do something like this...

var userID = current.user;

var userName = current.user.getDisplayValue();

If this doesn't work or doesn't make sense post your script here and I can give you more specific direction.

[Matt Campbell1]

I'm using powershell for this.

What i'm trying to accomplish is to pull all the variable data from a "new user" request. It contains info like, what type of access the user needs, manager reporting to, profile etc.. 

#$credentials = Get-Credential
Clear

$ritm = "RITM0049524"

#get the ritm sys_id
Write-Host ("RITM: " + $ritm)
$query = "numberSTARTSWITH" + $ritm
$result = Invoke-WebRequest -Uri ("https:///api/now/table/sc_req_item?sysparm_query=" + $query) -Credential $credentials
$json = ConvertFrom-Json -InputObject $result
$ritmSysId = $json.result[0].sys_id
Write-Host ("RITM sys_id: " + $ritmSysId)

#query the sc_item_option_mtom table
$query = "request_item=" + $ritmSysId
$result = Invoke-WebRequest -Uri ("https:///api/now/table/sc_item_option_mtom?sysparm_query=" + $query) -Credential $credentials
$json = ConvertFrom-Json -InputObject $result
Write-Host ("Option items: " + $json.result.Count)

foreach ($x in $json.result)
{
$result = Invoke-WebRequest -Uri ("https:///api/now/table/sc_item_option/" + $x.sc_item_option.value) -Credential $credentials
$json2 = ConvertFrom-Json -InputObject $result
Write-Host ("Sys ID: " + $json2.result.sys_id)
Write-Host ("Value: " + $json2.result.value)
try
{
$result = Invoke-WebRequest -Uri ("https:///api/now/table/item_option_new/" + $json2.result.item_option_new.value) -Credential $credentials -ErrorAction Stop
$json3 = ConvertFrom-Json -InputObject $result
Write-Host $json3.result.sys_name
Write-Host $json3.result.name
Write-Host $json3.result.variable_name
Write-Host $json3.result.question_text
}
catch
{
}
Write-Host ""
}

[Mark Stanger]

Try adding the 'display_value' parameter to your URL like this.

/api/now/table/sc_item_option?sysparm_display_value=true

[Matt Campbell1]

IT seems to be working, going to tweak the script.