Sometimes we need to build a query which will return some data based on not lookup attributes, but on text attributes. You might need this when correct relationships between entities were not adequately defined during the implementation phase, but links between different objects can be determined by using data stored in the text fields. As an example, let’s check how to return data about the contact and related organization just using an email address.
Here is an example of fetch XML you can use to find information about the contact, including organization, when an organization has precisely the same e-mail address as a contact.
<fetch version="1.0" mapping="logical" distinct="true" > <entity name="contact" > <attribute name="fullname" /> <attribute name="emailaddress1" /> <order attribute="fullname" descending="false" /> <link-entity name="account" from="emailaddress1" to="emailaddress1" alias="account" > <attribute name="emailaddress1" /> <attribute name="name" /> </link-entity> </entity> </fetch>
You can use the same approach when you need to link GUID which is stored in some text field with the normal lookup attribute stored on another entity.