Dynamics Portal - Use FetchXML as filter criteria within Lists - Outer Joins

(0) Share

Report

Posted on by GeraldGut

Hi Everybody,

currently I am a bit struggling regarding creating a FetchXML to be used within a List for Dynamics Portal.

My Goal is to show a List menu only (Grid Configuration - List Actions), when an opportunity has NO sales order OR when a existing sales order has a specific value set AND the linked account in the opportunity has a specific field value set.

I try to show the menu only when:

1. Linked account of the opportunity has field set to XXX AND

2. no sales order for the opportunity exists OR

3. in case a sales order exists for the opportunity and a specific field is set (OptionSet ne value) within the sales order.

I am stuck with the or condition:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
 <entity name="opportunity">
 <attribute name="name" />
 <attribute name="customerid" />
 <attribute name="estimatedvalue" />
 <attribute name="statuscode" />
 <attribute name="opportunityid" />
 <order attribute="name" descending="false" />
 <link-entity name="salesorder" from="opportunityid" to="opportunityid" link-type="outer" alias="at" />
	<link-entity name="salesorder" from="opportunityid" to="opportunityid" link-type="inner" alias="so" />
 <link-entity name="account" from="accountid" to="parentaccountid" link-type="inner" alias="au">
 <filter type="and">
 <condition attribute="rep_portal_sonderlogik_ausfuehren" operator="eq" value="283270002" />
 </filter>
 </link-entity>
 <filter type="and">
 <condition entityname="at" attribute="opportunityid" operator="null" />
 </filter>
	<filter type="or">
	 <condition entityname="so" attribute="rep_material_bestellt" operator="ne" value="283270002" />
	</filter>
 </entity>
</fetch>

I tried around some time, but somehow I receive always empty results.

I think there is something wrong with my and / or filterings.

Someone has an idea?

Thx Gerald

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

eugenevanstaden 441 on at

Like (0)

Report
Copy link

Link copied!

@GeraldGut

Does this fetch return results if you use advance find or fetchxml builder in XrmToolbox?

If it returns results but not in Portal then you need to look at Table Permissions.

Was this reply helpful? Yes No
GeraldGut 92 on at

Like (1)

Report
Copy link

Link copied!
Hi Eugenevanstaden,

I tested it within XRMToolbox with the FetchXML Tester Tool and there was no result.
It is definetely related to the FetchXML syntax.

Anyway, I think I got the solution which I want to share with you. I switched my FetchXML to use default CRM fields instead of my custom ones that it is easier to follow. Maybe it is somehow of interest in future:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="opportunity"> <attribute name="name" /> <attribute name="customerid" /> <attribute name="estimatedvalue" /> <attribute name="statuscode" /> <attribute name="opportunityid" /> <order attribute="name" descending="false" /> <filter type="and"> <condition entityname="ac" attribute="ownershipcode" operator="eq" value="1" /> <filter type="or"> <condition entityname="noord" attribute="opportunityid" operator="null" /> <condition entityname="so" attribute="freighttermscode" operator="ne" value="21" /> </filter> </filter> <link-entity name="salesorder" from="opportunityid" to="opportunityid" link-type="outer" alias="noord" /> <link-entity name="salesorder" from="opportunityid" to="opportunityid" link-type="outer" alias="so" /> <link-entity name="account" from="accountid" to="parentaccountid" link-type="inner" alias="ac" /> </entity> </fetch>

To get to my target it was necessary to join all entities with outer joins.
Additionally the filter has to be set with correct And/Or structure to filter all joined records.

If someone finds a better solution for this, please let me know.

Thx anyway for your help.

Best regards
Gerald Gut

Was this reply helpful? Yes No
GeraldGut 92 on at

Like (0)

Report
Copy link

Link copied!

Hi Eugenevanstaden,

just one addition.
To be able to show / hide the action menu on portal side within the entitylist, based on this fetchXML filter.
It is necessary to also provide entity permissions within the webRole used by portal user.
Otherwise the FetchXML can retrieve records within the XrmToolbox, but not on Portal as the portal user do not have e.g. access to fetched sales orders.

Best regards,
Gerald

Was this reply helpful? Yes No
Verified answer

eugenevanstaden 441 on at

Like (0)

Report
Copy link

Link copied!

@GeraldGut

It definitely sounds like Permissions which you need to set up in relation to the parent table which is Opportunity.

Was this reply helpful? Yes No
Spearruleina 38 on at

Like (0)

Report
Copy link

Link copied!

No. (Or not entirely... ; )
He specifically mentioned he'd solved his primary issue of empty query results by fixing the logic of the outer join + the null OR the *other* outer join + the condition. A tricky logic fix. Secondarily, maybe perms, but not 'definitely sounds like'...
(Sorry, We Geralds @GeraldGut gotta stick together ; )

I do happen find great help in seeing other (Geralds or other named people!) struggle against the awkwardness of FetchXML (and succeed!). This complex join and filter will definitely stay in my bookmarks for future reference.

[ALSO: Just remember never to open this query up in the PowerApps View designer and then save it or you will break the underlying XML which often evaluates to too complex for the GUI to represent! Better have a backup of your Solution's src/Entities/Opportunity/SavedQueries/{blahblahGUID}.xml as text in source control in case someone does save it in the GUI some day : ]

Was this reply helpful? Yes No