Act! CRM Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started¶

Act! CRM Version Support

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

Establish a Connection¶

Connect to Act! CRM¶

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

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

Important Notes¶

Configuration Files and Their Paths¶

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

Advanced Features¶

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

User Defined Views

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

SSL Configuration

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

Proxy

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

Query Processing

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

User Defined Views¶

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File¶

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Define Views Using DDL Statements¶

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

Create a View¶

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

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

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

Alter a View¶

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

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

The view is then updated in the JSON configuration file.

Drop a View¶

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views¶

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

Work with User Defined Views¶

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration¶

Customize the SSL Configuration¶

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

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

Data Model¶

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

Views

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

Tables¶

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

Act! CRM Connector Tables¶

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

Activities¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM Activities WHERE Location = 'London'

SELECT * FROM Activities WHERE Subject = 'Team meeting'

Insert¶

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

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

Update¶

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

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

Delete¶

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

DELETE FROM Activities WHERE ID = '12345'

Columns¶

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

Companies¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM Companies WHERE Id = '12345'

SELECT * FROM Companies WHERE Name = 'TSS company'

Insert¶

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

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

Update¶

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

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

Delete¶

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

DELETE FROM Companies WHERE ID = '12345'

Columns¶

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

ContactNotes¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM ContactNotes WHERE ContactId = '12345'

Delete¶

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

DELETE FROM ContactNotes WHERE ContactId = '12345' AND ID = '56789'

Columns¶

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

Contacts¶

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

Table Specific Information¶

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

Select¶

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

SELECT * FROM Contacts WHERE Id = '12345'

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

Insert¶

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

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

Update¶

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

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

Delete¶

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

DELETE FROM Contacts WHERE ID = '12345'

Columns¶

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

GroupContacts¶

Query and delete the Contacts belonging to a Group.

Table Specific Information¶

Select¶

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

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

SELECT * FROM GroupContacts WHERE GroupId = '12345'

SELECT * FROM GroupContacts WHERE FullName = 'Contact name'

Delete¶

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

DELETE FROM GroupContacts WHERE GroupId = '12345' AND ID = '56789'

Columns¶

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

Groups¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM Groups WHERE Name = 'Group name'

Insert¶

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

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

Update¶

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

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

Delete¶

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

DELETE FROM Groups WHERE ID = '12345'

Columns¶

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

Histories¶

Get, Create, Update or Delete all history items.

Table Specific Information¶

Select¶

Id supports the = operator.

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

SELECT * FROM Histories

SELECT * FROM Histories where ID = '1234'

Insert¶

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

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

Update¶

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

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

Delete¶

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

DELETE FROM Histories WHERE ID = '12345'

Columns¶

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

Notes¶

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

Table Specific Information¶

Select¶

Id supports the = operator.

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

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

Insert¶

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

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

Update¶

Any column where ReadOnly=False can be updated.

UPDATE Notes SET isPrivate = false WHERE ID = 123456

Delete¶

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

DELETE FROM Notes WHERE ID = 123456

Columns¶

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

Opportunities¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM Opportunities WHERE Name = 'Opportunity name'

Insert¶

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

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

Update¶

Any column where ReadOnly=False can be updated.

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

Delete¶

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

DELETE FROM Opportunities WHERE ID = 'f3fd9661-8fab-4fe3-8321-225f2b770f60'

Columns¶

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

OpportunityCompanies¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM OpportunityCompanies WHERE OpportunityId = '12345'

SELECT * FROM OpportunityCompanies WHERE Name = 'Company name'

Delete¶

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

DELETE FROM OpportunityCompanies WHERE OpportunityId = '123456' AND ID = '345678'

Columns¶

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

OpportunityContacts¶

Query and delete the Contacts associated with an Opportunity.

Table Specific Information¶

Select¶

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

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

SELECT * FROM OpportunityContacts WHERE OpportunityId = '12345'

SELECT * FROM OpportunityContacts WHERE FullName = 'Contact name'

Delete¶

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

DELETE FROM OpportunityContacts WHERE OpportunityId = '12345' AND ID = '34567'

Columns¶

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

