Difference between revisions of "Sync Filter examples with linked entities"

From Resco's Wiki
Jump to: navigation, search
(Created page with "== 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.parentc...")
 
(Contracts of a specific type)
Line 140: Line 140:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 +
== 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.)
  
 
<syntaxhighlight lang="xml">
 
<syntaxhighlight lang="xml">
 +
<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>
 +
</syntaxhighlight>
  
// Example illustrating chained links.
+
== Accounts with my affiliated company ==
// 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.)
+
All (active) accounts which have affiliated company assigned to me (as a mobile user).
<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>
 
  
</syntaxhighlight>
 
 
<syntaxhighlight lang="xml">
 
<syntaxhighlight lang="xml">
// All (active) accounts which have affiliated company assigned to me (as a mobile user).
+
<fetch version="1.0">
<fetch version="1.0">
+
<entity name="account">
<entity name="account">
+
<filter type="and">
<filter type="and">
+
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="statecode" operator="eq" value="0" />
+
</filter>
</filter>
+
<link-entity name="fro_affiliatedcompany" alias="L0" from="fro_affiliatedcompanyid" to="fro_affiliatedcompanyid" link-type="inner">
<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">
<link-entity name="systemuser" alias="L2" from="fro_affiliatedcompany" to="fro_affiliatedcompanyid" link-type="inner">
+
<filter type="and">
<filter type="and">
+
<condition attribute="systemuserid" operator="eq-userid" />
<condition attribute="systemuserid" operator="eq-userid" />
+
</filter>
</filter>
 
</link-entity>
 
 
</link-entity>
 
</link-entity>
</entity>
+
</link-entity>
</fetch>
+
</entity>
 +
</fetch>
 +
</syntaxhighlight>
 +
 
 +
== 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.
  
</syntaxhighlight>
 
 
<syntaxhighlight lang="xml">
 
<syntaxhighlight lang="xml">
// sharepointdocumentlocation records related (regardingobjectid) to either my (eq-userid) account or to some activity.
+
<fetch version="1.0">
// Note (specific to sharepointdocumentlocation): We explicitly allow locations with regardingobjectid=null. These records are needed as they define root folders for various entities.
+
<entity name="sharepointdocumentlocation">
//
+
<filter type="and">
// This is the first example of an outer link, so let's explain the difference from an inner link.
+
<filter type="or">
//
+
<condition attribute="regardingobjectid" operator="null" />
// Links combine data from two entities.
+
<condition attribute="accountid" entityname="L0" operator="not-null" />
// First of all we have the base entity - in this case sharepointdocumentlocation - to which we add supplementary info from linked record from another entity.
+
<condition attribute="activityid" entityname="L1" operator="not-null" />
// The trick is that when the linked record is not found, then
+
</filter>
// - Inner link causes skipping the base record,
+
</filter>
// - Outer link keeps base record, but supplies null in place of missing linked data.
+
<link-entity name="account" alias="L0" from="accountid" to="regardingobjectid" link-type="outer">
// 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="and">
<filter type="or">
+
<condition attribute="ownerid" operator="eq-userid" />
<condition attribute="regardingobjectid" operator="null" />
 
<condition attribute="accountid" entityname="L0" operator="not-null" />
 
<condition attribute="activityid" entityname="L1" operator="not-null" />
 
</filter>
 
 
</filter>
 
</filter>
<link-entity name="account" alias="L0" from="accountid" to="regardingobjectid" link-type="outer">
+
</link-entity>
<filter type="and">
+
<link-entity name="appointment" alias="L1" from="activityid" to="regardingobjectid" link-type="outer" />
<condition attribute="ownerid" operator="eq-userid" />
+
</entity>
</filter>
+
</fetch>
</link-entity>
+
</syntaxhighlight>
<link-entity name="appointment" alias="L1" from="activityid" to="regardingobjectid" link-type="outer" />
+
 
</entity>
+
== Non-cancelled campaigns ==
</fetch>
+
 
 +
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.
  
// Non-cancelled (statuscode!=4) campaigns with start time +- 1 year which are targeted towards specific accounts.
+
<syntaxhighlight lang="xml">
// 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">
<fetch version="1.0">
+
<entity name="campaign">
<entity name="campaign">
+
<filter type="and">
<filter type="and">
+
<condition attribute="statuscode" operator="ne" value="4"/>
<condition attribute="statuscode" operator="ne" value="4"/>
+
<filter type="or">
<filter type="or">
+
<condition attribute="loa_sellinstart" operator="today"/>
<condition attribute="loa_sellinstart" operator="today"/>
+
<condition attribute="loa_sellinstart" operator="next-x-months" value="12"/>
<condition attribute="loa_sellinstart" operator="next-x-months" value="12"/>
+
<condition attribute="loa_sellinstart" operator="last-x-months" value="12"/>
<condition attribute="loa_sellinstart" operator="last-x-months" value="12"/>
 
</filter>
 
 
</filter>
 
