Deep dive: Sync filters in Field Service

Deep dives
Aggregate questionnaires via Power BI template Aggregate questions in repeating groups Combined entity form Create records from repeatable groups Generate repeatable groups Initiate Teams call from record context Questionnaire visualization in Power BI Shopping cart with barcode scanning Timesheets Linked entities in FetchXML Sync download optimization Sync filters in Field Service

‎This page describes a recurring data-model pattern found in Dynamics 365 Field Service and similar applications, the sync filter design problem it creates, and the optimization options that have proven effective in real deployments. The patterns and recommendations here apply to any application built around the same shape — a "job assigned to user" entity that anchors a chain of related records — even when the underlying entities have different names.

For the underlying mechanics of FetchXML link performance, reverse lookups, and IN/EXISTS link types, see Deep dive: Linked entities in FetchXML. This page focuses on how those mechanics manifest in Field Service-style data models and what to do about them.

The booking-centric model

In a Field Service mobile app, the technician's "job" is the anchor for everything else they need on the device. In Microsoft's Field Service data model, the job is a bookableresourcebooking (a booking) — an assignment that connects a work order with the person responsible for executing it. Other implementations use a service appointment, a visit, or a custom entity, but the structural role is the same.

Starting from the booking, the technician needs the related data:

The work order itself (msdyn_workorder), together with its tasks (msdyn_workordertask), services, and products
The incidents on each work order (msdyn_workorderincident)
The service account for each work order, and through it the account's contacts
The customer assets (msdyn_customerasset) associated with those accounts or incidents

The data on the device is therefore small (a few hundred bookings per technician, a few hundred work orders, a few hundred accounts) — but every entity above sits in a server table that is typically large to very large. The technician's slice is a tiny, dynamic projection of a huge data set.

This contrast is one of the core sync challenges for Field Service models.

Sync filter design for this shape

Each entity in the chain needs a sync filter that selects only the records related to the technician's bookings. The natural way to write each filter is to chain through the booking relationship.

For example:

Bookings — bookings assigned to me within the relevant period
Work orders — work orders that have a booking assigned to me
Work order tasks — tasks that belong to work orders that have a booking assigned to me
Service accounts — accounts used as the service account on a work order that has a booking assigned to me
Customer assets — assets belonging to an account used as the service account on a work order that has a booking assigned to me

Each filter goes one or more links deeper than the one above it. The asset filter has three chained links; the account filter has two; even the work order filter has one reverse link. Every link is a join in the underlying SQL query, and most of the links here are reverse links (the lookup points from the child to the parent, so the sync traverses the relationship backwards).

For an example of how this looks as FetchXML, here is the filter for a chain of three reverse links:

<!-- Customer assets belonging to accounts that have work orders booked to me in the last 30 days -->
<fetch>
  <entity name="msdyn_customerasset">
    <link-entity name="account" from="accountid" to="msdyn_account">
      <link-entity name="msdyn_workorder" from="msdyn_serviceaccount" to="accountid">
        <link-entity name="bookableresourcebooking" from="msdyn_workorder" to="msdyn_workorderid">
          <filter>
            <condition attribute="starttime" operator="on-or-after" value="@@TODAY-30" />
          </filter>
          <link-entity name="bookableresource" from="bookableresourceid" to="resource">
            <condition attribute="userid" operator="eq-userid" />
          </link-entity>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

These filters produce very selective queries — they return a rather small result for each technician — but they are also dynamic (the booking set changes day by day) and expensive to evaluate on the server, because each linked entity adds a join, security checks, and (for reverse links) potential row duplication.

Why these queries are expensive

The cost of a Field Service sync filter compounds across the chain:

Each link adds a join. Even when the result set is small, the server has to traverse the relationship between large tables to determine which rows match.
Reverse links cause branching. For example, when filtering accounts by their related work orders, the server has to consider every work order linked to each account before determining whether the account qualifies. This is the most expensive class of join in FetchXML. See cost of reverse lookups.
Security checks run on every linked entity. Not just the base entity. For an asset filter that links through account, work order, and booking, the server runs security checks on four tables, not one. See hidden costs of the security model.

The end result is filters that are correct in business logic but slow — sometimes slow enough to time out the server.

