Code Focused

Build a More Powerful CRM System with Visual Basic .NET Extensions

Accessing a customer relationship management system with a custom .NET application can add important functionality.

• By Joe Kunk
• 06/30/2012

My college-age daughter is dipping her toes into the world of entrepreneurship. During her summer break, she'll be opening and managing a direct sales office for Cutco kitchen products. To help ensure she has a successful business experience (and reduce her parents' contributions to her tuition payments), we've discussed the need for an effective customer relationship management (CRM) system. A CRM is an essential requirement for any business, particularly for ones heavily dependent on smaller sales across many new customers, such as hers.

Sage ACT! 2012 Pro was chosen for its ability to be installed on a laptop independent of any other software or hardware other than an Internet connection. It's a commercial CRM application with almost 3 million customers. At its core, ACT! manages contacts, dynamic groups, companies, calendars, task lists, sales opportunities and reports.

In this article, I'll introduce Sage ACT! Pro 2012, examine its technical architecture for extensions using the ACT! 2012 .NET SDK, and provide Visual Basic code samples for accessing and updating ACT! from external .NET applications via the ACT! Framework.

A Look at ACT!
ACT! is a rich client application written in Microsoft .NET for the Microsoft .NET Framework 3.5. It installs SQL Server 2008 R2 Express as its database. If you don't have a copy of ACT! 2012 Pro, it's available for a 30-day trial here. While a full discussion of the features of ACT! is outside the scope of this article, I'll present an overview of the Contacts screen to give a feel for the product's capabilities. (Additional screenshots are included in the code download.)

Figure 1 shows the Contacts screen. One of the strengths of ACT! is the significant amount of information available on each of the primary screens of Contacts, Groups, Companies, Calendar, Task List, Opportunities and Dashboard. Contacts can be navigated one at a time with full detail, as shown in Figure 1, or by a List View. Companies, Groups and Opportunities also have a List View available.

[Click on image for larger view.]

Figure 1. The ACT! Contacts screen, with the sample database, shows that contacts can be navigated one at a time with full detail, or by a List View.

Besides the standard contact information, this screen displays multiple tabs that contain considerable information about the contact. The tabs and their uses are shown in Table 1.

The Contacts Lookup search box in the upper-left of Figure 1 allows a contact record to be quickly located. With the large amount of information available for a properly maintained contact record, the ACT! user is able to "remember" all past conversations and touch points with the contact. This promotes a sense of familiarity, attention to detail and personal relationship when interacting with the contact, even for an unexpected phone call. Other features include Lookups, duplicate contact detection, e-mail marketing, mail merge, Outlook integration, Google Apps integration, Sage accounting integration, fax integration, database synchronization, data import and export, annual events, activity series, smart tasks, reports and dashboards.

Customizations and Technical Architecture
The ACT! user has a number of tools for customizing the ACT! screens, reports and documents within the application without requiring any programming skills. The Tools/Define Fields menu option provides a means to add custom fields, set field security, update dropdown list values, manage product lists, and set process lists including sales pipeline stages. The Tools/Define Layouts menu option provides a limited forms designer for the Contacts, Groups, Companies and Opportunities screens, including the ability to modify the contents of the tabs. The Tools/Design Dashboards menu option provides a means to customize the ACT! Default Dashboard display view. Then there are the standard menu and toolbar customizations you normally expect of a modern application. All of these customizations are available to any ACT! user with sufficient rights.

ACT! has a three-tier architecture: presentation, business logic and data. The presentation layer (the application) is represented by the ACT.UI assembly (the application). It can be extended by plug-ins, custom controls and custom tabs. Plug-ins load directly into the ACT! application context of the current user, and have full access to framework and application functionality within the limits of the current user permissions. Custom controls extend the designable views for Contacts, Groups and Companies.

The business-logic layer is represented by the ACT.Framework assembly (the framework). It provides access to core functionality for data, data exchange, database creation and maintenance, database schema metadata and modifications, security and synchronization. It provides access to the primary entities of Contacts, Groups, Companies and Opportunities, plus the extended data items of notes, histories, activities and documents.

The data layer is represented by the SQL Server 2008 R2 Express database, installed as the "(local)\ACT7" instance.

OLE DB Provider for Direct Data Access
Sage ACT! provides an OLE DB provider for direct read-only access to the ACT! database without going through the ACT framework. The OLE DB provider is useful for reporting using SAP Crystal Reports for Visual Studio 2010 or any other reporting tool capable of using OLE DB as a data source. Crystal Reports is a free add-on for Visual Studio 2010 Professional, Premium and Ultimate editions. Note that it's not installed with Visual Studio 2010, and it's approximately a 300MB download. No cost or registration is needed, but be sure to allocate time for the initial download and installation.

The OLE DB provider can also be used in an application using an OleDbDataReader and OleDBCommand to query the database. The following connection string worked correctly for me; change the relevant portions based on

your configuration. The Data Source is the SQL instance into which the ACT! database was installed. The ACT! user name and password is supplied, and any queries run within that user's security context:

Provider=ACTOLEDB2.1; Data Source=(local)\ACT7; User Id=Chris Huffman; 
Password=""; Initial Catalog=ACT2012Demo; Persist Security Info=True;

The OLE DB provider does not expose all tables and views in the ACT! database; it exposes only certain views. The following views were available under the Chris Huffman administrator login when I tested it:

ACCESSOR, ACT_COLUMNS, ACT_DATABASE_CONFIG, ACT_FOREIGN_KEYS, ACT_LOCALSTRING, 
ACT_PRIMARY_KEYS, ACT_TABLES, ACTIVITY, ACTIVITY_TYPE, COMPANY, 
COMPANY_ACTIVITY, COMPANY_CONTACT, COMPANY_HISTORY, COMPANY_NOTE, 
COMPANY_OPPORTUNITY, CONTACT, CONTACT_ACTIVITY, CONTACT_CONTACT, 
CONTACT_HISTORY, CONTACT_NOTE, CONTACT_OPPORTUNITY, GROUP_ACTIVITY, 
GROUP_CONTACT, GROUP_HISTORY, GROUP_NOTE, GROUP_OPPORTUNITY, HISTORY, 
HISTORY_TYPE, LOGON_HISTORY, NOTE, OPPORTUNITY, OPPORTUNITY_ACTIVITY, 
OPPORTUNITY_HISTORY, OPPORTUNITY_NOTE, OPPORTUNITY_PRODUCTSERVICE,PICKLIST, 
PICKLIST_ITEM, PROCESS, PRODUCT, PRODUCT_SERVICE, RESOURCE, 
SECONDARY_CONTACT, STAGE, SYNC_DATABASE, SYNC_DBMAP_INFORMATION, 
SYNC_DBMAP_INFORMATION_HISTORY, SYNC_LOG_ADD, SYNC_LOG_DELETE, 
SYNC_LOG_EXCEPTION, SYNC_LOG_FILE, SYNC_LOG_UPDATE, SYNC_SUBSCRIPTION, 
SYNCSET, SYNCSET_ACCESSOR, TEAM, TEAM_USER

Direct Access to the Database
Full sysadmin access to the database instance is available when running integrated security with the account that installed the instance. This can be very useful in some cases. I created a new SQL Server user named ACTDataReader with datareader and denydata¬writer permissions to the ACT2012Demo database, which was installed alongside the ACT! application. Any edits to the ACT! databases should be made through the ACT! application or the framework to avoid data corruption by ensuring all business rules are properly enforced. If you choose to allow updates via a custom login, be sure to make a full database backup before making any changes.

I used the following connection string for accessing the database directly with LINQ-to-SQL:

Data Source=(local)\ACT7; Initial Catalog=ACT2012Demo; Persist Security 
Info=True; User ID=ACTDataReader; Password=password;

I used the SQLMetal command to generate the DataContext for LINQ-to-SQL:

C:\Program Files\Microsoft Visual Studio 10.0\VC>   
sqlmetal /server:(local)\ACT7  /database:ACT2012Demo /views /functions    
/sprocs /code:C:\Article\ACT2012DemoDB.vb   
/provider:SQL2008>C:\Article\ACT2012Demo.txt

Using this connection string and DataContext, a Windows Forms data grid can be filled with the contents of the TBL_ATTACHMENTS table with just four lines of code:

Dim dataContext As New ACT2012Demo(GetConnectionString) Dim attachments = (From item In dataContext.TBL_ATTACHMENT Select item Order By item.DISPLAYNAME).ToList() Me.DataGridView1.DataSource = attachments

Consuming the ACT! Framework
The framework can be accessed outside the ACT! application by console applications, Windows services, Web services and so on. Trusted devices can also synchronize with the database via the framework.

To demonstrate the use of the framework from a .NET application, I've created a single Windows Forms application that demonstrates four different capabilities, as shown in Figure 2. The full Visual Studio 2010 solution is available in the code download.

[Click on image for larger view.]

Figure 2. A Windows Forms application that demonstrates four different capabilities.

To make sure the needed ACT! assemblies are available for reference from a consistent place for each application you write, I recommend you create a folder named Assemblies in the root of your ACT! application development folder path, and include the following assemblies:

Act.Framework.ComponentModel.Core, Act.Framework, Act.Shared.Collections, Act.Shared.ComponentModel, Act.Shared.Diagnostics, Act.UI.Core and Act.UI.

For many applications, you'll need to obtain a list of fields in the ACT! database. Because users can create custom fields, you should generate a fresh list before each application is coded. To do so, run the ActDiag.exe installed with ACT!, found at C:\Program Files\ACT\Act for Windows. Figure 3 shows the ActDiag utility with the menu items for producing a database structure report. Choose the Field List to CSV file option and open it in Excel. A copy of this report for the ACT2012Demo database is included in the download for this article. Related to this, sample code was recently posted to the ACT! Community Web site that demonstrates Visual Basic code to return the real field name (ContactFieldDescriptor) from the display name.

[Click on image for larger view.]

Figure 3. The ActDiag.exe utility with the menu items for producing a database structure report.

The application has the following imports (the Dim statements represent items declared at the form level, so they're available to all four form options):

Imports Act.Framework.ComponentModel
Imports Act.Shared.Collections

Dim framework As Act.Framework.ActFramework = New Act.Framework.ActFramework
Dim ACTUserName As String = "Chris Huffman"
Dim ACTUserPassword As String = ""
Dim PADFile As String =    
   "C:\Users\Public\Documents\ACT\ACT Data\Databases\ACT2012Demo.PAD"

The following code displays the PickList dropdown contents for the City field. Notice that the field name of BUSINESS_CITY is used. The ActDiag Field List is how I learned that City was an alias for the field BUSINESS_CITY. It's useful to understand that each field has three names: alias, display name and real name. The alias name coincides with the name in the OLE DB. The display name is its name within the application, and the real name is the database column name:

framework.LogOn(PADFile, ACTUserName, ACTUserPassword)
Dim cityField =    framework.Contacts.GetFieldDescriptor( _    
name:="TBL_CONTACT.BUSINESS_CITY", isReal:=True)
Dim pickList = framework.PickLists.GetPickList(cityField)
Me.DataGridView1.DataSource = pickList.Items
framework.LogOff()

Binding an ACT! framework ContactList object directly to a data grid will not show the contact's fields because the fields are defined by metadata. To have a list that can be bound, the application defines the following class to be filled from the ACT! ContactList:

Public Class ContactIdentity
  Public Property CompanyName As String
  Public Property FirstName As String
  Public Property LastName As String
  Public Property Phone As String
  Public Property MobilePhone As String
  Public Property City As String
  Public Property State As String
End Class

The code shown in Listing 1 logs into the ACT2012Demo database. It also creates a sort criteria for company name ascending, gets all the contacts visible to Chris Huffman sorted by company name ascending, and displays the ContactIdentity information for each contact in the grid.

Creating a Complex Lookup
A Lookup is a process in ACT! where a set of records are filtered by one or more criteria. A Lookup can be saved as a group for later use. The usefulness of Lookups is that they can be repetitively refined until you have exactly the set of records that you want, and then operations can be performed against that set of records such as data updates, writing mail-merge letters, sending e-mails and so on.

Listing 2, found in the code download, illustrates the creation of a Lookup on State = 'CA,' and then a further refinement of that Lookup for City = 'Los Angeles.' To illustrate updating data in a contact record based on data value, the User10 field is updated with the current date and time for only those records in the Lookup where User10 field is blank, and Chris Huffman is the Record Owner.

In ACT!, the Record Owner is the person responsible for maintaining the accuracy of the record's information. By default, it's the person who created the record, but it can be changed to any defined ACT! user in the database. This is typically done to distribute the responsibility for updating the information among multiple users after a large data import, but it can be for any other valid business reason. Users other than the Record Owner should not change the data, so the check for Record Owner more realistically models how data would be changed in the ACT! application. Prior to running the code in Listing 2, I changed the record access to public, and the Record Owner to someone other than Chris Huffman for one of the records to show that record would not be modified.

In Listing 2, there are several points of interest. You see an array of Criteria objects initialized in-line with a single Criteria object. The State Lookup replaces any existing Lookup and is done without any sort because it will be sorted when further refined by city. The Lookup is narrowed by new criteria that specify the city as Los Angeles. A sort is built upon the combination of the contact's last name and first name, both ascending. The Lookup is narrowed by the City criteria and sorted. A loop is done to transfer the data to the List(Of ContactIdentity) for display in the data grid. Each iteration of the loop retrieves the current value of the User10 field to verify it's blank, and also checks the IsRequestingUser¬RecordManager property to ensure that the logged in user is the Record Manager for that contact before changing it. After the loop completes, refreshing the screen in ACT! will show the changed data in the User10 field. Running it again will not update the User10 field, unless it's cleared first.

ACT! is a powerful CRM tool that can serve a single user on a laptop or multiple users with a central server. In this article, I've demonstrated how to access the ACT! database information both independently and indirectly with the SDK assemblies from an external application. I've shown a common scenario of defining a complex sorted Lookup and conditionally changing field values within that Lookup. In an upcoming column, I'll show you how to add custom functionality to ACT! in the UI by using plug-ins.

If you're interested in purchasing any Cutco knives or cookware, let me know.

• Get Code Download