New to the SDK... How to combine tables? Where are the primary keys?

[Cavall]

Hello...

First day trying to use this...

My purpose is to be able to tap into the data in my ACT database to produce better reporting possibly through MS Access or Crystal, etc.

I can see the tables, but was a bit confused when I looked at the "CONTACT" table...

I was expecting to see all my fields for a particular contact, but that was certainly not the case.

I use a field called "ContactType" to segregate contacts into "Customer", "Employee", etc., but this field is actually in a table called "CUSTOM TABLE 0". 

I do not see anywhere there would be a primary key to link these two tables, nor do I know where to look to understand why some fields are in the "CUSTOM TABLE 0" and some are in the "CONTACT" table, etc.

Does anyone have a guide or any ideas on how I should start learning how to use this to combine these tables of data and produce valued reporting?!?!

Thanks very much!

Cavall

[jason]

Hi Cavall,

Have you tried using the SDK and the Contact object rather than hitting the tables directly?

You could create an ACT! plug-in and do something like the following "brute force" method...

            ContactList allContacts = this.ActApp.ActFramework.Contacts.GetContacts(null, false);      // Get list of all contacts

            foreach (Contact c in allContacts)
            { // For each Contact in the database..
                           .....

            }

HTH,

jason

[Cavall]

Thanks for resonding, Jason.

Most of the things I am doing are pulling data from other sources into Microsoft Access or Crystal and then producing custom reports out of there and setting up automatic macros that will run the reports and email them at a certain time.

It has been a little while since I was deep in the programming, so if there is a way to pull the tables directly and link them in a relational fashion that would be the easiest for me.... I just don't know if that is possible with the SDK or not.

I was told by ACT support people that the SDK would allow me to do this... maybe they don't understand the SDK totally....

Got any ideas or suggestions?

Thanks

[Carlton_Jones]

Cavall,

In response to your original question:

When enough custom fields are created the table ‘fills up’ and the ACT! program creates ‘Overflow’ tables to handle the additional fields. I believe this is why some of your custom fields are listed in the TBL_CONTACT table and others in CUSTOM TABLE 0.

Follow up: 

I think what Jason was referring to was the idea that rather than attempting to pull these entities on a SQL Table level. To retrieve the information you need at the SDK level through Contact method calls (programmatically).

I could be that you need the ActReader or ActPassword utilities that give you access to the Reader account in SQL or the ‘sa’ password for SQL respectively.

These can be purchased through Corporate Sales. If you'd like more information they can be reached at 866-421-7752.

Another option that you’d probably want to look at is the OLE DB provider that is available ‘out of the box’ with ACT!.

This would be a free option that might be able to retrieve the information you need.

This is our KnowledgeBase Article that details importing some fields to Excel - HERE

Ultimately, an ACC who specializes in ACT! reporting might be your best option.

Message Edited by Carlton_Jones on 02-21-2008 12:25 PM