Sync Filter examples with linked entities

From Resco's Wiki
Jump to navigation Jump to search
Sync Filter examples

In some cases, the criteria for which records of table X you want to download depend on a related table Y. For example, if you're in charge of accounts in Boston, you want to see only the contacts of your Boston accounts. These are only the contacts whose parent account is in Boston (i.e., the parentcustomerid lookup points to a Boston account). In the FetchXML language, we express this via an inner link from the contact entity to the account entity. 

<fetch>
	<entity name="contact">
		<link-entity name="account" from="accountid" to="parentcustomerid" link-type="inner">
			<filter type="and">
				<condition attribute="city" operator="eq" value="Boston"/>
			</filter>
		</link-entity>
	</entity>
</fetch>

In the example above:

  • The contact record has a lookup parentcustomerid which points to a unique account record.
  • The account record is determined by the entity name account and the ID accountid.

We call these sync filters with linked entity.

Note These examples apply to entities without a formal parent-child relation. The behavior is somewhat different there; see Sync Filter examples with child entities for details.

Sync filters must match!

The sync filters in tables X and Y must be consistent. If you're only downloading accounts from Boston, make sure your contacts table has a matching sync filter. Mismatched sync filters may result in inconsistent/missing client data.

Example of a mismatched sync filter: sync filter conditions don't match.

sync filters mismatch for linked entities

Correct sync filter: All sync filter conditions for the account entity are also included in the contact entity sync filter.

sync filters match for linked entities

Active contacts belonging to active accounts

This is an example of a classic inner link that connects contacts with their accounts. The link uses lookup contact.parentcustomerid pointing to the account's primary key (accountid).

Because the link is inner (outer links will be discussed later), standalone contacts (not assigned to any account) are skipped. 

<fetch version="1.0">
	<entity name="contact">
		<filter type="and">
			<condition attribute="statuscode" operator="ne" value="2" />
			<condition attribute="statecode" entityname="L0" operator="ne" value="1" />
		</filter>
		<link-entity name="account" alias="L0" from="accountid" to="parentcustomerid" link-type="inner" />
	</entity>
</fetch>

Account distributor records that belong to active accounts

The filter is defined within the linked entity. The Alias L0 is used nowhere, i.e. it could be omitted. 

<fetch version="1.0">
	<entity name="pe_accountdistributor">
		<link-entity name="account" alias="L0" from="accountid" to="pe_accountid" link-type="inner">
			<filter type="and">
				<condition attribute="statecode" operator="eq" value="0"/>
			</filter>
		</link-entity>
	</entity>
</fetch>

Marketing lists owned by a client's business unit

<fetch version="1.0">
	<entity name="list">
		<link-entity name="systemuser" alias="L0" from="systemuserid" to="ownerid" link-type="inner">
			<filter type="and">
				<condition attribute="businessunitid" operator="eq-businessid"/>
			</filter>
		</link-entity>
	</entity>
</fetch>

Contracts that belong to an account owned by mobile user or his team(s)

<fetch version="1.0">
	<entity name="pe_contract">
		<link-entity name="account" alias="L0" from="accountid" to="pe_accountid" link-type="inner">
			<filter type="and">
				<condition attribute="ownerid" operator="eq-useroruserteams"/>
			</filter>
		</link-entity>
	</entity>
</fetch>

Phone calls attended by me (i.e. by the current user)

<fetch version="1.0">
	<entity name="phonecall">
		<link-entity name="activityparty" alias="L0" from="activityid" to="activityid" link-type="inner">
			<filter type="and">
				<condition attribute="partyid" operator="eq-userid" />
			</filter>
		</link-entity>
	</entity>
</fetch>

My recent tasks

Recent (max 2 years old) tasks that a) are owned by me, and b) I am one of the task participants. 

<fetch version="1.0">
	<entity name="task">
		<filter type="and">
			<condition attribute="modifiedon" operator="last-x-years" value="2" />
			<condition attribute="ownerid" operator="eq-userid" />
		</filter>
		<link-entity name="activityparty" alias="L0" from="activityid" to="activityid" link-type="inner">
			<filter type="and">
				<condition attribute="partyid" operator="eq-userid" />
			</filter>
		</link-entity>
	</entity>
</fetch>

All activities where the user has a role

All activities where I (i.e. mobile user) take one of the specified roles. (See https://crmpolataydin.wordpress.com/2014/08/29/mscrm-participationtypemask-values/.) 

<fetch version="1.0">
	<entity name="activity">
		<link-entity name="activityparty" from="activityid" to="activityid">
			<filter type="and">
				<condition attribute="participationtypemask" operator="in">
					<value>1</value>
					<value>2</value>
					<value>3</value>
					<value>4</value>
					<value>5</value>
					<value>6</value>
					<value>7</value>
					<value>9</value>
				</condition>
				<condition attribute="partyid" operator="eq-userid" />
			</filter>
		</link-entity>
	</entity>
</fetch>

Contracts of a specific type

Contracts of specific (agreement) type which belong to an account from given business unit 

<fetch version="1.0">
	<entity name="contract">
		<filter type="and">
			<condition attribute="atx_agreementtype" operator="eq" value="963350004"/>
		</filter>
		<link-entity name="account" alias="L0" from="accountid" to="customerid" link-type="inner">
			<filter type="and">
				<condition attribute="atx_businessunitid" operator="eq" value="b9f80649-db2b-e611-80c8-0050569e1f25" uitype="businessunit" uiname="DE"/>
			</filter>
		</link-entity>
	</entity>
</fetch>

Example illustrating chained links

  • atx_event records related to contacts located in London.
  • atx_event_contact is some NN-relation between events and contacts. (Several contacts can take part in a single event. Each contact can attend several events.)
<fetch version="1.0">
	<entity name="atx_event">
		<link-entity name="atx_event_contact" alias="L0" from="atx_eventid" to="atx_eventid" link-type="inner">
			<link-entity name="contact" alias="L2" from="contactid" to="contactid" link-type="inner">
				<filter type="and">
					<condition attribute="address1_city" operator="eq" value="London"/>
				</filter>
			</link-entity>
		</link-entity>
	</entity>
</fetch>

Accounts with my affiliated company

All (active) accounts which have affiliated company assigned to me (as a mobile user). 

<fetch version="1.0">
	<entity name="account">
		<filter type="and">
			<condition attribute="statecode" operator="eq" value="0" />
		</filter>
		<link-entity name="fro_affiliatedcompany" alias="L0" from="fro_affiliatedcompanyid" to="fro_affiliatedcompanyid" link-type="inner">
			<link-entity name="systemuser" alias="L2" from="fro_affiliatedcompany" to="fro_affiliatedcompanyid" link-type="inner">
				<filter type="and">
					<condition attribute="systemuserid" operator="eq-userid" />
				</filter>
			</link-entity>
		</link-entity>
	</entity>
</fetch>

SharePoint document location records

Sharepointdocumentlocation records related (regardingobjectid) to either my (eq-userid) account or to some activity.

Note (specific to sharepointdocumentlocation): We explicitly allow locations with regardingobjectid=null. These records are needed as they define root folders for various entities.

This is the first example of an outer link, so let's explain the difference from an inner link. Links combine data from two entities. First of all, we have the base entity - in this case sharepointdocumentlocation - to which we add supplementary info from the linked record from another entity. The trick is that when the linked record is not found, then:

  • Inner link causes skipping the base record,
  • Outer link keeps base record, but supplies null in place of missing linked data.

While inner links reduce base records (to those which have linked record), outer links return the same base records with optional added information. 

<fetch version="1.0">
	<entity name="sharepointdocumentlocation">
		<filter type="and">
			<filter type="or">
				<condition attribute="regardingobjectid" operator="null" />
				<condition attribute="accountid" entityname="L0" operator="not-null" />
				<condition attribute="activityid" entityname="L1" operator="not-null" />
			</filter>
		</filter>
		<link-entity name="account" alias="L0" from="accountid" to="regardingobjectid" link-type="outer">
			<filter type="and">
				<condition attribute="ownerid" operator="eq-userid" />
			</filter>
		</link-entity>
		<link-entity name="appointment" alias="L1" from="activityid" to="regardingobjectid" link-type="outer" />
	</entity>
</fetch>

Non-cancelled campaigns

Non-cancelled (statuscode!=4) campaigns with start time +- 1 year which are targeted towards specific accounts.

Note that this sync filter uses 5 links (Dynamics's limit is 10 links), hence it might consume considerable Server resources. 

<fetch version="1.0">
	<entity name="campaign">
		<filter type="and">
			<condition attribute="statuscode" operator="ne" value="4"/>
			<filter type="or">
				<condition attribute="loa_sellinstart" operator="today"/>
				<condition attribute="loa_sellinstart" operator="next-x-months" value="12"/>
				<condition attribute="loa_sellinstart" operator="last-x-months" value="12"/>
			</filter>
		</filter>
		<link-entity name="campaignitem" alias="L2" from="campaignid" to="campaignid" link-type="inner">
			<link-entity name="list" alias="L3" from="listid" to="entityid" link-type="inner">
				<link-entity name="listmember" alias="L5" from="listid" to="listid" link-type="inner">
					<link-entity name="account" alias="L7" from="accountid" to="entityid" link-type="inner">
						<link-entity name="loa_partnerfunction" alias="L9" from="loa_mainaccountid" to="accountid" link-type="inner">
							<filter type="and">
								<condition attribute="loa_partneraccountid" operator="eq-customerid"/>
								<condition attribute="loa_partnerfunction" operator="in">
									<value>29</value>
									<value>17</value>
								</condition>
							</filter>
						</link-entity>
					</link-entity>
				</link-entity>
			</link-entity>
		</link-entity>
	</entity>
</fetch>

Active connections

Active connections that connect my business unit's reg. groups or individuals with some other object. (The other side of the connection is unspecified.) 

<fetch version="1.0">
	<entity name="connection">
		<filter type="and">
			<condition attribute="statecode" operator="eq" value="0" />
			<filter type="or">
				<condition attribute="progres_businessunit" entityname="L0" operator="eq-businessid" />
				<condition attribute="progres_businessunit" entityname="L1" operator="eq-businessid" />
			</filter>
		</filter>
		<link-entity name="progres_registrationgroup" alias="L1" from="progres_registrationgroupid" to="record1id" link-type="outer" />
		<link-entity name="progres_individual" alias="L0" from="progres_individualid" to="record1id" link-type="outer" />
	</entity>
</fetch>

Tasks with a work order

Tasks with a workorder that requires my work either today or tomorrow.

Note: msdyn_workorder, msdyn_workorder, and bookableresource are core field service entities. 

<fetch version="1.0">
	<entity name="task">
		<link-entity name="account" alias="L0" from="accountid" to="regardingobjectid" link-type="inner">
			<link-entity name="msdyn_workorder" alias="L1" from="msdyn_serviceaccount" to="accountid" link-type="inner">
				<link-entity name="bookableresourcebooking" alias="L2" from="msdyn_workorder" to="msdyn_workorderid" link-type="inner">
					<filter type="or">
						<condition attribute="starttime" operator="today" />
						<condition attribute="starttime" operator="tomorrow" />
					</filter>
					<link-entity name="bookableresource" alias="L3" from="bookableresourceid" to="resource" link-type="inner">
						<filter type="and">
							<condition attribute="userid" operator="eq-userid" />
						</filter>
					</link-entity>
				</link-entity>
			</link-entity>
		</link-entity>
	</entity>
</fetch>
Retrieved from "https://docs.resco.net/mediawiki/index.php?title=Sync_Filter_examples_with_linked_entities&oldid=11418"