OpportunityGroups¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM OpportunityGroups WHERE OpportunityId = '123456'

SELECT * FROM OpportunityGroups WHERE Name = 'Group name'

Delete¶

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

DELETE FROM OpportunityGroups WHERE OpportunityId = '123456' AND ID = '345678'

Columns¶

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

OpportunityProducts¶

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

Table Specific Information¶

Select¶

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

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

SELECT * FROM OpportunityProducts WHERE OpportunityId = '12345'

SELECT * FROM OpportunityProducts WHERE Name = 'Product name'

Update¶

Any column where ReadOnly=False can be updated.

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

Delete¶

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

DELETE FROM OpportunityProducts WHERE OpportunityId = '12345' AND ID = '34567'

Columns¶

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

Products¶

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

Table Specific Information¶

Select¶

Id supports the = operator.
Name supports the <=, <, =, !=, >, >=, and CONTAINS operators.
Cost supports the <=, <, =, !=, >, and >= operators.
ItemNumber supports the <=, <, =, !=, >, >=, and CONTAINS operators.
Price supports the <=, <, =, !=, >, and >= operators.

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

SELECT * FROM Products WHERE Name = 'Product name'

Insert¶

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

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

Update¶

Any column where ReadOnly=False can be updated.

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

Delete¶

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

DELETE FROM Products WHERE ID = 12345

Columns¶

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

Views¶

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

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

Act! CRM Connector Views¶

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

ActivityCompanies¶

Query the Companies participating in an Act! CRM Activity.

Table Specific Information¶

Select¶

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

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

SELECT * FROM ActivityCompanies WHERE ActivityId = '12345'

SELECT * FROM ActivityCompanies WHERE Name = 'TSS Company'

Columns¶

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

AnalyticsHistory¶

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

Table Specific Information¶

Select¶

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

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

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

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

SELECT * FROM AnalyticsHistory

Columns¶

Name	Type	Description
`EmailActions`	`String`	Email Actions
`Sends`	`Integer`	Count of mail sent

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

CampaignLeads¶

Gets contacts leads.

Table Specific Information¶

Select¶

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

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

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

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

SELECT * FROM CampaignLeadsinfo

Columns¶

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

CampaignLeadsinfo¶

Gets opportunities within a campaign within a specified date range.

Table Specific Information¶

Select¶

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

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

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

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

SELECT * FROM CampaignLeadsInfo

Columns¶

Name	Type	Description
`CampaignId`	`String`	Campaign Id
`CampaignName`	`String`	Campaign Name
`LeadCount`	`Integer`	Lead Count
`TotalValue`	`Integer`	Total Value
`WeightedValue`	`Integer`	Weighted Value
`Status`	`String`	Staus
`UserId`	`String`	User Id

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

CampaignResults¶

Gets campaign result information.

Table Specific Information¶

Select¶

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

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

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

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

SELECT * FROM CampaignResults

Columns¶

Name	Type	Description
`CampaignDate`	`Datetime`	Campaign Date
`CampaignName`	`String`	Campaign Name
`Clicks`	`Integer`	Clicks
`Opens`	`Integer`	Opens
`Sends`	`Integer`	Sends
`UniqueClicks`	`Integer`	Unique Clicks
`UniqueSends`	`Integer`	Unique Sends
`CampaignId`	`String`	Campaign Ids

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

CampaignSent¶

Gets campaign sent within a specified date range.

Columns¶

Name	Type	Description
`CampaignId`	`String`	Campaign Id
`CampaignName`	`String`	Campaign Name
`SendDate`	`Datetime`	Send Date

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

CloseReason¶

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

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM CloseReason where Status = 'open'

Columns¶

Name	Type	Description
`Count`	`Integer`
`Reason`	`String`
`Status`	`String`	Status of Activities The allowed values are open, won, lost, inactive.

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

CloseReasonDetails¶

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

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM CloseReasonDetails where Status = 'open' and CloseReason = 'Not specified'

Columns¶

