ACT! encrypted fields and Crystal

[Petzl]

Ok...so some fields in ACT! are encrypted. e.g. The "Scheduled For" field from the activities table. How are you meant to report on them through Crystal?

Any ideas?

 

[Storyteller]

Hello Petzl,
You will have to get a copy of the ACT! SDK for the secret decoder ring ;-) Also remember that ACT! uses a "Unique ID" to identify the Contact.

You will have to create a formula along the lines of:
@Contact
{Contacts.UniqueID} = {Contacts.ContactName}
(I do not have ACT! in front of me now, so my formula might be a bit wonky. But hopefully you get the idea

Regards,
Michael

 

[philn99]

I had the same problem. the fields are not encrypted, they are translated into unique fields by ACT!

I used an alias file approach within Cystal and my reports work fine. E-Mail me if you want more information.

 

[Petzl]

Phil,

Thanks for the response. I would be interested in knowing more about your approach if you could be so kind!!

Cheers,

Petzl

 

[yehong]

Hi Phil, I am having the same problem. Would you suggest me how were you able to fix this problme within Crystal?

thanks,

 

[FoxGolfer]

Phil,
I would like more information as well. Help!
Thanks in advance,

 

[1452192]

I need an answer as well. I have the same problem. Please help

 

[FoxGolfer]

Can somebody contact either Storyteller or Philn99? They seem to have found the "secret decoder ring" for ACT! 6.0 and C.R.; and there are several of us who have not. I downloaded the ACT! SDK and didn't find what I was looking for. The schema published for the ACT tables does NOT list the linking fields. For example, I have tried numerous linking combination with no success in listing the "Record Manager"; all I receive is the unique id, which is a series of ASCII characters.

Tom

 

[Storyteller]

Hello All,

Follow this link to see the ACT! table linking structure in Crystal Reports:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/act_table_linking.pdf

A more general document on table linking in Crystal can be found here:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_pclinking.pdf

A Crystal Reports Knowledge Base article: "Specific linking structure required for ACT! database" Article ID: c2006006
Follow this link:

http://support.crystaldecisions.com/library/kbase/articles/c2006006.asp

Hope this information helps,
Michael

 

[mhelenek]

Hi all,

To clear things up - this is not a linking issue. The Record Manager in the Contact table is set to a Unique Id. Unique Id's are calculated by ACT! and set in the corresponding field (Contact.Unique Id) in the Contact table.

In order to look up the name of the person who is the Record Manager for a given record, you have to set Record Manager = Contact.Name WHERE Contact.Unique Id = Record Manager.

The problem is that Crystal does not support SQL statements through a file data source and ACT! databases have no ODBC source. So, the challenge is how, within a report, does one output the NAME field from the Contact table?

The only solution I can think of without knowning how to emulate the above SQL solution is to do 2 queries for the report. In the first query, dump the output to a 2 dimensional array. In the second query, use a formula field to lookup the "name" that is equal to the Unique Id in the Record Manager field.

So the question becomes - does anyone out there know how to output a field lookup in the midst of a report in Crystal without SQL/ODBC?

 

[mhelenek]

Hi again,

Sorry to confuse the original issue by using different field names. I also want to correct the Contact.Name I used above - the correct field name is Contact.Contact.

The same concept applies to "Scheduled For" - in order to produce a meaningful result for this field, you need to find out which record in the Contact table the activity pertains to by comparing the data in {Activity.Scheduled For} to the {Contact.Unique Id} field and outputting the field from that record that makes sense for your situation (usually Contact.Contact).

Does anyone know for sure if a subreport's global variables are accessible to the main report's scripts? Or, does anyone know if it's possible to do a separate query within a report? What we need here is a subselect.

Thoughts anyone?

 

[FoxGolfer]

By definition, global variable are useable by any formula within a report; SHARED variables are what you should use in a subreport; that's the usage for which they were designed. They must be declared and defined like any variable.
I'm not sure about the subselect; I'd have to experiment with that.

 

[mhelenek]

Hi all,

After some experimentation, I've discovered there are several solutions to resolving Unique Id's. The easiest (and this works for most situations) is to use the Select Expert to make sure you only get the records you want. Then, use Group By Contact.Contact and your group headers will print out the name instead of the binary id. If you intend to group by a field other than the name of the salesperson, simply replace Contact.Contact with your preferred field.

The second, for those who need to perform look-ups on names (record managers, sales contacts, etc.), is to write your own 2 dimensional array. I didn't verify that Crystal allows multi-dimensional arrays, but you can simply write 2 arrays and cross-reference them. Here's the code to do so (insert this into a Formula Field object):

Shared StringVar Array RecordManagers;
Shared StringVar Array UniqueIds;

Redim Preserve RecordManagers [RecordNumber];
Redim Preserve UniqueIds [RecordNumber];

UniqueIds [RecordNumber] := {Contact.Unique Id};

RecordManagers[RecordNumber] := {Contact.Contact};
RecordManagers[RecordNumber];

NOTE: This code resolves the Record Manager to a Contact name. To resolve another field, like "Scheduled For" simply change the names around and make sure your select links your tables properly (i.e. only returns the records you want).

Within a subreport, you can cross reference your results on a record by record basis with the arrays above by saying:

If (current-table.field) = UniqueIds[RecordNumber] then RecordManagers[RecordNumber] else "No matching record found"

Remember that a formula is somewhat like a function and outputs the last line like a return value.

Good luck and hope that helps!
-Matt

 

[Storyteller]

Hello All,
I was wondering if someone could describe the type of report they are wanting to create?

In particular what information do you want to see in the details section? For example: Activity Date, Activity Time, Activity Duration, Activity Type, Regarding, etc.

I would assume that you want to group by Record Manager?, Contact?

I have some time to fiddle with a report and if you can give me some specific requests I can work them into my report and I'll post the results.

Regards,
Michael

 

[FoxGolfer]

Michael,
The types of reports I need are essential summary type reports for a specified period; usually a month but could be any period.
Summarizing (count of each and % of total) the number of calls by record manager; summarizing a udf (same statistical calculation).
I'm afraid that I won't be able to produce one of the reports I need, summarizing by a keyword located in the "regarding" field, but you can try that if you have time.
TIA,
Tom

 

[Storyteller]

Hello All,
I have been working on the reports and for some reason I am having a problem with repeating records in the details section. Once I have it figured out the rest is relatively easy ;-)

I'll keep everyone informed when I get this one figured out.

Regards,
Michael