Community
Showing results for 
Search instead for 
Do you mean 
Reply

Relationship Report

Bronze Contributor
Posts: 1,394
Country: USA

Relationship Report

Has anyone written a relationship report that they would be willing to share?  I'm thinking of a report that would have the contact's basic information and then a list of all the relationships' basic informatin.

John Purdy
ACT! Premium 2016 (V. 18)
Dell XPS Ultrabook with 4GB, Win 10 Pro, & Office 365, 32bit
Copper Super Contributor
Posts: 478
Country: Australia

Re: Relationship Report

As i understand it the relationship data can not be put into a report yet... could be wrong but that was the reply i got about 6 months ago


Jason Dawson Financial Planning
Brisbane | Australia - JDFP Home Page

Act Ver - Act! Premium Version 17.1.169.0, Hot Fix 3
OS - Windows 7 (stand alone machine)
MS Office - Enterprise 2007
Phone - Samsung Galaxy s6
Silver Contributor
Posts: 1,813
Country: USA

Re: Relationship Report

There is nothing in the aCT report writer, and the OLEDB Providers both 1 and 2 didn't have anything relating to the relationship tab.  I"ve not Tried ACT REader

Test Drive Act! and Quickbooks over Citrix http://GoTryAct.com

Jon Klubnik| ActTrainer.com
(866)710 4228

ACT! Certified Consultant / ACT! Premier Trainer / ACT! Hosting Provider

Bronze Contributor
Posts: 955
Country: Australia

Re: Relationship Report

I believe this could be done using either the ACTreader or using Crystal reports and any one of a number of report running tools like Saber

 

Let me know offline if you wish to pursue this further

Paul Buchtmann
pbuchtmann@ozemail.com.au Australia
Australia's First ACT Consultant (1989)

FREE Record History to change default to Call or To-Do for ACT! 2010-2012 versions. Custom versions also available.
Nickel Elite Contributor
Posts: 508
Country: USA

Re: Relationship Report

I have written one using and ODC file, and a query - both using Excel and the ACT! Reader.  I was going to just attach the file, but then realized it stores the password and wouldn't work on your machine.  Here's the basics, email me for more details:

 

1. In Excel, Connection Properties>Definition>Connection String:

 

DRIVER=SQL Server;SERVER=OWNER-PC\ACT7;UID=ACTReader;PWD=zaxscdvf;APP=2007 Microsoft Office system;WSID=OWNER-PC

 2. Command type is SQL

 

3. Command Text:

 

SELECT  C1.COMPANYNAME, C1.FULLNAME, PH.NUMBERDISPLAY AS PHONE,
       (AD.LINE1 + ', ' + AD.LINE2) AS ADDRESS,
       (AD.CITY + ', ' + AD.STATE + ', ' + AD.POSTALCODE) AS "CITY/ST/ZIP",
       C2.FULLNAME RELATIONSHIP ,REL.CONTACT2ROLE AS ROLE
FROM ACT11Demo.dbo.TBL_CONTACT_CONTACT REL,
     ACT11Demo.dbo.TBL_CONTACT C1,
     ACT11Demo.dbo.TBL_CONTACT C2,
     ACT11Demo.dbo.TBL_ADDRESS AD,
     ACT11Demo.dbo.TBL_PHONE PH
WHERE C1.CONTACTID = REL.CONTACTID1 AND
      C2.CONTACTID = REL.CONTACTID2 AND
      AD.CONTACTID = C1.CONTACTID AND
      PH.CONTACTID = C1.CONTACTID AND
      PH.TYPEID = 'C3AE9586-6158-4D0D-9FAF-253CB0152F18' AND
      AD.TYPEID = '31BD07D5-4CB7-4038-9505-D2347AA530A1'
ORDER BY C1.COMPANYNAME, C1.FULLNAME

 

Hope this helps - I've done more as well - like remove duplicate rows and just display relationships in a hierarchical list, email me if you want more... 

 

 

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Nickel Elite Contributor
Posts: 508
Country: USA

Re: Relationship Report

also, that was the ODC file, here's the string for the query:

 

 

Provider=SQLOLEDB.1;Password=********;Persist Security Info=True;User ID=ACTReader;Initial Catalog=rbrDataSolutions;Data Source=OWNER-PC\ACT7;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=OWNER-PC;Use Encryption for Data=False;Tag with column collation when possible=False

 

 

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Relationship Report

[ Edited ]

"Relationship data is exposed in the OLEDB2 Provider as the table Contact_Contact."

 

http://community.act.com/t5/Developer-s-Forum/Relationships-in-the-SDK/m-p/27908/highlight/true#M122...

 

-- Jim Durkin

 

 

Nickel Elite Contributor
Posts: 508
Country: USA

Re: Relationship Report

yep, shoulda said this was for v2009, not the new ones...
Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
New Member
Posts: 2
Country: USA

Re: Relationship Report

This is indeed helpful.

 

How does one connect using a specific database file (.PAD) instead of the ACT11Demo.dbo?  I can't seem to run the script so that it accesses my database....

 

I would also be interested in including the "email" and "sector" fields.

 

Many thanks!

Annie Schmidt

International Peace Institute

New Member
Posts: 2
Country: USA

Re: Relationship Report

Hi,

 

I have successfully accessed the CONTACT_CONTACT table via the ACT Reader Utility, and I have pulled the data into Excel.  However, this isn't very useful to me.  What I want to do is make a subreport for the Contacts report that shows a list of related contacts for each contact.  Is it possible to make this type of "external" query in an ACT report?

 

Thanks--

Michael