ContactReview :: View topic - Crystal 11

Lois · n00b

So we learned that any Notes fields inserted into existing Crystal 11 reports turned to mush after upgrading to GM 8.5 because of the new Notes table.

We received the SQL query to run against the database to create a 'view' table and that worked beautifully. We add the vconthist table to the report, keep conthist in the report, replace the conthist.notes field with vconthist.notes and the notes appear.

However, the report is replicating the history records multiple times and I'm sure it's a linking issue with the new vconthist table. Can anyone help me to understand how to link this new table? We've tried every possible combination, but no luck. Here are the original links from the conthist table:

conthist.accountno to contact1.accountno
conthist.userid to opmgr.userid
conthist.loprecid to opmgr.opid

Thanks in advance.

Lois · n00b

Nevermind - figured it out.

For reports with conthist.notes in it, add the vnotes and vconthist notes tables to the report.

Link vnotes->accountno to vconthist->accountno
Link vconthist recid and accountno to its counterparts in conthist
If the report contains Opportunities and the OpMgr table, link vconthist->loprecid to conthist->loprecid.

If you link any fields from the vconthist table to other tables, you'll end up with multiple repeats of the same history record. At least that is what I experienced.

Hope this helps someone else dealing with Crystall 11 and GM v8.5.

DougCastell · GoldMine Guru

Deborah · Obsessive GoldMiner

Lois, can you please paste the SQL Query you are talking about? I assume it is a derived table that combines columns from the conthist and notes tables and creates a vnotes table?

I can't believe I haven't seem more chatter about this issue because all Crystal Reports will need to be revised and I assume the SQL REports aren't pulling notes as well.

Deborah · Obsessive GoldMiner

For those who will need this in the future to show notes from activities, you need to create a view in SQL:

create view vnotes as
select accountno, duration, userid, resultcode, actvcode, rectype,
ondate, ref, cast(cast(notes as varbinary(max))as varchar(max)) as 'Notes'
from conthist

Use the vnotes in Crystal Reports instead of conthist, linking on accountno.

What a PITA!