Unanswered

Using contain-values operator for MultiSelectPicklistType (Choices) in outer-joined FetchXML makes it behave like an inner join.

(0) Share

Report

Posted on by DFA

I have tried to simplify the setup as much as possible.

I have two custom entities, cra4f_myFirstentity and cra4f_mySecondentity, with a 1-N relationship between them.
cra4f_mySecondentity contains a MultiSelectPicklistType (Choices), called cra4f_mychoices, with the possible values of 538410000, 538410001 and 538410002.

There is one record in cra4f_myFirstentity which matches one record in cra4f_mySecondentity.
The one record in cra4f_mySecondentity has it’s cra4f_mychoices set to 538410000.

Using the eq-operator with link-type set to outer works as expected.
It returns every record in the first entity, no matter if the second entity matches or not.
If the second entity matches, then it is included in the result (not the case here):

Using the contain-values operator with link-type set to outer does not seem to work as expected.
It only returns records from the first entity if the second entity matches (not the case here):

Is this a known issue? And is there a recommended workaround?

I could generate all possible combinations and apply them using the in-operator on a or-filter.
For the above example, 538410001 would become something like:

But it seems that the whole purpose of the contain-values operator is to avoid this mess.

This is a re-post from the Microsoft Dynamics 365 forum, as I was informed that this forum is a better match.

Thanks,
DFKA

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (13)

Answers (0)

Prakash4691 1,332 on at

Like (1)

Report

Hi @DFA ,

There is no operator called "contain-values" instead it has "contains" but that will check for string value not choices. If you want to check for multiple choice selection you have to use "in" operator like in below screenshot.

If it answers your question, kindly give kudo and accept it as solution.

Regards,

Prakash

Was this reply helpful? Yes No
DFA 21 on at

Like (1)

Report

"Two new condition operators have been added to support querying values in choices: ContainValues and DoesNotContainValues or the FetchXml contain-values and not-contain-values operators."
Source: https://docs.microsoft.com/en-us/powerapps/developer/data-platform/multi-select-picklist#query-data-from-choices

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

@DFA ,

That works only for ODATA filter not in fetch xml.

Was this reply helpful? Yes No
DFA 21 on at

Like (1)

Report

Why does it then say FetchXML, and contains examples for FetchXML?

Just to be clear, the contain-values operator works as expected in every query I have, except for the ones which are link-type outer.

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

@DFA ,

In your case is that choices or multi select option set because through UI you will not be able to create multi select option set.

Was this reply helpful? Yes No
DFA 21 on at

Like (0)

Report

“MultiSelect option set” in D365:

In the D365UI it is displayed like this:

Under Microsoft Power Platform admin center the type of this same field is “Choices”:

And it is displayed in a model-driven app like so:

Was this reply helpful? Yes No
meelamri 468 on at

Like (1)

Report
Hi,

I replicated the same scenario in my environment. You are right, I reproduced the same issue using the following fetchXml

<fetch> <entity name="mea_myfirstentity" > <attribute name="mea_name" /> <link-entity name="mea_mysecondentity" from="mea_myfirstentity" to="mea_myfirstentityid" link-type="outer" > <filter> <condition attribute="mea_mychoices" operator="contain-values" > <value>862010000</value> </condition> </filter> </link-entity> </entity> </fetch>

However, I have the correct result using OData request

/api/data/v9.2/mea_myfirstentities?$select=mea_name&$expand=mea_myFirstentity_mea_myFirstentity_mea_m($filter=(Microsoft.Dynamics.CRM.ContainValues(PropertyName='mea_mychoices',PropertyValues=['862010000'])))

Was this reply helpful? Yes No
DFA 21 on at

Like (1)

Report

Thanks for that melamri, it’s nice to know that it’s not just me seeing things.

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

@DFA ,

For me it worked fine without any issue. Including query below for the reference.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="cr5b9_test1">
<attribute name="cr5b9_test1id"/>
<attribute name="cr5b9_name"/>
<attribute name="createdon"/>
<order attribute="cr5b9_name" descending="false"/>
<link-entity name="cr5b9_test2" from="cr5b9_test1" to="cr5b9_test1id" link-type="inner">
<filter type="and">
<condition attribute="new_multise" operator="contain-values">
<value>100000000</value>
<value>100000001</value>
</condition>
</filter>
<attribute name="cr5b9_name"/>
</link-entity>
</entity>
</fetch>

Try link-type as inner. I tried with both it worked fine.

Regards,

Prakash

Was this reply helpful? Yes No
DFA 21 on at

Like (1)

Report

“Outer” is in the title and the supplied statement for a reason, changing it to inner is not a viable solution.
And no, it does not work as expected with outer. I just got another confirmation from another ISV regarding this.

Was this reply helpful? Yes No