Contains on Dataverse table filter odata query

[shavora]

Hi,

According to this guide the contains function works on dataverse tables (former cds). According to documentation substringof should work for odata filter queries. 

I get none to work.

Error: No function signature for the function with name 'contains' matches the specified arguments. The function signatures considered are: contains(Edm.String Nullable=true, Edm.String Nullable=true).

Error: An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

This is on a ID column, the guid. Removing the single quotes also gives error.

I really didn't want to do another apply to each 😞

[MarconettiMarco]

Hello @shavora ,

instead of using "Filter rows", please try to use the "Fetch Xml Query", it should be easier.

BR,

Marco

[shavora]

Hi, this looks interesting, can I add more conditions to it? I have some not equal to null and such I need to add? 😃

[MarconettiMarco]

Sure @shavora ,

you can build the query in the Dynamics365CRM Advanced find and download the Fetch XML.

Otherwise download the Xrm toolbox Fetch Xml builder:

https://www.xrmtoolbox.com/plugins/Cinteros.Xrm.FetchXmlBuilder/

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[shavora]

Mm, I don't have Windows so I will look in the Dynamics365CRM Advanced - do you know where this feature is located?

[MarconettiMarco]

Sure @shavora ,

select this function:

Then build your query and click on "Download Fetch XML":

Copy and paste the Fetch in Power Automate:

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[shavora]

Unfortunately, it's not working out. The "like" operator is giving me this error: Expected type of attribute value: System.Guid. Exception raised: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="key_candidates">
<attribute name="key_candidatesid"/>
<attribute name="key_name"/>
<filter type="and">
<condition attribute="key_candidatesid" operator="like" value="78bd0725-c5c0-ec11-983f-000d3a495916,d61b5bfa-11c7-ec11-a7b6-000d3a44569c,feda5b3b-14c7-ec11-a7b6-000d3a44569c,58f0987e-2eeb-ec11-bb3d-6045bd9072bf,825af739-bdd2-ec11-a7b5-0022487ed664," />
<condition attribute='statecode' operator='eq' value='Active'/>
<condition attribute='key_statuscandidate' operator='ne' value='100000006'/> 
<condition attribute='key_usercreatedinportalen' operator='not-null'/>
</filter>
</entity>
</fetch>

I found some more advanced way to use an array for a filter condition, but I think it's beyond my level still, but maybe for someone else: https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/317521/how-to-pass-dynamic-variabl...

[victorbe69]

Man, you just saved me 3 days of struggling with this unbearable ODATA filter row thing that is insane.

Now using XML fetch query is so much simpler. I just create my view & query on the CRM, and use the XML file in PowerAutomate.... THANK YOU !!