Sync Filter examples with lookups

Client data present a projection of server database: It contains selected entities, selected columns, and selected rows. The row selection is defined by means of a sync filter definition. When defining the sync filter, we have to take into consideration:

• Performance limits (storage limitations of the mobile device, web traffic, Server imposed limitations such as API calls limits)
• Referential integrity (are lookups pointing to non-existing records allowed?)

Remarks concerning the last point:

• Resco Mobile CRM is rather tolerant in a sense that it allows lookups pointing to a non-existing record. (It does not enforce referential integrity.)
• To improve the user experience you can allow lookup labels for the concerned entity. (The user will see the referred record name even if the record is not present in the Client DB.)

In the following, we'll discuss ways how to achieve referential integrity when designing sync filters. First of all, some situations are handled automatically:

• In the case of child-parent relations, SyncEngine automatically modifies sync filters to warrant that for any parent record all of its child records are present.
• You cannot define sync filters for intersect (NN) entities. This is because their sync filter is automatically composed out of sync filters of related entities.

There is one exception to the last point - NN relations between child entities: MCRM v13.1 does not create automatic sync filters in this case. The consequence is that FullSync downloads all records of the intersect entity and unneeded records are deleted only in sync cleanup phase. (It is possible that future MCRM versions will improve this behavior.)

However, the rest is up to you. If you have a lookup, it is your responsibility to decide if you define sync filter so that the target records are downloaded to the client.

Connections

Connections are an easy way to connect records without having to create a custom relationship between two entities.

• A connection can be used between records of the same type or of different types, e.g. a contact can be connected to another contact, or to an account.
• If your data model heavily relies on connections between various entity types and the connection table is huge, then it might be reasonable to limit downloaded connection records.

Let's say we have these sync filters for accounts and contacts:

<fetch version="1.0">
	<entity name="account">
		<filter type="and">
			<filter type="or">
				<condition attribute="ownerid" operator="eq-useroruserteams" />
			</filter>
		</filter>
	</entity>
</fetch>
<fetch version="1.0">
	<entity name="contact">
		<filter type="and">
			<filter type="or">
				<condition attribute="ownerid" operator="eq-useroruserteams" />
			</filter>
		</filter>
	</entity>
</fetch>

Here is how we can limit connections between contacts and accounts. (Notice the usage of outer links.)

<fetch version="1.0">
	<entity name="connection">
		<filter type="and">
			<condition attribute="statecode" operator="eq" value="0" />
			<filter type="or">
				<condition attribute="ownerid" entityname="L0" operator="eq-useroruserteams" />
				<condition attribute="ownerid" entityname="L1" operator="eq-useroruserteams" />
				<condition attribute="ownerid" entityname="L2" operator="eq-useroruserteams" />
				<condition attribute="ownerid" entityname="L3" operator="eq-useroruserteams" />
			</filter>
		</filter>
		<link-entity name="account" alias="L0" from="accountid" to="record1id" link-type="outer" />
		<link-entity name="account" alias="L1" from="accountid" to="record2id" link-type="outer" />
		<link-entity name="contact" alias="L2" from="contactid" to="record1id" link-type="outer" />
		<link-entity name="contact" alias="L3" from="contactid" to="record2id" link-type="outer" />
	</entity>
</fetch>

Salesorders example

Let's say we have this sync filter for salesorders (6 months old orders owned by my business unit):

<fetch version="1.0">
	<entity name="salesorder">
		<filter type="and">
			<condition attribute="modifiedon" operator="last-x-months" value="6"/>
		</filter>
		<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>

This is the implicitly defined sync filter for order details (contains inner link to parent entity constrained by its sync filter):

<entity name="salesorderdetail">
	<link-entity name="salesorder" alias="L1" from="salesorderid" to="salesorderid" link-type="inner">
		<filter type="and">
			<condition attribute="modifiedon" operator="last-x-months" value="6"/>
		</filter>
		<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>
	</link-entity>
</entity>

Let's say we have an NN relation on salesorderdetails that is represented by intersect entity xxx_salesorderdetail_salesorderdetail. The entity contains 2 lookups conveniently named "first" and "second", which point to salesorderdetail records. The optimal sync filter is below. All we did, we added constraints (inner links to salesorderdetail) to both relation ends.

<fetch version="1.0">
	<entity name="xxx_salesorderdetail_salesorderdetail">
		<link-entity name="salesorderdetail" alias="L2" from="salesorderdetailid" to="first" link-type="inner" >
			<link-entity name="salesorder" alias="L1" from="salesorderid" to="salesorderid" link-type="inner">
				<filter type="and">
					<condition attribute="modifiedon" operator="last-x-months" value="6"/>
				</filter>
				<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>
			</link-entity>
		</link-entity>
		<link-entity name="salesorderdetail" alias="K2" from="salesorderdetailid" to="second" link-type="inner" >
			<link-entity name="salesorder" alias="K1" from="salesorderid" to="salesorderid" link-type="inner">
				<filter type="and">
					<condition attribute="modifiedon" operator="last-x-months" value="6"/>
				</filter>
				<link-entity name="systemuser" alias="K0" from="systemuserid" to="ownerid" link-type="inner">
					<filter type="and">
						<condition attribute="businessunitid" operator="eq-businessid"/>
					</filter>
				</link-entity>
			</link-entity>
		</link-entity>
	</entity>
</fetch>