When dealing with more complex queries in dynamics portals you quickly loose overview and access to fields deep down in your linked entities. Let me demonstrate this with an example. Let’s say we want to display a table of all the contacts in our system together with some information about their parent account.
The information we want to display should look something like the table below:
Account number | Account | Contact |
To gather all this information, we should be fine with a simple fetchXML like this:
<fetch> <entity name="contact" > <attribute name="fullname" /> <link-entity name="account" from="accountid" to="parentcustomerid" > <attribute name="name" /> <attribute name="accountnumber" /> </link-entity> </entity> </fetch>
To display the data inside the portal we can create a web template that looks similar to the one below: {% fetchxml contactsQuery %} <fetch> <entity name="contact" > <attribute name="fullname" /> <link-entity name="account" from="accountid" to="parentcustomerid" > <attribute name="name" /> <attribute name="accountnumber" /> </link-entity> </entity> </fetch> {% endfetchxml %} {% assign contacts = contactsQuery.results.entities %} <table width="100%" border="1"> <tr> <th>Account number</th> <th>Account</th> <th>Contact</th> </tr> {% for contact in contacts%} <tr> <td>{{contact["accountnumber"]}}</td> <td>{{contact["name"]}}</td> <td>{{contact["fullname"]}}</td> </tr> {% endfor %} </table>
When you check the portal you will see that the columns with the information about the account are not filled in. This is because the portal will try and find the attributes “accountnumber” and “name” on the root record, in this case the contact.
To handle this issue we can use aliases on attributes of the account. This can be done by adding this snippet into the attribute alias=”…”.
Let’s add some aliases to our original fetchXML:
<fetch> <entity name="contact" > <attribute name="fullname" /> <link-entity name="account" from="accountid" to="parentcustomerid" > <attribute name="name" alias="accountname" /> <attribute name="accountnumber" alias="accountnumber" /> </link-entity> </entity> </fetch>
And update our web template so we can take advantage of our aliases:
{% fetchxml contactsQuery %} <fetch> <entity name="contact" > <attribute name="fullname" /> <link-entity name="account" from="accountid" to="parentcustomerid" > <attribute name="name" alias="accountname" /> <attribute name="accountnumber" alias="accountnumber" /> </link-entity> </entity> </fetch> {% endfetchxml %} {% assign contacts = contactsQuery.results.entities %} <table width="100%" border="1"> <tr> <th>Account number</th> <th>Account</th> <th>Contact</th> </tr> {% for contact in contacts%} <tr> <td>{{contact["accountnumber"]}}</td> <td>{{contact["accountname"]}}</td> <td>{{contact["fullname"]}}</td> </tr> {% endfor %} </table>
If we now go back to the portal, we can see that all the information is displayed correctly:
We can also use these aliases in liquid filters, for example the “group_by”. This allows us to simplify the “group_by” parameter when dealing with multiple linked entities.
Let’s update the web template to group all contacts from the same account together:
{% fetchxml contactsQuery %} <fetch> <entity name="contact" > <attribute name="fullname" /> <link-entity name="account" from="accountid" to="parentcustomerid" > <attribute name="name" alias="accountname" /> <attribute name="accountnumber" alias="accountnumber" /> </link-entity> </entity> </fetch> {% endfetchxml %} {% assign contacts = contactsQuery.results.entities %} {% assign accountGroups = contacts | group_by: "accountnumber" %} <table width="100%" border="1"> <tr> <th>Account number</th> <th>Account</th> <th>Contact</th> </tr> {% for accountGroup in accountGroups %} {% for contact in accountGroup.items %} <tr> <td>{{contact["accountnumber"]}}</td> <td>{{contact["accountname"]}}</td> <td>{{contact["fullname"]}}</td> </tr> {% endfor %} {% endfor %} </table>
This will result in list of contacts being group based on the account number of the parent account:
The main reasons why we use aliases are to display attributes from linked entities and to also use these attributes in liquid filters. You can also use aliases on the linked entities itself. This can cause some problems when using lookup or option set attributes. That’s why it is best to use the alias on the attributes.
Saved me a lot of troubleshooting on displaying linked entity info! Can I reference your blog post in blog posts of my own?
Hey Django, I'm glad this post helped you! You can reference this blog post if you like!
[…] bericht Using attribute aliases in FetchXML verscheen eerst op […]
[…] bericht Using attribute aliases in FetchXML verscheen eerst op […]