The moving time window trap

Most Field Service filters need a time window: "bookings within the next 30 days," "work orders updated in the last quarter." The natural way to express this — using a relative date range like last-x-days or a window centered on today — creates a moving time window. The filter result set changes on every sync, even when no underlying records changed, because the window itself moves.

Moving windows cause two problems:

Records continuously enter and leave the filter, triggering unnecessary downloads and cleanup.
During incremental sync, records that drift into scope but weren't modified are missed entirely.

A better pattern is to use on-or-after with a fixed-style reference like @@TODAY-30 (no upper bound). The result set still shifts over time, but the query is simpler, and incremental sync misses fewer records.

Optimization options

Three approaches have proven effective in production for booking-centric models. They are not mutually exclusive — most heavily-optimized Field Service deployments use some combination.

Option 1: IN/EXISTS link types

Available in Sync Filter since release 18.0.

When a sync filter links through a small result set (such as MyBookings — bookings assigned to me, typically a few hundred records at most), replacing the inner link with an IN or EXISTS link can dramatically improve query performance. The server translates the link into a subquery rather than a join, which avoids row duplication and lets the query planner stop traversing related rows once the first match is found.

This works particularly well in the booking-centric model because MyBookings and MyWorkOrders are inherently small for each technician — exactly the conditions where subqueries outperform joins.

A real example: in one production deployment with 5.7M work order incidents, 330K work orders, and 440K bookings, the same filter caused a server timeout when expressed with inner links, but executed quickly when the booking link was changed to IN. The full example, query plan analysis, and detailed performance discussion are on the deep-dive page.

Limitations:

The linked entity's columns cannot be referenced outside the link element. If you need to filter or sort on attributes of the linked entity, IN/EXISTS doesn't apply.
The query planner makes the final decision. IN/EXISTS is a recommendation to the server, not a guarantee of which execution plan is used.

Option 2: Record touching

A pattern used in some production deployments to avoid the expense of chained reverse-link filters entirely.

The idea: instead of asking "which records are related to my bookings?" at query time, mark records as "needs to be synced to someone" at the time the relationship is established. When a booking is assigned to a technician, the server sets a flag (e.g., to_be_synced = 1) on each related record — the work order, the service account, the assets, the incidents. The mobile app's sync filter then becomes trivial: give me records where to_be_synced = 1. No reverse links, no chained joins.

When it works well:

Chained reverse-link queries are timing out or causing server load problems
The relationship between bookings and related records is stable enough that the touching logic can be implemented reliably (typically via server-side workflow or plugin)
The mobile app's StdIncSync strategy is sufficient for the touched entities — no LinkedSyncFilters needed

When it doesn't:

Records get touched for every technician, not only the one who needs them. A service account assigned to bookings for multiple technicians ends up touched on behalf of all of them. The mobile app then downloads records that don't strictly need to be on its device.
In one real deployment, this resulted in 400K to 1M unneeded record downloads per incremental sync — enough to cause server throttling. See the case study below for the full story and resolution.

Record touching is a powerful tool but it shifts the cost rather than eliminating it. The new cost (unneeded downloads) may or may not be cheaper than the original cost (complex queries).

Option 3: Drop the filter

Often the most under-appreciated option. Instead of trying to optimize a chained filter, remove the filter entirely for the relevant entity and accept that all records of that entity will be on every device.

The math is usually less scary than it sounds. Consider an account entity:

Server has 500,000 accounts
Each account record on the device takes ~500 bytes
All accounts on the device = 250 MB of local storage
Full sync time impact = ~4 minutes additional (at typical download speed of 0.5 ms/record)

For modern mobile devices, 250 MB of additional local storage is rarely a constraint. Four minutes of additional full sync time is significant but bounded — and full sync is infrequent (typically once per app reinstall or major customization change). In exchange, you get:

No reverse-link query cost on the server for that entity
No risk of incremental sync missing records because they drifted in or out of filter scope
Faster incremental sync (no filter to evaluate, fewer records to clean up)
Simpler troubleshooting

This trade-off is the conclusion of the case study below: a deployment that was struggling with optimized filters chose to simplify by dropping filters on the critical account tree, accepting a few hundred MB of additional storage and ~13 minutes of additional full sync time in exchange for incremental sync that "dramatically faster (by 5–15 min)" — and the customer reported everything working as expected.

