Using the ODBC driver, I am trying to query ChoiceLabels - this simple query works in SSMS but not using the ODBC driver in my reporting application:
select * from choicelabels
The odbc driver returns:
A table could not be found that matches choicelabels.
Is there something else I need to do to query this table? I need to join it against an entity table in order to decode the choice value.
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 [PickListOptions] WHERE [ColumnName] =’...’ 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!
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 support@cdata.com.