Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
clange
Posts: 2
Country: United States
Message 11 of 39 (13,344 Views)

Re: Export Notes from ACT to excel

Hi iseekyoda! How did you manage to get the unique ids? I used the OLEDB technique to get the company and notes list and I dont get the unique IDs.. I also get an RTF format for the notes.

Please use plain text.
Platinum Elite Contributor
GLComputing
Posts: 13,888
Country: Australia
Message 12 of 39 (13,349 Views)

Re: Export Notes from ACT to excel

For the OLE/DB Provider, each View contains the key column(s) required to JOIN with other relatable Views (i.e. "Contact CONTACTID" can join to "Contact History CONTACTID").
 
The general naming pattern for the Primary Keys [Table]ID, so HISTORYID for TBL_HISTORY, NOTEID for TBL_NOTE, etc.
 
Some tables have composite natural keys such as CONTACTID, HISTORYID for the TBL_CONTACT_HISTORY table, and all keys are of GUID type
 
With ACT! 2009 Premium version, you also have the ActReader for direct ODBC SQL access of the raw tables.
 
Also in ACT! 2009, look in the ACT! Program Files folder for ACTDataDictionaryDocument.rtf - very useful doc.
Please use plain text.
New Member
clange
Posts: 2
Country: United States
Message 13 of 39 (13,715 Views)

Re: Export Notes from ACT to excel

I pointed to VRP_CONTACT_NOTE and the data structure I got had no CONTACTID.

 

In addition, I also tried to get data from VRP_CONTACT and ContactID wasnt imported into my worksheet.

 

Unfortunately, we are not using ACT9...

Please use plain text.
Platinum Elite Contributor
GLComputing
Posts: 13,888
Country: Australia
Message 14 of 39 (13,745 Views)

Re: Export Notes from ACT to excel

Please use plain text.
New Member
explorer70
Posts: 2
Country: United States
Message 15 of 39 (13,655 Views)

Re: Export Notes from ACT to excel

Hi everybody,

 

I can't get the record IDs either. I'm using ACT! 2009 (v11.1.183.0, Trial version) and Excel 2007.

Could this be a limitation of the ACT trial version?

 

Please use plain text.
Platinum Elite Contributor
GLComputing
Posts: 13,888
Country: Australia
Message 16 of 39 (13,555 Views)

Re: Export Notes from ACT to excel

[ Edited ]

There are no limiations in the trial version except time.

 

Presonally, I prefer itExport as the easiest way to export from ACT!, but for another method (not sure if this needs 11.1 Premium), you might try the ActReader utility and this data map:

http://blog.glcomputing.com.au/2009/04/getting-table-structure-and-links-from.html

Message Edited by GLComputing on 04-20-2009 06:14 PM
Please use plain text.
New Member
sapnaoswal
Posts: 6
Country: India
Message 17 of 39 (12,709 Views)

Re: Export Notes from ACT to excel

I downloaded 'itExport' but it allows only 10 records export. It pops-up saying 'This version is not registered. Only 10 records will be exported'.

I am using ACT trial version 'ACT! by Sage Premium 2009 (11.0) Version 11.1.183.0, Hotfix 1' . Is it because I am using trial version the tool does not allow export more than 10 records?

Please use plain text.
Platinum Elite Contributor
GLComputing
Posts: 13,888
Country: Australia
Message 18 of 39 (12,682 Views)
Please use plain text.
CT
Astute Commentator
CT
Posts: 2
Country: Australia
Message 19 of 39 (12,336 Views)

Re: Export Notes from ACT to excel

Hi all

 

Time to weigh into this thread.

 

I have ACT! 2007 (Version 9.0.1.162, Hotfix 1), and it has served my purposes for many years, and prior to that ACT! 2000.

 

Just recently I have a need to export contact records and contact notes to ExcelLike so many others have found, the views that boast "open access to contact notes in a flat file format" is lipservice to the actual requirement - that is linking the notes with the contact.

 

From Excel, using the OLE/DB Reporting Provider offers views of ACT! data, but without any contact key/IDAll of the links posted to this thread boldly state that access is freely available and unfettered, then offer links to pages which do not actually solve the problem ... at least without expecting a user to spend money on some external software (or being limited to just 10 records).

 

I can't understand why ACT! would publish the OLE/DB Reporting Provider without keys to allow linkage of the contact view with the contact notes view.

 

 

Here is a dump of the VRP_CONTACT_NOTE view field names with some sample data

Field Name                Example Data
NOTE Created On 13/07/2009 6:16
NOTE Edited On 13/07/2009 7:44
NOTE Last Edited By Colin
NOTE Note "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\f0\fs16 Not in the office today!\par
}
"
NOTE Note Date 13/07/2009 6:15
NOTE NOTETYPEID 100
NOTE Private Note 0
NOTE Record Creator Colin
NOTE Record Manager Colin
NOTE TYPES Active TRUE
NOTE TYPES Created On 28/03/2007 16:08
NOTE TYPES Custom FALSE
NOTE TYPES Description A General Note
NOTE TYPES Edited On 
NOTE TYPES Name Note
NOTETYPE NOTETYPEID 100

 

 

 

And here is the VRP_CONTACT view field names

CONTACT Address 1
CONTACT Address 2
CONTACT Address 3
CONTACT Alternate Extension
CONTACT Alternate Phone
CONTACT Birth Date
CONTACT City
CONTACT Company
CONTACT Contact
CONTACT Country
CONTACT Create Date
CONTACT Department
CONTACT Edit Date
CONTACT E-mail
CONTACT Extension
CONTACT Fax Extension
CONTACT Fax Phone
CONTACT First Name
CONTACT Home Address 1
CONTACT Home Address 2
CONTACT Home Address 3
CONTACT Home City
CONTACT Home Country
CONTACT Home Extension
CONTACT Home Phone
CONTACT Home State
CONTACT Home ZIP Code
CONTACT ID/Status
CONTACT Is Imported
CONTACT Last Attempt
CONTACT Last Edited By
CONTACT Last E-mail
CONTACT Last Meeting
CONTACT Last Name
CONTACT Last Reach
CONTACT Last Results
CONTACT Letter Date
CONTACT Messenger ID
CONTACT Middle Name
CONTACT Mobile Extension
CONTACT Mobile Phone
CONTACT Name Prefix
CONTACT Name Suffix
CONTACT Pager Extension
CONTACT Pager Phone
CONTACT Personal E-mail
CONTACT Phone
CONTACT Private Contact
CONTACT Record Creator
CONTACT Record Manager
CONTACT Referred By
CONTACT Salutation
CONTACT Spouse
CONTACT State
CONTACT Title
CONTACT User 1
CONTACT User 10
CONTACT User 2
CONTACT User 3
CONTACT User 4
CONTACT User 5
CONTACT User 6
CONTACT User 7
CONTACT User 8
CONTACT User 9
CONTACT Web Site
CONTACT ZIP Code
[Note: do not get confused between the field "CONTACT ID/Status" which designates customer, partner, etc etc, and a database ID code that uniquely identifies the contact]

 

There is no linkage available at all between these 2 viewsI figure that there is a chance that I am missing something basic here, but I have spend probably about 10 hours trying every last technique of ODBC and OLE within my knowledge base, but cannot resolve itThe SDK is beyond my skill setMike Lazarus, in your post of 9th April I should expect to see CONTACT ID, but it is not hereWhat might I be doing wrong?

 

I recognise that some fields might be too long for Excel.

I recognise that I will have a 1:many relationship to deal with.

 

All of this can be handled by some simple Excel cell formulasI just want to get the notes out linked to the contact to which they pertain.

 

The closest acknowledgement is from iseekyoda2 who claims that he/she couldn't see the keys, but one day later (Feb 22nd) couldiseekyoda2...please tell us how?

 

Thanks for any insights....

Please use plain text.
Platinum Elite Contributor
GLComputing
Posts: 13,888
Country: Australia
Message 20 of 39 (12,246 Views)

Re: Export Notes from ACT to excel

ODBC is usually only available with the Premium version - via ACTReader

 

But, you might have a look at the files from this ACT! Knowledge Base article -

http://kb.sagesoftwareonline.com/cgi-bin/sagesoftwareonline.cfg/php/enduser/std_adp.php?p_faqid=2292...

 

I haven't played with OLEDB in ACT! 2007 for a while, so not sure if those fields were somehow missing in the views?

Please use plain text.