|One of the most common requirements for database synchronization systems is to be able to filter the data rows that will be sent to the client device based on some type of criteria. For example, in a sales application a salesperson may only need to receive the customers located in some territory or area. The basic Microsoft Sync Framework offers only limited support for row filtering, however.There are two ways to filter data in SyncStudio: Simple Filtering and Advanced Filtering.
Simple Filtering is useful when a row is intended to be given to one or more users, based on the user group. In this case all you need to do is the following:
Simple filtering can be very useful for things like subscription-based data synchronization, in which every group of user needs to get a completely different set of records based on their group membership. Other filtering scenarios in which simple filtering fits the need are those in which the records can be assigned to only one user. However, if your requirement is for filtering to be done based on some more complicated set of criteria, such as a user being in some combination of region, territory, etc. then you should consider using Advanced Filtering instead, as described in the next section below.
An example is shown in the screenshot below. Here a table called CUSTLIST is being set-up for filtering. Note that the table has a field called SyncStudioUserGroup (which the developer added) which was declared as nvarchar (20). Here the developer simply checked the “Filter Rows” option for this table to configure the filtering.
In the next screenshot we see the case of a table that cannot be filtered because it does not have the SyncStudioUserGroup field. When this happens you will see that the filtering checkbox is disabled and instead says “This table cannot be filtered” and there is a help icon next to the checkbox. If you click on the help icon you will get a message that tells you to add the SyncStudioUserGroup field to this table so that it can be filtered.
Please note that the SyncStudioUserGroup field must be set to synchronize.
Advanced Filtering allows you to set up your own custom filtering criteria. In order to use Advanced Filtering you need to first enable the table for Simple Filtering, exactly as described above, and then you have to make a manual change to the stored procedures that control the synchronization.
For every table to be synchronized the MS Sync Framework will create a number of stored procedures. The one you need to modify is called <Your Table Name>_selectchanges. For example, for a table called CUSTLIST the stored procedure will be: CUSTLIST_selectchanges, as shown in the code snippet below:
ALTER PROCEDURE [dbo].[CUSTLIST_selectchanges]
In the Microsoft SQL Server Management Studio you can see these stored procedures by selecting the database, then Programmability, then Stored Procedures, as seen in the next screenshot. For every table that you have set for synchronization you will see that there are a number of stored procedures. The only one that needs to be modified for Advanced Filtering is _selectchanges.
In the Stored Procedure declaration above, please notice the presence of the SyncStudioUserGroup parameter, which in SyncStudio will be automatically populated with the user group. The next step is to locate the WHERE clause of the stored procedure, which will contain the following code:
Simple filtering is done in the WHERE clause by [side].SyncStudioUserGroup=@SyncStudioUserGroup, where the parameter @SyncStudioUserGroup will be automatically populated with the user group.
The idea is to replace this code with other conditions of your choice. For example, you could create an auxiliary (non-synchronized) table called FILTERSETTINGS that contains two fields: USERGROUP and FILTERCODE. You could then modify that portion of the WHERE clause to be something like:
([side].FILTERCODE IN (SELECT FILTERCODE FROM FILTERSETTINGS WHERE USERGROUP=@SyncStudioUserGroup) OR EXISTS(SELECT FILTERCODE FROM FILTERSETTINGS WHERE (USERGROUP=@SyncStudioUserGroup AND FILTERCODE=’ALL’))
Where [side].FILTERCODE is an additional synchronized field in your table in which you can store any arbitrary value, a territory or group, for example. Please note that fields in the table that you are synchronizing need to be prefixed with the [side] qualifier as shown above.
The code snippet above, which assumes that your table has an additional synchronized field called FILTERCODE, will filter based on the contents of the FILTERCODE field and the associations made in the FILTERSETTINGS table between the USERID and FILTERCODE fields.
For example, assume that the FILTERSETTINGS table has the following rows:
Row #1: USERGROUP=GROUP1, FILTERCODE=SALES
Row #2: USERGROUP=GROUP2, FILTERCODE=SALES
Row #3: USERGROUP=GROUP3, FILTERCODE=OPSUS
Row #4: USERGROUP=GROUP4, FILTERCODE=OPSEU
Row #5: USERGROUP=GROUP5, FILTERCODE=ALL
In this case the code snippet above will send any records with field FILTERCODE=SALES to USERGROUP1 and USERGROUP2, records with FILTERCODE=OPSUS will be sent to USERGROUP3, records with FILTERCODE=OPSEU will be sent to USERGROUP4 and finally USERGROUP5 is marked as “ALL” so it will get all the records.
Some important things to keep in mind when you use Advanced Filtering:
Future releases of SyncStudio will add more filtering functionality to our product, for example, to keep track of these custom changes to the stored procedures.