Skip to Content

Act! CRM Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started

Act! CRM Version Support

The connector leverages the Act! Web API for bidirectional access to Act! CRM data from Act! Premium on-premises and Act! Premium Cloud. The connector also supports Act! Premium for Desktop, but you need an active subscription to connect.

Establish a Connection

Connect to Act! CRM

You can connect to either Act! CRM or Act! Premium Cloud. Set the following to connect:

  • User: The username used to authenticate to the Act! Database name.
  • Password: The password used to authenticate to the Act! Database name.
  • URL: The URL where the Act! CRM account is hosted. For example: http://serverName/.
  • ActDatabase: The name of the Act! Database name you want to connect to. This is found by going to the About Act! Premium menu of your account, found at the top right of the page, in the ? menu. Use the Database Name in the window that appears.
  • ActCloudName (Act! Premium Cloud only): The handle assigned to the Act! Premium Cloud account. It is found in the browser's address field when opening the online account, in the form https://eup1-iis-04.eu.hosted.act.com/ActCloudName.

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Act! CRM connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Act! CRM and then processes the rest of the query in memory (client-side).

User Defined Views

The Act! CRM connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Activities WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

Act! CRM connector models entities in the Act! CRM Premium APIs as relational Views. The connector leverages the Act! Web API for bidirectional access to Act! CRM data from Act! Premium on-premises and Act! Premium Cloud. The connector also supports Act! Premium for Desktop, but you need an active subscription to connect.

Views

The views are defined in schema files, .rsd files with a simple format that makes them easy to edit. Set the Location property to the path to the folder containing the .rsd files.

Tables

The connector models the data in Act! CRM as a list of tables in a relational database that can be queried using standard SQL statements.

Act! CRM Connector Tables

Name Description
Activities Create, update, delete and query the Activities in your Act! CRM database.
Companies Create, update, delete and query the Companies created in your Act! CRM database.
ContactNotes Query and delete the Notes for a given Act! CRM Contact.
Contacts Create, update, delete and query the Contacts in your Act! CRM database.
GroupContacts Query and delete the Contacts belonging to a Group.
Groups Create, update, delete and query the Groups participating in your Act! CRM database.
Histories Get, Create, Update or Delete all history items.
Notes Create, update, delete and query the Notes in your Act! CRM database.
Opportunities Create, update, delete and query the Opportunities in your Act! CRM database.
OpportunityCompanies Query and delete the Companies related to an Act! CRM opportunity.
OpportunityContacts Query and delete the Contacts associated with an Opportunity.
OpportunityGroups Query and delete the Act! CRM Groups associated with an Opportunity.
OpportunityProducts Update, delete and query the Products associated with an Opportunity.
Products Create, update, delete and query the Products in your Act! CRM database.

Activities

Create, update, delete and query the Activities in your Act! CRM database.

Table Specific Information
Select
  • Id supports the = operator.
  • Subject supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • ActivityPriorityName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ActivityTypeName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentDisplayName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileSize supports the <=, <, =, !=, >, and >= operators.
  • AttachmentFileSizeDisplay supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentLastModified supports the <=, <, =, !=, >, and >= operators.
  • AttachmentPersonal supports the = operator.
  • Details supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EndTime supports the <=, <, =, !=, >, and >= operators.
  • IsCleared supports the = operator.
  • IsPrivate supports the = operator.
  • IsTimeless supports the = operator.
  • Location supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecurSpecFrequency supports the <=, <, =, !=, >, and >= operators.
  • RecurSpecIsEndless supports the = operator.
  • RecurSpecMonth supports the <=, <, =, !=, >, and >= operators.
  • RecurSpecRecurType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecurSpecSeriesEnd supports the <=, <, =, !=, >, and >= operators.
  • RecurSpecSeriesStart supports the <=, <, =, !=, >, and >= operators.
  • StartTime supports the <=, <, =, !=, >, and >= operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM Activities WHERE Location = 'London'

SELECT * FROM Activities WHERE Subject = 'Team meeting'
Insert

To insert a activity, you must specify atleast one contact, start time and end time.

INSERT INTO Activities (ContactsAggregate, StartTime, EndTime) VALUES ('123456', '2020-01-27 10:00:000', '2020-01-31 10:00:00')
Update

Any column where ReadOnly=False can be updated. Specify the Activity ID when updating a group

UPDATE Activities SET ActivityPriorityId = 1, isPrivate = false WHERE ID = '12345'
Delete

Activities can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Activities WHERE ID = '12345'
Columns
Name Type ReadOnly Description
Id [KEY] String True A unique identifier that represents the activity.
ActivityPriorityId Integer False Activity Priority value
ActivityPriorityName String False Displays the priority of the activity.
ActivityTypeId Integer True Displays the ID of the type of the activity.
ActivityTypeName String True Displays the type of the activity.
AttachmentDisplayName String False Gets presentation name of the attachment.
AttachmentFileExtension String False Gets the extension of the Attachment
AttachmentFileName String False Gets a string representing the directory's full path.
AttachmentFileSize Int False Gets the size (bytes) of the attachments.
AttachmentFileSizeDisplay String False Gets the displayable text representation of the attachment
AttachmentFileType String False Gets the registered system file type name of the attachment
AttachmentLastModified Datetime False Gets the last modified date of the attachment
AttachmentPersonal Bool False Indicates whether the attachment is bound for the personal supplemental files or the workgroup supplemental files.
CompaniesAggregate String False List of companies that are associated to this activity.
ContactsAggregate String False List of contacts that are associated to this activity.
Created Datetime True The date and time the activity was created.
Details String False Additional detailed information about the activity.
Edited Datetime True The date and time the activity was last updated.
EndTime Datetime False The time the activity is to end
GroupsAggregate String False List a groups that are associated to this activity.
IsCleared Bool False Indicates that the activity has been cleared.
IsPrivate Bool False Indicates elevated security that only the creator/owner has access to this activity.
IsTimeless Bool False Indicates whether a specifies action is to be completed at a specific time-of-day.
Location String False Describes the physical location that the activity is to take place.
OpportunitiesAggregate String False List a opportunities that are associated to this activity.
RecurSpecDayDayAsInt Int False An integer representation of the day of the week portion of a recurrence pattern.
RecurSpecDayDaysOfWeek String False Representation of the day of the week portion of a recurrence pattern.
RecurSpecDayDayType String False Describing the day-portion of the recurrence pattern.
RecurSpecDayOrdinal String False Describes recurrence in relative terms for certain recurrence patterns (i.e. First, Third, Last) .
RecurSpecDayTypedDay String False Describing the TypedDay (Day, WeekDay or Weekend Day) for the recurrence pattern when DayType is
RecurSpecFrequency Int False Represents how often for the recurrence patterns
RecurSpecIsEndless Bool False Flag denoting whether an end date has been specified for the pattern. Note: If false, the following defaults are used: For Daily and Weekly: 2 years beyond the start date For Monthly and Yearly: June 6, 2073.
RecurSpecMonth Int False Represents the specific month for certain recurrence patterns
RecurSpecRecurType String False Represents a unit of time used to separate recurences (i.e. Daily, Weekly, Monthly, Yearly).
RecurSpecSeriesEnd Datetime False Specifiying the ending time for a given day.
RecurSpecSeriesStart Datetime False Specifiying the starting time for a given day.
ScheduledBy String True The contact who created this activity.
ScheduledFor String False The name of the activity's organizer.
SeriesID String True A unique identifier that represents the activity. The ID that is returned is based on if it is a recurring or not. Recurrent activity: RecurSourceActivityId or Single Occurence activity: ActivityID
StartTime Datetime False The time the activity is to begin
Subject String False A description of the action to be completed.

Companies

Create, update, delete and query the Companies created in your Act! CRM database.

Table Specific Information
Select
  • Id supports the = operator.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Description supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Division supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Fax supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HierarchyLevel supports the <=, <, =, !=, >, and >= operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Industry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NumberOfEmployees supports the <=, <, =, !=, >, and >= operators.
  • Phone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PhoneExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Region supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Revenue supports the <=, <, =, !=, >, and >= operators.
  • ShippingAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • SicCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Territory supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TickerSymbol supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TollFreeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TollFreePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM Companies WHERE Id = '12345'

SELECT * FROM Companies WHERE Name = 'TSS company'
Insert

To insert a company, you need to specify the Company Name

INSERT INTO Companies (Name) VALUES ('New Company')
Update

Any column where ReadOnly=False can be updated. Specify the Company ID when updating a group

UPDATE Companies SET AddressCity = 'Bangalore', AddressCountry = 'India' WHERE ID = '12345'
Delete

Companies can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Companies WHERE ID = '12345'
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique identifier (id) for this company.
Created Datetime True Created date of the company.
Name String False The company's given name.
AddressCity String False An inhabited place of greater size, population, or importance than a town or village.
AddressCountry String False A nation with its own government, occupying a particular territory.
AddressLine1 String False Primary street address
AddressLine2 String False Secondary street address
AddressLine3 String False Miscellaneous street address
AddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState String False A nation or territory considered as an organized political community under one government.
BillingAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
BillingAddressCountry String False A nation with its own government, occupying a particular territory.
BillingAddressLine1 String False Primary street address
BillingAddressLine2 String False Secondary street address
BillingAddressLine3 String False Miscellaneous street address
BillingAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
BillingAddressState String False A nation or territory considered as an organized political community under one government.
Description String False A brief written representation or account of this company.
Division String False The name of a smaller part of a parent company tasked with overseeing different types of products or services than those offered by the parent company.
Edited Datetime True Edited data of the company.
EditedBy String True User who edited the company record.
Fax String False The company's fax phone number.
FaxExtension String False The company's fax phone number extension.
HierarchyLevel Int False The positional level of where this company lays within a company hierarchy.
IdStatus String False The relative social or professional position of an individual within a group or organization.
Industry String False Economic activity concerned with the processing of raw materials and manufacture of goods in factories.
NumberOfEmployees Int False The total number of employees that this company operates with.
ParentID String True The unique identifier (id) of the parent company.
Phone String False The company's phone number.
PhoneExtension String False The company's phone number extension.
RecordOwner String True Owner of the company record.
ReferredBy String False Mention or allude to.
Region String False A part of a country, of the world, etc., that is different or separate from other parts in some way.
Revenue Decimal False The amount of money that this company actually receives during a specific period, including discounts and deductions for returned merchandise.
ShippingAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
ShippingAddressCountry String False A nation with its own government, occupying a particular territory.
ShippingAddressLine1 String False Primary street address
ShippingAddressLine2 String False Secondary street address
ShippingAddressLine3 String False Miscellaneous street address
ShippingAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
ShippingAddressState String False A nation or territory considered as an organized political community under one government.
SicCode String False Standard Industrial Classification (SIC) codes are four-digit numerical codes assigned by the U.S. government to business establishments to identify the primary business of the establishment.
Territory String False Geographical area.
TickerSymbol String False An arrangement of characters (usually letters) representing a particular security listed on an exchange or otherwise traded publicly.
TollFreeExtension String False The company's toll free phone number.
TollFreePhone String False The company's toll free phone number.
Website String False The company's website.

