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.

So this isn't an issue with the fetch, it's an issue with importing a solution? — Daryl, Nov 14 '13 at 16:09
The solution will not import because the saved query is bad; if I remove the nested link-entity then the solution imports. — Bvrce, Nov 14 '13 at 16:11

score 3 · Accepted Answer · answered Nov 19 '13 at 07:28

3

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.

answered Nov 19 '13 at 07:28

Bvrce

2,170
2
27
45

score 2 · Answer 2 · answered Nov 14 '13 at 15:53

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.

answered Nov 14 '13 at 15:53

Sunil

624
4
10

I updated with the error that occurs when re-importing the solution. – Bvrce Nov 14 '13 at 15:58
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 – Sunil Nov 14 '13 at 16:17

score 2 · Answer 3 · answered Nov 14 '13 at 15:57

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.

If the view is from the statistics entity then does fetch have to have or can it start with to do the query from the "outside-in". — Bvrce, Nov 14 '13 at 16:06

FetchXML to Get 2nd Relation Entity

3 Answers3