Techniques to build complex sync filters in Woodford

When dealing with offline functionality the key is to limit the amount of data being synced to the device. In some cases you may have thousands if not millions of records for an entity and can take a long amount of time and data.

Entity Limit

Resco CRM has a limit on how many entities that you can link in the filter. I assume that its 5 entities but I have been able to link around 7? I’m not sure what the actual rules are here but there is a limit on what you can query and the best way is just to test your sync filter and see if it doesn’t throw an error.

Account Example:

This is an example of a sync filter where I need to get account records which belong to the user’s work order, van stock and additional account details needed for handling shipments etc.

The main problem I was having before was there were thousands of accounts in the system and the syncing times for a full sync was very long and down right unusable. The account entity was being used in a lot of different areas which not naturally link together and what I really needed is multiple sync filters. As Resco CRM has no concept of multiple sync filters you can still achieve it using the one sync filter they give you.

So if you ever need to get records to sync to device where they do not naturally link together in a single inner query, what you will need to do is create all the linked entities as outer. The reason being is you will need to also get all the records outside of one query and into another. In the example above I need to see all accounts which belong to the entity ‘Sync Share Records’, ‘Products’ and ‘Warehouse Bin’ but none of them naturally link together, therefore they are all outer joins and in separate linked filters so I can retrieve all of the account records which are returned in each linked filter query.

There is one problem though. It will retrieve all the accounts which also doesn’t belong to the current user. You can get around this by adding and or using a user lookup to the entities you are querying against. In the above query I created custom user lookup’s on the Warehouse Bin and Bookable Resource Bookings called ‘Resource Owner’. The reason I did this was to limit the amount of linked entities so that it doesn’t reach the entity limit and that I am certain that the user will not change on those records. To populate these lookup’s I used regular Dynamics CRM workflows to assign the user value when a booking is scheduled or when a resource is assigned a warehouse.

My queries:

  1. L2.Resource User
  2. L6.Resource Owner
  3. L7.Resource Owner

The final step was to add the conditions against the account entity rather than each linked entity. Doing it this way you can retrieve all the accounts from each query even if one of the accounts doesn’t exist in another query, you also need to make sure you add these in an OR statement to retrieve the records in each of the queries.

Creating an Intersect Entity

In my Account example I was using this entity called ‘Sync Share Records’. This is a custom entity which I created to in order to retrieve accounts only related to work orders, but this isn’t the only entity I needed retrieve against the work order.

Asset sync filter

Product sync filter

As you can see i’m using the sync share record entity for assets and products as well. This is because we are re purposing these entities in a lot of different areas of our system and the sync filter limit is too restrictive to retrieve all the records against the work order. To create these sync share records I used Dynamic CRM workflows to create and populate account, asset and product lookup’s based on certain conditions, such as when work order product is created or when a work order is created with a primary customer asset. This is a scalable solution but you just need to make sure that you have a reference to a work order so the user on the mobile device can see these records. Note* From my experience changing the entity sync configuration from standard sync to full sync seemed to have worked better.

Finally you need to delete these ‘sync share records’ when the user doesn’t need to see them anymore. You can create a recurring bulk deletion job to delete these records after the work order is completed or using any other condition of your choosing. Also make sure in the Plugins section in Woodford to enable deletion of Sync Share Records.

Hope this helps 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *