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!
|
|
Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 10 Jul 06 3:58 PM
|
fiogf49gjkf0d I was hoping some one could help me out with this. I've found a Link on support.saleslogix.com but info they gave isn't working for me. I've listed the link below.
http://support.saleslogix.com/kb/v52/sales/04170210.php3
I followed all of the steps in the link above and I've even tried the following:
Select distinct ContactId from History where DateDiff(day, Createdate,GetDate()) < 90
Select distinct ContactId from History where DateDiff(day, Createdate,GetDate()) > 90
Select distinct ContactId from History where (Not(DateDiff(day, Createdate,GetDate()) < 90))
Is there anyone out there that has a group setup that will display contacts not contacted in the last 90 days?
Thank you,
Gabe |
|
|
| |
| |
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 10 Jul 06 4:44 PM
|
fiogf49gjkf0d select contactid from contact where contactid in (select distinct contactid from history where ...)
... = Your DateDiff or whatever other code you wish to call against the "child" table in this instance. Each of the TACO groups must be relative to their table (contact/contactid) so you always have to return any result relative to those base tables.
If you were on 6.2.3+ you could use the SLX Profiler. What I do is I'll make a group and profile it's SQL (or the SQL of an existing group). I'll modify the SQL in Query Analyzer so that it runs then reverse engineer that into the proper group layout, sort order, and conditions. Doing this for every group is overkill but it may be necessary for those groups that have SQL that just isn't showing up the way you want it to. |
|
|
| |
| |
| |
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 10 Jul 06 5:17 PM
|
fiogf49gjkf0d Frank to your questions
1. No, I didn't check "use value as literal".
2. No, I didn't put ( ) around the SQL. But when I click OK it will put them automatically.
Here is the View SQL statement:
SELECT DISTINCT A1.CONTACTID, A1.NAMELF, A1.ACCOUNT, A1.WORKPHONE, A1.EMAIL, A1.ACCOUNTMANAGERID FROM CONTACT A1 WHERE (A1.ACCOUNTMANAGERID='U6UJ9A000007') AND NOT (A1.CONTACTID IN (SELECT DISTINCT contactid FROM sysdba.HISTORY WHERE (DATEDIFF([day], CreateDATE, GETDATE()) < 90))
I have also tried
SELECT DISTINCT A1.CONTACTID, A1.NAMELF, A1.ACCOUNT, A1.WORKPHONE, A1.EMAIL, A1.ACCOUNTMANAGERID FROM CONTACT A1 WHERE (A1.ACCOUNTMANAGERID='U6UJ9A000007') AND (A1.CONTACTID IN (SELECT DISTINCT contactid FROM sysdba.HISTORY WHERE (DATEDIFF([day], CreateDATE, GETDATE()) > 90))
and
SELECT DISTINCT A1.CONTACTID, A1.NAMELF, A1.ACCOUNT, A1.WORKPHONE, A1.EMAIL, A1.ACCOUNTMANAGERID FROM CONTACT A1 WHERE (A1.ACCOUNTMANAGERID='U6UJ9A000007') AND (A1.CONTACTID IN (SELECT DISTINCT contactid FROM sysdba.HISTORY WHERE (NOT (DATEDIFF([day], CreateDATE, GETDATE()) < 90)))
When I use the last two statements I get results but they aren't accurate. They will show contacts that have had updates within the last 90 days.
Thank You all for your help and quick replies! 
-Gabe |
|
|
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 10 Jul 06 5:17 PM
|
fiogf49gjkf0d I just created a group to show contacts with history records created in the last 90 days.
Here is how I built my condition:
Field: Contact.ContactID
Operator: IN
Value is: (select contactid from history where history.startdate > GetDate()-90)
Use Value as literal (checked)
Here is what I see in View SQL:
SELECT A1.CONTACTID, A1.ACCOUNT, A1.LASTNAME FROM CONTACT A1 WHERE (A1.CONTACTID IN (select contactid from history where history.startdate > GetDate()-90))
|
|
|
| |
| |
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 11 Jul 06 12:39 PM
|
fiogf49gjkf0d Originally posted by Frank Chaffin
I just created a group to show contacts with history records created in the last 90 days.
Here is how I built my condition:
Field: Contact.ContactID
Operator: IN
Value is: (select contactid from history where history.startdate > GetDate()-90)
Use Value as literal (checked)
Here is what I see in View SQL:
SELECT A1.CONTACTID, A1.ACCOUNT, A1.LASTNAME FROM CONTACT A1 WHERE (A1.CONTACTID IN (select contactid from history where history.startdate > GetDate()-90))
|
|
Frank, it worked great but I did have to make a few changes:
1. Added "DISTINCT"
2. Left "Use Value as Literal" UNCHECKED (Didn't get any results when checked).
3. Added "AND (NOT ContactId = '' OR ContactId = NULL)". Because without it, I wouldn’t get anything.
What I used:
SELECT DISTINCT CONTACTID FROM sysdba.HISTORY WHERE (MODIFYDATE > GETDATE() - 90) AND (NOT ContactId = '' OR contactid = NULL)
View SQL:
SELECT DISTINCT A1.CONTACTID, A1.NAMELF, A1.ACCOUNT, A1.WORKPHONE, A1.EMAIL, A1.ACCOUNTMANAGERID, A1.NAMELF FROM CONTACT A1 WHERE NOT (A1.CONTACTID IN (SELECT DISTINCT CONTACTID FROM sysdba.HISTORY WHERE (MODIFYDATE > GETDATE() - 90) AND (NOT ContactId = '' OR contactid = NULL))) AND (A1.ACCOUNTMANAGERID=:USERID) ORDER BY A1.NAMELF ASC
Thank you all for your help!
-Gabe
|
|
|
| |
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 11 Jul 06 5:27 PM
|
fiogf49gjkf0d When I write it out like "AND (NOT ContactId = '' OR contactid = NULL)" SQL will do all of the extra work for me by formatting it correctly.... lazy, I know and I'm sure that it will bite me in the ass one day down the road (probably when SQL 2007 comes out), but it works for now.
When I used the statement like you gave "AND (NOT (ContactiID = '' OR contactid = NULL))" I did not get any results, don't know why.
Please correct me if I'm wrong, I don't want to mislead anyone. I'm still very new to SQL and SalesLogix. Thank you for your help!
-Gabe
|
|
|
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 11 Jul 06 8:28 PM
|
fiogf49gjkf0d According to the Slx help, when you check the Use as Literal option Slx should take the syntax as you entered it. If you don't check this option Slx tries to figure out what you want to do. Some times this results in different behavior than I want. The behavior can also vary between different versions of Slx.
In any event just make sure that you cross test with Query Analyzer to verify that the proper records are being sourced.
|
|
|
| |
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 12 Jul 06 9:24 AM
|
fiogf49gjkf0d Originally posted by John Gundrum
Query Analyzer might not be the best place to test results. Use the Administrator SQL to test. This will guarantee the sql statement is compatible with the provider.
John G. |
|
John,
My point was to verify that the results returned by the Slx Provider are correct. To do that he needs to take the provider out of the picture. To do that in MSSQL databases I use the MSSQL's Query Analyzer.
Does this make sense? |
|
|
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 13 Jul 06 7:02 AM
|
fiogf49gjkf0d Time to revisit. I knew we had something here that showed accounts or contacts with no activity since x date. I finally got around to digging for it and found an Account group tab called 'No Activity Since 2004'. When I looked at the condition I found it had only one condition. A very simple one.
NOT Account.Accountid in (SELECT * FROM c_AccountNoActivity) True END
Editing the condition showed the value as
SELECT * FROM c_AccountNoActivity
and the 'Use Value as a Literal' is NOT checked.
That rung a bell in my head (it reverberated for quite sometime :p ). I recalled that it was easier to create a view of all account IDs that had some kind of activity or history. This required the use of a UNION to join the results of the history and activity tables. The view in SQL Server looks like this
SELECT DISTINCT ACCOUNTID
FROM sysdba.HISTORY
WHERE (ACCOUNTID LIKE 'A%') AND (CREATEDATE >= '01/01/2004')
UNION
SELECT DISTINCT ACCOUNTID
FROM sysdba.ACTIVITY
WHERE (ACCOUNTID LIKE 'A%') AND (CREATEDATE >= '01/01/2004')
All it does is return the account IDs with history or activity where the createdate is greater than 1/1/2004. The NOT in the account group condition reverses the logic to show accounts not in the view.
This method solved a few issues.
1) It solved the issue with using UNIONs through the provider (I've heard its not supported .. maybe thats changed)
2) It simplified the condition and readability in SLX.
3) It put the business logic on the backend. Which is better. It can be called anytime from anywhere.
4) It avoids any provider incompatibilities
So from the looks, all you would have to do is change the WHERE conditions to do a DATEDIFF() or what ever condition you require.
John G.
|
|
|
| |
|
Re: Creating an contact group which displays contacts not contacted within the last 'x' number of days.
Posted: 14 Jul 06 9:50 AM
|
fiogf49gjkf0d Oh, something else is that I'm using the accountID. I believe you wanted the contactID. If you really want to show it at the contact level the view would look like
SELECT DISTINCT CONTACTID
FROM sysdba.HISTORY
WHERE (CONTACTID LIKE 'C%') AND (CREATEDATE >= DATEADD(dd,-90,GetDate()))
UNION
SELECT DISTINCT CONTACTID
FROM sysdba.ACTIVITY
WHERE (CONTACTID LIKE 'C%') AND (CREATEDATE >= DATEADD(dd,-90,GetDate()))
<.PRE>
John G.
|
|
|
|