5/17/2024 4:46:41 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Account transfer query
Posted: 10 Aug 07 1:00 PM
|
I am trying to figure out how to build a query of accounts who have been transfered to another rep within X months.
Any ideas? |
|
|
|
Re: Account transfer query
Posted: 15 Aug 07 6:35 AM
|
AFAIK there's no flag for you to key off. SalesLogix does NOT know if the accountmanager has been changed.
Having said that..
IF you transfer manually
AND
You have the AccountManager control being "tracked" (record to history) on the account detail form
THEN
You could look at history.
If you are using territory re-alignment.. not possible.
--
rjl |
|
|
|
Re: Account transfer query
Posted: 14 Sep 07 1:05 PM
|
You can do this just like Bob mentioned, as long as it hasn't been done with the territory re-align... (Sorry I misread the message the first time through). Just create a query through the query builder with the following criteria:
Account.History.Category = 'DATABASE CHANGE'
AND
Account.History.Description Contains 'Accountmanagerid'
I also modified the query layout to show the 'Notes' field which identifies the seccode of the previous & new owners... Not the easiest to read, but it shows which accounts have had a change to the account manager. |
|
|
|
Re: Account transfer query
Posted: 19 Nov 07 11:51 PM
|
Hi Missy,
I've built a similar query that uses 2 views to show what you are asking. The first statement i've used to create a view named 'MANAGER_CHANGES' and the second statement uses the first view and the user and account tables. (i've also created another view using the second statement so that any report/query can simply use the second view)
This is purely T-SQL (i'm assuming that's ok as we're in that forum...)
If you want to include the 'X months' scenario, simply add 'and datediff(mm,MODIFYDATE,getdate()) <= 1' (where the 1 at the end is the number of months) to the first view, after the last line
SELECT ACCOUNTID, ACCOUNTNAME, STARTDATE AS dated, DESCRIPTION, USERID, USERNAME,substring(longnotes, 46, 12) AS FromValue,
substring(longnotes, 19, 12) AS ToValue, LONGNOTES
FROM sysdba.HISTORY
WHERE (TYPE = 262156) AND (DESCRIPTION LIKE '%accountmanagerid%')
SELECT sysdba.MANAGER_CHANGES.ACCOUNTID, sysdba.MANAGER_CHANGES.ACCOUNTNAME, sysdba.MANAGER_CHANGES.dated,
sysdba.MANAGER_CHANGES.USERNAME, sysdba.MANAGER_CHANGES.DESCRIPTION, sysdba.USERINFO.USERNAME AS FromManager,
USERINFO_1.USERNAME AS ToManager
FROM sysdba.MANAGER_CHANGES INNER JOIN
sysdba.USERINFO ON sysdba.MANAGER_CHANGES.FromValue = sysdba.USERINFO.USERID INNER JOIN
sysdba.USERINFO AS USERINFO_1 ON sysdba.MANAGER_CHANGES.ToValue = USERINFO_1.USERID
by the way - all this relies on the fact that the Control on the Account Form is audited (Record Changes = True). This is, as standard, turned on (i think!?) so it should be ok. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|