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!

Administration Forums - General Administration
Forum to discuss general administration topics for SalesLogix (including LAN & remote topics). View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to General Administration | New Thread

View: Search:

Author

Thread: Creating a group that returns accounts that have not been called by their account manager in xx days

Rick Moore
Posts: 9

Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Mar 09 12:36 PM

As we see more and more situations where multiple people working with accounts, the "Last History" fails to catch accounts where the "Account Manager" or other associated user has not been in contact with the account.

I've seen several references to doing a bit of this, but was unable to find a method using the "IN" function in the criteria that would return exactly what we needed. In my case there were a few additional criteria in the query including ISPrimary=T and History.Result=value.

This could also be adapted easily for users where key accounts require an on-site visit each period (monthly/quarterly) from the salesperson - I've got to go back and add that at another client.

Note: This works well for an established system where they have both salespeople and customer service representatives working with accounts. What does not get returned are accounts where the Account Manager has NO calls with the account.

The first step was to create the view in SQL:

In SQL Server, create a new query with the following tables:
Account, Contact, UserInfo, History – join the accounts on account.accountmanagerid = userinfo.userid; account.accountid = contact.accountid, contact.contactid = history.contactid

The following statement returns only phone calls (ie. History Type = 262146)

SELECT sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.TYPE AS AcctType, sysdba.CONTACT.CONTACTID, sysdba.CONTACT.ACCOUNTID,
sysdba.CONTACT.LASTNAME, sysdba.CONTACT.FIRSTNAME, sysdba.CONTACT.ISPRIMARY, sysdba.HISTORY.CATEGORY,
MAX(sysdba.HISTORY.STARTDATE) AS LastCall, sysdba.HISTORY.DURATION, sysdba.HISTORY.USERID, sysdba.HISTORY.RESULT,
sysdba.HISTORY.TYPE AS HistType
FROM sysdba.HISTORY INNER JOIN
sysdba.USERINFO INNER JOIN
sysdba.ACCOUNT INNER JOIN
sysdba.CONTACT ON sysdba.ACCOUNT.ACCOUNTID = sysdba.CONTACT.ACCOUNTID ON
sysdba.USERINFO.USERID = sysdba.ACCOUNT.ACCOUNTMANAGERID ON sysdba.HISTORY.CONTACTID = sysdba.CONTACT.CONTACTID
GROUP BY sysdba.ACCOUNT.ACCOUNT, sysdba.ACCOUNT.TYPE, sysdba.CONTACT.LASTNAME, sysdba.CONTACT.FIRSTNAME, sysdba.CONTACT.ISPRIMARY,
sysdba.HISTORY.CATEGORY, sysdba.HISTORY.DURATION, sysdba.HISTORY.USERID, sysdba.HISTORY.RESULT, sysdba.HISTORY.TYPE,
sysdba.CONTACT.CONTACTID, sysdba.CONTACT.ACCOUNTID
HAVING (sysdba.HISTORY.TYPE = 262146)

Once you get the statement working correctly, paste it into word and replace “sysdba.” With “” (null) to remove the sysdba reference (which SLX doesn’t like anyway).
Ahead of the select statement you now have, you will need to add the create view component

Create view MYVIEWNAME as

SELECT ACCOUNT.ACCOUNT, ACCOUNT.TYPE AS AcctType, CONTACT.CONTACTID, CONTACT.ACCOUNTID, CONTACT.LASTNAME, CONTACT.FIRSTNAME, CONTACT.ISPRIMARY, HISTORY.CATEGORY, MAX(HISTORY.STARTDATE) AS LastCall, HISTORY.DURATION, HISTORY.USERID, HISTORY.RESULT, HISTORY.TYPE AS HistType

FROM HISTORY INNER JOIN
USERINFO INNER JOIN
ACCOUNT INNER JOIN
CONTACT ON ACCOUNT.ACCOUNTID = CONTACT.ACCOUNTID ON
USERINFO.USERID = ACCOUNT.ACCOUNTMANAGERID ON HISTORY.CONTACTID = CONTACT.CONTACTID
GROUP BY ACCOUNT.ACCOUNT, ACCOUNT.TYPE, CONTACT.LASTNAME, CONTACT.FIRSTNAME, CONTACT.ISPRIMARY, HISTORY.CATEGORY, HISTORY.DURATION, HISTORY.USERID, HISTORY.RESULT, HISTORY.TYPE, CONTACT.CONTACTID, CONTACT.ACCOUNTID
HAVING (HISTORY.TYPE = 262146)

The next step is to Open SLX Administrator and Open the Execute SQL function
Add SQL and paste the new statement into the SQL (with the Create View portion).
Execute the SQL on the host

Log Off and back onto the Administrator and Open Database Manager, right click on the new view and go to properties
-- Click “Enable”

Log off / on administrator and go to Tools, Global Joins. Create a global join with Parent Table CONTACT using CONTACTID joined to “NEW VIEW” using CONTACTID

Then you can go into the SalesLogix client and create a new contact group using the new view.

Happy Friday !!

[Reply][Quote]

SLX_Novice
Posts: 246

Re: Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 09 Mar 09 8:55 AM

Thanks for this post Rick, this is a nice tool. I'll give it a shot!

[Reply][Quote]

Walter Shpuntoff
Posts: 167

Re: Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Mar 09 5:10 AM

That will only work for Contacts that have been called at some point.
i.e. if they have no call history, then they won't show in the group.

For those to show up, you just use the query builder and create a group like this:

FieldName: Contact.ContactId
FieldType: String
Operator: IN
Value SELECT DISTINCT CONTACT ID FROM HISTORY WHERE DATEDIFF(D, HISTORY.CREATEDATE, GETDATE()) < XX

(Whatever your xx value is)
Flip the condition to NOT

I am very wary of building views with MAX operators. When the database gets big, they can really impact performance.

ws

[Reply][Quote]

Rick Moore
Posts: 9

Re: Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Mar 09 4:08 PM

Hey Walter,

I would agree with you on the MAX operators, in this requirement they require a group where the contact has not been contacted by their account manager. I actually tried unsucessfully to do this in an IN statement. Since I need History.CompletedBy = Contact.AccountManager and the date filter, do you have any thoughts on how to this could be accomplished without using the view method? Ideally, I'd love to have both the ones who haven't been called by their account manager at all and the ones not contacted by them recently in one group... I know that's asking a lot

Thanks,
Rick

[Reply][Quote]

Walter Shpuntoff
Posts: 167

Re: Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Mar 09 4:33 PM

As you build your query - look at the SQL it is generating & you can recycle the table aliases.

i.e. If Contact is A1, then it becomes CompletedBy = A1.AccountManager

Is that what you needed?

ws

[Reply][Quote]

Phil Parkin
Posts: 819

Re: Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Mar 09 11:15 PM

So you need something like this as your IN clause? Note - I used 30 as the 'days' period.

Select Distinct C.ContactID
From Contact C
Left Join History H On (C.ContactID = H.ContactID and C.AccountManagerID = H.CompletedUser)
Where DATEDIFF(D, H.CreateDate, GetDate()) > 30 or H.ContactID is Null

Phil

[Reply][Quote]

Phil Parkin
Posts: 819

Re: Creating a group that returns accounts that have not been called by their account manager in xx days Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Mar 09 1:10 AM

The day has moved on a little and my brain is working properly now. This works better:

select c.ContactID
from Contact c
where  not exists (select H1.ContactID
from History H1
where H1.ContactID = c.ContactID and DateDiff(D, H1.CreateDate, GetDate()) <= 30 and (H1.CompletedUser = c.AccountManagerID or H1.CompletedUser is Null))

[Reply][Quote]

Page 1 of 1

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!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 11/26/2024 11:22:46 AM