Dataverse Input Tool - Custom OData Filter

Hi,

Need a bit of advice here! I just started using Dataverse Input tool. I can authenticate to a CRM API endpoint and use the "Manually" option in define filters section to get data from some tables there.

I cannot figure out how to use the same type of filter expression that I am using on other APIs (still OData) in the "Custom OData Query" option of define filters within the tool, I keep receiving the "invalid custom OData query" error message.

I have also tried searching community posts for a solution or example but ran out of time and could not find a solution.

Can you please share a sample custom OData query that works for you or advise on what changes I need to make to the below?

One of the sample filter expressions I tried was (I tried using the entire expression and also excluding the "$filter =" bit):

$filter = year(ReservationDate) ge 2023

Many thanks

Error Message

Tips and Tricks

Connectors

Accepted answers

NazK

Could not get the Dataverse to work, forgot to post the final solution.

We used OAuth 2.0 direct implementation:

Go to the authorization endpoint for example https://login.microsoftonline.com/[TENANT_ID_Here]/oauth2/v2.0/token
Define the grant type, for example: grant_type = client_credentials
Define Scope for example: scope = https://[Org_Name_Here].crm[xx].dynamics.com/.default
Post using a download tool and parse the response to get Authorisation token.
Use the authorisation token to authenticate to the endpoint you want to query.
Create a FetchXML or OData query fitting your specific usecase and filters

From here you can do anything with the WebAPI call, be it a FetchXML or OData query.
Be mindful that current max page size for MS crm dynamics is "5000" so you will need to use paging cookies and an iterative macro that passes the nextPageLink value as the new url but you will need to also "url decode" that at the end of each loop since the first API call might have needed to be encoded by download tool and so on!

Tips:

For OData/Web API paging, Microsoft uses the @odata.nextLink property to handle pagination.
For FetchXML paging, including how to use count, page, and paging-cookie attributes, check out: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/page-results?tabs=sdk

All comments

apathetichell

Hey - sounds like you have a solid handle of odata api calls and syntax - why not just use the download tool and skip the dataverse input tool?

NazK

Hey thanks for the reply,

I have to stick with the dataverse for now as that is the only way the only available authentication method that is setup for us is using Service Principal method for now.

If I am not mistaken using the download tool we can implement OAuth 2 if we know authentication endpoint and what it will return to parse, or Basic Authentication that the tool allows on its own.

Not sure if I have seen a flow for it using Service principal authentication method but if you do know of a sample please do share!

Thanks

apathetichell

sorry- I haven't used dataverse - just odata with some other apis. tbh - however alteryx is authing in as service principal you should be able to do via download tool or python or powershell.

Joseph Douce

Did anyone find solution?

NazK

Not really ended up doing a direct OAuth2 implementation in the end and dealing with the queries in the url directly.

NazK

Could not get the Dataverse to work, forgot to post the final solution.

We used OAuth 2.0 direct implementation:

Go to the authorization endpoint for example https://login.microsoftonline.com/[TENANT_ID_Here]/oauth2/v2.0/token
Define the grant type, for example: grant_type = client_credentials
Define Scope for example: scope = https://[Org_Name_Here].crm[xx].dynamics.com/.default
Post using a download tool and parse the response to get Authorisation token.
Use the authorisation token to authenticate to the endpoint you want to query.
Create a FetchXML or OData query fitting your specific usecase and filters

Tips:

For OData/Web API paging, Microsoft uses the @odata.nextLink property to handle pagination.
For FetchXML paging, including how to use count, page, and paging-cookie attributes, check out: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/page-results?tabs=sdk

Scott_Morin

I was able to get this custom odata filter to work correctly in the connector. It's a simpler query but it confirms you need to include the $filter= at the star of the custom filter statement. For context, cr332_allocation is the logical name of the column that I'm filtering on in the table specified in the connector configuration.

$filter=(cr332_allocation eq 'Scott Test')

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703