Thursday, May 03, 2012 1:26 PM
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 03, 2012 1:47 PMModerator
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 03, 2012 3:50 PM
Thanks for the suggestion
can i code as below
serverprovision.tables["globaltables"].filterclause = "[side].[state] = AP";
so do i need to add another set of above statements like below
serverprovision.tables["globaltables"].filterclause = "[side].[COUNTRY] = INDIA";
is this correct or any thing that I need to change.
please suggest me
Thursday, May 03, 2012 11:56 PMModerator
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...
serverprovision.tables["globaltables"].filterclause = "[side].[state] = AP AND [side].[COUNTRY] = INDIA";
Friday, May 04, 2012 10:41 AM
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.
Friday, May 04, 2012 11:07 AMModerator
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 04, 2012 2:30 PM
- Marked As Answer by Veera G Monday, May 07, 2012 9:38 AM
Tuesday, July 10, 2012 2:03 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!
Wednesday, July 11, 2012 1:11 AMModerator
have a look at this: Adding Filter to Local Database Cache generated sync
moving forward, i suggest you open up a new thread.