Contains on Dataverse table filter odata query

(0) Share

Report

Posted on by Gyllentid

894

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.

Screenshot 2022-08-02 at 13.43.06.png

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 😞

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Sort by

MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

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

BR,
Marco

Was this reply helpful? Yes No
Gyllentid 894 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Gyllentid 894 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Gyllentid 894 on at

Like (0)

Report
Copy link

Link copied!
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-variable-value-as-array-for-a-attribute-in-fetchxml

Was this reply helpful? Yes No
Victor 365 7 on at

Like (0)

Report
Copy link

Link copied!

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 !!

Was this reply helpful? Yes No