SalesLogix Activity / History Report

[reporting]

Hello all. I am working on a SalesLogix Activity / History Report. This report will print the most recent History record (what has been done with a client) as well as the next Activity record (the next thing to be done with that client). No problems with that part of the project. Where the problem arises is that my client wants the last History record to print if there are no planned Activities and vice versa: print the next Activity record if there is no History. At the end of the day, I was looking at how to do that and (long day!) ran into a roadblock. I have some ideas but am here to see if someone else has done that kind of report.

----- ----- ----- ----- ----- -----

Report Layout:

This is the Table Structure in my report:
ACCOUNT
CONTACT
HISTORY
HISTORY_PICKLIST (Alias of PICKLIST used with HISTORY Table)
ACTIVITY
ACTIVITY_PICKLIST (Alias of PICKLIST used with ACTIVITY Table)

I have the following links:
ACCOUNT.AccountID ROJ CONTACT.AccountID (need all contacts of each ACCOUNT)
CONTACT.ContactID = HISTORY.ContactID (Tried ROJ here but it didn't work)
HISTORY.Type = HISTORY_PICKLIST.ID
CONTACT.ContactID = ACTIVITY.ContactID (Also tried ROJ here with same result...)
ACTIVITY.Type = ACTIVITY_PICKLIST.ID

Groups:
G1: ACCOUNT.SecCodeID
G2: ACCOUNT.Account
G3: Contact Name (Formula = Last Name, First Name)

Sections:
GH1: Visible
GH2: Suppressed
GH3: Suppressed if Contact Name formula is NULL (ie if there is an Activity with an Account but not a Contact); except for ACCOUNT.SecCodeID, all fields on the report are actually placed in GH3: this allowed me to rapidly show only the last History record and the next Activity Record - when placed in the detail lines, all History & Activity records were shown on the report.
All other sections are also suppressed...

Fields:[tt]
GH3a: G2 G3 HISTORY.CompletedDate ACTIVITY.StartDate
GH3b: HISTORY.Text ACTIVITY.Text
GH3c: HISTORY.Description ACTIVITY.Description
[/tt]
G2 = Group2 Name & G3 = Group3 Name

Sorts
HISTORY.CompletedDate (DESC)
ACTIVITY.StartDate (ASC)

----- ----- ----- ----- ----- -----

As noted before, the final part of this report is to print the last History record if there are no planned Activities and print the next Activity record if there is no History. I thought of 2 possible solutions:

a. Putting the relevant fields into a String Array Variable, then printing those elements.
b. A subreport for History and another SR for Activities.

So my questions are:
1. Has anyone done a report like this. If so, how did you solve this problem?
2. Any other solutions?

Thanks very much,

John
John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[kenhamady]

I assume you are connecting via ODBC. Will you need to put any criteria on the History record or the Activity record? If so this can cancel the effect of using outer joins and push you into subreports. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[reporting]

Thanks Ken. Yes, it is an ODBC connection into an MS SQL Server database.

I tried subreports (one for the Activity Table and one for the History table), Array Variables (crashed), and Multiple Variables. Both the regular report, the subreport and the multiple variables report give me the same result: results when a Contact has both Activities and History, and nothing when the contact has only Activities or History.

Tomorrow, I will try an Alias tables: one contact table for the History table and another one foe the Activity table.

Thanks again,

John John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[kenhamady]

Try 1 table alone in each of 3 reports:

1) The contact table in the primary report
2) The Activity Table in the first subreport
3) The History table in the second subreport


I assume that the ContactID is in both Activity and History.
I am not sure I understand what the picklists are used for. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[reporting]

Thanks again Ken. Tried that yesterday and it gave the same result as the other attempts. Thinking about it again this AM, I will try the Alias Contact table I mentioned late yesterday. I will also try to see if I can figure out a way to show a suppressed series of sub-sections depending on whether History is present, Activity is present, or both are present. Problem is that I am starting to run out of budgeted time on this report...

By the way:

1. The picklist tables are used because HISTORY.Text & ACTIVITY.Text are actually codes in the History & Activity tables...
2. ContactID is the join field from Contact to History & Activity.

Thanks again,

John
John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[kenhamady]

John,

Linked subreports can't make the main report lose records, so either something is not correct in your configuration or I misunderstand the problem.

Start with just a report of the contact table all by itself. That should get all of the Contacts.

Now add a linked subreport that uses just the Activity table linked to the main report by Contact ID, with your date criteria. You may get some blank subreports but you shouldn't lose any contacts.

Last add a linked subreport that uses just the History table liked the same way. Again, you shouldn't lose any records. Where does this differ from your test? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[reporting]

Hi there Ken. Just wanted to tell you that, because of budget issues, we had a meeting about the SalesLogix vs. Crystal issues this AM and the end result was ... they are going to alter the logic when a new prospect is created. 98% of the prospects come from their web site, and are automatically entered in SalesLogix. With the new logic, they will create both a History record ("New Prospect From Web Site&quot as well as an Activity ("Initial Call&quot when the web prospects are imported. Problem solved in SalesLogix!!! They will work on process for the other 2%...

Some time (when I have more time...), I will try and figure this one out!

Thanks again,

John
John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[rotaman]

Hi all,

our company recently installed Saleslogix, but we are having lots of issues with this CRM, and have not really found any competence out there to help us out with some issues we are having. Please post a reply if you have any expertise in this CRM

Thanks

 

[rotaman]

Hi all,

simply question. Does Saleslogix log activity? Can we find out how users are working?

thanks for any reply

 

[IdoMillet]

John, I suspect the problem that was lurking in the background was due to each contact possibly having multiple records in both history as well as activity tables. This creates a record inflation situation. The best solution is to create at least one view in SQL Server that returns just the last activity record for each contact. To simplify things, you can also throw in a view that returns the last history record for each contact.

At that point, by using these views instead of the tables, the record inflation problem is solved.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Nippon1]

Hello:

I would like to build a Query for the Notes/History & Opportunity. Will it work?

Also, does anyone know how to lock down the ability to add duplicate records into the DB?

Thanks,
sam@freightcapital.com

 

[rotaman]

Hi all,

I know this isn't really what you're talking about, but i'm figuring you're the most competant group to answer my question.

Here it is:

In SLX, When a user leaves, you usually "retire" the user. Well we simply deleted him in the Administrator, and now just figured out that we could not delete any of the history elements in the users accounts. The error we get is "Owner Access Violation. You can only delete your own histories!" These can not even be deleted with an admin account.

So my question is: are these histories deletable? If so, how?

Thanks!