Name	Type	Description
`OppId`	`String`	Opp Id
`OppName`	`String`	Opp Name
`ActualCloseDate`	`Datetime`	Actual Close Date
`ActualValue`	`Integer`	Actual Value
`CloseReason`	`String`	Close Reason
`Status`	`String`	Status The allowed values are open, won, lost, inactive.

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

CompanyContacts¶

Query the Contacts belonging to a Company.

Table Specific Information¶

Select¶

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

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

SELECT * FROM CompanyContacts WHERE Fullname = 'Contact name'

Delete¶

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

DELETE FROM CompanyContacts WHERE CompanyId = '12345' AND ID = '56789'

Columns¶

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

CompanyNotes¶

Query the Notes for a given Act! CRM Company.

Table Specific Information¶

Select¶

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

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

SELECT * FROM CompanyNotes WHERE CompanyId = '12345'

Columns¶

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

ContactActivities¶

Query the Activities for a given Act! CRM Contact.

Table Specific Information¶

Select¶

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

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

SELECT * FROM ContactActivities WHERE ContactId = '12345'

SELECT * FROM ContactActivities WHERE Subject = 'Team meeting'

SELECT * FROM ContactActivities WHERE Location = 'My Location'

Columns¶

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

ContactGroups¶

Query the Act! CRM Groups the Contact belongs to.

Table Specific Information¶

Select¶

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

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

SELECT * FROM ContactGroups WHERE ContactId = '12345'

SELECT * FROM ContactGroups WHERE Name = 'Contact name'

Columns¶

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

ContactHistories¶

Get all the history related to a contact.

Table Specific Information¶

Select¶

ContactId supports the = operator.

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

SELECT * FROM ContactHistories

SELECT * FROM ContactHistories WHERE ContactId = '12345'

Columns¶

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

ForecastByProduct¶

Generates forecasted opportunity data by given products.

Table Specific Information¶

Select¶

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

ProductId supports the = operator.

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

SELECT * FROM ForecastByProduct

Columns¶

Name	Type	Description
`CountPercent`	`Integer`	Count Percent
`OppCount`	`Integer`	Opp Count
`ProductId`	`String`	Product ID
`ProductName`	`String`	Product Name
`TotalValue`	`Integer`	Total Value
`TotalValuePercent`	`Integer`	Total Value Percent
`WeightedValue`	`Integer`	Weighted Value
`WeightedValuePercent`	`Integer`	Weighted Value Percent

ForecastByProductDetails¶

Generates forecasted opportunity data by a given product..

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM ForecastByProductDetails where Status = 'inactive'

Columns¶

Name	Type	Description
`EstCloseDate`	`Datetime`	Est Close Date
`OppName`	`String`	Opp Name
`ProbabilityPct`	`Integer`	Probability Pct
`ProductName`	`String`	Product Name
`ProductWeightedValue`	`Integer`	Product Weighted Value
`Status`	`String`	Status
`ProductId`	`String`	Product Id

ForecastByRep¶

Generates forecasted opportunity data by given users.

Table Specific Information¶

Select¶

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

UserId supports the = operator.

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

SELECT * FROM ForecastByRep where UserID = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
SELECT * FROM ForecastByRep

Columns¶

Name	Type	Description
`CountPercent`	`Integer`	Count Percent
`OppCount`	`Integer`	Opp Count
`SalesRep`	`String`	Sales Rep
`TotalValue`	`Integer`	Total Value
`TotalValuePercent`	`Integer`	Total Value Percent
`UserId`	`String`	User Id
`WeightedValue`	`Integer`	Weighted Value
`WeightedValuePercent`	`Integer`	Weighted Value Percent

ForecastByRepDetails¶

Generates forecasted opportunity data by a given user.

Table Specific Information¶

Select¶

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

UserId supports the = operator.

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

SELECT * FROM ForecastByRepDetails where UserID = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'

Columns¶

Name	Type	Description
`EstCloseDate`	`Datetime`	Est Close Date
`OppName`	`String`	Opp Name
`ProbabilityPct`	`Integer`	Probablity Pct
`RepName`	`String`	Rep Name
`Status`	`String`	Status
`WeightedValue`	`Integer`	Weighted Value
`UserId`	`String`	User ID

GroupNotes¶

