Saving query as a view - column titles not displayed and filter not working

[BobCapel]

I created a query that joins 4 tables thru 3 link-entity levels and selects attributes for display from each table. The joins are M:1, outer 1:M and outer 1:M. If I save the query as a view, none of the column titles in the joined tables are displayed when I select the view in D365, although columns are there (the column titles are shown as grey boxes). Also, no rows are returned/displayed. Should this work, or am I expecting too much?

[rappen]

Generally - we are expecting too much... Regarding the type of query, it will return the data, but the view can't find the column titles, and in some cases, the query is too complex to get the data back. I have not found it in the documentation, but we know that columns are all good with one link-level down (or up), but the titles won't show up from the second level. I had a conversation with MS about this issue one or two years ago, and I know it is in the backlog to allow more levels in views - but probably Ms Copilot gets all the attention... ;-)

Just a wildshot - do you have any news, @JimDaly ?

[JimDaly]

Our documentation around saving views is here Customize views. The FetchXml of the query is just part of what is saved in the view, the LayoutXml is a factor as well. There are other columns within the SavedQuery table that aren't explained and may be important.

You may want to look at data for other views that are shipped for examples.

[BobCapel]

Thanks for your support, and for providing this tool. It's a life saver. I found that if I "Edit columns" when viewing data and save the column headings will appear. However, the column layout doesn't "stick" if I export the view definition. Also, I found the problem with no rows returned is related to what seems to be a bug in the new feature that lets you add a condition comparing against a row in a linked table when using date fields. I'll report that in a separate issue.

[rappen]

Thanks @JimDaly for general info!

Regarding more specifics: the issue we get when we have a query with more than one level of link-entity. The query works fine, and returned as it should, showing all columns. FXB is saving also the LayoutXML correctly. But all column titles more than one level away from the root entity cannot find the header column text.

My example

FetchXML:

<fetch>
  <entity name='contact'>
    <attribute name='contactid' />
    <attribute name='fullname' />
    <link-entity name='account' from='accountid' to='parentcustomerid' link-type='outer' alias='employer'>
      <attribute name='name' />
      <link-entity name='account' from='accountid' to='parentaccountid' link-type='outer' alias='parent'>
        <attribute name='name' />
        <link-entity name='account' from='accountid' to='parentaccountid' link-type='outer' alias='grandparent'>
          <attribute name='name' />
          <link-entity name='account' from='accountid' to='parentaccountid' link-type='outer' alias='greatgrandparent'>
            <attribute name='name' />
          </link-entity>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

LayoutXML:

<grid name='resultset' object='2' jump='fullname' select='1' icon='1' preview='1'>
  <row name='result' id='contactid'>
    <cell name='fullname' width='116' />
    <cell name='employer.name' width='100' />
    <cell name='parent.name' width='97' />
    <cell name='grandparent.name' width='145' />
    <cell name='greatgrandparent.name' width='145' />
    <cell name='contactid' ishidden='1' />
  </row>
</grid>

Result in FXB:

Result in Dynamics: (It may look like I blurred the three columns - I didn't. To me it looks like the code trying to find the display name of the columns just crashed.)

I know when I reported this a year or two ago, a team at MS was going to fix this, but I've heard nothing since then. (Unless I just missed it...) Checking in the docs, I can't find any comment on the issue we have when we have multiple levels.

I just saw an interesting thing: If I move a column right or left, I get at least "Account Name" on those columns that previously had nothing.

[BobCapel]

I noticed the same behavior. However, if you navigate away from the view (after adjusting the column names and seeing them displayed) and then navigate back, they are lost again. Also, the data returned by the fetch is often incorrect and inconsistent with results returned in FXB. I've decided this just isn't going to be reliable for complex queries involving multiple link entities.