</filter>
<link-entity name="campaignitem" alias="L2" from="campaignid" to="campaignid" link-type="inner">
+
</filter>
<link-entity name="list" alias="L3" from="listid" to="entityid" link-type="inner">
+
<link-entity name="campaignitem" alias="L2" from="campaignid" to="campaignid" link-type="inner">
<link-entity name="listmember" alias="L5" from="listid" to="listid" link-type="inner">
+
<link-entity name="list" alias="L3" from="listid" to="entityid" link-type="inner">
<link-entity name="account" alias="L7" from="accountid" to="entityid" link-type="inner">
+
<link-entity name="listmember" alias="L5" from="listid" to="listid" link-type="inner">
<link-entity name="loa_partnerfunction" alias="L9" from="loa_mainaccountid" to="accountid" link-type="inner">
+
<link-entity name="account" alias="L7" from="accountid" to="entityid" link-type="inner">
<filter type="and">
+
<link-entity name="loa_partnerfunction" alias="L9" from="loa_mainaccountid" to="accountid" link-type="inner">
<condition attribute="loa_partneraccountid" operator="eq-customerid"/>
+
<filter type="and">
<condition attribute="loa_partnerfunction" operator="in">
+
<condition attribute="loa_partneraccountid" operator="eq-customerid"/>
<value>29</value>
+
<condition attribute="loa_partnerfunction" operator="in">
<value>17</value>
+
<value>29</value>
</condition>
+
<value>17</value>
</filter>
+
</condition>
</link-entity>
+
</filter>
 
</link-entity>
 
</link-entity>
 
</link-entity>
 
</link-entity>
 
</link-entity>
 
</link-entity>
 
</link-entity>
 
</link-entity>
</entity>
+
</link-entity>
</fetch>
+
</entity>
 +
</fetch>
 +
</syntaxhighlight>
 +
 
 +
== 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.)
  
</syntaxhighlight>
 
 
<syntaxhighlight lang="xml">
 
<syntaxhighlight lang="xml">
// 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">
<fetch version="1.0">
+
<entity name="connection">
<entity name="connection">
+
<filter type="and">
<filter type="and">
+
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="statecode" operator="eq" value="0" />
+
<filter type="or">
<filter type="or">
+
<condition attribute="progres_businessunit" entityname="L0" operator="eq-businessid" />
<condition attribute="progres_businessunit" entityname="L0" operator="eq-businessid" />
+
<condition attribute="progres_businessunit" entityname="L1" operator="eq-businessid" />
<condition attribute="progres_businessunit" entityname="L1" operator="eq-businessid" />
 
</filter>
 
 
</filter>
 
</filter>
<link-entity name="progres_registrationgroup" alias="L1" from="progres_registrationgroupid" to="record1id" link-type="outer" />
+
</filter>
<link-entity name="progres_individual" alias="L0" from="progres_individualid" to="record1id" link-type="outer" />
+
<link-entity name="progres_registrationgroup" alias="L1" from="progres_registrationgroupid" to="record1id" link-type="outer" />
</entity>
+
<link-entity name="progres_individual" alias="L0" from="progres_individualid" to="record1id" link-type="outer" />
</fetch>
+
</entity>
 +
</fetch>
 +
</syntaxhighlight>
 +
 
 +
== 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.
  
</syntaxhighlight>
 
 
<syntaxhighlight lang="xml">
 
<syntaxhighlight lang="xml">
 
+
<fetch version="1.0">
// Tasks with a workorder, which requires my work either today or tomorrow.
+
<entity name="task">
// Note: msdyn_workorder, msdyn_workorder and bookableresource are core FieldService entities
+
<link-entity name="account" alias="L0" from="accountid" to="regardingobjectid" link-type="inner">
<fetch version="1.0">
+
<link-entity name="msdyn_workorder" alias="L1" from="msdyn_serviceaccount" to="accountid" link-type="inner">
<entity name="task">
+
<link-entity name="bookableresourcebooking" alias="L2" from="msdyn_workorder" to="msdyn_workorderid" link-type="inner">
<link-entity name="account" alias="L0" from="accountid" to="regardingobjectid" link-type="inner">
+
<filter type="or">
<link-entity name="msdyn_workorder" alias="L1" from="msdyn_serviceaccount" to="accountid" link-type="inner">
+
<condition attribute="starttime" operator="today" />
<link-entity name="bookableresourcebooking" alias="L2" from="msdyn_workorder" to="msdyn_workorderid" link-type="inner">
+
<condition attribute="starttime" operator="tomorrow" />
<filter type="or">
+
</filter>
<condition attribute="starttime" operator="today" />
+
<link-entity name="bookableresource" alias="L3" from="bookableresourceid" to="resource" link-type="inner">
<condition attribute="starttime" operator="tomorrow" />
+
<filter type="and">
 +
<condition attribute="userid" operator="eq-userid" />
 
</filter>
 
</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>
 
</link-entity>
 
</link-entity>
 
</link-entity>
</entity>
+
</link-entity>
</fetch>
+
</entity>
 +
</fetch>
 +
 
 +
[[Category:Synchronization]]

Revision as of 15:35, 7 July 2020

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>

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.

<syntaxhighlight lang="xml"> <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>