Query the Notes for a given Act! CRM Group.

Table Specific Information¶

Select¶

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

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

SELECT * FROM GroupNotes WHERE GroupId = '12345'

Columns¶

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

OpportunityCount¶

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

Table Specific Information¶

Select¶

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

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

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

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

SELECT * FROM OpportunityCount

Columns¶

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

OpportunityNotes¶

Query the Notes for a given Act! CRM Opportunity.

Table Specific Information¶

Select¶

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

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

SELECT * FROM OpportunityNotes WHERE OpportunityId = '12345'

Columns¶

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

Pipeline¶

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

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM Pipeline

Columns¶

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

RevenueActualVsPredicted¶

Generates actual vs predicted opportunity revenue.

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM RevenueActualVsPredicted

Columns¶

Name	Type	Description
`Actual`	`Integer`	Actual
`Potential`	`Integer`	Potential
`Projected`	`Integer`	Projected
`TimePeriod`	`String`	Time Period
`Interval`	`String`	Interval The allowed values are Daily, Weekly, Monthly, Quarterly, Yearly.
`FiscalYearStart`	`Integer`	Fiscal Year Start

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

RevenueCost¶

Generates opportunity periodic revenue versus cost data for given products.

Table Specific Information¶

Select¶

ProductId supports the = operator.
Interval supports the = operator.
Start supports the = operator.
End supports the = operator.
FiscalYearStart supports the = operator.

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

SELECT * FROM RevenueCost where ProductId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'
Select * FROM RevenueCost

Columns¶

Name	Type	Description
`AlternateText`	`String`	Alternate Text
`Cost`	`Integer`	Cost
`OppCount`	`Integer`	Opp Count
`Revenue`	`Integer`	Revenue
`TimePeriod`	`String`	Time Period
`ProductId`	`String`	Product Id
`Interval`	`String`	Interval The allowed values are daily, weekly, monthly, quarterly, yearly.
`FiscalYearStart`	`Integer`	Fiscal Year Start

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

RevenueWonVsLost¶

Generates opportunty win versus loss data.

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM RevenueWonVsLost

Columns¶

Name	Type	Description
`Count`	`Integer`	Count
`CountPercent`	`Integer`	Count Percent
`Revenue`	`Integer`	Revenue
`RevenuePercent`	`Integer`	Revenue Percent
`Status`	`String`	Status

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

SalesByProduct¶

Generates opportunity sales by product data for given products.

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM SalesByProduct

Columns¶

Name	Type	Description
`Percent`	`Integer`	Percent
`ProductId`	`String`	Product Id
`ProductName`	`String`	Product Name
`Revenue`	`Integer`	Revenue

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

SalesByProductDetails¶

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

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM SalesByProductDetails where ProductId = 'b7c23fa5-6cd3-4bcd-a036-f8bbb85ba760'

Columns¶

Name	Type	Description
`ClosedDate`	`Datetime`	Closed Date
`OppName`	`String`	Opp Name
`ProductName`	`String`	Product Name
`ProductValue`	`Integer`	Product Value
`ProductId`	`String`	Product Id

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

SalesByRep¶

Generates opportunity sales by user data for given users.

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM SalesByRep

Columns¶

Name	Type	Description
`CountPercent`	`Integer`	Count Percent
`OppCount`	`Integer`	Opp Count
`Revenue`	`Integer`	Revenue
`RevenuePercent`	`Integer`	Revenue Percent
`SalesRep`	`String`	Sales Rep
`UserId`	`String`	User Id

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

SalesByRepDetails¶

Generates opportunity sales by user data for a given user.

Table Specific Information¶

Select¶

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

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

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

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

Columns¶

Name	Type	Description
`ClosedDate`	`Datetime`	Close Date
`OppName`	`String`	Opp Name
`OppValue`	`Integer`	Opp Value
`RepName`	`String`	Rep Name
`UserId`	`String`	User Id

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

StageTime¶

Generates opportunity data for the average time in a stage.

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM StageTime

Columns¶

Name	Type	Description
`AvgDays`	`Integer`	Average Days
`StageName`	`String`	Stage Name
`StageId`	`String`	Stage Id

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

