locked
Filter clause in microsoft sync 2.1 RRS feed

  • Question

  • HI All,

    I am new to MS SYNC.

    I got a scenario where I need to do filter on multiple columns on a table. (ex  filter clause state = 'AP' AND COUNTRY ='INDIA').

    please give me with example

    Thanks in Advance

    for all the memeber.

    Thursday, May 3, 2012 1:26 PM

Answers

All replies

  • that should be fine, just add both columns as filter columns... the filter clause supports having more than one column there...the filter clause is actually just concatenated with the WHERE clause in the select changes stored proc, so any condition that wont break the WHERE clause will work...

    Thursday, May 3, 2012 1:47 PM
  • Thanks for the suggestion

    can i code as below

    serverprovision.tables["globaltables"].addfiltercolumn("Id");

    serverprovision.tables["globaltables"].filterclause = "[side].[state] = AP";

    so do i need to add another set of above statements like below

    serverprovision.tables["globaltables"].addfiltercolumn("Id");

    serverprovision.tables["globaltables"].filterclause = "[side].[COUNTRY] = INDIA";

    is this correct or any thing that I need to change.

    please suggest me

    thanks

    veera


    veera

    Thursday, May 3, 2012 3:50 PM
  • you can only have one filter clause per table... combine the two filter clauses you have into one... also, the filter column has to be the column you specify in the filterclause...

    in your code above, you're adding ID as the filter column but you are filtering on  state and Country...

    e.g.

    serverprovision.tables["globaltables"].addfiltercolumn("state");

    serverprovision.tables["globaltables"].addfiltercolumn("Country");

    serverprovision.tables["globaltables"].filterclause = "[side].[state] = AP AND [side].[COUNTRY] = INDIA";

    Thursday, May 3, 2012 11:56 PM
  • Thanks june.

    I am having couple of doubts like

    can we write joins, group by, having  on tables ,

    can we check Nulls on columns before filtering,,

    and conditions like 'LIKE' starts with '%abc'

    AND if any links which will be more helpfull.


    veera

    Friday, May 4, 2012 10:41 AM
  • as i have mentioned, the filter clause is just concatenated with the WHERE clause of the selectchanges stored procedure...

    am not sure why would you want to do groups and having in the filter, but am sure you can simulate joins using the IN clause and LIKE will also work.

    sync fx is for synching data, if you have more complex business rules, i would suggest you do it in your app prior to sync.

    if you want to test filtering, add one filter, go take a look a the selectchanges sp and play around adding your filter there...once you're happy with it, copy the your filter in the FilterClause of Sync framework and see if Sync Framework will produce the same WHERE clause when you provision. 

    or you can leave the SP as it is. however, note that this modification is done outside of Sync fx, so its not in your provisioning code.

    Friday, May 4, 2012 11:07 AM
  • thanks JUNE


    veera

    • Marked as answer by Veera G Monday, May 7, 2012 9:38 AM
    Friday, May 4, 2012 2:30 PM
  • Veera and JuneT,

    I have not been able to find any good examples setting a filter to the tables I am using in my LocalDataCache.  I have a LocalDataCache object that I added to my app, that has a table in it that I need to filter.   Where do I set this  addfiltercolumn and set the filterclause ?    I have a table on the server that I only want records in it that are specific to the user's UserID.   I cannot get any kind of filtering to work.

    I don't see serverprovision object on the syncagent...  where are you finding examples to work with?  All of the examples I find are not complete.  All I want to do is set a filter on what CompactSQL syncs to on the server.  I am running Sync 2.1 with VS2010 on the client  and SQL Server 2008 R2 on the server.

    Any help would be very appreciated!

    Thank you!

    Jeff



    • Edited by jdressing1 Tuesday, July 10, 2012 2:11 PM
    Tuesday, July 10, 2012 2:03 PM
  • have a look at this: Adding Filter to Local Database Cache generated sync

    moving forward, i suggest you open up a new thread.

    Wednesday, July 11, 2012 1:11 AM