Retrieving column choices

Starting from version 3.8, Enesys SharePoint Query allows you to easily retrieve a list of all the possible choices from any column of type Choice (or derivated).

It is handy when you need to quickly fill the possible values for a parameter, or if you need to display the string representation of some workflow columns.

Add a new columnChoices statement:

The columnChoices statement allows you to retrieve the choices from a list or a site column. In the first screen, you will need to choose whether you want to retrieve data from a list or a site column.

If you want to retrieve choices from a site column, check the correct radio button and enter the relative URL to the SharePoint site.

If you want to retrieve choices from a list column, check the correct radio button and click on the Add button. It will now display a form for selecting the list containing the column.

Once you have selected your list or your site, you can select the column to query.

The designer only displays the columns of type “Choice” or derivated:

Note that you can choose to show all columns by unchecking the “Show only Choices” checkbox, but those columns won’t be selectable.

Once the column is selected, you can execute the statement and see the results:

As you can see on the previous screen shot, 2 columns are returned:

• ID : either contains the lookup ID or an auto-incremented integer
• Choice : contains the possible choices

Having an auto-incremented integer allows you to join the columnChoices statement with a list statement to have string representations for some integer values.

For example, columns of type “WorkflowStatus” return integer values (Approval column):

By joining the list results with a columnChoices , you can have the string representation of those values.

Using a columnChoices statement, we query the same list, and select the Approval column of type WorkflowStatus:

We now need to join the Approval column from the list statement, with the ID column from the columnChoices statement using a sqlQuery statement:

A string representation for the Approval column is now available.