Incremental sync strategy

Beyond filter design, the choice of incremental sync strategy per entity matters. For Field Service-style filters, the trade-offs are:

Standard IncSync (default) may miss related records that weren't directly modified. Example: when a booking is created, the work order is modified, but the related service account often isn't — so StdIncSync downloads the booking and work order but not the account, leaving a dangling reference.
Incremental Linked SyncFilter addresses this for chains of related entities, but in models with many links it can become expensive and produce large unneeded downloads. Useful when the chain is short and the filtered entities are well-bounded.
AlwaysFullSync is often the best option for the small, dynamic, selective filters typical of Field Service models. Each entity's full population is small per user, the filter is dynamic, and the simplicity of full sync removes a class of edge cases.

See Sync Filter and incremental sync for the full comparison.

Case study: Record touching in production

A real production deployment using a booking-centric model with the record touching mechanism. Names of customer-specific entities and any business detail have been generalized.

Original setup

The data model used serviceappointment as the job entity (connecting work order with technician). Record touching was implemented as follows: when a work order was assigned, its service account was marked by setting to_be_synced = 1.

Sync filters:

Work orders — assigned to me
Accounts — to_be_synced = 1 plus related complaints and feed items
Cases — last 90 days
Assets — all records (no filter)
Other entities synchronized without filters

The IncSync strategy was Incremental Linked SyncFilter for all entities with links in their filter — to ensure that changed related records were downloaded along with their parents.

Observed result

The customer data model was compact and selective, with a small device footprint. Full sync performance was acceptable. However:

Incremental sync was very slow
Daily incremental sync caused 400,000 to 1,000,000 unneeded record downloads, large enough to risk server throttling

The root cause was traced to the account-related entity tree:

Critical path = account tree — responsible for ~95% of IncSync unneeded downloads.
    account
        <- asset <- complaint
        <- case <- feeditem

The combination of LinkedSyncFilters and a touched-account filter meant that whenever an account was touched on behalf of any technician, every other technician's incremental sync downloaded the related cascade for that account. The "small footprint" the design had achieved came at the cost of cross-technician churn.

Resolution

The team decided to give up the small-footprint goal for the critical account tree, in exchange for filter simplicity. The revised setup:

Drop filters on accounts and assets (sync all records)
Keep date-based filters on cases (last 90 days), complaints (last 4 years), and feed items (last 90 days)
Stop using LinkedSyncFilters — no longer needed once the filters are simple

Expected impact per device:

Entity	Server records	New filter	Local records	Local table size
Account	150K	All records	150K	~70 MB
Asset	540K	All records	540K	~350 MB
Case	665K	Last 90 days	55K	~35 MB
Complaint	1M	Last 4 years	1M	~210 MB
Feed item	21M	Last 90 days	1M	~230 MB

Total device storage increase: roughly 500 MB. Total full sync time increase: roughly 13 minutes (at 0.5 ms/record). Incremental sync became 5 to 15 minutes faster per run, the unneeded-download volume dropped dramatically, and the server throttling risk was eliminated.

The customer reported the new setup working as expected.

Lessons

Optimizing for a small device footprint can produce expensive sync behavior. The two goals are in conflict.
LinkedSyncFilters scales poorly with the number of links — it amplifies the underlying filter cost.
For some entity trees, removing the filter is genuinely the best option once the storage cost is concrete and bounded.
Touched-record patterns work, but the touching must be precise enough that records aren't synced unnecessarily for users who don't need them.

Beyond bookings

The patterns on this page apply to any application built around the same shape — a per-user job entity that anchors a chain of related records. Concrete examples include:

Field service apps using serviceappointment instead of bookableresourcebooking
Sales visit / route planning apps with a per-user visit entity
Inspection apps with per-user inspection assignments
Custom job, work item, or task entities specific to a customer's data model

In each of these cases, the anchor entity is small and assigned to a user, but the related entities are large server-side. The sync filter design space, the reverse-link cost problem, the moving time window trap, and the three optimization options (IN/EXISTS, record touching, dropping filters) apply universally to all such scenarios.