ContactNotes

Query and delete the Notes for a given Act! CRM Contact.

Table Specific Information
Select
  • Id supports the = operator.
  • ContactId supports the = operator.
  • ManageUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • CreateUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsPrivate supports the = operator.
  • NoteText supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • DisplayDate supports the <=, <, =, !=, >, and >= operators.
  • AttachmentDisplayName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentLastModified supports the <=, <, =, !=, >, and >= operators.
  • AttachmentPersonal supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ContactNotes WHERE ContactId = '12345'
Delete

ContactNotes can be deleted by providing ContactId and ID (Note Id) and issuing a DELETE statement.

DELETE FROM ContactNotes WHERE ContactId = '12345' AND ID = '56789'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier (id) for a given note.
ContactId [KEY] String False The unique identifier (id) for the contact included in the note.
ManageUserID String False The unique identifier (id) of the user managing the note.
CreateUserID String False The unique identifier (id) of the user who created the note.
IsPrivate Boolean False Indicator if the note is private.
NoteText String False Descriptive text of the note.
NoteTypeID Long False The ID of the type of the note.
DisplayDate Datetime False Display date of the note.
Created Datetime False Datetime of the note creation.
Edited Datetime False Datetime of the last edit of the note.
AttachmentDisplayName String False Display name of the attachment.
AttachmentFileExtension String False File extension of the attachment.
AttachmentFileName String False File name of the attachment.
AttachmentFileSize Long False File size of the attachment.
AttachmentFileSizeDisplay String False Display file size of the attachment.
AttachmentFileType String False Type of the attachment file.
AttachmentLastModified Datetime False Last modified datetime of the attachment.
AttachmentPersonal Boolean False Indicator if the attachment is personal.

Contacts

Create, update, delete and query the Contacts in your Act! CRM database.

Table Specific Information
  • Id supports the = operator.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • FullName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Company supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternateExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternatePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Birthday supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Department supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FirstName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsUser supports the = operator.
  • JobTitle supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastResults supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Latitude supports the <=, <, =, !=, >, and >= operators.
  • Longitude supports the <=, <, =, !=, >, and >= operators.
  • MessengerID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MiddleName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobileExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobilePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NamePrefix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NameSuffix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PersonalEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Salutation supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.
Select

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM Contacts WHERE Id = '12345'

SELECT * FROM Contacts WHERE FullName = 'Full contact name'
Insert

To insert a contact, you need to specify the contact name.

INSERT INTO Contacts (FirstName) VALUES ('Contact First Name')
Update

Any column where ReadOnly=False can be updated. Specify the Contact ID when updating a group

UPDATE Contacts SET Company = 'User Company' Website = 'UserWebsite.com' WHERE ID = '12345'
Delete

Contacts can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Contacts WHERE ID = '12345'
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique identifier (id) for this contact.
Created Datetime True Created date of the contact.
FullName String True The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
Company String False The organization that this contact is associated with.
AltEmailAddress String False A unique electronic address that an individual can be contacted.
AlternateExtension String False The contact's alternate phone number extension.
AlternatePhone String False The contact's alternate phone number.
Birthday String False The anniversary of the day on which a person was born.
BusinessAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry String False A nation with its own government, occupying a particular territory.
BusinessAddressLine1 String False Primary street address
BusinessAddressLine2 String False Secondary street address
BusinessAddressLine3 String False Miscellaneous street address
BusinessAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState String False A nation or territory considered as an organized political community under one government.
BusinessExtension String False The contact's business phone number extension.
BusinessPhone String False The contact's business phone number.
CompanyID String False The unique identifier (companyID) for a given companies.
ContactType String True The type of contact: Contact, Secondary, or User.
Department String False An area of special expertise or responsibility.
Edited Datetime True Edited date of the contact.
EditedBy String True User who edited the contact.
EmailAddress String False A unique electronic address that an individual can be contacted.
FaxExtension String False The contact's fax phone number extension.
FaxPhone String False The contact's fax phone number.
FirstName String False A personal given name.
HomeAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry String False A nation with its own government, occupying a particular territory.
HomeAddressLine1 String False Primary street address
HomeAddressLine2 String False Secondary street address
HomeAddressLine3 String False Miscellaneous street address
HomeAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState String False A nation or territory considered as an organized political community under one government.
HomeExtension String False The contact's home phone extension.
HomePhone String False The contact's home phone number.
IdStatus String False The relative social or professional position of an individual within a group or organization.
IsUser Bool True An indicator if this contact is also a user within the system.
JobTitle String False The title or position this contact holds.
LastName String False The family name or surname.
LastResults String False Identifies last communication or action with a contact.
Latitude Decimal False The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude Decimal False The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID String True An online identifier alias.
MiddleName String False A name between one's first name and surname.
MobileExtension String False The contact's mobile phone extension.
MobilePhone String False The contact's mobile phone number.
NamePrefix String False A patronymic prefix.
NameSuffix String False Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension String False The contact's pager phone number extension
PagerPhone String False The contact's pager phone number.
PersonalEmailAddress String False A unique electronic address that an individual can be contacted.
RecordOwner String True Owner user of the contact.
ReferredBy String False Indiciates how this contact came about.
Salutation String True A gesture or utterance made as a greeting or acknowledgment.
Website String False The contact's web site.

GroupContacts

Query and delete the Contacts belonging to a Group.

Table Specific Information
Select
  • Id supports the = operator.
  • GroupId supports the = operator.
  • FullName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AltEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternateExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternatePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Birthday supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Company supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ContactType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Department supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FirstName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsUser supports the = operator.
  • JobTitle supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastResults supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Latitude supports the <=, <, =, !=, >, and >= operators.
  • Longitude supports the <=, <, =, !=, >, and >= operators.
  • MessengerID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MiddleName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobileExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobilePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NamePrefix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NameSuffix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PersonalEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Salutation supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to FullName and GroupId. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM GroupContacts WHERE GroupId = '12345'

SELECT * FROM GroupContacts WHERE FullName = 'Contact name'
Delete

GroupContacts can be deleted by providing GroupId and ID (Contact Id) and issuing a DELETE statement.

DELETE FROM GroupContacts WHERE GroupId = '12345' AND ID = '56789'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier (id) for this contact.
GroupId [KEY] String False The unique identifier (id) of the group to retrieve contacts from.
Created Datetime False Created date of the group.
FullName String False The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
AltEmailAddress String False A unique electronic address that an individual can be contacted.
AlternateExtension String False The contact's alternate phone number extension.
AlternatePhone String False The contact's alternate phone number.
Birthday String False The anniversary of the day on which a person was born.
BusinessAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry String False A nation with its own government, occupying a particular territory.
BusinessAddressLine1 String False Primary street address
BusinessAddressLine2 String False Secondary street address
BusinessAddressLine3 String False Miscellaneous street address
BusinessAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState String False A nation or territory considered as an organized political community under one government.
BusinessExtension String False The contact's business phone number extension.
BusinessPhone String False The contact's business phone number.
Company String False The organization that this contact is associated with.
CompanyID String False The unique identifier (companyID) for a given companies.
ContactType String False The type of contact: Contact, Secondary, or User.
Department String False An area of special expertise or responsibility.
Edited Datetime False Edited date of the group.
EditedBy String False User who edited the group.
EmailAddress String False A unique electronic address that an individual can be contacted.
FaxExtension String False The contact's fax phone number extension.
FaxPhone String False The contact's fax phone number.
FirstName String False A personal given name.
HomeAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry String False A nation with its own government, occupying a particular territory.
HomeAddressLine1 String False Primary street address
HomeAddressLine2 String False Secondary street address
HomeAddressLine3 String False Miscellaneous street address
HomeAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState String False A nation or territory considered as an organized political community under one government.
HomeExtension String False The contact's home phone extension.
HomePhone String False The contact's home phone number.
IdStatus String False The relative social or professional position of an individual within a group or organization.
IsUser Bool False An indicator if this contact is also a user within the system.
JobTitle String False The title or position this contact holds.
LastName String False The family name or surname.
LastResults String False Identifies last communication or action with a contact.
Latitude Decimal False The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude Decimal False The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID String False An online identifier alias.
MiddleName String False A name between one's first name and surname.
MobileExtension String False The contact's mobile phone extension.
MobilePhone String False The contact's mobile phone number.
NamePrefix String False A patronymic prefix.
NameSuffix String False Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension String False The contact's pager phone number extension
PagerPhone String False The contact's pager phone number.
PersonalEmailAddress String False A unique electronic address that an individual can be contacted.
RecordOwner String False Owner user of the record.
ReferredBy String False Indiciates how this contact came about.
Salutation String False A gesture or utterance made as a greeting or acknowledgment.
Website String False The contact's web site.

Groups

Create, update, delete and query the Groups participating in your Act! CRM database.

Table Specific Information
Select
  • Id supports the = operator.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLatitude supports the <=, <, =, !=, >, and >= operators.
  • AddressLongitude supports the <=, <, =, !=, >, and >= operators.
  • Description supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HierarchyLevel supports the <=, <, =, !=, >, and >= operators.
  • ParentID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HasSubgroups supports the = operator.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordManager supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsPrivate supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following query is processed server side:

SELECT * FROM Groups WHERE Name = 'Group name'
Insert

To insert a group, you need to specify the Group Name

INSERT INTO Groups (Name, Description) VALUES ('Group Name', 'This is sample group')
Update

Any column where ReadOnly=False can be updated. Specify the Group ID when updating a group

UPDATE Groups SET isPrivate = false, AddressCity = 'Group City', AddressCountry = 'Group Country' WHERE ID = '12345'
Delete

Groups can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Groups WHERE ID = '12345'
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique identifier (id) for a group.
Created Datetime True A timestamp when this group was created
Name String False The name of the group.
AddressCity String False An inhabited place of greater size, population, or importance than a town or village.
AddressCountry String False A nation with its own government, occupying a particular territory.
AddressLine1 String False Primary street address
AddressLine2 String False Secondary street address
AddressLine3 String False Miscellaneous street address
AddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState String False A nation or territory considered as an organized political community under one government.
AddressLatitude Decimal False The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
AddressLongitude Decimal False The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
Description String False A brief written respresentation of a group.
Edited Datetime True Edited date of the group.
EditedBy String True User who edited the group.
HierarchyLevel Int False The level this group appears in its hierarchy.
ParentID String True The group's parent entity.
HasSubgroups Bool False An indicator if this group contains subgroups.
RecordOwner String False Owner user of the record.
RecordManager String True Manager user of the record.
IsPrivate Boolean False Indicator if the group is private.

