CRM 4.0 - Ejemplos de fetch

Fetch XML Examples

The following are examples of use of the QueryXML parameter. See also Fetch XML Schema.

Example 1

Return all attributes of the account entity where the owning user's last name equals the value crmlastname, first name equals the value crmfirstname, and nickname equals the value crmnickname.
 Copy Code
<fetch mapping='logical'>
   <entity name='account'><all-attributes/>
      <link-entity name='systemuser' to='owninguser'>
         <filter type='and'>
            <condition attribute = 'lastname' operator='eq'
                       value='crmlastname'/>
            <condition attribute='firstname' operator='eq'
                       value='crmfirstname'/>
            <condition attribute='nickname' operator='eq'
                       value='crmnickname'/>
         </filter>
      </link-entity>
   </entity>
</fetch>

Example 2

Return all attributes from the account entity for accounts created today.
 Copy Code
<fetch mapping='logical'>
   <entity name='account'><all-attributes/>
      <filter type='and'>
         <condition attribute = 'createdon' operator='today'/>
      </filter>
   </entity>
</fetch>

Example 3

Return name and accountid from all accounts created within the last seven days.
 Copy Code
<fetch mapping='logical'>
   <entity name='account'>
      <attribute name = 'name'/>
      <attribute name = 'accountid'/>
      <filter type='and'>
         <condition attribute = 'createdon' operator='last-seven-days'/>
      </filter>
   </entity>
</fetch>"

Example 4

Return contactid and birthdate from all contacts where the birth date is December 12, 2000 (time format YYYY-MM-DDTHH:MM:SSZ).
 Copy Code
<fetch mapping='logical'>
   <entity name='contact'>
      <attribute name = 'contactid'/>
      <attribute name = 'birthdate'/>
      <filter type='and'>
         <condition attribute = 'birthdate' operator='on'
                    value='2000-12-12T00:00:00Z'/>
      </filter>
   </entity>
</fetch>

Example 5

Return all attributes from accounts where the account name either starts with "a" or starts with "d".
 Copy Code
<fetch mapping='logical'>
   <entity name='account'><all-attributes/>
      <filter type='or'>
         <condition attribute = 'name' operator='like' value='a%'/>
         <condition attribute = 'name' operator='like' value='d%'/>
      </filter>
   </entity>
</fetch>

Example 6

Return name and accountid from all accounts where the name starts with either "a" or "x", or the name ends with "!".
 Copy Code
<fetch mapping='logical'>
   <entity name='account'>
      <attribute name = 'name'/>
      <attribute name = 'accountid'/>
      <filter type='or'>
         <condition attribute = 'name' operator='like' value='a%'/>
         <condition attribute = 'name' operator='like' value='x%'/>
         <condition attribute = 'name' operator='like' value='%!'/>
      </filter>
   </entity>
</fetch>

Example 7

Retrieve the ticker symbol of the account whose name is "My Account".
 Copy Code
<fetch mapping='logical'>
   <entity name='account'>
      <attribute name='tickersymbol' />
      <filter type='and'>
           <condition attribute='name' operator='eq' value='My Account' />
      </filter>
   </entity>
</fetch>

Example 8

Retrieve all leads that were created within the last seven days.
 Copy Code
<fetch mapping='logical'>
   <entity name='lead'>
      <all-attributes />
      <filter type='and'>
         <condition attribute='createdon' operator='last-seven-days' />
      </filter>
   </entity>
</fetch>

Example 9

Retrieve all contacts whose first name begins with "A" or last name ends with "Z". Sort the result set in ascending order by last name.
 Copy Code
<fetch mapping='logical'>
   <entity name='contact'>
      <attribute name='firstname' />
      <attribute name='lastname' />
      <filter type='or'>
         <condition attribute='firstname' operator='like' value='A%' />
         <condition attribute='lastname' operator='like' value='%Z' />
      </filter>
      <order attribute='lastname' descending='false' />
   </entity>
</fetch>

Example 10

Retrieve the number of users in the system.
 Copy Code
<fetch mapping='logical' aggregate='true'>
   <entity name='systemuser'>
      <attribute name='systemuserid' aggregate='count' alias='number' />
   </entity>
</fetch>

Example 11

Retrieve the first 10 account records created by user Jeff Smith in the last month.
 Copy Code
<fetch mapping='logical' page='1' count='10'>
   <entity name='account'>
      <all-attributes />
      <link-entity name='systemuser' to='createdby'>
            <filter type='and'>
                  <condition attribute='firstname'
                             operator='eq' value='Jeff' />
                  <condition attribute='lastname'
                             operator='eq' value='Smith' />
            </filter>
      </link-entity>
      <filter type='and'>
            <condition attribute='createdon' operator='last-month' />
      </filter>
   </entity>
</fetch>

Example 12 – Inner Joins Between Entities

Retrieves the name field on the Invoice entity. This query returns a maximum of one (1) record that matches criteria on Page 1 of the result set.
 Copy Code
<fetch mapping='logical' page='1' count='1'>
   <entity name='invoice'>
      <attribute name = 'name'/>
      <link-entity name='invoicedetail' to='invoiceid' from='invoiceid'>
      </link-entity>
      <filter type='and'>
         <condition attribute = 'accountid' operator='eq'
                    value='{7F901912-DD67-47B9-A5B3-B702B9F84680}'/>
      </filter>
      <link-entity name='account' to='accountid'>
      </link-entity>
      <filter type='and'>
         <condition attribute = 'name' operator='like' value='%order%'/>
      </filter>
   </entity>
</fetch>

Example 13 – Inner Joins Between Entities

Retrieves the name field on the Invoice entity. This query returns a maximum of 10 records that match the criteria on Page 1 of the result set.
 Copy Code
<fetch mapping='logical' page='1' count='10'>
   <entity name='invoice'>
      <attribute name = 'name'/>
      <link-entity name='invoicedetail' to='invoiceid' from='invoiceid'>
      </link-entity>
      <filter type='and'>
         <condition attribute = 'accountid' operator='eq'
                    value='{7F901912-DD67-47B9-A5B3-B702B9F84680}'/>
      </filter>
      <link-entity name='account' to='accountid'>
         <filter type='and'>
            <condition attribute = 'name' operator='like'
                       value='%Account%'/>
         </filter>
      </link-entity>
   </entity>
</fetch>

Example 14 – Using the OrderBy Clause

Retrieves all contacts whose first name begins with "A" or last name ends with "Z" and sorts the result set in ascending order by last name.
 Copy Code
<fetch mapping='logical'>
   <entity name='contact'>
      <attribute name='firstname' />
      <attribute name='lastname' />
      <filter type='or'>
         <condition attribute='firstname' operator='like' value='A%' />
         <condition attribute='lastname' operator='like' value='%Z' />
      </filter>
      <order attribute='lastname' descending='false' />
   </entity>
</fetch>