how do you retrieve value column from sys_choice using table api

[jsamm]

Using table api with the following query

https://<site>/api/now/table/sys_choice?name=sometable&element=somecolumn

returns json

{

  "result": [

      {

          "label": "High"

      },

      {

          "label": "Medium"

      },

      //etc

  ]

}

How do you get the value column to be returned?   I've tried "sysparm_display_value=all".   I've tried "sysparm_fields=label,value".   Nothing works...when I specify a column that exists on the sys_choice table that is not "label" i get empty results...the correct number of results, but all empty

{

  "result": [

      {},

      {},

//etc

  ]

}

What i want is

{

  "result": [

      {

          "label": "High",

          "value": "1"

      },

      {

          "label": "Medium",

          "value": 2

      },

      //etc

  ]

}

Seems like a simple thing to do...can't seem to figure it out...when i query another table like incident with no filters or field specifications all the fields are dumped as expected...

[Mike Allen]

Even when using postman, to the url: https://dev13851.service-now.com/api/now/table/sys_choice?sysparm_query=name=incident^element=catego... , I get a full response:

[jsamm]

Yeah, i just did the reverse experiment with soapui...the problem lies on the server side...

[jsamm]

Same result with SoapUI...seems like the snow server side is the issue to me.   What controls are available to restrict the fields/columns that are returned by the rest api?   Is the table set up incorrectly?

[jberk · Accepted solution]

This is happening due to an ACL issue, the user doesn't have the "personalize choices" role so they only data on the sys_choice table they can see is the label, to correct this apply the "personalize", "personalize choices", or "personalize response"s role to the user you are running the query with and they should be able to see the rest of the fields on the sys_choice table. Alternatively you could alter the ACL that allows for all fields to be read on the sys_choice table.

-JB

[toddpreece]

I never would have guessed this in a year..totally solved this issue for us. Thanks.