07-16-2010 12:48 PM
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.
07-16-2010 03:04 PM
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
07-17-2010 05:16 AM
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
07-17-2010 05:44 PM
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
07-22-2010 03:24 PM
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...
07-22-2010 03:26 PM
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
07-25-2010 07:00 PM - edited 07-25-2010 07:01 PM
"Relationship data is exposed in the OLEDB2 Provider as the table Contact_Contact."
-- Jim Durkin
10-26-2011 09:04 AM
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.
International Peace Institute
11-29-2011 01:08 PM
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?