To combine data from different records in your query you need to use the
<link-entity> element. This is equivalent to a join in SQL.
<link-entity> needs 3 key pieces of information:
- The entity type to link to
- The attribute in the main entity to join from
- The attribute in the new entity to join to
<fetch> <entity name="account"> <attribute name="name" /> <link-entity name="contact" to="primarycontactid" from="contactid"> <attribute name="fullname" /> </link-entity> </entity> </fetch>
The part that always confuses me is the naming of the
from attributes on the
<link-entity> element – in my head they should be other way around, which is one reason why a tool such as SQL 4 CDS or FetchXML Builder is so useful to me.
Unlike SQL, which can have very flexible join criteria, FetchXML requires exactly one attribute on the source entity to exactly match one attribute on the target entity.
Although joins would normally be done on attributes that form a relationship, this isn’t a requirement. You can join on any attributes that are compatible, e.g. you could use the following to find leads that have the same email address as a contact:
<fetch> <entity name="lead"> <link-entity name="contact" to="emailaddress1" from="emailaddress1"> <attribute name="fullname" /> </link-entity> <attribute name="fullname" /> </entity> </fetch>
For a great example of putting this type of join into practise, take a look at Jonas Rapp’s blog on creating a view to show contacts in the same state as the current user.
If you have a many-to-many (N:N) relationship you can’t join the entities directly, you have to join via the intermediate “intersect” entity. This is a hidden entity type that is created for you automatically and isn’t available for you to view directly in your app or even look at in Advanced Find, but you need to be aware of the details of these when you are building queries that navigate these relationships.
For example, contacts can be part of many marketing lists, and each marketing list can contain many contacts, so this forms a many-to-many relationship. The contact entity itself can’t hold a list of all the marketing lists it’s part of in a field of its own, as there’s no way for it to store a potentially unlimited number of lists. Similarly, the marketing list entity can’t store all its members directly. Instead, a third intersect entity is used to join the two, and a record of this entity type is created for each contact/marketing list pair:
With this data we can see there are two contacts and two marketing lists. The first contact is a member of one list, and the second contact is a member of both.
Querying this relationship to find a list of all contacts that are part of a marketing list, and the names of the lists they are part of, would look like:
<fetch> <entity name="contact"> <link-entity name="listmember" to="contactid" from="entityid"> <link-entity name="list" to="listid" from="listid"> <attribute name="listname" /> </link-entity> </link-entity> <attribute name="fullname" /> </entity> </fetch>
Finding the intersect details
To build this query you need to know the names of the intersect entity and the attributes in it to join on. You can find the intersect entity name from the relationship properties in the customisation screens, for example here in the Maker Portal:
The intersect entity is shown here in the “Relationship entity name” field. Unfortunately it doesn’t show the foreign key fields on this entity that link to the two main entities. To find those names you’ll need to use a tool such as SQL 4 CDS or Metadata Explorer.
With SQL 4 CDS, you can use the Object Explorer pane to help add the correct syntax for a join. Double-click on a relationship to add the join into your query. If you select an N:N relationship the join will include all three required entities. For example, the contact to marketing list relationship includes the
FetchXML supports two link types –
An inner join will include only records where there is a match across both entities. By comparison, outer join will include all the records from the first entity even if there is no match in the second entity.
We can modify the first query to include all accounts even when they don’t have a primary contact by adding the
<fetch> <entity name="account"> <attribute name="name" /> <link-entity name="contact" to="primarycontactid" from="contactid" link-type="outer"> <attribute name="fullname" /> </link-entity> </entity> </fetch>
When a query with a
<link-entity> is executed, the attributes retrieved from the link are given a prefix of the entity name, e.g.:
|Data8 Ltd||Mark Carrington|
Another option is to give the
<link-entity> an alias, which changes the prefix, e.g.:
<fetch> <entity name="account"> <attribute name="name" /> <link-entity name="contact" to="primarycontactid" from="contactid" alias="person"> <attribute name="fullname" /> </link-entity> </entity> </fetch>
|Data8 Ltd||Mark Carrington|
Each query can contain a maximum of 10 links. Hopefully this should be enough for most queries. There is no way (that I know of) to increase this limit, so if you do need more you’ll need to execute multiple separate queries and join the results in your own code.
Because the links are ultimately converted to SQL joins, having more joins than this would likely result in some inefficient queries as the database engine would start hitting query optimizer timeouts, so this seems like a reasonable limit.
outer link types are equivalent to the
INNER JOIN and
LEFT OUTER JOIN join types in SQL.
to attributes in the link are equivalent to the
ON clause of the join, and the
alias attribute is equivalent to the
AS table alias clause.
However, the FetchXML syntax is more restrictive than SQL as you can only use a single exact field match for the join criteria, rather than the complex criteria that you can use in a SQL
ON clause (although it is possible to extend this a little further using a
<filter> element which I’ll look at next time). Aliases can also only be applied to a
<link-entity> and not the root
In SQL 4 CDS, the first entity you reference in the
FROM clause will be used as the main
<entity> and any following joins will become
<link-entity> elements. Although you can apply table aliases at any point, they will only be reflected in the generated FetchXML in the
<link-entity> elements, e.g.:
SELECT a.name, c.fullname FROM account AS a LEFT OUTER JOIN contact AS c ON a.primarycontactid = c.contactid;
is translated to:
<fetch xmlns:generator="MarkMpn.SQL4CDS"> <entity name="account"> <link-entity name="contact" to="primarycontactid" from="contactid" alias="c" link-type="outer"> <attribute name="fullname" /> </link-entity> <attribute name="name" /> </entity> </fetch>
Next time I’ll take a look at the
<filter> element and particularly see how that can be combined with