Fast Tip Friday: Expanding Many-to-One Relations, or Turning Rows into Columns, or I want My Report to Look This Way

By dpegen • March 20th, 2015

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.

 

 

 

 

Leave a Comment

« | Home | »

  • Been sent a support request?

    Click here to enter your ticket number: Ticket

  • Our Business

    Our Mission
    Bridging the gap between technology and business processes. Provide quality business consulting, with an emphasis on technology, maintain superior client relationships and enjoy the trust and respect of our clients

  • Us in a nutshell

    Swiftpage
    CRM applications
    Data Conversion
    Custom Programming
    SQL Experts
    Lotus Notes
    Quotewerks
    Stonefield Query
    MS Office Programming
    Business analysis
    Process Workflow
    Social Media and SEO
    Linux, Mainframe
    Been there, done that



  • Facebook Like Us!