Simple 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:

• Modify your table structure to add a new field called (exactly) SyncStudioUserGroup. This field must be nvarchar 20. Make the same change (add this new field) to every table that will need filtering. Please note that SyncStudio will not automatically make changes to your database structures—you need to add this field yourself to every table that has to be filtered.

• In the SyncStudio UI select the table, check the Filter Rows checkmark and mark the fields for synchronization, including this new field. Please note that you will need to re-provision the database, re-generate the code and re-deploy the project to IIS.

• Now any rows that have a user group in this field will be sent ONLY to users that have identified themselves (at the client side) as belonging to that particular user group.

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

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]
@sync_min_timestamp       BigInt,
@sync_scope_local_id      INT,
@sync_scope_restore_count INT,
@sync_update_peer_key     INT,
@SyncStudioUserGroup     NVarChar(20)

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:

WHERE      (
(
[side].SyncStudioUserGroup=@SyncStudioUserGroup
) . . . (other statements in the WHERE clause)

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:

• Generally, you will need to enable the table for Simple Filtering. The reason for this requirement is that Simple Filtering will provide the stored procedure with the USER GROUP of the user that is being synchronized, which will allow you to create your own code to determine what records need to be sent to that particular user. Note: the USER GROUP is specified at the client.

• The stored procedures described above are created during the database provisioning phase. This means that when you set a new table for filtering you must make the changes in the SyncStudio Studio and then de-provision and re-provision the database in order for the stored procedures to be generated, before you can make any custom changes. Please read notes (3) and (4) below regarding these changes.

• When you de-provision and re-provision a database your custom code will not be preserved, so you will have to manually add the changes yourself. If you use Advanced Filtering you should always make a backup of the database, or at least script the stored procedures and save them before you de-provision, as the de-provisioning process will erase all custom changes.

• If you have a database that has been fully provisioned for synchronization, with all the appropriate tables marked for Simple (i.e., Row-based) Filtering and then you decide to use Advanced Filtering for one or more tables that are currently being synchronized and filtered, and this is the only change that you are making (i.e., no other changes to the data structures, no new/altered synchronized tables or fields, etc.) then you do not need to de-provision and re-provision the database. The reason is that since you did not change the structure of any tables or fields the synchronization code will remain the same. However, if you also need to make any other changes to the schema, such as adding a new synchronized table or any new fields then the de-provisioning and re-provisioning is mandatory, and you will have to manually re-apply any custom changes that you may have made to the _selectchanges stored procedures.

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.