Community
Showing results for 
Search instead for 
Do you mean 
Reply

sync data with SAGE ACT! through database directly

New Member
Posts: 2
Country: USA

sync data with SAGE ACT! through database directly

i have a thrid system which also include customer module.

 

but, my customer select SAGE as their CRM system.

 

till now, i have got the readonly user for SQLserver of ACT DB.

 

so , my question is, i want customer information & project information in SAGE ACT.

 

how can i get them through database using SQL query directly?

 

or, is there any db structure document for SAGE ?

 

i want to know which fields to pull for my system.

 

thanks.

 

 

New Member
Posts: 2
Country: USA

Re: sync data with SAGE ACT! through database directly

[ Edited ]

the detail information is below:

 

1. Client Information

a. Client number

b. Company name

c. Address details for company

d. Other contact details of company �

email address for report sending, email address for invoice sending

 

2. Project information

a. Project number

b. Project type & scope of works

c. Client or site contact comments relating to the job or past events

d. Project site name, property classification

e. Site Contact

f. Address details for site contact

g. Other contact details of site contact �

phone number, special comments, etc

 

3. Past reports for this property

4. Agents details � qualifications, contact details, email address etc.

5. Some projects (in particular the end report) will require a number of dates to be obtained from past reports in order to know when the next activity is due � i.e. many projects repeat annually and an activity conducted on 11 August 2010 needs to be conducted every 12 months. Therefore we and the field agent will need to know this and therefore our system will need to be able to pull this information from ACT and input it into this years project details and reports.

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: sync data with SAGE ACT! through database directly

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: sync data with SAGE ACT! through database directly

[ Edited ]

The ACT! reader and the ACTOLDDB2.1 SQL connections are read only. If you want to import your records into the ACT database you will need to use the SDk and create a plugin. Below I have simplified the code you would call in your plugin:. 

 

 '------------------------------------------------------------------
 ' Create an array to hold all of the lookup criteria.
 ' Create a lookup using myCriteria
 ' SEE BELOW for a break down of these two lines of code
 '------------------------------------------------------------------
 Dim myCriteria() As Criteria = New Criteria() {New Criteria(Act.Framework.Lookups.LogicalOperator.End, (0), (0), Me.HostFramework.Lookups.GetCriteriaColumn(HostFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.CLIENTNUMBER", True)), OperatorEnum.EqualTo, "DURK001234")}
 Dim myContactList As Act.Framework.Contacts.ContactList = Me.HostFramework.Lookups.LookupContactsReplace(myCriteria, True, True).GetContacts(Nothing)

 '------------------------------------------------------------------
 ' If this contact was not found by the "DURK001234" value
 ' the cList count will be zero, else it will be the first position (0)
 '------------------------------------------------------------------
 Dim ContactToUpdate As Act.Framework.Contacts.Contact
 If cList.Count = 0 Then
     '------------------------------------------------------------------
     ' Create a new contact record in the ACT database
     '------------------------------------------------------------------
     ContactToUpdate = Me.HostFramework.Contacts.CreateContact
 Else
     '------------------------------------------------------------------
     ' Use the contact we found in the lookup
     '------------------------------------------------------------------
     ContactToUpdate = cList(0)
 End If

 '------------------------------------------------------------------
 ' Update the fields in the contact record
 '------------------------------------------------------------------
 HostFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.CLIENTNUMBER", True).SetValue(ContactToUpdate, "DURK001234")
 HostFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.City", True).SetValue(ContactToUpdate, "Denville")
 HostFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.State", True).SetValue(ContactToUpdate, "New Jersey")
 HostFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.Zip", True).SetValue(ContactToUpdate, "07834")

 '------------------------------------------------------------------
 ' Save changes to the contact
 '------------------------------------------------------------------
 ContactToUpdate.Update()

 

 

The first two lines of code are the hardest ones in ACT to understand at first pass so I am breaking them down into its smaller components below:

 

 '------------------------------------------------------------------
 ' Get the field descriptor which holds the unique client number
 '------------------------------------------------------------------
 Dim myFieldDescriptor As Act.Framework.Contacts.ContactFieldDescriptor = Nothing
 myFieldDescriptor = HostFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.CLIENTNUMBER", True)

 '------------------------------------------------------------------
 ' Get the column to lookup on
 '------------------------------------------------------------------
 Dim myCriteriaColumn As CriteriaColumn = Me.HostFramework.Lookups.GetCriteriaColumn(FieldDescriptor)

 '------------------------------------------------------------------
 ' Get the value to lookup
 '------------------------------------------------------------------
 Dim ClientNumberValue As String = "DURK001234"

 '------------------------------------------------------------------
 ' Create an array to hold all of the lookup criteria.
 '------------------------------------------------------------------
 Dim myCriteria() As Criteria = New Criteria() {New Criteria(Act.Framework.Lookups.LogicalOperator.End, (0), (0), cColumn1, oOperator1, ClientNumberValue)}

 Dim myLookup As ContactLookup
 Dim myContactList As Act.Framework.Contacts.ContactList

 '------------------------------------------------------------------
 ' Create the Lookup 
 '------------------------------------------------------------------
 myLookup = Me.HostFramework.Lookups.LookupContactsReplace(myCriteria, True, True)

 '------------------------------------------------------------------
 ' Set our contact list to the lookup
 '------------------------------------------------------------------
 myContactList = myLookup.GetContacts(Nothing)

 

 

Hope this helps

-- Jim Durkin