Sync Filter examples with linked entities

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

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>