UserActivities¶

Generates activity sales by user data for given users.

Table Specific Information¶

Select¶

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

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

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

SELECT * FROM UserActivities

Columns¶

Name	Type	Description
`ActivityCount`	`Integer`	Activity Count
`UserId`	`String`	User Id
`UserName`	`String`	User Name
`TypeId`	`String`	Type Id

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

UserActivityDetails¶

Generated schema file.

Table Specific Information¶

Select¶

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

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

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

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

Columns¶

Name	Type	Description
`ActivityId`	`String`	Activity Id
`ContactName`	`String`	Contact Name
`Location`	`String`	Location
`Regarding`	`String`	Regarding
`Type`	`String`	Type
`UserName`	`String`	User Name
`TypeId`	`String`	Type Id
`UserId`	`String`	UserId

Pseudo-Columns¶

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

Name	Type	Description
`Date`	`Datetime`	Date Range of Activities By User

Stored Procedures¶

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

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

Act! CRM Connector Stored Procedures¶

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

AddCompanyToOpportunity¶

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

Stored Procedures Specific Information¶

Process of adding company to opportunity¶

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

EXEC AddCompanyToOpportunity CompanyId = '123456', OpportunityId = '567890'

Input¶

Name	Type	Description
`OpportunityId`	`String`	The unique identifier (id) for a given opportunity.
`CompanyId`	`String`	The unique identifier (id) for a given company that aren't associated with a opportunity.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddContactToGroup¶

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

Stored Procedures Specific Information¶

Process of adding contact to group¶

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

EXEC AddContactToGroup ContactId = '123456', GroupId = '567890'

Input¶

Name	Type	Description
`GroupId`	`String`	The unique identifier (id) for a given group.
`ContactId`	`String`	The unique identifier (id) for a given contact that is associated with a group.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddContactToNote¶

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

Input¶

Name	Type	Description
`NoteId`	`String`	The unique identifier (id) for a given note.
`ContactId`	`String`	The unique identifier (id) for a given contact that is associated with a history item.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddContactToOpportunity¶

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

Stored Procedures Specific Information¶

Process of adding contact to opportunity¶

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

EXEC AddContactToOpportunity ContactId = '123456', OpportunityId = '567890'

Input¶

Name	Type	Description
`OpportunityId`	`String`	The unique identifier (id) for a given opportunity.
`ContactId`	`String`	The unique identifier (id) for a given contact that aren't associated with a opportunity.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddGroupToOpportunity¶

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

Stored Procedures Specific Information¶

Process of adding group to opportunity¶

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

EXEC AddGroupToOpportunity GroupId = '123456', OpportunityId = '567890';

Input¶

Name	Type	Description
`OpportunityId`	`String`	The unique identifier (id) for a given opportunity.
`GroupId`	`String`	The unique identifier (id) for a given group that aren't associated with a opportunity.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddOpportunityProduct¶

Creates a new Opportunity Product

Stored Procedures Specific Information¶

Process of adding opportunity product¶

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

EXECUTE AddOpportunityProduct OpportunityId = '123456', ProductName = 'Sample Opportunity Product'

Input¶

Name	Type	Description
`OpportunityId`	`String`	The unique identifier (id) for a given opportunity.
`ProductName`	`String`	The name of the product
`ProductCost`	`String`	The cost of the product
`ProductDiscount`	`String`	The discount value
`ProductDiscountPrice`	`String`	The discount price value
`ProductItemNumber`	`String`	The item number of the product
`ProductPrice`	`String`	The price of the product
`ProductQuantity`	`String`	The product quantity
`ProductTotal`	`String`	The product total

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddOrRemoveContactToActivity¶

Add or remove the contact to an activity

Stored Procedures Specific Information¶

Process of adding or removing contact to activity¶

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

For Adding Contact

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

For Removing Contact

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

Input¶

Name	Type	Description
`ActivityId`	`String`	The ID of the activity to which contact will be associated or disassociated
`ContactId`	`String`	The ID of the contact to be associated or disassociated
`Type`	`String`	Type to specify whether to add or remove contact.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

