04-14-2011 01:33 PM
We’re (still) running 7.2.2 LAN with remotes. We have an extension table to our account table, anr_acc_ext, with a 1:1 relationship joined on accountid. In that table, we store the userid of the Account Manager’s manager (Sales_Manager). The problem is that we tend to do major re-organizations of sales territory fairly often, and although the territory re-alignment tool realigns the accounts correctly, it doesn’t touch the Sales Manager field, so many, many accounts show the wrong Sales Manager.
I’d like to run an update, and preferably create a Database Integrity rule that checks and updates the sales manager field if it is not correct. Having trouble getting there.
So, as a summary, it looks something like this:
The account table has accountmangerid
The anr_acc_ext table is joined to the account table by accountid and has the sales_managerid.
The usersecurity table has the accountmangerid (userid) and the sales_Mnagerid (managerid).
This would work in Query Analyzer, but we already know that the SLX provider for 7.2 doesn’t like the joins.
Set Sales_Manager = S.managerID
from sysdba.usersecurity s
join sysdba.account a on a.accountmanagerid = s.userid
join sysdba.anr_acc_ext x on x.accountid = a.accountid
Of course, I’d want to throw a “where” clause in there as well to only update records that are not correct. I can write a bunch of individual rules specifically setting the value for each account manager and sales manager, but was hoping for something more universal and re-usable. Or somehow getting territory realignmnet to do it, or getting the update to kick the script that happens when I use the screens. (Re-picking the account manager sets it correctly).
Thanks for what ever you can provide!
Solved! Go to Solution.
04-19-2011 02:20 PM
Thanks for the ideas RJ. I tried a couple different versions of an update from, but still no go. Took a look at the "Use VB script" section in Integrity Checker, but there are not really any examples there or could I find anyone out in the ether, so couldn't do anything there. Also, I could put it in a VB Script in DTS package, but it still needs to call the SLX provider to create the remote transactions, so I seem be back where I started.
04-19-2011 02:56 PM
Just to close this one out, I got a number of quick and useful responses on the IT Toolbox. Scot Zimmerman pointed out: