FetchXML to Get 2nd Relation Entity

Question

Is it possible to display fields from nested link-entities in a view?

I have 3 entities: statistics, account, and address. Statistics has a lookup to Account and account has a lookup to Address. I want fields from all these entities in the Statistics view.

I have tried this and get an error: To use this saved view, you must remove criteria and columns that refer to deleted or no-searchable items.

    <savedquery>
        <IsCustomizable>1</IsCustomizable>
        <CanBeDeleted>1</CanBeDeleted>
        <isquickfindquery>0</isquickfindquery>
        <isprivate>0</isprivate>
        <isdefault>0</isdefault>
        <returnedtypecode>10008</returnedtypecode>
        <savedqueryid>{df101ac4-2e4d-e311-9377-005056bd0001}</savedqueryid>
        <layoutxml>
          <grid name="resultset" object="10008" jump="sl_name" select="1" preview="1" icon="1">
            <row name="result" id="sl_statisticsid">
              <cell name="sl_amount" width="100" />
              <cell name="sl_date" width="100" />
              <cell name="sl_debtor" width="100" />
              <cell name="sl_divisioncode" width="100" />
              <cell name="sl_source" width="100" />
              <cell name="sl_statstype" width="100" />
              <cell name="relatedAccount.wl_towncity" width="100"/>
              <cell name="relatedAccount.relatedAddress.wl_city" width="100" />
            </row>
          </grid>
        </layoutxml>
        <querytype>0</querytype>
        <fetchxml>
          <fetch version="1.0" output-format="xml-platform" mapping="logical">
            <entity name="sl_statistics">
              <order attribute="sl_amount" descending="false" />
              <attribute name="sl_statstype" />
              <attribute name="sl_source" />
              <attribute name="sl_divisioncode" />
              <attribute name="sl_debtor" />
              <attribute name="sl_date" />
              <attribute name="sl_amount" />
              <link-entity name="account" from="accountid" to="sl_debtor" alias="relatedAccount">
                <attribute name="wl_towncity" />
                <link-entity name="wl_postalcode" from="wl_postalcodeid" to="wl_postaltowncity" alias="relatedAddress">
                  <attribute name="wl_city" />
                </link-entity>
              </link-entity>
              <attribute name="sl_statisticsid" />
            </entity>
          </fetch>
        </fetchxml>
        <LocalizedNames>
          <LocalizedName description="Statistics and Address" languagecode="1033" />
        </LocalizedNames>
      </savedquery>

If this line is removed then the view works:

<cell name="relatedAddress.wi_city" width="100" disableSorting="0" />

Does anyone know how to reference an element from a nested link-entity in the GridXML?

I have also tried this for the offending line:

<cell name="relatedAccount.relatedAddress.wi_city" width="100" disableSorting="0" />

It is starting to look like it is not possible to have a view that displays fields from nested link-entities.

Subtenant

So this isn't an issue with the fetch, it's an issue with importing a solution? – Vargo

The solution will not import because the saved query is bad; if I remove the nested link-entity then the solution imports. – Subtenant

Answer 1

This is not possible, according to the best of my knowledge, but please someone prove me wrong.

A limitation of GridXML appears to be that attributes can only be included that are from the first link-entity, not any nested link-entities.

Subtenant

Answer 2

You can access data from related entities by nesting

<link-entity>

tags which is what you are doing in the code that you posted. What issues are you seeing with the fetchxml you have posted? Are you receiving an error or are you not getting correct results.

Teletypewriter

I updated with the error that occurs when re-importing the solution. – Subtenant

Could you post your GridXML as well. If the view is on the sb_statistics, the top level entity in your fetchxml should be sb_statistics so we can eliminate the second sample that you posted. The error could be because of inconsistency between your GridXML and FetchXML – Teletypewriter

Answer 3

Here is an example of my FetchXml that works:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
  <entity name="new_coursesection">
    <attribute name="new_coursesectionid" groupby="true" alias="id" />
    <filter type="and">
      <condition attribute="new_coursesectionid" operator="eq" value="{0}" />
    </filter>
    <link-entity name="new_contactcoursesection" from="new_coursesectionid" to="new_coursesectionid" alias="new_contactcoursesection1" link-type="outer">
      <attribute name="new_contactcoursesectionid" aggregate="countcolumn" alias="contactcoursesectioncount" />
    </link-entity>
  </entity>
</fetch>

I'm doing an aggregate, but you shouldn't have to. I'd try providing an alias for your link-entities and attribute.

Vargo

If the view is from the statistics entity then does fetch have to have <entity name="sb_statistics"> or can it start with <entity name="wi_postalcode"> to do the query from the "outside-in". – Subtenant

@Subtenant I'm guessing it does, but I don't know. – Vargo