AddOrRemoveContactToCompany¶

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

Stored Procedures Specific Information¶

Process of adding or removing contact to company¶

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

For Adding Contact To Company

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

For removing contact from company

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

Input¶

Name	Type	Description
`CompanyId`	`String`	The unique identifier (id) for a given company.
`ContactId`	`String`	The unique identifier (id) for a given contact that aren't associated with a company.
`Type`	`String`	Whether to add or remove the contact

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

ClearActivity¶

Clears an activity.

Stored Procedures Specific Information¶

Process of clearing an activity¶

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

EXEC ClearActivity ActivityId = '123456', IsPrivate = true

Input¶

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

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

ClearActivityAlarms¶

Clears an activity alarms

Stored Procedures Specific Information¶

Process of clearing activity alarms¶

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

EXEC ClearActivityAlarms ActivityId = '123456'

Input¶

Name	Type	Description
`ActivityId`	`String`	The unique identifier (id) for a given activity

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

SubscribeContactToHotLeads¶

Subscribes a contact to hot-leads

Stored Procedures Specific Information¶

Process of subscribing contact to hot-leads¶

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

EXEC SubscribeContactToHotLeads ContactId = '123456', Active = true;

Input¶

Name	Type	Description
`ContactId`	`String`	The unique identifier (id) for a given contact
`Active`	`Boolean`	If the contact will be subscribed to hot leads.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

UnclearActivity¶

Unclears an activity.

Stored Procedures Specific Information¶

Process of unclearing activity¶

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

EXEC UnclearActivity ActivityId = '123456';

Input¶

Name	Type	Description
`ActivityId`	`String`	The unique identifier (id) for a given occurrence activity.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Stored Procedure Execution Status

UpdateContactAccessLevel¶

Updates access level of an already existing contact.

Stored Procedures Specific Information¶

Process of updating access level of an already existing contact¶

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

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

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

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

EXECUTE UpdateContactAccessLevel AccessorAggregate = '{
  "Id": "123456",
  "AccessorType": "Accessor Type",
  "Name": "Accessor Name"
}', ContactId = '567890', Type = 'Public'

Input¶

Name	Type	Description
`ContactId`	`String`	The unique identifier (id) for a given contact.
`Type`	`String`	The type of the contact
`Id`	`String`	The unique identifier (id) for a given accessor.
`AccessorType`	`String`	The type of the accessor
`Name`	`String`	The name of the accessor
`AccessorAggregate`	`String`	This is a map of your property names to the values for this object. These are the values that we will sync into corresponding Accessor object.

Result Set Columns¶

Name	Type	Description
`Status`	`String`	Whether or not the access level was updated.

System Tables¶

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

Schema Tables¶

The following tables return database metadata for Act! CRM:

sys_catalogs: Lists the available databases.
sys_schemas: Lists the available schemas.
sys_tables: Lists the available tables and views.
sys_tablecolumns: Describes the columns of the available tables and views.
sys_procedures: Describes the available stored procedures.
sys_procedureparameters: Describes stored procedure parameters.
sys_keycolumns: Describes the primary and foreign keys.
sys_indexes: Describes the available indexes.

Data Source Tables¶

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

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

Query Information Tables¶

The following table returns query statistics for data modification queries:

sys_identity: Returns information about batch operations or single updates.

sys_catalogs¶

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns¶

Name	Type	Description
`CatalogName`	`String`	The database name.

sys_schemas¶

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns¶

Name	Type	Description
`CatalogName`	`String`	The database name.
`SchemaName`	`String`	The schema name.

sys_tables¶

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns¶

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

sys_tablecolumns¶

Describes the columns of the available tables and views.

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

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Activities'

Columns¶

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

sys_procedures¶

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns¶

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

sys_procedureparameters¶

Describes stored procedure parameters.

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

SELECT * FROM sys_procedureparameters WHERE ProcedureName='StoredProc' AND Direction=1 OR Direction=2

Columns¶

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

sys_keycolumns¶

Describes the primary and foreign keys.

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

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Activities'

Columns¶

