Fetch XML: Conditions are not supported on Link entities

Sarthak Behera 0

Data type is Choice for the msdyn_energytype attribute in dataverse. Only using <attribute name="msdyn_energytype" /> in the fetchXML is returning the Integer value instead of the label. Therefore, I'm trying to create a link-entity with stringmap (which has the choice details). However, I'm getting an error that says Conditions are not supported on Link entities.

<fetch>
  <entity name="msdyn_purchasedenergy">
    <attribute name="msdyn_name" />
    <link-entity name="stringmap" from="attributevalue" to="msdyn_energytype" link-type="outer" alias="sm" visible="true">
      <attribute name="value" />
      <filter>
        <condition attribute="attributename" operator="eq" value="msdyn_energytype" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Entire call stack is as follows:

I have created a similar query (snippet below) for another entity but it works fine.

<fetch>
	<!-- Entity: Organizational hierarchy -->
	<entity name="msdyn_sustainabilityorganizationalhierarchy">
		<!-- Organizational unit -->
        <link-entity name="msdyn_sustainabilityorganizationalunit" from="msdyn_sustainabilityorganizationalunitid" to="msdyn_organizationalunitid" link-type="outer" alias="msdyn_sustainabilityorganizationalunit">
			<attribute name="msdyn_name" />
			<!-- Organizational unit type-->
            <link-entity name="stringmap" from="attributevalue" to="msdyn_organizationalunittype" link-type="inner" alias="stringmap" visible="true">
        		<attribute name="value" />
        		<filter>
          			<condition attribute="attributename" operator="eq" value="msdyn_organizationalunittype" />
        		</filter>
      		</link-entity>
		</link-entity>
	</entity>
</fetch>

Please advise on what can be changed in the xml query for msdyn_purchasedenergy entity to resolve this issue. I'm using this query in a copy activity of ADF

phemanth 8,645 Reputation points Microsoft Vendor

2024-02-12T09:05:14.1366667+00:00
@Sarthak Behera
Thanks for reaching out to Microsoft Q&A.

The issue you’re facing is likely due to the link-type attribute in your FetchXML query. In the working query, you have used link-type="inner" for the stringmap link-entity, whereas in the query that’s causing an error, you have used link-type="outer". The link-type="outer" is used for left outer joins, which include all the records from the primary entity and only the matched records from the link-entity. If there are no matches, the link-entity will contain null. This might be causing issues when you’re trying to apply a condition on the link-entity. Try changing link-type="outer" to link-type="inner" in your FetchXML query for the msdyn_purchasedenergy entity. Here’s how your modified query would look:

<fetch> <entity name="msdyn_purchasedenergy"> <attribute name="msdyn_name" /> <link-entity name="stringmap" from="attributevalue" to="msdyn_energytype" link-type="inner" alias="sm" visible="true"> <attribute name="value" /> <filter> <condition attribute="attributename" operator="eq" value="msdyn_energytype" /> </filter> </link-entity> </entity> </fetch>

Please try this modification and see if it resolves your issue. If the problem persists, there might be other factors at play, and I’d recommend checking the data types and relationships in your Dataverse schema

Hope this helps. Do let us know if you any further queries.
Sarthak Behera 0 Reputation points

2024-02-12T12:37:49.57+00:00

Hi, When I use **link-type="inner",**I get an error saying that only left-outer join is supported. The surprising thing is everything works in the other query, but fails here. Please note that the entity type for msdyn_purchasedenergy is elastic. COuld it be causing some issues?
phemanth 8,645 Reputation points Microsoft Vendor

2024-02-13T10:21:20.1066667+00:00
@Sarthak Behera

The issue might indeed be related to the entity type being elastic. Elastic entities in Dataverse have some limitations compared to standard entities. One of these limitations is that they only support left outer joins in FetchXML queries.

Unfortunately, this means you won’t be able to use an inner join with conditions in your FetchXML query for the msdyn_purchasedenergy entity.

As a workaround, you might consider retrieving the data without the condition and then filtering the results in your application code. This isn’t as efficient as doing it in the FetchXML query, but it might be the only option given the limitations of elastic entities.

Here’s how your modified FetchXML would look:

<fetch> <entity name="msdyn_purchasedenergy"> <attribute name="msdyn_name" /> <link-entity name="stringmap" from="attributevalue" to="msdyn_energytype" link-type="outer" alias="sm" visible="true"> <attribute name="value" /> </link-entity> </entity> </fetch>

This will return all msdyn_purchasedenergy records along with their msdyn_energytype values. You can then filter the results in your application code to only include records where attributename equals msdyn_energytype. I hope this helps! If you have any other questions please do let us know
Sarthak Behera 0 Reputation points

2024-02-13T13:42:26.07+00:00

Hi,
thanks for the suggestion. Upon trying, I get the following error.

Seems like we can only create link-entity for lookups. The datatype of msdyn_energytype in the elastic entity msdyn_purchasedenergy is Choice. Please advise
phemanth 8,645 Reputation points Microsoft Vendor

2024-02-14T09:16:13.2533333+00:00
@Sarthak Behera
For Choice fields, the label of the selected option is not stored in the entity’s table but in a separate system table. Unfortunately, this system table is not accessible via FetchXML. However, when you retrieve the entity data using FetchXML, the labels of the Choice fields are automatically included in the result. You don’t need to join with another entity to get them. Here’s how you can modify your FetchXML:

<fetch> <entity name="msdyn_purchasedenergy"> <attribute name="msdyn_name" /> <attribute name="msdyn_energytype" /> </entity> </fetch>

In this FetchXML, I’ve removed the link-entity. When you run this FetchXML, it will return both the value and label of the msdyn_energytype field. Please note that the label will be in the user’s language that is set in their personal options.

Share via

Fetch XML: Conditions are not supported on Link entities