Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Bronze Super Contributor
Posts: 146
Registered: ‎04-01-2009
Accepted Solution

Updating extention table with SQL or Integrity checker

Hi,

 

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.

 

update sysdba.anr_acc_ext

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!

 

Patrick

 

Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Updating extention table with SQL or Integrity checker

HAve you tried an Update From? How about a VBscript?

RJ Samp
Bronze Super Contributor
Posts: 146
Registered: ‎04-01-2009

Re: Updating extention table with SQL or Integrity checker

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.

 

Patrick

Highlighted
Bronze Super Contributor
Posts: 146
Registered: ‎04-01-2009

Re: Updating extention table with SQL or Integrity checker

Just to close this one out, I got a number of quick and useful responses on the IT Toolbox.   Scot Zimmerman pointed out:

"The OOTB territory realignment already has a way to realign regional and divisional managers. These fields (RegionalManagerID, DivisionalManagerID) already exist in the Account table. They just aren't exposed in any of the standard forms. I'd recommend that you move the data from your custom field into the RegionalManagerID field and modify your forms to use this. Then your realignment problem is solved. "
For various reasons, that wasn't my choice solution, but think it was probably the best overall solution.  I actually went with the solution that allowed me to get the job done now which was from Ron Fisher:
"Needs to look something like this..

update sysdba.anr_acc_ext
Set Sales_Manager = (Select S.managerID
from sysdba.usersecurity s where s.userid = (Select a.accountmangerid from Sysdba.Account A where a.accountid = sysdba.anr_acc_ext.accountid ))  "