Name	Type	Description
`CatalogName`	`String`	The name of the database containing the key.
`SchemaName`	`String`	The name of the schema containing the key.
`TableName`	`String`	The name of the table containing the key.
`ColumnName`	`String`	The name of the key column.
`IsKey`	`Boolean`	Whether the column is a primary key in the table referenced in the TableName field.
`IsForeignKey`	`Boolean`	Whether the column is a foreign key referenced in the TableName field.
`PrimaryKeyName`	`String`	The name of the primary key.
`ForeignKeyName`	`String`	The name of the foreign key.
`ReferencedCatalogName`	`String`	The database containing the primary key.
`ReferencedSchemaName`	`String`	The schema containing the primary key.
`ReferencedTableName`	`String`	The table containing the primary key.
`ReferencedColumnName`	`String`	The column name of the primary key.

sys_foreignkeys¶

Describes the foreign keys.

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

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns¶

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

sys_primarykeys¶

Describes the primary keys.

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

SELECT * FROM sys_primarykeys

Columns¶

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

sys_indexes¶

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

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

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns¶

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

sys_connection_props¶

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

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

jdbc:cdata:actcrm:config:

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

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

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns¶

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

sys_sqlinfo¶

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

Discovering the Data Source's SELECT Capabilities¶

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

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

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

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

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

Columns¶

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

sys_identity¶

Returns information about attempted modifications.

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

SELECT * FROM sys_identity

Columns¶

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

Advanced Configurations Properties¶

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

Authentication

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

SSL

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

Schema

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

Miscellaneous

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

Authentication¶

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

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

ActCloudName¶

The handle assigned to the Act! Premium Cloud account.

Data Type¶

string

Default Value¶

""

Remarks¶

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

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

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

ActDatabase¶

The Act! Database to connect to.

Data Type¶

string

Default Value¶

""

Remarks¶

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

URL¶

The URL of the ActCRM account.

Data Type¶

string

Default Value¶

""

Remarks¶

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

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

ActEdition¶

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

Possible Values¶

Act CRM, Act Premium Cloud

Data Type¶

string

Default Value¶

Act CRM

Remarks¶

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

User¶

The Act! CRM user account used to authenticate.

Data Type¶

string

Default Value¶

""

Remarks¶

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

Password¶

The password used to authenticate the user.

Data Type¶

string

Default Value¶

""

Remarks¶

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

SSL¶

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

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

SSLServerCert¶

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

Data Type¶

string

Default Value¶

""

Remarks¶

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

This property can take the following forms:

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

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

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

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

Schema¶

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

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

Location¶

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

Data Type¶

string

Default Value¶

%APPDATA%\ActCRM Data Provider\Schema

Remarks¶

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

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

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

BrowsableSchemas¶

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

Data Type¶

string

Default Value¶

""

Remarks¶

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

Tables¶

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

Data Type¶

string

Default Value¶

""

Remarks¶

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

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

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

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

Views¶

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

Data Type¶

string

Default Value¶

""

Remarks¶

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

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

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

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

Miscellaneous¶

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

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

IncludeCustomFields¶

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

Data Type¶

bool

Default Value¶

true

Remarks¶

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

MaxRows¶

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

Data Type¶

int

Default Value¶

-1

Remarks¶

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

Other¶

These hidden properties are used only in specific use cases.

Data Type¶

string

Default Value¶

""

Remarks¶

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

Specify multiple properties in a semicolon-separated list.

Integration and Formatting¶

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

Pagesize¶

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

Data Type¶

int

Default Value¶

5000

Remarks¶

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

PseudoColumns¶

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

Data Type¶

string

Default Value¶

""

Remarks¶

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

RowScanDepth¶

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

Data Type¶

int

Default Value¶

500

Remarks¶

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

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

Timeout¶

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

Data Type¶

int

Default Value¶

60

Remarks¶

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

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

TypeDetectionScheme¶

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

Possible Values¶

None, RowScan

Data Type¶

string

Default Value¶

RowScan

Remarks¶

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

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

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

UserDefinedViews¶

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

Data Type¶

string

Default Value¶

""

Remarks¶

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

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