FetchXML View to Include Attributes from Nested Link-Entity

Question

I would like to have a view that show attributes from 3 entities:
Statistics has a lookup to Account and Account has a lookup to Address.

The view is on Statistics and I want attributes from all 3 entities; is this even possible?

The problem is with the GridXML.
I want to include the attribute wl_city in the GridXML.

This is the FetchXML with link-entities:

<fetchxml>
  <fetch version="1.0" output-format="xml-platform" mapping="logical">
    <entity name="sb_statistics">
      <order attribute="sb_amount" descending="false" />
      <!-- It is easy to get these into the GridXML -->
      <attribute name="sb_debtor" />
      <attribute name="sb_date" />
      <attribute name="sb_amount" />
      <link-entity name="account" from="accountid" to="sb_debtor" 
       alias="relatedAccount" link-type="outer">
        <!-- It is possible to get this into the GridXML
             by using the link-entity alias: relatedAccount.wl_towncity -->
        <attribute name="wl_towncity" />
        <link-entity name="wl_postalcode" from="wl_postalcodeid" 
          to="wl_postaltowncity" alias="relatedAddress" link-type="outer">
          <!-- I have trouble getting this attribute into the GridXML -->
          <attribute name="wl_city" />
        </link-entity>
      </link-entity>
      <attribute name="sb_statisticsid" />
    </entity>
  </fetch>
</fetchxml>

When I change the GridXML as below this error is displayed when the view is opened:
"To use this saved query, you must remove criteria and columns that refer to deleted or non-searchable items"

  <layoutxml>
    <grid name="resultset" object="10008" jump="sb_name" select="1" preview="1" 
     icon="1">
      <row name="result" id="sb_statisticsid" multiobjectidfield="1">
        <cell name="sb_amount" width="100" />
        <cell name="sb_date" width="100" />
        <cell name="sb_debtor" width="100" />
        <cell name="relatedAccount.relatedAddress.wl_city" width="100" />
      </row>
    </grid>
  </layoutxml>

The below GridXML shows this error when the view is opened:
"Unexpected Error An error has occured".

  <layoutxml>
    <grid name="resultset" object="10008" jump="sb_name" select="1" preview="1" 
     icon="1">
      <row name="result" id="sb_statisticsid" multiobjectidfield="1">
        <cell name="sb_amount" width="100" />
        <cell name="sb_date" width="100" />
        <cell name="sb_debtor" width="100" />
        <cell name="relatedAddress.wl_city" width="100" />
      </row>
    </grid>
  </layoutxml>

The GridXML below results in this error being shown when the view is opened:
"To use this saved view, you must remove criteria and columns that refer to deleted or non-searchable columns".

  <layoutxml>
    <grid name="resultset" object="10008" jump="sb_name" select="1" preview="1" 
     icon="1">
      <row name="result" id="sb_statisticsid" multiobjectidfield="1">
        <cell name="sb_amount" width="100" />
        <cell name="sb_date" width="100" />
        <cell name="sb_debtor" width="100" />
        <cell name="wl_city" width="100" />
      </row>
    </grid>
  </layoutxml>

This saved query works, but it only includes attributes from the primary entity and the first link-entity.

<savedquery>
  <IsCustomizable>1</IsCustomizable>
  <CanBeDeleted>0</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="sb_name" select="1" preview="1" 
     icon="1">
      <row name="result" id="sb_statisticsid" multiobjectidfield="1">
        <cell name="sb_amount" width="100" />
        <cell name="sb_date" width="100" />
        <cell name="sb_debtor" width="100" />  
        <cell name="relatedAccount.wl_city" width="100" />              
      </row>
    </grid>
  </layoutxml>
  <querytype>0</querytype>
  <fetchxml>
    <fetch version="1.0" output-format="xml-platform" mapping="logical">
      <entity name="sb_statistics">
        <order attribute="sb_amount" descending="false" />
        <attribute name="sb_debtor" />
        <attribute name="sb_date" />
        <attribute name="sb_amount" />
        <link-entity name="account" from="accountid" to="sb_debtor" 
         alias="relatedAccount" link-type="outer">
          <attribute name="wl_towncity" />
          <link-entity name="wl_postalcode" from="wl_postalcodeid" 
           to="wl_postaltowncity" alias="relatedAddress" link-type="outer">
            <attribute name="wl_city" />
          </link-entity>
        </link-entity>
        <attribute name="sb_statisticsid" />
      </entity>
    </fetch>
  </fetchxml>
  <LocalizedNames>
    <LocalizedName description="Statistics and Address" languagecode="1033" />
  </LocalizedNames>
</savedquery>

Is GridXML limited to showing only attributes from the primary entity and the first link-entity?

I can't speak to any limitations of the GridXML, but `relatedAddress.wl_city` is the correct aliased name you should be using. — Daryl, Nov 18 '13 at 13:28
Can you create a personal view via advanced find and see what the gridxml looks like in the SQL table? — Andy Meyers, Nov 18 '13 at 15:22
It appears to be the worst ever limitation of GridXML, I can display the data from fetchxmlbuilder just fine, it returns ALL fields of all multi nested link-entity's and all their fields, including relatedAddress.wl_city but how to get that resultset to work in GRidXML? the only option I have found so far is to take the same FetchXML and use RDL SSRS CRM online reports instead. but those reports are soo slow. I want quick view ones. — hamish, Apr 30 '16 at 11:21

score 6 · Accepted Answer · answered Nov 19 '13 at 07:29

6

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:29

Bvrce

2,170
2
27
45

2

I am still keen to find a real working solution. how can "no answer" be the answer? – hamish May 02 '16 at 05:41

score 2 · Answer 2 · answered Apr 04 '16 at 14:20

2

It should work when using link-type="inner" for nested link.

<entity name="sb_statistics">
    ...
    <link-entity name="account" from="accountid" to="sb_debtor" 
     alias="relatedAccount" link-type="outer">
      <attribute name="wl_towncity" />
      <link-entity name="wl_postalcode" from="wl_postalcodeid" 
       to="wl_postaltowncity" alias="relatedAddress" link-type="inner"> //link-type="inner"
        <attribute name="wl_city" />
      </link-entity>
    </link-entity>
    <attribute name="sb_statisticsid" />
  </entity>

answered Apr 04 '16 at 14:20

Niels Steenbeek

4,692
2
41
50

1

that didn't work. I also have the exact same problem. so I tried link-type="inner" it didn't work neither. I tried disableMetaDataBinding. that didn't fix it neither I tried – hamish Apr 30 '16 at 11:15
1

@hamish Maybe try using Advinced Find = > Edit Columns => Add Columns => Select nested entity => Select field from nested Entity => ok ok => Download Fetch XML => Request is shown – Niels Steenbeek May 02 '16 at 07:43

score 2 · Answer 3 · answered Apr 19 '16 at 03:39

I have found no evidence that it can be done. With or without link-type='inner' the designer (in 2013) says, "The relatedAddress.wl_city column is no longer a valid column because it has been deleted as a column option. You need to remove this column and, if you want, add a different one."

It does NOT need multiple dereferrences, nor does that work. If you dump the keyValuePairs of the AttributeCollection returned by the fetch, you will see the key is relatedAddress.w1_city -- not its parent nor the combination.

Like the UI, it just appears the layout is limited to only root and children, no grandchildren nor further descendants.

score 0 · Answer 4 · edited Sep 27 '16 at 16:28

I think it's a little late to answer this question, but maybe someone come to this post and find it helpful.

first thing you should know is that, fetchxml will return only column that are not null, so if you are querying a column that there is no data in that, then fetchxml automatically remove it from result set.

second thing is, if you have different table with different relationship, then alias name will be added to the column name, so in your case relatedAccount.wl_towncity and relatedAddress.wl_city is correct and not relatedAccount.relatedAddress.wl_city. in your example, you put alias name after each other that is not correct.

third thing that you should know is that when a nested result will return, the type is object, but original type AliasedValue , so first you have to cast the object to AliasedValue. then it become ready to cast it to OptionSetValue. after that you have to look for .Value that has the result of what you want

score 0 · Answer 5 · edited Aug 13 '20 at 05:57

I made it work like this: I still have an issue with unresolved columnheaders.

     <fetch distinct='true'>
    <entity name='rdiac_riskobject'>
      <attribute name='rdiac_riskobjectid' />
      <attribute name='rdiac_name' />
      <attribute name='rdiac_riskobjectproduct' />
      <link-entity name='rdiac_riskobject_rdiac_propertydetail' from='rdiac_riskobjectid' to='rdiac_riskobjectid' intersect='true'>
        <link-entity name='rdiac_propertydetail' alias='pd1' from='rdiac_propertydetailid' to='rdiac_propertydetailid'>
          <attribute name='rdiac_valuestring' />
          <link-entity name='rdiac_propertysvconfig' from='rdiac_property' to='rdiac_propertyid'>
            <filter>
              <condition attribute='rdiac_svfield' operator='eq' value='100000000'/>
            </filter>
          </link-entity>
        </link-entity>
      </link-entity>
      <link-entity name='rdiac_riskobject_rdiac_propertydetail'  from='rdiac_riskobjectid' to='rdiac_riskobjectid' intersect='true'>
        <link-entity name='rdiac_propertydetail' alias='pd2' from='rdiac_propertydetailid' to='rdiac_propertydetailid'>
          <attribute name='rdiac_valuestring' />
          <link-entity name='rdiac_propertysvconfig' from='rdiac_property' to='rdiac_propertyid'>
            <filter>
              <condition attribute='rdiac_svfield' operator='eq' value='100000001'/>
            </filter>
          </link-entity>
        </link-entity>
      </link-entity>
    </entity>
  </fetch>

  <grid name='resultset' object='10139' jump='rdiac_riskobjectproduct' select='1' preview='0' icon='1' >
    <row name='result' id='rdiac_riskobjectid' >
      <cell name='rdiac_riskobjectproduct' width='100' />
      <cell name='pd1.rdiac_valuestring' width='200' />
      <cell name='pd2.rdiac_valuestring' width='200' />
    </row>
  </grid>

FetchXML View to Include Attributes from Nested Link-Entity

5 Answers5