How to handle FK records that move into a filtered scope but don't get sync'd because they are not "changed" RRS feed

  • Question

  • SFx 2.1; SqlServer 2008; PC with SqlCe; WCF

    In SFx 1.0 I had control over the SQL statemets including GetChanges() ... so I could modify the SQL statements at runtime.  In SFx 2.1 the statements are in SPs. 

    This is an issue when I try to deal with Filtered Scopes.  When a PK record is "changed" it moves into  filtered scope; however, the FK records are not "changed" ... so the PK record that was actually changed falls in the scope and is selected by GetChanges whereas the FK records are not selected.

    I think I need to handle this by identifying each PK record that has just moved into the scope and running a different SP for the FK records ... one that does not have the "local_update_pier_timestamp = @sync_min_timestamp" part of the WHERE clause.

    Currently to make it work, before I select changes, I am running an SP that identifies all the FK records that just moved into the scope and doing something like "UPDATE MyTable SET AnyField=AnyField where MyPKField = 123" - this forces the record to appear "changed" ... the problem with this kludge is that everyone who already had this record in their database syncs it again unnecessarily.

    Question: See any holes in this logic?  Is there a way to get more granular control of the SP (ex passing an additional parameter ... @IncludeTimestampInFilter) through the framework?  Any other ideas on how to handle this?

    EDIT: Maybe I could intercept the changes in ChangesSelected event and merge the additional records into the recordset ... I'd have to figure out how to handle the change tracking columns and working with batching would present problems too ... any thoughts?
    Sunday, June 26, 2011 5:48 PM

All replies

  • if you're doing DownloadOnly sync, your approach may work.

    but if you are doing bidirectional syncs, when you manually select the FK entries (either via a custom SQL/SP or via the ChangesSelected), the updates you're making will be recorded as changes in your local database and will be picked up for upload in the next sync which will in turn be picked up by other clients as well. now to work around this, you would then again try to intercept the upload in the ChangesSelected and remove this FK rows from synching on upload.

    If you're not synching that many rows, an update on the FK rows in the server which will cascade to other clients would require less coding on your part.

    if you really want to customize the queries almost similar to the offline providers, have a look at the approach here: http://jtabadero.wordpress.com/2010/03/05/passing-dynamic-filter-values-to-sync-framework-collaboration-scenario-sync-scopes/

    you should be able to replace the queries in the custom server provider.

    Monday, June 27, 2011 1:04 AM