An Act! CRM data base contains lots of information and getting it out is frequently the problem you have. So we tend to look at using different reporting tools, or Microsoft Excel, for the extracting or reporting task. And the way the data is stored in Act! can sometimes make extracting the information the way you want a bit more difficult than it should be. But if you understand the data base structure and your SQL Select statements you can do it easily.
Here is an example. We just want a table of people names and their main and cell phone numbers, like this:
FULLNAME PHONE MOBILE Chris Huffman 212-555-2485 212-555-4784
So you think you can easily query it like this:
Select TBL_CONTACT.FULLNAME, TBL_PHONE.TYPEID, TBL_PHONE.NUMBERVALUE From TBL_CONTACT Inner Join TBL_PHONE On TBL_PHONE.CONTACTID = TBL_CONTACT.CONTACTID Where TBL_CONTACT.FULLNAME = 'Chris Huffman'
But this statement gives you
Chris Huffman C3AE9586-6158-4D0D-9FAF-253CB0152F18 2125552485 Chris Huffman 2870C5A5-7B3B-49DE-AD57-763EA3BF41D0 2125552486 Chris Huffman 2B7C5F46-A48A-4C6F-BB9D-C672DAB0DC4C 5165555455 Chris Huffman 511DF006-2CDB-40B5-8D1A-B313C873DD1C 2125554784
And what are those strange GUIDs? Well, here is the answer.
The GUIDs are the internal designation of a type of phone number, like Business or Cell. They are stored in the Act! TBL_PICKLIST and TBL_PICKLISTITEMS tables. You can get a list of the Phone Type IDs using this query:
SELECT PLI.[PICKLISTITEMID] ,PLI.[PICKLISTID] ,PLI.[NAME] ,PLI.[DESCRIPTION] ,PLI.[ISCUSTOM] FROM [TBL_PICKLISTITEM] PLI LEFT JOIN TBL_PICKLIST ON TBL_PICKLIST.PICKLISTID=PLI.PICKLISTID WHERE TBL_PICKLIST.NAME='Phone Types'
Which gives you
PICKLISTITEMID PICKLISTID NAME DESCRIPTION ISCUSTOM C7953F28-8CD8-4184-A4AD-D2CCBCDB855B E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Alternate An Alternate Phone Number 0 C3AE9586-6158-4D0D-9FAF-253CB0152F18 E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Business The phone number at the Contacts Work or Business 0 2870C5A5-7B3B-49DE-AD57-763EA3BF41D0 E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Fax The phone number for the Fax machine 0 2B7C5F46-A48A-4C6F-BB9D-C672DAB0DC4C E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Home The phone number at Home 0 511DF006-2CDB-40B5-8D1A-B313C873DD1C E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Mobile The mobile/cellular phone number 0 00320990-7438-4B5A-BA53-D4E5C6E2A135 E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Pager The Pager phone number 0 17057633-4B31-4AE8-BE33-ED27C7FD1B12 E3613AB4-5D70-4D89-9F71-5E4FC48C1F41 Toll-Free The Toll-Free phone number 0
So now all we need to do is ask for the specific phone numbers we want. Like this.
Select TBL_CONTACT.FULLNAME, P1.NUMBERDISPLAY AS 'Phone', P2.NUMBERDISPLAY AS 'Mobile' From TBL_CONTACT Left Join TBL_PHONE P1 On P1.CONTACTID = TBL_CONTACT.CONTACTID AND P1.TYPEID='C3AE9586-6158-4D0D-9FAF-253CB0152F18' LEFT JOIN TBL_PHONE P2 ON P2.CONTACTID = TBL_CONTACT.CONTACTID AND P2.TYPEID='511DF006-2CDB-40B5-8D1A-B313C873DD1C' Where TBL_CONTACT.FULLNAME = 'Chris Huffman'
Note that we are joining to the Phone table (TBL_PHONE) twice, once for the Business phone number and once for the Cell phone number. And because we are referencing TBL_PHONE twice we need to assign each reference a unique Table Alias (the P1 and P2 names) so that we (and SQL Server) can distinguish between them. And also note that we are using LEFT JOIN so that, if the contact in question does not have one of the phone numbers we just get a blank (actually a NULL) rather than having the entire row suppressed. This gives us the desired result shown at the top of this post.
Note that this technique is also required if you are looking to extract Contact names and different addresses from the TBL_ADDRESS table. Just look for the correct address Type IDs in the Picklist tables.