Histories

Get, Create, Update or Delete all history items.

Table Specific Information
Select
  • Id supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM Histories

SELECT * FROM Histories where ID = '1234'
Insert

To insert a history, you must specify atleast one contact or company or opportunity or groups along with start time and end time.

INSERT INTO Histories (ContactsAggregate, StartTime, EndTime) VALUES ('9ff95080-7ca2-46ae-8161-04ac83bb20c5, 0ee7ab70-40df-4396-a7ef-132c7555e3ad', '2020-01-27 10:00:000', '2020-01-31 10:00:00')
Update

Any column where ReadOnly=False can be updated. Specify the History ID when updating a group

UPDATE Histories SET Regarding = 'Test', isPrivate = false WHERE ID = '12345'
Delete

Histories can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Histories WHERE ID = '12345'
Columns
Name Type ReadOnly Description
Id [KEY] String True Id of the history item created
AttachmentDisplayName String True Name of the attachment related to a history
AttachmentFileExtension String True FileExtension of the attachment related to a history
AttachmentFileName String True FileName of the attachment related to a history
AttachmentFileSize Integer True File Size of the attachment related to a history
AttachmentFileSizeDisplay String True File Size Display of the attachment related to a history
AttachmentFileType String True FileType of the attachment related to a history
AttachmentLastModified Datetime True File Last Modified date of the attachment related to a history
AttachmentPersonal Boolean True Attachment Personal
CompaniesAggregate String False Companies associated with a history
ContactsAggregate String False Contacts associated with a history
Created Datetime True The datetime when history was created
CreateUserID String True User ID of the user who craeted the history
Details String False Details of history
Duration String True Duration
Edited Datetime True Datetime when history was last edited
EndTime Datetime False End Time of history
GroupsAggregate String False Groups associated with history
HistoryTypeDescription String True Description of the type of history
HistoryTypeId Integer True History Type ID
HistoryTypeName String True Name of history type
TypeID Integer False Id of history type
IsPrivate Boolean False Indicates whether history is private or not
ManageUserID String True User ID of manager
OpportunitiesAggregate String False Opportunities associated with history
OutlookID String False Outlook Id
RecordManager String True Record Manager
RecordManagerID String True Id of Record Msnager
Regarding String False Tells what history is about
StartTime Datetime False Start Time

Notes

Create, update, delete and query the Notes in your Act! CRM database.

Table Specific Information
Select
  • Id supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to Id. (Note that the ID is a required criteria to query the Notes view) The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM Notes WHERE ID = '2413d3d1-2345-6578-4859-792664d177af'
Insert

To insert a note, you must specify atleast one of the contacts, groups, companies or opportunities.

INSERT INTO Notes (NoteText, Contacts) VALUES ('This is sample note', ['123456'])
Update

Any column where ReadOnly=False can be updated.

UPDATE Notes SET isPrivate = false WHERE ID = 123456
Delete

Notes can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Notes WHERE ID = 123456
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique identifier (id) for a given note.
ManageUserID String True The unique identifier (id) of the user managing the note.
CreateUserID String True The unique identifier (id) of the user who created the note.
IsPrivate Boolean False Indicator if the note is private.
NoteText String False Descriptive text of the note.
NoteTypeID Long True The ID of the type of the note.
DisplayDate Datetime True Display date of the note.
Created Datetime True Datetime of the note creation.
Edited Datetime True Datetime of the last edit of the note.
AttachmentDisplayName String False Display name of the attachment.
AttachmentFileExtension String False File extension of the attachment.
AttachmentFileName String False File name of the attachment.
AttachmentFileSize Long False File size of the attachment.
AttachmentFileSizeDisplay String False Display file size of the attachment.
AttachmentLastModified Datetime False Last modified datetime of the attachment.
AttachmentFileType String False Type of the attachment file.
AttachmentPersonal Boolean False Indicator if the attachment is personal.
Companies String False List of companies that are associated to this note entity.
Contacts String False List of contacts that are associated to this note entity.
Groups String False List of groups that are associated to this note entity.
Opportunities String False List of opportunities that are associated to this note entity.

Opportunities

Create, update, delete and query the Opportunities in your Act! CRM database.

Table Specific Information
Select
  • Id supports the = operator.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ActualCloseDate supports the <=, <, =, !=, >, and >= operators.
  • Competitor supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ContactNames supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Creator supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • DaysOpen supports the <=, <, =, !=, >, and >= operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EstimatedCloseDate supports the <=, <, =, !=, >, and >= operators.
  • GrossMargin supports the <=, <, =, !=, >, and >= operators.
  • IsPrivate supports the = operator.
  • OpenDate supports the <=, <, =, !=, >, and >= operators.
  • Probability supports the <=, <, =, !=, >, and >= operators.
  • ProductTotal supports the <=, <, =, !=, >, and >= operators.
  • Reason supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Source supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • WeightedTotal supports the <=, <, =, !=, >, and >= operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following query is processed server side:

SELECT * FROM Opportunities WHERE Name = 'Opportunity name'
Insert

To insert a Opportunity, you must specify the Name and EstimatedCloseDate

INSERT INTO Opportunities (Name, EstimatedCloseDate) VALUES ('Sample Opportunity', '2020-01-30')
Update

Any column where ReadOnly=False can be updated.

UPDATE Opportunities SET ContactsAggregate = '123456' WHERE ID = 'f3fd9661-8fab-4fe3-8321-225f2b770f60'
Delete

Opportunities can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Opportunities WHERE ID = 'f3fd9661-8fab-4fe3-8321-225f2b770f60'
Columns
Name Type ReadOnly Description
Id [KEY] String True A unique identifier that represents the opportunity.
Created Datetime True Created date of the opportunity.
Name String False The opportunity name.
ActualCloseDate Datetime False The date that the opportunity as closed.
CompaniesAggregate String False List of companies that are associated to this opportunity entity.
Competitor String False Gets the name of the competitor for this opportunity.
ContactNames String False The names of the contacts associated with this opportunity.
ContactsAggregate String False The names of the contacts associated with this opportunity.
Creator String False Indicates elevated security that only the creator has access to this opportunity.
DaysOpen Int True The number of days this opportunity has been opened.
Edited Datetime True The date that this record was edited.
EditedBy String True The user's name that last editied this record.
EstimatedCloseDate Datetime False The date the opportunity is expected to be closed.
GrossMargin Decimal False Represents the net sales less the cost of goods and services sold.
GroupsAggregate String False List a groups that are associated to this opportunity entity.
IsPrivate Bool False Indicates whether this opportunity is private
Manager String False The name of the record manager of this opportunity.
OpenDate Datetime False The date the opportunity was opened.
Probability Int False The likelihood this opportunity will will be won by the close date.
ProductTotal Decimal False The total cost of the product or services.
Reason String False Describs why the opportunity status changed.
RecordOwner String False The user's name that owners this record.
RelatedEntitiesResolver Bool False If true prevents related entities from serializing.
Source String False Describs the source of the opportunity.
StageDescription String False Gets or sets the description of the stage.
StageId String False Gets the unique identifier of the stage.
StageName String False The name of the stage.
StageNumber Int False The ordinal number of the stage.
StageProbability Int False The probability of the stage.
StageProcessDescription String False The description of the stage process.
StageProcessId String False The unique identifier of the stage process.
StageProcessName String False The name of the stage process.
Status Int False The status of the opportunity.
TotalPerCompany Decimal False The average total per associated company.
TotalPerContact Decimal False The average total per associated contact.
TotalPerGroup Decimal False The average total per associated group.
WeightedTotal Decimal False The weight of the product.

OpportunityCompanies

Query and delete the Companies related to an Act! CRM opportunity.

Table Specific Information
Select
  • Id supports the = operator.
  • OpportunityId supports the = operator.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Description supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Division supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Fax supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HierarchyLevel supports the <=, <, =, !=, >, and >= operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Industry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NumberOfEmployees supports the <=, <, =, !=, >, and >= operators.
  • ParentID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Phone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PhoneExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Region supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Revenue supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • SicCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Territory supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TickerSymbol supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TollFreeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TollFreePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM OpportunityCompanies WHERE OpportunityId = '12345'

SELECT * FROM OpportunityCompanies WHERE Name = 'Company name'
Delete

OpportunityCompanies can be deleted by providing an OpportunityId and ID (Company Id) and issuing a DELETE statement.

DELETE FROM OpportunityCompanies WHERE OpportunityId = '123456' AND ID = '345678'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier (id) for this company.
OpportunityId [KEY] String False The unique identifier of the opportunity the companies are related to.
Created Datetime False Created date of the company.
Name String False The company's given name.
AddressCity String False An inhabited place of greater size, population, or importance than a town or village.
AddressCountry String False A nation with its own government, occupying a particular territory.
AddressLine1 String False Primary street address
AddressLine2 String False Secondary street address
AddressLine3 String False Miscellaneous street address
AddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState String False A nation or territory considered as an organized political community under one government.
BillingAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
BillingAddressCountry String False A nation with its own government, occupying a particular territory.
BillingAddressLine1 String False Primary street address
BillingAddressLine2 String False Secondary street address
BillingAddressLine3 String False Miscellaneous street address
BillingAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
BillingAddressState String False A nation or territory considered as an organized political community under one government.
Description String False A brief written representation or account of this company.
Division String False The name of a smaller part of a parent company tasked with overseeing different types of products or services than those offered by the parent company.
Edited Datetime False Edited date of the company.
EditedBy String False User who edited the company.
Fax String False The company's fax phone number.
FaxExtension String False The company's fax phone number extension.
HierarchyLevel Int False The positional level of where this company lays within a company hierarchy.
IdStatus String False The relative social or professional position of an individual within a group or organization.
Industry String False Economic activity concerned with the processing of raw materials and manufacture of goods in factories.
NumberOfEmployees Int False The total number of employees that this company operates with.
ParentID String False The unique identifier (id) of the parent company.
Phone String False The company's phone number.
PhoneExtension String False The company's phone number extension.
RecordOwner String False Owner user of the record.
ReferredBy String False Mention or allude to.
Region String False A part of a country, of the world, etc., that is different or separate from other parts in some way.
Revenue String False The amount of money that this company actually receives during a specific period, including discounts and deductions for returned merchandise.
ShippingAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
ShippingAddressCountry String False A nation with its own government, occupying a particular territory.
ShippingAddressLine1 String False Primary street address
ShippingAddressLine2 String False Secondary street address
ShippingAddressLine3 String False Miscellaneous street address
ShippingAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
ShippingAddressState String False A nation or territory considered as an organized political community under one government.
SicCode String False Standard Industrial Classification (SIC) codes are four-digit numerical codes assigned by the U.S. government to business establishments to identify the primary business of the establishment.
Territory String False Geographical area.
TickerSymbol String False An arrangement of characters (usually letters) representing a particular security listed on an exchange or otherwise traded publicly.
TollFreeExtension String False The company's toll free phone number.
TollFreePhone String False The company's toll free phone number.
Website String False The company's website.

OpportunityContacts

Query and delete the Contacts associated with an Opportunity.

Table Specific Information
Select
  • Id supports the = operator.
  • OpportunityId supports the = operator.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • FullName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AltEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternateExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternatePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Birthday supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Company supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • CompanyID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ContactType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Department supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FirstName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsUser supports the = operator.
  • JobTitle supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastResults supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Latitude supports the <=, <, =, !=, >, and >= operators.
  • Longitude supports the <=, <, =, !=, >, and >= operators.
  • MessengerID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MiddleName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobileExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobilePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NamePrefix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NameSuffix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PersonalEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Salutation supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM OpportunityContacts WHERE OpportunityId = '12345'

SELECT * FROM OpportunityContacts WHERE FullName = 'Contact name'
Delete

OpportunityContacts can be deleted by providing an OpportunityId and ID (Contact Id) and issuing a DELETE statement.

DELETE FROM OpportunityContacts WHERE OpportunityId = '12345' AND ID = '34567'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier (id) for this contact.
OpportunityId [KEY] String False The unique identifier (id) for the opportunity to query contacts from.
Created Datetime False Created date of the contact.
FullName String False The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
AltEmailAddress String False A unique electronic address that an individual can be contacted.
AlternateExtension String False The contact's alternate phone number extension.
AlternatePhone String False The contact's alternate phone number.
Birthday String False The anniversary of the day on which a person was born.
BusinessAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry String False A nation with its own government, occupying a particular territory.
BusinessAddressLine1 String False Primary street address
BusinessAddressLine2 String False Secondary street address
BusinessAddressLine3 String False Miscellaneous street address
BusinessAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState String False A nation or territory considered as an organized political community under one government.
BusinessExtension String False The contact's business phone number extension.
BusinessPhone String False The contact's business phone number.
Company String False The organization that this contact is associated with.
CompanyID String False The unique identifier (companyID) for a given companies.
ContactType String False The type of contact: Contact, Secondary, or User.
Department String False An area of special expertise or responsibility.
Edited Datetime False Edited date of the contact.
EditedBy String False User who edited the contact.
EmailAddress String False A unique electronic address that an individual can be contacted.
FaxExtension String False The contact's fax phone number extension.
FaxPhone String False The contact's fax phone number.
FirstName String False A personal given name.
HomeAddressCity String False An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry String False A nation with its own government, occupying a particular territory.
HomeAddressLine1 String False Primary street address
HomeAddressLine2 String False Secondary street address
HomeAddressLine3 String False Miscellaneous street address
HomeAddressPostalCode String False A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState String False A nation or territory considered as an organized political community under one government.
HomeExtension String False The contact's home phone extension.
HomePhone String False The contact's home phone number.
IdStatus String False The relative social or professional position of an individual within a group or organization.
IsUser Bool False An indicator if this contact is also a user within the system.
JobTitle String False The title or position this contact holds.
LastName String False The family name or surname.
LastResults String False Identifies last communication or action with a contact.
Latitude Decimal False The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude Decimal False The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID String False An online identifier alias.
MiddleName String False A name between one's first name and surname.
MobileExtension String False The contact's mobile phone extension.
MobilePhone String False The contact's mobile phone number.
NamePrefix String False A patronymic prefix.
NameSuffix String False Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension String False The contact's pager phone number extension
PagerPhone String False The contact's pager phone number.
PersonalEmailAddress String False A unique electronic address that an individual can be contacted.
RecordOwner String False Owner user of the record.
ReferredBy String False Indiciates how this contact came about.
Salutation String False A gesture or utterance made as a greeting or acknowledgment.
Website String False The contact's web site.

OpportunityGroups

Query and delete the Act! CRM Groups associated with an Opportunity.

Table Specific Information
Select
  • Id supports the = operator.
  • OpportunityId supports the = operator.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Description supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HierarchyLevel supports the <=, <, =, !=, >, and >= operators.
  • ParentID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM OpportunityGroups WHERE OpportunityId = '123456'

SELECT * FROM OpportunityGroups WHERE Name = 'Group name'
Delete

OpportunityGroups can be deleted by providing an OpportunityId and ID (Group Id) and issuing a DELETE statement.

DELETE FROM OpportunityGroups WHERE OpportunityId = '123456' AND ID = '345678'
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique identifier (id) for a group.
OpportunityId [KEY] String False The unique identifier (id) for the opportunity to retrieve groups from.
Created Datetime True A timestamp when this group was created
Name String True The name of the group.
AddressCity String True An inhabited place of greater size, population, or importance than a town or village.
AddressCountry String True A nation with its own government, occupying a particular territory.
AddressLine1 String True Primary street address
AddressLine2 String True Secondary street address
AddressLine3 String True Miscellaneous street address
AddressPostalCode String True A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState String True A nation or territory considered as an organized political community under one government.
Description String True A brief written respresentation of a group.
Edited Datetime True Edited date of the group.
EditedBy String True User who edited the group.
HierarchyLevel Int True The level this group appears in its hierarchy.
ParentID String True The group's parent entity.
RecordOwner String True Owner user of the record.

OpportunityProducts

Update, delete and query the Products associated with an Opportunity.

Table Specific Information
Select
  • Id supports the = operator.
  • OpportunityId supports the = operator.
  • CreateDate supports the <=, <, =, !=, >, and >= operators.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Cost supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditDate supports the <=, <, =, !=, >, and >= operators.
  • ItemNumber supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Price supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Created supports the <=, <, =, !=, >, and >= operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM OpportunityProducts WHERE OpportunityId = '12345'

SELECT * FROM OpportunityProducts WHERE Name = 'Product name'
Update

Any column where ReadOnly=False can be updated.

UPDATE OpportunityProducts SET ItemNumber = 'Product Item Number', Price = 40 WHERE OpportunityId = '12345' AND ID = '34567'
Delete

OpportunityProducts can be deleted by providing an OpportunityId and ID (Product Id) and issuing a DELETE statement.

DELETE FROM OpportunityProducts WHERE OpportunityId = '12345' AND ID = '34567'
Columns
Name Type ReadOnly Description
Id [KEY] String True A unique identifier (id) for a given product.
OpportunityId [KEY] String True A unique identifier (id) of the opportunity containing the products.
Created Datetime True A timestamp when this group was created
CreateDate Datetime True The date that the product was created.
Cost String False The cost of the product.
EditDate Datetime True The date that the opportunity product was lasted modified.
ItemNumber String False A item number assigned to this opportunity product.
Name String False The name of the product.
Price String False The price of the opportunity product.
Discount Int False The opportunity product's discounted percentage (ignore on updates).
DiscountPrice Int False The discounted price of the opportunity product.
ProductID String True A unique identifier (id) for a given product.
Quantity Int False The quantity of opportunity products.
Type String False The quantity of opportunity products.
IsQuickBooksproduct Boolean False Define is product linked with Quickbooks Product.
RecordOwner String False Record Owner.
RecordManager String False Record Manager.
Total Int False The total pricie of the opportunity product (adjusted price * quantity).

Products

Create, update, delete and query the Products in your Act! CRM database.

Table Specific Information
Select
  • Id supports the = operator.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Cost supports the <=, <, =, !=, >, and >= operators.
  • ItemNumber supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Price supports the <=, <, =, !=, >, and >= operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following query is processed server side:

SELECT * FROM Products WHERE Name = 'Product name'
Insert

To insert a product, you need to specify the Product Name, Cost, Item Number and Price.

INSERT INTO Products (Name, Cost, ItemNumber, Price) VALUES ('Product 1', 10, 'PROD_1', 20)
Update

Any column where ReadOnly=False can be updated.

UPDATE Products SET Cost = 20, Price = 30 WHERE ID = 12345
Delete

Products can be deleted by providing an ID and issuing a DELETE statement.

DELETE FROM Products WHERE ID = 12345
Columns
Name Type ReadOnly Description
Id [KEY] String True A unique identifier (id) for a given product.
CreateDate Datetime True The date that the product was created.
Name String False The name of the product.
Cost Decimal False The cost of the product.
EditDate Datetime True The date that the opportunity product was lasted modified.
ItemNumber String False A item number assigned to this opportunity product.
Price Decimal False The price of the opportunity product.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Act! CRM Connector Views

Name Description
ActivityCompanies Query the Companies participating in an Act! CRM Activity.
AnalyticsHistory Gets historical e-marketing information for a given date range.
CampaignLeads Gets contacts leads.
CampaignLeadsinfo Gets opportunities within a campaign within a specified date range.
CampaignResults Gets campaign result information.
CampaignSent Gets campaign sent within a specified date range.
CloseReason Generates opportunity data to summerizes how many times a reason has been closed.
CloseReasonDetails Generates opportunity data to summerizes how many times a reason has been closed.
CompanyContacts Query the Contacts belonging to a Company.
CompanyNotes Query the Notes for a given Act! CRM Company.
ContactActivities Query the Activities for a given Act! CRM Contact.
ContactGroups Query the Act! CRM Groups the Contact belongs to.
ContactHistories Get all the history related to a contact.
ForecastByProduct Generates forecasted opportunity data by given products.
ForecastByProductDetails Generates forecasted opportunity data by a given product..
ForecastByRep Generates forecasted opportunity data by given users.
ForecastByRepDetails Generates forecasted opportunity data by a given user.
GroupNotes Query the Notes for a given Act! CRM Group.
OpportunityCount Generates a summary of the total number of opportunities within a stage.
OpportunityNotes Query the Notes for a given Act! CRM Opportunity.
Pipeline Gets all opportunity matching the custom filter that are currently being worked.
RevenueActualVsPredicted Generates actual vs predicted opportunity revenue.
RevenueCost Generates opportunity periodic revenue versus cost data for given products.
RevenueWonVsLost Generates opportunty win versus loss data.
SalesByProduct Generates opportunity sales by product data for given products.
SalesByProductDetails Generates opportunity sales by product detail data for a given product.
SalesByRep Generates opportunity sales by user data for given users.
SalesByRepDetails Generates opportunity sales by user data for a given user.
StageTime Generates opportunity data for the average time in a stage.
UserActivities Generates activity sales by user data for given users.
UserActivityDetails Generated schema file.

ActivityCompanies

Query the Companies participating in an Act! CRM Activity.

Table Specific Information
Select
  • ActivityId supports the = operator.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BillingAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Description supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Division supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Fax supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HierarchyLevel supports the <=, <, =, !=, >, and >= operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Industry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NumberOfEmployees supports the <=, <, =, !=, >, and >= operators.
  • ParentID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Phone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PhoneExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Region supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Revenue supports the <=, <, =, !=, >, and >= operators.
  • ShippingAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ShippingAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • SicCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Territory supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TickerSymbol supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TollFreeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • TollFreePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ActivityCompanies WHERE ActivityId = '12345'

SELECT * FROM ActivityCompanies WHERE Name = 'TSS Company'
Columns
Name Type Description
Id [KEY] String The unique identifier (id) for this company.
ActivityId [KEY] String A unique identifier that represents the activity the companies are part of.
Created Datetime Created date and the activity.
Name String The company's given name.
AddressCity String An inhabited place of greater size, population, or importance than a town or village.
AddressCountry String A nation with its own government, occupying a particular territory.
AddressLine1 String Primary street address
AddressLine2 String Secondary street address
AddressLine3 String Miscellaneous street address
AddressPostalCode String A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState String A nation or territory considered as an organized political community under one government.
BillingAddressCity String An inhabited place of greater size, population, or importance than a town or village.
BillingAddressCountry String A nation with its own government, occupying a particular territory.
BillingAddressLine1 String Primary street address
BillingAddressLine2 String Secondary street address
BillingAddressLine3 String Miscellaneous street address
BillingAddressPostalCode String A code of letters and digits added to a postal address to aid in the sorting of mail.
BillingAddressState String A nation or territory considered as an organized political community under one government.
Description String A brief written representation or account of this company.
Division String The name of a smaller part of a parent company tasked with overseeing different types of products or services than those offered by the parent company.
Edited Datetime Edited date of the activity.
EditedBy String User that edited the activity.
Fax String The company's fax phone number.
FaxExtension String The company's fax phone number extension.
HierarchyLevel Int The positional level of where this company lays within a company hierarchy.
IdStatus String The relative social or professional position of an individual within a group or organization.
Industry String Economic activity concerned with the processing of raw materials and manufacture of goods in factories.
NumberOfEmployees Int The total number of employees that this company operates with.
ParentID String The unique identifier (id) of the parent company.
Phone String The company's phone number.
PhoneExtension String The company's phone number extension.
RecordOwner String Owner of the activity.
ReferredBy String Mention or allude to.
Region String A part of a country, of the world, etc., that is different or separate from other parts in some way.
Revenue Decimal The amount of money that this company actually receives during a specific period, including discounts and deductions for returned merchandise.
ShippingAddressCity String An inhabited place of greater size, population, or importance than a town or village.
ShippingAddressCountry String A nation with its own government, occupying a particular territory.
ShippingAddressLine1 String Primary street address
ShippingAddressLine2 String Secondary street address
ShippingAddressLine3 String Miscellaneous street address
ShippingAddressPostalCode String A code of letters and digits added to a postal address to aid in the sorting of mail.
ShippingAddressState String A nation or territory considered as an organized political community under one government.
SicCode String Standard Industrial Classification (SIC) codes are four-digit numerical codes assigned by the U.S. government to business establishments to identify the primary business of the establishment.
Territory String Geographical area.
TickerSymbol String An arrangement of characters (usually letters) representing a particular security listed on an exchange or otherwise traded publicly.
TollFreeExtension String The company's toll free phone number.
TollFreePhone String The company's toll free phone number.
Website String The company's website.

AnalyticsHistory

Gets historical e-marketing information for a given date range.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM AnalyticsHistory where Date > '2023-03-12'

SELECT * FROM AnalyticsHistory
Columns
Name Type Description
EmailActions String Email Actions
Sends Integer Count of mail sent
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

CampaignLeads

Gets contacts leads.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • Status supports the = operator.
  • UserId supports the = operator.
  • CampaignId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM CampaignLeadsinfo where UserId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'

SELECT * FROM CampaignLeadsinfo
Columns
Name Type Description
AmaScore Integer Ama Score
BusinessEmail String Business Email
BusinessPhone String Business Phone
CompanyName String Company Name
ContactId String Contact Id
ContactName String Contact Name
Sends Integer Sends
TotalClicks Integer Total Clicks
TotalOpens Integer Total Opens
UniqueClicks Integer Unique Clicks
UniqueOpens Integer Unique Opens

CampaignLeadsinfo

Gets opportunities within a campaign within a specified date range.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • Status supports the = operator.
  • UserId supports the = operator.
  • CampaignId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM CampaignLeadsInfo where Date >= '2023-04-15'

SELECT * FROM CampaignLeadsInfo
Columns
Name Type Description
CampaignId String Campaign Id
CampaignName String Campaign Name
LeadCount Integer Lead Count
TotalValue Integer Total Value
WeightedValue Integer Weighted Value
Status String Staus
UserId String User Id
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

CampaignResults

Gets campaign result information.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • CampaignId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM CampaignResults where Date >= '2023-03-12' and Date  <= '2023-04-15'

SELECT * FROM CampaignResults
Columns
Name Type Description
CampaignDate Datetime Campaign Date
CampaignName String Campaign Name
Clicks Integer Clicks
Opens Integer Opens
Sends Integer Sends
UniqueClicks Integer Unique Clicks
UniqueSends Integer Unique Sends
CampaignId String Campaign Ids
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

CampaignSent

Gets campaign sent within a specified date range.

Columns
Name Type Description
CampaignId String Campaign Id
CampaignName String Campaign Name
SendDate Datetime Send Date
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

CloseReason

Generates opportunity data to summerizes how many times a reason has been closed.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. The Status is required to make a request and the rest of the filter is executed client-side within the connector

  • Date supports the '=,>,<,>=,<=' operators.
  • Status supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM CloseReason where Status = 'open'
Columns
Name Type Description
Count Integer
Reason String
Status String Status of Activities The allowed values are open, won, lost, inactive.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

CloseReasonDetails

Generates opportunity data to summerizes how many times a reason has been closed.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. The Status and CloseReason are required to make a request and the rest of the filter is executed client-side within the connector

  • Date supports the '=,>,<,>=,<=' operators.
  • Status supports the = operator.
  • CloseReason supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM CloseReasonDetails where Status = 'open' and CloseReason = 'Not specified'
Columns
Name Type Description
OppId String Opp Id
OppName String Opp Name
ActualCloseDate Datetime Actual Close Date
ActualValue Integer Actual Value
CloseReason String Close Reason
Status String Status The allowed values are open, won, lost, inactive.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

CompanyContacts

Query the Contacts belonging to a Company.

Table Specific Information
Select
  • Id supports the = operator.
  • CompanyId supports the = operator.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • FullName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AltEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternateExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AlternatePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Birthday supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • BusinessPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Company supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ContactType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Department supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FaxPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • FirstName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeAddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomeExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HomePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IdStatus supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsUser supports the = operator.
  • JobTitle supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • LastResults supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Latitude supports the <=, <, =, !=, >, and >= operators.
  • Longitude supports the <=, <, =, !=, >, and >= operators.
  • MessengerID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MiddleName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobileExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • MobilePhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NamePrefix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • NameSuffix supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PagerPhone supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • PersonalEmailAddress supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • ReferredBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Salutation supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Website supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following query is processed server side:

SELECT * FROM CompanyContacts WHERE Fullname = 'Contact name'
Delete

CompanyContacts can be deleted by providing CompanyId and ID and issuing a DELETE statement.

DELETE FROM CompanyContacts WHERE CompanyId = '12345' AND ID = '56789'
Columns
Name Type Description
Id [KEY] String The unique identifier (id) for this contact.
CompanyId [KEY] String The unique identifier (id) of the company to retrieve contacts from.
Created Datetime Created data of the contact.
FullName String The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
AltEmailAddress String A unique electronic address that an individual can be contacted.
AlternateExtension String The contact's alternate phone number extension.
AlternatePhone String The contact's alternate phone number.
Birthday String The anniversary of the day on which a person was born.
BusinessAddressCity String An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry String A nation with its own government, occupying a particular territory.
BusinessAddressLine1 String Primary street address
BusinessAddressLine2 String Secondary street address
BusinessAddressLine3 String Miscellaneous street address
BusinessAddressPostalCode String A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState String A nation or territory considered as an organized political community under one government.
BusinessExtension String The contact's business phone number extension.
BusinessPhone String The contact's business phone number.
Company String The organization that this contact is associated with.
ContactType String The type of contact: Contact, Secondary, or User.
Department String An area of special expertise or responsibility.
Edited Datetime Edited date of the contact.
EditedBy String User who edited the contact.
EmailAddress String A unique electronic address that an individual can be contacted.
FaxExtension String The contact's fax phone number extension.
FaxPhone String The contact's fax phone number.
FirstName String A personal given name.
HomeAddressCity String An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry String A nation with its own government, occupying a particular territory.
HomeAddressLine1 String Primary street address
HomeAddressLine2 String Secondary street address
HomeAddressLine3 String Miscellaneous street address
HomeAddressPostalCode String A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState String A nation or territory considered as an organized political community under one government.
HomeExtension String The contact's home phone extension.
HomePhone String The contact's home phone number.
IdStatus String The relative social or professional position of an individual within a group or organization.
IsUser Bool An indicator if this contact is also a user within the system.
JobTitle String The title or position this contact holds.
LastName String The family name or surname.
LastResults String Identifies last communication or action with a contact.
Latitude Decimal The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude Decimal The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID String An online identifier alias.
MiddleName String A name between one's first name and surname.
MobileExtension String The contact's mobile phone extension.
MobilePhone String The contact's mobile phone number.
NamePrefix String A patronymic prefix.
NameSuffix String Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension String The contact's pager phone number extension
PagerPhone String The contact's pager phone number.
PersonalEmailAddress String A unique electronic address that an individual can be contacted.
RecordOwner String Owner user of the record.
ReferredBy String Indiciates how this contact came about.
Salutation String A gesture or utterance made as a greeting or acknowledgment.
Website String The contact's web site.

CompanyNotes

Query the Notes for a given Act! CRM Company.

Table Specific Information
Select
  • CompanyId supports the = operator.
  • ManageUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • CreateUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsPrivate supports the = operator.
  • NoteText supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • DisplayDate supports the <=, <, =, !=, >, and >= operators.
  • AttachmentDisplayName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentLastModified supports the <=, <, =, !=, >, and >= operators.
  • AttachmentPersonal supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM CompanyNotes WHERE CompanyId = '12345'
