Reading the documentation, I see that the view is ‘PicklistOptions’ however now I’m seeing that I can’t seem to filter on columnname.

select * from PickListOptions where TableName = 'myentity' and ColumnName = 'mychoice'

This ends up returning all of the options from `myentity` without respect for the columnname…

I am also unable to filter the columnname in picklistattributemetadata. It appears the driver is not respecting this filter option during query processing.

Best I can determine, this is a bug with the ODBC driver. I’ve submitted a support request, will update with their response.

Hi @DaveJones4 

You might want to try enabling the IncludeNavigationProperties in the connection configuration by setting it in the Other connection property  as follows  Other= ‘IncludeNavigationProperties=True’ and give it another try by running the same query:

SELECT * FROM TPickListOptions] WHERE sColumnName] =’...’ AND �TableName]=’...’

Let us know if this helps.

Hi @Ethem Q  - thanks so much for the reply! I set that in the ODBC Configuration app as so:

But this did not yield any change to the query results, it’s still not respecting that filter criteria 

Here’s another weird thing happening… when I don’t filter at all, I don’t seem to get all the results. I have to add the tablename to ensure that I get the option set I’m looking for. 

In my explicit case, the tablename is `citations` and the column is `rsmhhs_scopeseverity` - without adding `tablename=’citations’` I don’t get `rsmhhs_scopeseverity` included in the returnset...

Hi @DaveJones4 

There have been a few changes recently for the CDS driver so you might need to get an updated version from the support team and see if anything changes. In the meantime, the reason why you are not getting all the results is because we are defaulting the TableName to Account so when you run a simple SELECT query against PickListOptions you will get data just for the Account table. In order to get all the data you can run a query as follows:

SELECT * FROM  PickListOptions] WHERE  TableName] IN (SELECT TableName FROM sys_tables)

Let us know if you have any questions about this.

Hey @Ethem Q  - that makes sense, thanks so much for that info. I think it would be great to get that added to the documentation!

Support did sent me the latest build which appears to fix this, but that tidbit is still important because if I try to query using only a columnname where clause, I’ll get no results (presumably because it’s not part of the account table), but adding the tablename fixes that. 

As of now I’m using 24.0.9049.0 and it is resolved. Thanks!

SELECT * FROM CPickListOptions] WHERE nColumnName] =’...’ AND �TableName]=’...’

One last question, is there any documentation that explains what this `IncludeNavigationProperties` means as a driver option? 

Hi @DaveJones4,

IncludeNavigationProperties is a boolean indicating if navigation properties should be included in the column listing. This property defaults to false. I had assumed that the reason why the data was not being returned for you was perhaps due to the driver not exposing all the available columns for the Picklists objects and we have often seen customers resolve similar issues with missing columns when enabling this property.

As a matter of fact, the IncludeNavigationProperties was a hidden property until recently, however I noticed that the team have exposed the property in the latest version so you should see the IncludeNavigationProperties as a visible property in your connection configuration. While we are yet to update our online documentation for this change, you can find it in the offline help file:

C:\Program Files\CData\CData ODBC Driver for Microsoft Dataverse 2024\help.htm

You should find the information about IncludeNavigationProperties in the Connection String Options settings under 'Miscellaneous'

If you have any other questions, feel free to reach out to the support team at [email protected].