Merge Replication using joined tables and userid

I have an application where I want to use merge replication to link up with
Pocket pc's running sql sever ce. I have the queries written that will
extract the data I want to replicate but can not figure out how to set it up
in the replicate filters. Here is one of the queries.
SELECT dbo.SalesCall.SalesCallGUID, dbo.SalesCall.SalesCallID,
dbo.SalesCall.UserID, dbo.SalesCall.AccountID, dbo.SalesCall.CallDate,
dbo.SalesCall.VendorRepCall,
dbo.SalesCall.RegionalMgrCall, dbo.SalesCall.TechSupportCall,
dbo.SalesCall.Notes, dbo.SalesCall.IsActive,
dbo.SalesCall.Created, dbo.SalesCall.LastModified,
dbo.SalesCall.rowguid, dbo.SalesCall.ManagerCall, dbo.Users.LoginName
FROM dbo.SalesCall INNER JOIN
dbo.Account ON dbo.SalesCall.AccountID =
dbo.Account.AccountID INNER JOIN
dbo.UserTerritories ON dbo.Account.TerritoryID =
dbo.UserTerritories.TerritoryID INNER JOIN
dbo.Users ON dbo.UserTerritories.UserID =
dbo.Users.UserID
WHERE (dbo.SalesCall.IsActive = 1) AND (dbo.Users.LoginName =
'cbc\dgriffith') AND (dbo.SalesCall.LastModified > DATEADD([day], - 10,
GETDATE()))
I also want to replace the "cbc\dgriffith" with "cbc\" + login id of current
user.
Any Ideas? Is this even possible?
Thanks
I think so. When you create your publication, set as much of your filter
criteria that exists in the tables, then create your Joins down below for
the Accout, UserTerritories, and Users tables. Do not try to set up the
joins as part of your filter criteria.
"dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
news:E11C3AFB-25E2-4740-8803-5111A2FA03DB@.microsoft.com...
>I have an application where I want to use merge replication to link up with
> Pocket pc's running sql sever ce. I have the queries written that will
> extract the data I want to replicate but can not figure out how to set it
> up
> in the replicate filters. Here is one of the queries.
> SELECT dbo.SalesCall.SalesCallGUID, dbo.SalesCall.SalesCallID,
> dbo.SalesCall.UserID, dbo.SalesCall.AccountID, dbo.SalesCall.CallDate,
> dbo.SalesCall.VendorRepCall,
> dbo.SalesCall.RegionalMgrCall, dbo.SalesCall.TechSupportCall,
> dbo.SalesCall.Notes, dbo.SalesCall.IsActive,
> dbo.SalesCall.Created, dbo.SalesCall.LastModified,
> dbo.SalesCall.rowguid, dbo.SalesCall.ManagerCall, dbo.Users.LoginName
> FROM dbo.SalesCall INNER JOIN
> dbo.Account ON dbo.SalesCall.AccountID =
> dbo.Account.AccountID INNER JOIN
> dbo.UserTerritories ON dbo.Account.TerritoryID =
> dbo.UserTerritories.TerritoryID INNER JOIN
> dbo.Users ON dbo.UserTerritories.UserID =
> dbo.Users.UserID
> WHERE (dbo.SalesCall.IsActive = 1) AND (dbo.Users.LoginName =
> 'cbc\dgriffith') AND (dbo.SalesCall.LastModified > DATEADD([day], - 10,
> GETDATE()))
> I also want to replace the "cbc\dgriffith" with "cbc\" + login id of
> current
> user.
> Any Ideas? Is this even possible?
> Thanks
>
|||I guess that I still don't quite understand what part goes where.
There are two filter sections. The query I gave as an example when run from
query analyzer will return all sales calls made by dgriffith for the previous
10 days.
Thanks.
"Earl" wrote:

> I think so. When you create your publication, set as much of your filter
> criteria that exists in the tables, then create your Joins down below for
> the Accout, UserTerritories, and Users tables. Do not try to set up the
> joins as part of your filter criteria.
>
> "dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
> news:E11C3AFB-25E2-4740-8803-5111A2FA03DB@.microsoft.com...
>
>
|||You set up the filter in two steps. In your case, you would FIRST go to the
filter on SalesCalls and select all the fields that you want and set your
WHERE criteria. It's important to note that in the first section, you do not
handle the JOINED tables at all. Next, you select the JOIN down below and
click on the tables you want to JOIN (one each line) and set up the JOIN
criteria there. Thus, you will have set up 3 JOINS in the bottom section,
one each for Accounts, Users, and SalesTerritories.
"dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
news:3ACF74FF-DD99-4575-9B41-B26D91AB00A0@.microsoft.com...[vbcol=seagreen]
>I guess that I still don't quite understand what part goes where.
> There are two filter sections. The query I gave as an example when run
> from
> query analyzer will return all sales calls made by dgriffith for the
> previous
> 10 days.
> Thanks.
> "Earl" wrote:
|||Thanks for the help. I figured out part of the problem. For this to work I
would have to use left or right joins as there may not be records in some of
the tables. With an inner join I end up not seeing some records. I don't see
a way to change this behavior. I am now thinking I may have to do this using
multiple merge subscriptions.
"Earl" wrote:

> You set up the filter in two steps. In your case, you would FIRST go to the
> filter on SalesCalls and select all the fields that you want and set your
> WHERE criteria. It's important to note that in the first section, you do not
> handle the JOINED tables at all. Next, you select the JOIN down below and
> click on the tables you want to JOIN (one each line) and set up the JOIN
> criteria there. Thus, you will have set up 3 JOINS in the bottom section,
> one each for Accounts, Users, and SalesTerritories.
>
> "dpgriffith" <dpgriffith@.discussions.microsoft.com> wrote in message
> news:3ACF74FF-DD99-4575-9B41-B26D91AB00A0@.microsoft.com...
>
>
sql