Skip to Content

Act! CRM Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 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.
  • ActCloudRegion (Act! Premium Cloud only): The Region of the Act! Premium Cloud account.

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 supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

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

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).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Act! CRM connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view 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 so that each root element defines the name of a view, and includes 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

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset 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

To enable TLS, set the following:

  • URL: Prefix the connection string with https://

With this configuration, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

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.
CompanyContacts Query the Contacts belonging to a Company.
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.
IsAlarmCleared Bool False Boolean value of IsAlarmCleared.
IsAlarmed Bool False Boolean value of IsAlarmed.
LeadMinutes Int False Lead minutes.
AlarmDue String False Alarm due.
ExternalId String False External Id.
AccessorId String False Accessor Id.
ScheduledById String False Scheduled Id.
ScheduledForId String False Scheduled for Id.

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.
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.
BillingAddressLatitude Decimal False The Billing address latitude.
BillingAddressLongitude Decimal False The Billing address longitude.
ShippingAddressLatitude Decimal False The Shipping address latitude.
ShippingAddressLongitude Decimal False The Shipping address longitude.
HasDivisions Boolean False This readonly property indicates that this company has divisions.
ImportDate Datetime False This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Boolean False Indicates elevated security that only the creator/owner has access to this company.
RootParentId String False The unique identifier (id) of the parent company.
RecordManagerID String False The record manager.
CustomFields String False Custom fields.
RecordManager String False Manager of the activity.

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.
RecordManager String False Record Manager of the record.
RecordManagerID String False The record manager ID of the record.
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.

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 Datetime 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.
IsFavorite Bool False Assign this contact as a favorite.
IsImported Bool False Identifies last communication or action with a contact.
ImportDate Datetime False This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Bool False Identifies last communication or action with a contact.
LastEmail String False Last email.
LastAttempt String False Last attempt.
LastReach String False Last reach.
LastMeeting String False Last meeting.
LastLetterSent String False Last letter sent.
AmaScore Int False The contact's.
BusinessAddressLatitude Decimal False Business address latitude.
BusinessAddressLongitude Decimal False Business address longitude.
BusinessCountryCode Int False The contact's business phone country code.
BusinessMaskFormat String False The contact's business phone mask format.
MobileCountryCode Int False The contact's mobile phone country code.
MobileMaskFormat String False The contact's mobile phone mask format.
FaxCountryCode Int False The contact's fax phone country code.
FaxMaskFormat String False The contact's fax phone mask format.
HomeAddressLatitude Decimal False Home address latitude.
HomeAddressLongitude Decimal False Home address longitude.
HomeCountryCode Int False The contact's home phone country code.
HomeMaskFormat String False The contact's home phone mask format.
AlternateCountryCode Int False The contact's alternate phone country code.
AlternateMaskFormat String False The contact's alternate phone mask format.
PagerCountryCode Int False The contact's pager phone country code.
PagerMaskFormat String False The contact's pager phone mask format.
AemOptOut Bool False AemOptOut.
AemBounceBack Bool False AemBounceBack.
RecordManagerId String False The record manager.
QuickBooksId String False Quickbooks external ID to match record with quickbooks contact.
NylasContactId String False Nylas contact ID to match record with a contact.
CustomFields String False Custom fields.
RecordManager String False Record manager.

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 Datetime 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.
IsFavorite Bool False Assign this contact as a favorite.
IsImported Bool False Identifies last communication or action with a contact.
ImportDate Datetime False This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Bool False Identifies last communication or action with a contact.
LastEmail String False Last email.
LastAttempt String False Last attempt.
LastReach String False Last reach.
LastMeeting String False Last meeting.
LastLetterSent String False Last letter sent.
AmaScore Int False The contact's.
BusinessAddressLatitude Decimal False Business address latitude.
BusinessAddressLongitude Decimal False Business address longitude.
BusinessCountryCode Int False The contact's business phone country code.
BusinessMaskFormat String False The contact's business phone mask format.
MobileCountryCode Int False The contact's mobile phone country code.
MobileMaskFormat String False The contact's mobile phone mask format.
FaxCountryCode Int False The contact's fax phone country code.
FaxMaskFormat String False The contact's fax phone mask format.
HomeAddressLatitude Decimal False Home address latitude.
HomeAddressLongitude Decimal False Home address longitude.
HomeCountryCode Int False The contact's home phone country code.
HomeMaskFormat String False The contact's home phone mask format.
AlternateCountryCode Int False The contact's alternate phone country code.
AlternateMaskFormat String False The contact's alternate phone mask format.
PagerCountryCode Int False The contact's pager phone country code.
PagerMaskFormat String False The contact's pager phone mask format.
AemOptOut Bool False AemOptOut.
AemBounceBack Bool False AemBounceBack.
RecordManagerId String False The record manager.
QuickBooksId String False Quickbooks external ID to match record with quickbooks contact.
NylasContactId String False Nylas contact ID to match record with a contact.
CustomFields String False Custom fields.
RecordManager String False Record manager.

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.
Contacts Int False Get the total number of contacts within the group. Contacts is nullable and hide it if the value is null.
ImportDate Datetime True This readonly property displays the date that the opportunity was imported into the system.
RecordManagerID String False The record manager.
CustomFields String False Custom fields.

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')

INSERT INTO Histories (ContactsAggregate, StartTime, EndTime) VALUES ('[{\"id\":\"a58016a6-780c-48c0-84ff-b6b6b085eb8c\",\"displayName\": \"Kristian Kate\",\"company\": \"Smith Legal Services (demo)\",\"isInvited\": true}]', '2025-02-13 10:02:00', '2025-02-14 10:02: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.
RecordManager String False Manager of the activity.
RecordManagerId String False The record manager.

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 String 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.
DaysInStage Int False The number of days this opportunity has been in a given stage.
StageStartDate Datetime False The date the opportunity stage started.
SourceId String False The external source ID for the opportunity.
StageProcessStatus String False Stage process status.
StageProcessStagesCount Int False Stage process stages count
StageProcessStages String False Stage process stages
ImportDate Datetime True This readonly property displays the date that the opportunity was imported into the system.
QuickBooksInvoiceId String False Quickbooks external ID to match record with quickbooks contact.
RecordManagerId String False The record manager.
CustomFields String False Custom fields.
RecordManager String False Manager of the activity.

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 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.
AddressLatitude Decimal False The address latitude.
AddressLongitude Decimal False The address longitude.
BillingAddressLatitude Decimal False The billing address latitude.
BillingAddressLongitude Decimal False The billing address longitude.
HasDivisions Bool False This readonly property indicates that this company has divisions.
ImportDate Datetime False This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Bool False Indicates elevated security that only the creator/owner has access to this company.
RootParentId String False The unique identifier (id) of the parent company.
ShippingAddressLatitude Decimal False The shipping address latitude.
ShippingAddressLongitude Decimal False The shipping address longitude.
RecordManagerID String False The record manager.
CustomFields String False Custom fields.
RecordManager String False Manager of the activity.

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 Datetime 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.
IsFavorite Bool False Assign this contact as a favorite.
IsImported Bool False Identifies last communication or action with a contact.
ImportDate Datetime False This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Bool False Identifies last communication or action with a contact.
LastEmail String False Last email.
LastAttempt String False Last attempt.
LastReach String False Last reach.
LastMeeting String False Last meeting.
LastLetterSent String False Last letter sent.
AmaScore Int False The contact's.
BusinessAddressLatitude Decimal False The business address latitude.
BusinessAddressLongitude Decimal False The business address longitude.
BusinessCountryCode Int False The contact's business phone country code.
BusinessMaskFormat String False The contact's business phone mask format.
MobileCountryCode Int False The contact's mobile phone country code.
MobileMaskFormat String False The contact's mobile phone mask format.
FaxCountryCode Int False The contact's fax phone country code.
FaxMaskFormat String False The contact's fax phone mask format.
HomeAddressLatitude Decimal False The home address latitude.
HomeAddressLongitude Decimal False The home address longitude.
HomeCountryCode Int False The contact's home phone country code.
HomeMaskFormat String False The contact's home phone mask format.
AlternateCountryCode Int False The contact's alternate phone country code.
AlternateMaskFormat String False The contact's alternate phone mask.
PagerCountryCode Int False The contact's pager phone country code.
PagerMaskFormat String False The contact's pager phone mask.
AemOptOut Bool False AemOptOut.
AemBouncBack Bool False AemBouncBack.
RecordManagerID String False The record manager.
QuickbooksId String False Quickbooks external ID to match record with quickbooks contact.
NylasContactId String False Nylas contact ID to match record with a contact.
CustomFileds String False Custom fields.
RecordManager String False Manager of the activity.

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.
Contacts Int True Get the total number of contacts within the group. Contacts is nullable and hide it if the value is null.
HasSubgroups Bool True This readonly property indicates that this group has sub groups.
IsPrivate Bool True Indicates elevated security that only the creator/owner has access to this company.
ImportDate Datetime True This readonly property displays the date that the opportunity was imported into the system.
AddressLatitude Decimal True The address latitude.
AddressLongitude Decimal True The address longitude.
RecordManagerID String True The record manager.
CustomFields String True Custom fields.
RecordManager String True Manager of the activity.

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 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.
Name String False The name of the product.
Price Decimal False The price of the opportunity product.
Discount Decimal False The opportunity product's discounted percentage (ignore on updates).
DiscountPrice Decimal False The discounted price of the opportunity product.
ProductID String True A unique identifier (id) for a given product.
Quantity Decimal 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 Decimal False The total pricie of the opportunity product (adjusted price * quantity).
CustomFields String False Custom fields.
Edited Datetime False Edited.
EditedBy String False Edited by.

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.
CompanyNotes Query the Notes for a given Act! CRM Company.
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.
AddressLatitude 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.
AddressLongitude 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.
BillingAddressLatitude Decimal The Billing address latitude.
BillingAddressLongitude Decimal The Billing address longitude.
HasDivisions Boolean This readonly property indicates that this company has divisions.
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.
RootParentId String The unique identifier (id) of the parent company.
ShippingAddressLatitude Decimal The Shipping address latitude.
ShippingAddressLongitude Decimal The Shipping address longitude.
RecordManagerID String The record manager.
CustomFields String Custom fields.
RecordManager String Manager of the activity.

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 ReadOnly Description
Id [KEY] String False The unique identifier (id) for this contact.
CompanyId [KEY] String False The unique identifier (id) of the company to retrieve contacts from.
Created Datetime False Created data 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 Datetime 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.
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.
IsFavorite Bool False Assign this contact as a favorite.
IsImported Bool False Identifies last communication or action with a contact.
ImportDate Datetime False This readonly property displays the date that the opportunity was imported into the system.
IsPrivate Bool False Identifies last communication or action with a contact.
LastEmail String False The last email.
LastAttempt String False The last attempt.
LastReach String False The last reach.
LastMeeting String False The last meeting.
LastLetterSent String False The last letter sent.
AmaScore Int False The contact's.
BusinessAddressLatitude Decimal False The business address latitude.
BusinessAddressLongitude Decimal False The business address longitude.
BusinessCountryCode Int False The contact's business phone country code.
BusinessMaskFormat String False The contact's business phone mask format.
MobileCountryCode Int False The contact's mobile phone country code.
MobileMaskFormat String False The contact's mobile phone mask format.
FaxCountryCode Int False The contact's fax phone country code.
FaxMaskFormat String False The contact's fax phone mask format.
HomeAddressLatitude Decimal False The Home address latitude.
HomeAddressLongitude Decimal False The Home address longitude.
HomeCountryCode Int False The contact's home phone country code.
HomeMaskFormat String False The contact's home phone mask format.
AlternateCountryCode Int False The contact's alternate phone country code.
AlternateMaskFormat String False The contact's alternate phone mask format.
PagerCountryCode Int False The contact's pager phone country code.
PagerMaskFormat String False The contact's pager phone mask format.
AemOptOut Boolean False AemOptOut.
AemBounceBack Boolean False AemBounceBack.
RecordManagerID String False The record manager.
QuickbooksId String False Quickbooks external ID to match record with quickbooks contact.
NylasContactId String False Nylas contact ID to match record with a contact.
CustomFields String False Custom fields.
RecordManager String False Manager of the activity.

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.
RecordManager String Record Manager.
RecordManagerId String Record Manager Id.
Companies String List of companies that are associated to this note entity.
Contacts String List of contacts that are associated to this note entity.
Groups String List of groups that are associated to this note entity.
Opportunities String List of opportunities that are associated to this note entity.

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.
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.
CustomFields String Custom fields.
AddressLatitude Decimal The Address latitude.
AddressLongitude Decimal The Address longitude.

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 Decimal Count Percent
OppCount Integer Opp Count
ProductId String Product ID
ProductName String Product Name
TotalValue Decimal Total Value
TotalValuePercent Decimal Total Value Percent
WeightedValue Decimal Weighted Value
WeightedValuePercent Decimal 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 Decimal Product Weighted Value
Status String Status
ProductId String Product Id
OppId String Opp 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 Decimal Count Percent
OppCount Integer Opp Count
SalesRep String Sales Rep
TotalValue Decimal Total Value
TotalValuePercent Decimal Total Value Percent
UserId String User Id
WeightedValue Decimal Weighted Value
WeightedValuePercent Decimal 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 Decimal Weighted Value
UserId String User ID
OppId String Opp 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.
RecordManager String Record Manager of the record.
RecordManagerID String The record manager ID of the record.
Companies String List of companies that are associated to this note entity.
Contacts String List of contacts that are associated to this note entity.
Groups String List of groups that are associated to this note entity.
Opportunities String List of opportunities that are associated to this note entity.

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.
RecordManager String Manager of the activity.
RecordManagerId String The record manager.
Companies String List of companies that are associated to this note entity.
Contacts String List of contacts that are associated to this note entity.
Groups String List of groups that are associated to this note entity.
Opportunities String List of opportunities that are associated to this note entity.

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
ContactDisplayName String Contact Display Name
EstCloseDate Datetime Est Close Date
Name String Name
ProbabilityPct Integer Probability Pct
StageId String Stage Id
StageName String Stage Name
TotalValue Decimal Total Value
WeightedValue Decimal 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 Decimal 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 Decimal Percent
ProductId String Product Id
ProductName String Product Name
Revenue Decimal 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 Decimal 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 Decimal Count Percent
OppCount Integer Opp Count
Revenue Decimal Revenue
RevenuePercent Decimal 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 Decimal 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
StartTime Datetime The start time of the user activity.
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.
ColumnType String The role or classification of the column in the schema. Possible values include SYSTEM, LINKEDCOLUMN, NAVIGATIONKEY, REFERENCECOLUMN, and NAVIGATIONPARENTCOLUMN.

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

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'StoredProc' AND IncludeResultColumns='True'
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.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
DataTypeName String The name of the data type.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric 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.
Values String The values you can set in this parameter are limited to those shown in this column. Possible values are comma-separated.
SupportsStreams Boolean Whether the parameter represents a file that you can pass as either a file path or a stream.
IsPath Boolean Whether the parameter is a target path for a schema creation operation.
Default String The value used for this parameter when no value is specified.
SpecificName String A label that, when multiple stored procedures have the same name, uniquely identifies each identically-named stored procedure. If there's only one procedure with a given name, its name is simply reflected here.
IsProvided Boolean Whether the procedure is added/implemented by , as opposed to being a native Act! CRM procedure.
Pseudo-Columns
Name Type Description
IncludeResultColumns Boolean Whether the output should include columns from the result set in addition to parameters. Defaults to False.

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.

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
ActCloudRegion Identifies the handle assigned to the Act! CRM cloud account. (Not used for on-premise connections.).
ActDatabase The Act! Database to connect to.
URL Identifies the URL of the ActCRM account.
ActEdition Identifies the edition of Act! CRM being used.
User Specifies the user ID of the authenticating Act! CRM user account.
Password Specifies the password of the authenticating user account.

SSL

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

Schema

Property Description
Location Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC .
Tables Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA, TableB, TableC .
Views Optional setting that 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 Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
Pagesize The maximum number of records per page the provider returns when requesting data from Act! CRM.
PseudoColumns Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
RowScanDepth The maximum number of rows to scan to look for the columns available in a table.
Timeout Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
TypeDetectionScheme Enables scanning Act! CRM Contact entities to determine unique columns.
UserDefinedViews Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Authentication

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

Property Description
ActCloudRegion Identifies the handle assigned to the Act! CRM cloud account. (Not used for on-premise connections.).
ActDatabase The Act! Database to connect to.
URL Identifies the URL of the ActCRM account.
ActEdition Identifies the edition of Act! CRM being used.
User Specifies the user ID of the authenticating Act! CRM user account.
Password Specifies the password of the authenticating user account.

ActCloudRegion

Identifies the handle assigned to the Act! CRM cloud account (not used for on-premise connections).

Data Type

string

Default Value

"US"

Remarks

ActCloudRegion is the region in which the Act! CRM cloud account is created, determined by the location of the API endpoint. This can be one of the following:

  • US (default): https://apius.act.com
  • UK: https://apiuk.act.com
  • AUS / NZ: https://apiau.act.com
  • EU / International English: https://apieu.act.com

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

Identifies the URL of the ActCRM account.

Data Type

string

Default Value

""

Remarks

Enter the ActCRM URL in the form similar to http://{ActCRM instance}.com.

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

ActEdition

Identifies the edition of Act! CRM being used.

Possible Values

Act CRM, Act Premium Cloud

Data Type

string

Default Value

Act CRM

Remarks

Set either Act CRM (default) or Act Premium Cloud.

If Act Premium Cloud is in use, you must also set the ActCloudRegion.

User

Specifies the user ID of the authenticating Act! CRM user account.

Data Type

string

Default Value

""

Remarks

The authenticating server requires both User and Password to validate the user's identity.

Password

Specifies the password of the authenticating user account.

Data Type

string

Default Value

""

Remarks

The authenticating server requires both User and Password to validate the user's identity.

SSL

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

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

SSLServerCert

Specifies 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 Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC .
Tables Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA, TableB, TableC .
Views Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC .

Location

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Data Type

string

Default Value

%APPDATA%\ActCRM Data Provider\Schema

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

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

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

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Tables

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, 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

If you are connecting to a data source with multiple schemas or catalogs, you must specify each table you want to view by its fully qualified name. This avoids ambiguity between tables that may exist in multiple catalogs or schemas.

Views

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, 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

If you are connecting to a data source with multiple schemas or catalogs, you must specify each view you want to examine by its fully qualified name. This avoids ambiguity between views that may 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 Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
Pagesize The maximum number of records per page the provider returns when requesting data from Act! CRM.
PseudoColumns Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
RowScanDepth The maximum number of rows to scan to look for the columns available in a table.
Timeout Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
TypeDetectionScheme Enables scanning Act! CRM Contact entities to determine unique columns.
UserDefinedViews Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

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

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

This property sets an upper limit on the number of rows the connector returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Other

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Data Type

string

Default Value

""

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Note

It is strongly recommended to set these properties only when advised by the support team to address specific scenarios or issues.

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=True Converts date-time values to GMT, instead of the local time of the machine. The default value is False (use local time).
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of records per page the provider returns when requesting data from Act! CRM.

Data Type

int

Default Value

1000

Remarks

When processing a query, instead of requesting all of the queried data at once from Act! CRM, the connector can request the queried data in pieces called pages.

This connection property determines the maximum number of results that the connector requests per page.

Note that setting large page sizes may improve overall query execution time, but doing so causes the connector to use more memory when executing queries and risks triggering a timeout.

PseudoColumns

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the connector exposes as table columns.

To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"

To include all pseudocolumns for all tables use: "*=*"

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

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.

Data Type

int

Default Value

60

Remarks

This property controls the maximum time, in seconds, that the connector waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the connector cancels the operation and throws an exception.

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond the timeout value if each paging call completes within the timeout limit.

Setting this property to 0 disables the timeout, allowing operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

TypeDetectionScheme

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

Possible Values

RowScan, None

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

Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Data Type

string

Default Value

""

Remarks

This property allows you to define and manage custom views through a JSON-formatted configuration file called UserDefinedViews.json. These views are automatically recognized by the connector and enable you to execute custom SQL queries as if they were standard database views. The JSON file defines each view as a root element with a child element called "query", which contains the 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)"
    }

}

You can define multiple views in a single file and specify the filepath using this property. For example: UserDefinedViews=C:\Path\To\UserDefinedViews.json. When you use this property, only the specified views are seen by the connector.

Refer to User Defined Views for more information.