Columns
Name Type Description
Id [KEY] String The unique identifier (id) for a given note.
CompanyId [KEY] String The unique identifier (id) for the company included in the note.
ManageUserID String The unique identifier (id) of the user managing the note.
CreateUserID String The unique identifier (id) of the user who created the note.
IsPrivate Boolean Indicator if the note is private.
NoteText String Descriptive text of the note.
NoteTypeID Long The ID of the type of the note.
DisplayDate Datetime Display date of the note.
Created Datetime Datetime of the note creation.
Edited Datetime Datetime of the last edit of the note.
AttachmentDisplayName String Display name of the attachment.
AttachmentFileExtension String File extension of the attachment.
AttachmentFileName String File name of the attachment.
AttachmentFileSize Long File size of the attachment.
AttachmentFileSizeDisplay String Display file size of the attachment.
AttachmentFileType String Type of the attachment file.
AttachmentLastModified Datetime Last modified datetime of the attachment.
AttachmentPersonal Boolean Indicator if the attachment is personal.

ContactActivities

Query the Activities for a given Act! CRM Contact.

Table Specific Information
Select
  • ContactId supports the = operator.
  • Created supports the <=, <, =, !=, >, and >= operators.
  • Subject supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Location supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentDisplayName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileExtension supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileSize supports the <=, <, =, !=, >, and >= operators.
  • AttachmentFileSizeDisplay supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentLastModified supports the <=, <, =, !=, >, and >= operators.
  • AttachmentPersonal supports the = operator.
  • CompaniesAggregate supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Details supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Edited supports the <=, <, =, !=, >, and >= operators.
  • EndTime supports the <=, <, =, !=, >, and >= operators.
  • GroupsAggregate supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsCleared supports the = operator.
  • IsPrivate supports the = operator.
  • IsTimeless supports the = operator.
  • OpportunitiesAggregate supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecurSpecFrequency supports the <=, <, =, !=, >, and >= operators.
  • RecurSpecIsEndless supports the = operator.
  • RecurSpecMonth supports the <=, <, =, !=, >, and >= operators.
  • RecurSpecRecurType supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecurSpecSeriesEnd supports the <=, <, =, !=, >, and >= operators.
  • RecurSpecSeriesStart supports the <=, <, =, !=, >, and >= operators.
  • StartTime supports the <=, <, =, !=, >, and >= operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ContactActivities WHERE ContactId = '12345'

SELECT * FROM ContactActivities WHERE Subject = 'Team meeting'

SELECT * FROM ContactActivities WHERE Location = 'My Location'
Columns
Name Type Description
Id [KEY] String A unique identifier that represents the activity.
ContactId String A unique identifier that represents the contact related to the activities.
Created Datetime The date and time the activity was created.
Subject String A description of the action to be completed.
Location String Describes the physical location that the activity is to take place.
ActivityPriorityName String Displays the priority of the activity.
ActivityTypeName String Displays the type of the activity.
AttachmentDisplayName String Gets presentation name of the attachment.
AttachmentFileExtension String Gets the extension of the Attachment
AttachmentFileName String Gets a string representing the directory's full path.
AttachmentFileSize Int Gets the size (bytes) of the attachments.
AttachmentFileSizeDisplay String Gets the displayable text representation of the attachment
AttachmentFileType String Gets the registered system file type name of the attachment
AttachmentLastModified Datetime Gets the last modified date of the attachment
AttachmentPersonal Bool Indicates whether the attachment is bound for the personal suppplemental files or the workgroup supplemental files.
CompaniesAggregate String List of companies that are associated to this activity.
Details String Additional detailed information about the activity.
Edited Datetime The date and time the activity was last updated.
EndTime Datetime The time the activity is to end
GroupsAggregate String List a groups that are associated to this activity.
IsCleared Bool Indicates that the activity has been cleared.
IsPrivate Bool Indicates elevated security that only the creator/owner has access to this activity
IsTimeless Bool Indicates whether a specifies action is to be completed at a specific time-of-day.
OpportunitiesAggregate String List a opportunities that are associated to this activity.
RecurSpecDayDayAsInt Int An integer representation of the day of the week portion of a recurrence pattern.
RecurSpecDayDaysOfWeek String Representation of the day of the week portion of a recurrence pattern.
RecurSpecDayDayType String Describing the day-portion of the recurrence pattern.
RecurSpecDayOrdinal String Describes recurrence in relative terms for certain recurrence patterns (i.e. First, Third, Last).
RecurSpecDayTypedDay String Describing the TypedDay (Day, WeekDay or Weekend Day) for the recurrence pattern when DayType is
RecurSpecFrequency Int Represents how often for the recurrence patterns
RecurSpecIsEndless Bool Flag denoting whether an end date has been specified for the pattern. Note: If false, the following defaults are used: For Daily and Weekly: 2 years beyond the start date For Monthly and Yearly: June 6, 2073.
RecurSpecMonth Int Represents the specific month for certain recurrence patterns
RecurSpecRecurType String Represents a unit of time used to separate recurences (i.e. Daily, Weekly, Monthly, Yearly).
RecurSpecSeriesEnd Datetime Specifiying the ending time for a given day.
RecurSpecSeriesStart Datetime Specifiying the starting time for a given day.
ScheduledBy String The contact who created this activity.
ScheduledFor String The name of the activity's organizer.
SeriesID String A unique identifier that represents the activity. The ID that is returned is based on if it is a recurring or not. Recurrent activity: RecurSourceActivityId or Single Occurence activity: ActivityID
StartTime Datetime The time the activity is to begin

ContactGroups

Query the Act! CRM Groups the Contact belongs to.

Table Specific Information
Select
  • ContactId supports the = operator.
  • Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCity supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressCountry supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine1 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine2 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressLine3 supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressPostalCode supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AddressState supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • Description supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • EditedBy supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • HierarchyLevel supports the <=, <, =, !=, >, and >= operators.
  • ParentID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • RecordOwner supports the <=, <, =, !=, >, >=, and CONTAINS operators.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ContactGroups WHERE ContactId = '12345'

SELECT * FROM ContactGroups WHERE Name = 'Contact name'
Columns
Name Type Description
Id [KEY] String The unique identifier (id) for a group.
ContactId [KEY] String The unique identifier (id) for the contact included in the group.
Created Datetime A timestamp when this group was created
Name String The name of the group.
AddressCity String An inhabited place of greater size, population, or importance than a town or village.
AddressCountry String A nation with its own government, occupying a particular territory.
AddressLine1 String Primary street address
AddressLine2 String Secondary street address
AddressLine3 String Miscellaneous street address
AddressPostalCode String A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState String A nation or territory considered as an organized political community under one government.
Description String A brief written respresentation of a group.
Edited Datetime Edited date of the contact.
EditedBy String User who edited the contact.
HierarchyLevel Int The level this group appears in its hierarchy.
ParentID String The group's parent entity.
RecordOwner String Owner user of the record.
Contact Int Get the total number of contacts within the group.
HasSubgroups Boolean This readonly property indicates that this group has sub groups.
RecordManager String Record Manager of the record.
RecordManagerID String The record manager ID of the record.
ImportDate Datetime This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Boolean Indicates elevated security that only the creator/owner has access to this company.

ContactHistories

Get all the history related to a contact.

Table Specific Information
Select
  • ContactId supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ContactHistories

SELECT * FROM ContactHistories WHERE ContactId = '12345'
Columns
Name Type Description
Id [KEY] String Id of the history item created
ContactId String Id of the contact
AttachmentDisplayName String Name of the attachment related to a history
AttachmentFileExtension String FileExtension of the attachment related to a history
AttachmentFileName String FileName of the attachment related to a history
AttachmentFileSize Integer File Size of the attachment related to a history
AttachmentFileSizeDisplay String File Size Display of the attachment related to a history
AttachmentFileType String FileType of the attachment related to a history
AttachmentLastModified Datetime File Last Modified date of the attachment related to a history
AttachmentPersonal Boolean Attachment Personal
CompaniesAggregate String Companies associated with a history
ContactsAggregate String Contacts associated with a history
Created Datetime The datetime when history was created
CreateUserID String User ID of the user who craeted the history
Details String Details of history
Duration String Duration
Edited Datetime Datetime when history was last edited
EndTime Datetime End Time of history
GroupsAggregate String Groups associated with history
HistoryTypeDescription String Description of the type of history
HistoryTypeId Integer History Type ID
HistoryTypeName String Name of history type
TypeID Integer Id of history type
IsPrivate Boolean Indicates whether history is private or not
ManageUserID String User ID of manager
OpportunitiesAggregate String Opportunities associated with history
OutlookID String Outlook Id
RecordManager String Record Manager
RecordManagerID String Id of Record Msnager
Regarding String Tells what history is about
StartTime Datetime Start Time

ForecastByProduct

Generates forecasted opportunity data by given products.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • ProductId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ForecastByProduct
Columns
Name Type Description
CountPercent Integer Count Percent
OppCount Integer Opp Count
ProductId String Product ID
ProductName String Product Name
TotalValue Integer Total Value
TotalValuePercent Integer Total Value Percent
WeightedValue Integer Weighted Value
WeightedValuePercent Integer Weighted Value Percent

ForecastByProductDetails

Generates forecasted opportunity data by a given product..

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. The ProductId is required to make a request and the rest of the filter is executed client-side within the connector.

  • Date supports the '=,>,<,>=,<=' operators.
  • ProductId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ForecastByProductDetails where Status = 'inactive'
Columns
Name Type Description
EstCloseDate Datetime Est Close Date
OppName String Opp Name
ProbabilityPct Integer Probability Pct
ProductName String Product Name
ProductWeightedValue Integer Product Weighted Value
Status String Status
ProductId String Product Id

ForecastByRep

Generates forecasted opportunity data by given users.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • UserId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ForecastByRep where UserID = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
SELECT * FROM ForecastByRep
Columns
Name Type Description
CountPercent Integer Count Percent
OppCount Integer Opp Count
SalesRep String Sales Rep
TotalValue Integer Total Value
TotalValuePercent Integer Total Value Percent
UserId String User Id
WeightedValue Integer Weighted Value
WeightedValuePercent Integer Weighted Value Percent

ForecastByRepDetails

Generates forecasted opportunity data by a given user.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. UserId is required to make a request and the rest of the filter is executed client-side within the connector.

  • UserId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM ForecastByRepDetails where UserID = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
Columns
Name Type Description
EstCloseDate Datetime Est Close Date
OppName String Opp Name
ProbabilityPct Integer Probablity Pct
RepName String Rep Name
Status String Status
WeightedValue Integer Weighted Value
UserId String User ID

GroupNotes

Query the Notes for a given Act! CRM Group.

Table Specific Information
Select
  • GroupId supports the = operator.
  • ManageUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • CreateUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsPrivate supports the = operator.
  • NoteText supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • DisplayDate supports the <=, <, =, !=, >, and >= operators.
  • AttachmentDisplayName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentLastModified supports the <=, <, =, !=, >, and >= operators.
  • AttachmentPersonal supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to GroupId. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM GroupNotes WHERE GroupId = '12345'
Columns
Name Type Description
Id [KEY] String The unique identifier (id) for a given note.
GroupId [KEY] String The unique identifier (id) for the group included in the note.
ManageUserID String The unique identifier (id) of the user managing the note.
CreateUserID String The unique identifier (id) of the user who created the note.
IsPrivate Boolean Indicator if the note is private.
NoteText String Descriptive text of the note.
NoteTypeID Long The ID of the type of the note.
DisplayDate Datetime Display date of the note.
Created Datetime Datetime of the note creation.
Edited Datetime Datetime of the last edit of the note.
AttachmentDisplayName String Display name of the attachment.
AttachmentFileExtension String File extension of the attachment.
AttachmentFileName String File name of the attachment.
AttachmentFileSize Long File size of the attachment.
AttachmentFileSizeDisplay String Display file size of the attachment.
AttachmentFileType String Type of the attachment file.
AttachmentLastModified Datetime Last modified datetime of the attachment.
AttachmentPersonal Boolean Indicator if the attachment is personal.

OpportunityCount

Generates a summary of the total number of opportunities within a stage.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • DateType supports the = operator.
  • ValueType supports the = operator.
  • ProbabilityValue supports the = operator.
  • ProbabilityOperation supports the = operator.
  • AmountValue supports the = operator.
  • AmountOperation supports the = operator.
  • OmitPrivate supports the = operator.
  • TypeId supports the = operator.
  • UserId supports the = operator.
  • StageId supports the = operator.
  • PickListItemId supports the = operator.
  • Status supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM OpportunityCount where Date = '2023-03-12'

SELECT * FROM OpportunityCount
Columns
Name Type Description
Count Integer Count
Ordinal Integer Ordinal
StageId String Stage Id
StageName String Stage Name
Value Integer Value
StartTime Datetime Start Time
EndTime Datetime End Time
DateType Integer Date Type
ValueType Integer Value Type
ProbabilityValue Integer Probability
ProbabilityOperation String Probability Operation
AmountValue Decimal Amount Value
AmountOperation String Amount Operation
OmitPrivate Boolean Omit Private
TypeId String Type Id
UserId String User Id
StageId String Stage Id
PickListItemId String Pick List Item Id
Status String Status

OpportunityNotes

Query the Notes for a given Act! CRM Opportunity.

Table Specific Information
Select
  • OpportunityId supports the = operator.
  • ManageUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • CreateUserID supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • IsPrivate supports the = operator.
  • NoteText supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • DisplayDate supports the <=, <, =, !=, >, and >= operators.
  • AttachmentDisplayName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentFileName supports the <=, <, =, !=, >, >=, and CONTAINS operators.
  • AttachmentLastModified supports the <=, <, =, !=, >, and >= operators.
  • AttachmentPersonal supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM OpportunityNotes WHERE OpportunityId = '12345'
Columns
Name Type Description
Id [KEY] String The unique identifier (id) for a given note.
OpportunityId [KEY] String The unique identifier (id) for the opportunity included in the note.
ManageUserID String The unique identifier (id) of the user managing the note.
CreateUserID String The unique identifier (id) of the user who created the note.
IsPrivate Boolean Indicator if the note is private.
NoteText String Descriptive text of the note.
NoteTypeID Long The ID of the type of the note.
DisplayDate Datetime Display date of the note.
Created Datetime Datetime of the note creation.
Edited Datetime Datetime of the last edit of the note.
AttachmentDisplayName String Display name of the attachment.
AttachmentFileExtension String File extension of the attachment.
AttachmentFileName String File name of the attachment.
AttachmentFileSize Long File size of the attachment.
AttachmentFileSizeDisplay String Display file size of the attachment.
AttachmentFileType String Type of the attachment file.
AttachmentLastModified Datetime Last modified datetime of the attachment.
AttachmentPersonal Boolean Indicator if the attachment is personal.

Pipeline

Gets all opportunity matching the custom filter that are currently being worked.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • DateType supports the = operator.
  • ValueType supports the = operator.
  • ProbabilityValue supports the = operator.
  • ProbabilityOperation supports the = operator.
  • AmountValue supports the = operator.
  • AmountOperation supports the = operator.
  • OmitPrivate supports the = operator.
  • TypeId supports the = operator.
  • UserId supports the = operator.
  • StageId supports the = operator.
  • PickListItemId supports the = operator.
  • Status supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM Pipeline
Columns
Name Type Description
Id [KEY] String
CompanyName String Company Name
ContactCount Integer Contact Count
ContactName String Contact Name
EstCloseDate Datetime Est Close Date
Name String Name
ProbabilityPct Integer Probability Pct
StageId String Stage Id
StageName String Stage Name
TotalValue Integer Total Value
WeightedValue Integer Weighted Value
StartTime Datetime Start Time
EndTime Datetime End Time
DateType Integer Date Type
ValueType Integer Value Type
ProbabilityValue Integer Probability
ProbabilityOperation String Probability Operation
AmountValue Decimal Amount Value
AmountOperation String Amount Operation
OmitPrivate Boolean Omit Private
TypeId String Type Id
UserId String User ID
StageId String Stage Id
PickListItemId String Pick List Item Id
Status String Status

RevenueActualVsPredicted

Generates actual vs predicted opportunity revenue.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Interval supports the = operator.
  • Date supports the '=,>,<,>=,<=' operators.
  • FiscalYearStart supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM RevenueActualVsPredicted
Columns
Name Type Description
Actual Integer Actual
Potential Integer Potential
Projected Integer Projected
TimePeriod String Time Period
Interval String Interval The allowed values are Daily, Weekly, Monthly, Quarterly, Yearly.
FiscalYearStart Integer Fiscal Year Start
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

RevenueCost

Generates opportunity periodic revenue versus cost data for given products.

Table Specific Information
Select
  • ProductId supports the = operator.
  • Interval supports the = operator.
  • Start supports the = operator.
  • End supports the = operator.
  • FiscalYearStart supports the = operator.

The connector uses the Act! CRM API to process search criteria that refer to the supported fields. The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM RevenueCost where ProductId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
Select * FROM RevenueCost
Columns
Name Type Description
AlternateText String Alternate Text
Cost Integer Cost
OppCount Integer Opp Count
Revenue Integer Revenue
TimePeriod String Time Period
ProductId String Product Id
Interval String Interval The allowed values are daily, weekly, monthly, quarterly, yearly.
FiscalYearStart Integer Fiscal Year Start
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

RevenueWonVsLost

Generates opportunty win versus loss data.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM RevenueWonVsLost
Columns
Name Type Description
Count Integer Count
CountPercent Integer Count Percent
Revenue Integer Revenue
RevenuePercent Integer Revenue Percent
Status String Status
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

SalesByProduct

Generates opportunity sales by product data for given products.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • ProductId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM SalesByProduct
Columns
Name Type Description
Percent Integer Percent
ProductId String Product Id
ProductName String Product Name
Revenue Integer Revenue
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

SalesByProductDetails

Generates opportunity sales by product detail data for a given product.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. ProductId is required to make a request and the rest of the filter is executed client-side within the connector.

  • Date supports the '=,>,<,>=,<=' operators.
  • ProductId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM SalesByProductDetails where ProductId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
Columns
Name Type Description
ClosedDate Datetime Closed Date
OppName String Opp Name
ProductName String Product Name
ProductValue Integer Product Value
ProductId String Product Id
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

SalesByRep

Generates opportunity sales by user data for given users.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • UserId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM SalesByRep
Columns
Name Type Description
CountPercent Integer Count Percent
OppCount Integer Opp Count
Revenue Integer Revenue
RevenuePercent Integer Revenue Percent
SalesRep String Sales Rep
UserId String User Id
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

SalesByRepDetails

Generates opportunity sales by user data for a given user.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. UserID is required to make a request and the rest of the filter is executed client-side within the connector

  • Date supports the '=,>,<,>=,<=' operators.
  • UserId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM SalesByRepDetails where UserId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
Columns
Name Type Description
ClosedDate Datetime Close Date
OppName String Opp Name
OppValue Integer Opp Value
RepName String Rep Name
UserId String User Id
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

StageTime

Generates opportunity data for the average time in a stage.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • StageId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM StageTime
Columns
Name Type Description
AvgDays Integer Average Days
StageName String Stage Name
StageId String Stage Id
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

UserActivities

Generates activity sales by user data for given users.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

  • Date supports the '=,>,<,>=,<=' operators.
  • UserId supports the = operator.
  • TypeId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM UserActivities
Columns
Name Type Description
ActivityCount Integer Activity Count
UserId String User Id
UserName String User Name
TypeId String Type Id
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

UserActivityDetails

Generated schema file.

Table Specific Information
Select

The connector will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. UserId is required to make a request and the rest of the filter is executed client-side within the connector.

  • Date supports the '=,>,<,>=,<=' operators.
  • TypeId supports the = operator.
  • UserId supports the = operator.

The connector processes other filters client-side within the connector. For example, the following queries are processed server side:

SELECT * FROM UserActivityDetails where UserId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
Columns
Name Type Description
ActivityId String Activity Id
ContactName String Contact Name
Location String Location
Regarding String Regarding
Type String Type
UserName String User Name
TypeId String Type Id
UserId String UserId
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Date Datetime Date Range of Activities By User

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Act! CRM.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Act! CRM, along with an indication of whether the procedure succeeded or failed.

Act! CRM Connector Stored Procedures

Name Description
AddCompanyToOpportunity Associate the company (if it is not already present) to an opportunity
AddContactToGroup Associate the contact (if it is not already present) to a group
AddContactToNote Associate the contact (if it is not already present) to a note
AddContactToOpportunity Associate the contact (if it is not already present) to an opportunity
AddGroupToOpportunity Associate the group (if it is not already present) to an opportunity
AddOpportunityProduct Creates a new Opportunity Product
AddOrRemoveContactToActivity Add or remove the contact to an activity
AddOrRemoveContactToCompany Associate the contact (if it is not already present) to a company.
ClearActivity Clears an activity.
ClearActivityAlarms Clears an activity alarms
SubscribeContactToHotLeads Subscribes a contact to hot-leads
UnclearActivity Unclears an activity.
UpdateContactAccessLevel Updates access level of an already existing contact.

AddCompanyToOpportunity

Associate the company (if it is not already present) to an opportunity

Stored Procedures Specific Information
Process of adding company to opportunity

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddCompanyToOpportunity are OpportunityId and CompanyId. For example:

EXEC AddCompanyToOpportunity CompanyId = '123456', OpportunityId = '567890'
Input
Name Type Description
OpportunityId String The unique identifier (id) for a given opportunity.
CompanyId String The unique identifier (id) for a given company that aren't associated with a opportunity.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddContactToGroup

Associate the contact (if it is not already present) to a group

Stored Procedures Specific Information
Process of adding contact to group

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddContactToGroup are ContactId and GroupId. For example:

EXEC AddContactToGroup ContactId = '123456', GroupId = '567890'
Input
Name Type Description
GroupId String The unique identifier (id) for a given group.
ContactId String The unique identifier (id) for a given contact that is associated with a group.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddContactToNote

Associate the contact (if it is not already present) to a note

Input
Name Type Description
NoteId String The unique identifier (id) for a given note.
ContactId String The unique identifier (id) for a given contact that is associated with a history item.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddContactToOpportunity

Associate the contact (if it is not already present) to an opportunity

Stored Procedures Specific Information
Process of adding contact to opportunity

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddContactToOpportunity are OpportunityId and ContactId. For example:

EXEC AddContactToOpportunity ContactId = '123456', OpportunityId = '567890'
Input
Name Type Description
OpportunityId String The unique identifier (id) for a given opportunity.
ContactId String The unique identifier (id) for a given contact that aren't associated with a opportunity.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddGroupToOpportunity

Associate the group (if it is not already present) to an opportunity

Stored Procedures Specific Information
Process of adding group to opportunity

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddGroupToOpportunity are GroupId and OpportunityId. For example:

EXEC AddGroupToOpportunity GroupId = '123456', OpportunityId = '567890';
Input
Name Type Description
OpportunityId String The unique identifier (id) for a given opportunity.
GroupId String The unique identifier (id) for a given group that aren't associated with a opportunity.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddOpportunityProduct

Creates a new Opportunity Product

Stored Procedures Specific Information
Process of adding opportunity product

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddOpportunityProduct are OpportunityId, ProductName, ProductCost, ProductDiscount, ProductDiscountPrice, ProductItemNumber, ProductPrice, ProductQuantity and ProductTotal. For example:

EXECUTE AddOpportunityProduct OpportunityId = '123456', ProductName = 'Sample Opportunity Product'
Input
Name Type Description
OpportunityId String The unique identifier (id) for a given opportunity.
ProductName String The name of the product
ProductCost String The cost of the product
ProductDiscount String The discount value
ProductDiscountPrice String The discount price value
ProductItemNumber String The item number of the product
ProductPrice String The price of the product
ProductQuantity String The product quantity
ProductTotal String The product total
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddOrRemoveContactToActivity

Add or remove the contact to an activity

Stored Procedures Specific Information
Process of adding or removing contact to activity

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddOrRemoveContactToActivity are ContactId, ActivityId and Type. For example:

For Adding Contact

EXEC AddOrRemoveContactToActivity ContactId = '123456', ActivityId = '567890', Type = 'add';

For Removing Contact

EXEC AddOrRemoveContactToActivity ContactId = '123456', ActivityId = '567890', Type = 'remove';
Input
Name Type Description
ActivityId String The ID of the activity to which contact will be associated or disassociated
ContactId String The ID of the contact to be associated or disassociated
Type String Type to specify whether to add or remove contact.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

AddOrRemoveContactToCompany

Associate the contact (if it is not already present) to a company.

Stored Procedures Specific Information
Process of adding or removing contact to company

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for AddOrRemoveContactToCompany are ContactId, CompanyId and Type. For example:

For Adding Contact To Company

EXEC AddOrRemoveContactToCompany CompanyId = '123456', ContactId = '567890', Type = 'add'

For removing contact from company

EXEC AddOrRemoveContactToCompany CompanyId = '123456', ContactId = '567890', Type = 'remove'
Input
Name Type Description
CompanyId String The unique identifier (id) for a given company.
ContactId String The unique identifier (id) for a given contact that aren't associated with a company.
Type String Whether to add or remove the contact
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

ClearActivity

Clears an activity.

Stored Procedures Specific Information
Process of clearing an activity

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for ClearActivity are ActivityId and other activity information. For example:

EXEC ClearActivity ActivityId = '123456', IsPrivate = true
Input
Name Type Description
ActivityId String The unique identifier (id) for a given activity
HistoryTypeId String The unique identifier of the history type
HistoryTypeName String The name of the history type
HistoryTypeDescription String The description of the history type
StartTime Datetime The time the activity is to begin
EndTime Datetime The time the activity is to end
IncludeDetailsToHistory Boolean Indicates whether the details will be included to activity history
Details String Additional detailed information about the activity
Subject String A description of the action to be completed
IsPrivate Boolean Indicates elevated security that only the creator/owner has access to this activity
AttachmentDisplayName String The presentation name of the attachment
AttachmentFileExtension String The extension of the attachment
AttachmentFileName String The directory's full path
AttachmentFileSize Integer The size (bytes) of the attachment
AttachmentFileSizeDisplay String The displayable text representation of the attachment
AttachmentFileType String The system file type name of the attachment
AttachmentPersonal Boolean Indicates whether the attachment is bound for the personal supplemental files or the workgroup supplemental files
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

ClearActivityAlarms

Clears an activity alarms

Stored Procedures Specific Information
Process of clearing activity alarms

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for ClearActivityAlarms are ActivityId. For example:

EXEC ClearActivityAlarms ActivityId = '123456'
Input
Name Type Description
ActivityId String The unique identifier (id) for a given activity
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

SubscribeContactToHotLeads

Subscribes a contact to hot-leads

Stored Procedures Specific Information
Process of subscribing contact to hot-leads

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for SubscribeContactToHotLeads are ContactId and Active. For example:

EXEC SubscribeContactToHotLeads ContactId = '123456', Active = true;
Input
Name Type Description
ContactId String The unique identifier (id) for a given contact
Active Boolean If the contact will be subscribed to hot leads.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

UnclearActivity

Unclears an activity.

Stored Procedures Specific Information
Process of unclearing activity

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for UnclearActivity are ActivityId. For example:

EXEC UnclearActivity ActivityId = '123456';
Input
Name Type Description
ActivityId String The unique identifier (id) for a given occurrence activity.
Result Set Columns
Name Type Description
Status String Stored Procedure Execution Status

UpdateContactAccessLevel

Updates access level of an already existing contact.

Stored Procedures Specific Information
Process of updating access level of an already existing contact

Act! CRM allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only = comparision. The available columns for UpdateContactAccessLevel are ContactId, Type, Id, AccessorType and Name. For example:

INSERT INTO AccessorAggregate#TEMP (Id, AccessorType, Name) VALUES ('123456', 'Accessor Type Value', 'Accessor Name');

EXEC UpdateContactAccessLevel AccessorAggregate = 'AccessorAggregate#TEMP', ContactId = '567890', Type = 'Public';

The second way of using the Stored Procedure is by adding the aggregate itself:

EXECUTE UpdateContactAccessLevel AccessorAggregate = '{
  "Id": "123456",
  "AccessorType": "Accessor Type",
  "Name": "Accessor Name"
}', ContactId = '567890', Type = 'Public'
Input
Name Type Description
ContactId String The unique identifier (id) for a given contact.
Type String The type of the contact
Id String The unique identifier (id) for a given accessor.
AccessorType String The type of the accessor
Name String The name of the accessor
AccessorAggregate String This is a map of your property names to the values for this object. These are the values that we will sync into corresponding Accessor object.
Result Set Columns
Name Type Description
Status String Whether or not the access level was updated.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Act! CRM:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Activities table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Activities'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the StoredProc stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='StoredProc' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Activities table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Activities'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:actcrm:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
ActCloudName The handle assigned to the Act! Premium Cloud account.
ActDatabase The Act! Database to connect to.
URL The URL of the ActCRM account.
ActEdition The edition of ActCRM being used. Set either Act CRM or Act Premium Cloud.
User The Act! CRM user account used to authenticate.
Password The password used to authenticate the user.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
IncludeCustomFields A boolean indicating if you would like to include custom fields in the column listing.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Act! CRM.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth The maximum number of rows to scan to look for the columns available in a table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
TypeDetectionScheme Enables scanning Act! CRM Contact entities to determine unique columns.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
ActCloudName The handle assigned to the Act! Premium Cloud account.
ActDatabase The Act! Database to connect to.
URL The URL of the ActCRM account.
ActEdition The edition of ActCRM being used. Set either Act CRM or Act Premium Cloud.
User The Act! CRM user account used to authenticate.
Password The password used to authenticate the user.

ActCloudName

The handle assigned to the Act! Premium Cloud account.

Data Type

string

Default Value

""

Remarks

ActCloudName is assigned to the account when it is created in Act! Premium Cloud.

It is found in the browser's address field when opening the online account, in the form https://eup1-iis-04.eu.hosted.act.com/ActCloudName.

This property is only applied for Act! Premium Cloud connections, and must not be used if attempting to connect to an on-premise Act! Premium (Web) database.

ActDatabase

The Act! Database to connect to.

Data Type

string

Default Value

""

Remarks

If you are using Act! Premium for Desktop, the database name is shown at the top of the page, as Act! Premium - {DatabaseName}. If you are using Act! Premium Cloud, click the ? icon in the top right and select About Act! Premium. You will find the Database Name in the window that appears.

URL

The URL of the ActCRM account.

Data Type

string

Default Value

""

Remarks

The ActCRM URL is in the form http://{act crm instance}.com.

For Premium On-Premise this property should look like http://serverName/.

ActEdition

The edition of ActCRM being used. Set either Act CRM or Act Premium Cloud.

Possible Values

Act CRM, Act Premium Cloud

Data Type

string

Default Value

Act CRM

Remarks

ActCloudName property is mandatory and needs to be filled only in Case of [Act Premium Cloud].

User

The Act! CRM user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the Act! CRM server.

Password

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\ActCRM Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\ActCRM Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
IncludeCustomFields A boolean indicating if you would like to include custom fields in the column listing.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Act! CRM.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
RowScanDepth The maximum number of rows to scan to look for the columns available in a table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
TypeDetectionScheme Enables scanning Act! CRM Contact entities to determine unique columns.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

IncludeCustomFields

A boolean indicating if you would like to include custom fields in the column listing.

Data Type

bool

Default Value

true

Remarks

Setting this to true will cause custom fields to be included in the column listing, but may cause poor performance when listing metadata.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Act! CRM.

Data Type

int

Default Value

5000

Remarks

The Pagesize property affects the maximum number of results to return per page from Act! CRM. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

RowScanDepth

The maximum number of rows to scan to look for the columns available in a table.

Data Type

int

Default Value

500

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

TypeDetectionScheme

Enables scanning Act! CRM Contact entities to determine unique columns.

Possible Values

None, RowScan

Data Type

string

Default Value

RowScan

Remarks

In the Act! CRM API, Contact entities can have different sets of associated attributes in addition to the standard columns.

To access these unique columns, set the following connection properties:

Property Description
RowScan Setting TypeDetectionScheme to RowScan will scan objects (rows) to heuristically determine additional columns when you connect. The RowScanDepth determines the number of objects to be scanned.
None Setting TypeDetectionScheme to None will return only the standard columns.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Activities WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json

Note that the specified path is not embedded in quotation marks.