Rajeev Pentyala – Technical Blog on Power Platform, Azure and AI

Did you know that by using the valueof attribute, you can create filters that compare columns on values in the same row.

Scenario 1 : Find Contact records where the firstname column value matches the lastname column value

• The following FetchXML has valueof attribute, which matches firstname with value of lastname and returns matching Contact records.

<fetch>
   <entity name='contact' >
      <attribute name='firstname' />
      <filter>
         <condition attribute='firstname'
            operator='eq'
            valueof='lastname' />
      </filter>
   </entity>
</fetch>

Scenario 2 : Cross table comparisons – Fetch rows where the contact fullname column matches the account name column

• In the below FetchXML, we are using valueof and link-entity to compare column values across the tables Contact and Account.
• Please note that, the link-entity element must use an alias attribute and the value of the valueof parameter must reference that alias and the column name in the related table.

<fetch>
   <entity name='contact'>
      <attribute name='contactid' />
      <attribute name='fullname' />
      <filter type='and'>
         <condition attribute='fullname'
            operator='eq'
            valueof='acct.name' />
      </filter>
      <link-entity name='account'
         from='accountid'
         to='parentcustomerid'
         link-type='outer'
         alias='acct'>
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

Please refer my past articles or Microsoft Docs on how to execute the FetchXML.

🙂