Getting Started

This page provides a guide to Establishing a Connection to Act! CRM in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Act! CRM

Establishing a Connection shows how to authenticate to Act! CRM and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Act! CRM through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Establishing a Connection

Connect to Act! CRM by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting 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.
Password: The password used to authenticate to the Act! database.
URL: The URL where the Act! CRM account is hosted. For example: http://serverName/.
ActDatabase: The name of the Act! database you want to connect to. This is found by going to the About Act! Premium menu of your account, found at the top right of the page, in the ? menu. Use the Database Name in the window that appears.
ActCloudRegion (Act! Premium Cloud only): The Region of the Act! Premium Cloud account.

SSL Configuration

Customizing the SSL Configuration

To enable TLS, set the following:

URL: Prefix the connection string with https://

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

To specify another certificate, see the SSLServerCert connection property.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort: the TCP port that the proxy server is running on.
ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
ProxyUser: the username of a user account registered with the proxy server.
ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
To tunnel the connection, set FirewallType to TUNNEL.
To authenticate, specify FirewallUser and FirewallPassword.
To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Data Model

CData Cloud models entities in the Act! CRM Premium APIs as relational Views. The Cloud leverages the Act! Web API for bidirectional access to Act! CRM data from Act! Premium on-premises and Act! Premium Cloud. The Cloud 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 Cloud models the data in Act! CRM as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Act! CRM Tables


Name	Description
Activities	[DEPRECATED] 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.
CompanyAccessControl	Query and update the access control level for a company.
CompanyContacts	Query, delete the Contacts belonging to a Company.
ContactNotes	Query and delete the Notes for a given Act! CRM Contact.
Contacts	Create, update, delete and query the Contacts in your Act! CRM database.
Documents	Query, create and delete the document history records.
GroupAccessControl	Query and update the access control level for a group.
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.
OpportunityAccessControl	Query and update the access control level for an opportunity.
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.
OpportunityProcesses	Query, create, update and delete opportunity process definitions.
OpportunityProcessStages	Query and create stage definitions within an opportunity process.
OpportunityProducts	Update, delete and query the Products associated with an Opportunity.
Products	Create, update, delete and query the Products in your A ct! CRM database.
ProductsInternal	Create, update, delete and query the Products in your A ct! CRM database.
RecurringTasks	Query, create, update and delete recurring task records.
SecondaryContacts	Query, create, update and delete secondary contacts for a contact.
Tasks	Query, create, update and delete the tasks.
Teams	Query, create, update and delete teams.
Users	Query, create and delete user accounts.

Activities

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

Table Specific Information

Select

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

The Cloud uses the Act! CRM API to process search criteria that refer to the supported fields. The Cloud processes other filters client-side within the Cloud. 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	False	Displays the type of the activity.
AttachmentDisplayName	String	False	Gets presentation name of the attachment.
AttachmentFileExtension	String	False	Gets the extension of the Attachment
AttachmentFileName	String	False	Gets a string representing the directory's full path.
AttachmentFileSize	Int	False	Gets the size (bytes) of the attachments.
AttachmentFileSizeDisplay	String	False	Gets the displayable text representation of the attachment
AttachmentFileType	String	False	Gets the registered system file type name of the attachment
AttachmentLastModified	Datetime	False	Gets the last modified date of the attachment
AttachmentPersonal	Bool	False	Indicates whether the attachment is bound for the personal supplemental files or the workgroup supplemental files.
CompaniesAggregate	String	False	List of companies that are associated to this activity.
ContactsAggregate	String	False	List of contacts that are associated to this activity.
Created	Datetime	True	The date and time the activity was created.
Details	String	False	Additional detailed information about the activity.
Edited	Datetime	True	The date and time the activity was last updated.
EndTime	Datetime	False	The time the activity is to end
GroupsAggregate	String	False	List a groups that are associated to this activity.
IsCleared	Bool	False	Indicates that the activity has been cleared.
IsPrivate	Bool	False	Indicates elevated security that only the creator/owner has access to this activity.
IsTimeless	Bool	False	Indicates whether a specifies action is to be completed at a specific time-of-day.
Location	String	False	Describes the physical location that the activity is to take place.
OpportunitiesAggregate	String	False	List a opportunities that are associated to this activity.
RecurSpecDayDayAsInt	Int	False	An integer representation of the day of the week portion of a recurrence pattern.
RecurSpecDayDaysOfWeek	String	False	Representation of the day of the week portion of a recurrence pattern.
RecurSpecDayDayType	String	False	Describing the day-portion of the recurrence pattern.
RecurSpecDayOrdinal	String	False	Describes recurrence in relative terms for certain recurrence patterns (i.e. First, Third, Last) .
RecurSpecDayTypedDay	String	False	Describing the TypedDay (Day, WeekDay or Weekend Day) for the recurrence pattern when DayType is
RecurSpecFrequency	Int	False	Represents how often for the recurrence patterns
RecurSpecIsEndless	Bool	False	Flag denoting whether an end date has been specified for the pattern. Note: If false, the following defaults are used: For Daily and Weekly: 2 years beyond the start date For Monthly and Yearly: June 6, 2073.
RecurSpecMonth	Int	False	Represents the specific month for certain recurrence patterns
RecurSpecRecurType	String	False	Represents a unit of time used to separate recurences (i.e. Daily, Weekly, Monthly, Yearly).
RecurSpecSeriesEnd	Datetime	False	Specifiying the ending time for a given day.
RecurSpecSeriesStart	Datetime	False	Specifiying the starting time for a given day.
ScheduledBy	String	True	The contact who created this activity.
ScheduledFor	String	False	The name of the activity's organizer.
SeriesID	String	True	A unique identifier that represents the activity. The id that is returned is based on if it is a recurring or not. Recurrent activity: RecurSourceActivityId or Single Occurence activity: ActivityID
StartTime	Datetime	False	The time the activity is to begin
Subject	String	False	A description of the action to be completed.
IsAlarmCleared	Bool	False	Boolean value of IsAlarmCleared.
IsAlarmed	Bool	False	Boolean value of IsAlarmed.
LeadMinutes	Int	False	Lead minutes.
AlarmDue	String	False	Alarm due.
ExternalId	String	False	External Id.
AccessorId	String	False	Accessor Id.
ScheduledById	String	False	Scheduled Id.
ScheduledForId	String	False	Scheduled for Id.

Companies

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

Table Specific Information

Select

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

The Cloud uses the Act! CRM API to process search criteria that refer to the supported fields. The Cloud processes other filters client-side within the Cloud. 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	False	Owner of the company record.
ReferredBy	String	False	Mention or allude to.
Region	String	False	A part of a country, of the world, etc., that is different or separate from other parts in some way.
Revenue	Decimal	False	The amount of money that this company actually receives during a specific period, including discounts and deductions for returned merchandise.
ShippingAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
ShippingAddressCountry	String	False	A nation with its own government, occupying a particular territory.
ShippingAddressLine1	String	False	Primary street address
ShippingAddressLine2	String	False	Secondary street address
ShippingAddressLine3	String	False	Miscellaneous street address
ShippingAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
ShippingAddressState	String	False	A nation or territory considered as an organized political community under one government.
SicCode	String	False	Standard Industrial Classification (SIC) codes are four-digit numerical codes assigned by the U.S. government to business establishments to identify the primary business of the establishment.
Territory	String	False	Geographical area.
TickerSymbol	String	False	An arrangement of characters (usually letters) representing a particular security listed on an exchange or otherwise traded publicly.
TollFreeExtension	String	False	The company's toll free phone number.
TollFreePhone	String	False	The company's toll free phone number.
Website	String	False	The company's website.
AddressLatitude	Decimal	False	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
AddressLongitude	Decimal	False	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
BillingAddressLatitude	Decimal	False	The Billing address latitude.
BillingAddressLongitude	Decimal	False	The Billing address longitude.
ShippingAddressLatitude	Decimal	False	The Shipping address latitude.
ShippingAddressLongitude	Decimal	False	The Shipping address longitude.
HasDivisions	Boolean	False	This readonly property indicates that this company has divisions.
ImportDate	Datetime	False	This readonly property displays the date that the opportunity was imported into the system.
IsPrivate	Boolean	False	Indicates elevated security that only the creator/owner has access to this company.
RootParentId	String	False	The unique identifier (id) of the parent company.
RecordManagerID	String	False	The record manager.
CustomFields	String	False	Custom fields.
RecordManager	String	False	Manager of the activity.
Acl	String	True	The access control level of the record.

CompanyAccessControl

Query and update the access control level for a company.

Table Specific Information

Select

CompanyId supports the '=' operator.

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

SELECT * FROM CompanyAccessControl WHERE CompanyId = '5d940c2d-e09e-4204-ad0a-375f73a550a7'

SELECT * FROM CompanyAccessControl

Update

Any column where ReadOnly=False can be updated. Specify the CompanyId when updating a company access control record.

UPDATE CompanyAccessControl SET Type = 'Private' WHERE CompanyId = '5d940c2d-e09e-4204-ad0a-375f73a550a7'

Columns


Name	Type	ReadOnly	Description
CompanyId [KEY]	String	True	The unique identifier for the company.
Type	String	False	Defines the company's access level. The allowed values are Public, Private, LimitedAccess.
AccessorsAggregate	String	False	The users or teams that have been granted limited access to the company.

CompanyContacts

Query, delete the Contacts belonging to a Company.

Table Specific Information

Select

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

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

SELECT * FROM CompanyContacts WHERE Fullname = 'Contact name'

Delete

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

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The unique identifier (id) for this contact.
CompanyId [KEY]	String	False	The unique identifier (id) of the company to retrieve contacts from.
Created	Datetime	False	Created data of the contact.
FullName	String	False	The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
AltEmailAddress	String	False	A unique electronic address that an individual can be contacted.
AlternateExtension	String	False	The contact's alternate phone number extension.
AlternatePhone	String	False	The contact's alternate phone number.
Birthday	Datetime	False	The anniversary of the day on which a person was born.
BusinessAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry	String	False	A nation with its own government, occupying a particular territory.
BusinessAddressLine1	String	False	Primary street address
BusinessAddressLine2	String	False	Secondary street address
BusinessAddressLine3	String	False	Miscellaneous street address
BusinessAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState	String	False	A nation or territory considered as an organized political community under one government.
BusinessExtension	String	False	The contact's business phone number extension.
BusinessPhone	String	False	The contact's business phone number.
Company	String	False	The organization that this contact is associated with.
ContactType	String	False	The type of contact: Contact, Secondary, or User.
Department	String	False	An area of special expertise or responsibility.
Edited	Datetime	False	Edited date of the contact.
EditedBy	String	False	User who edited the contact.
EmailAddress	String	False	A unique electronic address that an individual can be contacted.
FaxExtension	String	False	The contact's fax phone number extension.
FaxPhone	String	False	The contact's fax phone number.
FirstName	String	False	A personal given name.
HomeAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry	String	False	A nation with its own government, occupying a particular territory.
HomeAddressLine1	String	False	Primary street address
HomeAddressLine2	String	False	Secondary street address
HomeAddressLine3	String	False	Miscellaneous street address
HomeAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState	String	False	A nation or territory considered as an organized political community under one government.
HomeExtension	String	False	The contact's home phone extension.
HomePhone	String	False	The contact's home phone number.
IdStatus	String	False	The relative social or professional position of an individual within a group or organization.
IsUser	Bool	False	An indicator if this contact is also a user within the system.
JobTitle	String	False	The title or position this contact holds.
LastName	String	False	The family name or surname.
LastResults	String	False	Identifies last communication or action with a contact.
Latitude	Decimal	False	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude	Decimal	False	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID	String	False	An online identifier alias.
MiddleName	String	False	A name between one's first name and surname.
MobileExtension	String	False	The contact's mobile phone extension.
MobilePhone	String	False	The contact's mobile phone number.
NamePrefix	String	False	A patronymic prefix.
NameSuffix	String	False	Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension	String	False	The contact's pager phone number extension
PagerPhone	String	False	The contact's pager phone number.
PersonalEmailAddress	String	False	A unique electronic address that an individual can be contacted.
RecordOwner	String	False	Owner user of the record.
ReferredBy	String	False	Indiciates how this contact came about.
Salutation	String	False	A gesture or utterance made as a greeting or acknowledgment.
Website	String	False	The contact's web site.
IsFavorite	Bool	False	Assign this contact as a favorite.
IsImported	Bool	False	Identifies last communication or action with a contact.
ImportDate	Datetime	False	This readonly property displays the date that the opportunity was imported into the system.
IsPrivate	Bool	False	Identifies last communication or action with a contact.
LastEmail	String	False	The last email.
LastAttempt	String	False	The last attempt.
LastReach	String	False	The last reach.
LastMeeting	String	False	The last meeting.
LastLetterSent	String	False	The last letter sent.
AmaScore	Int	False	The contact's.
BusinessAddressLatitude	Decimal	False	The business address latitude.
BusinessAddressLongitude	Decimal	False	The business address longitude.
BusinessCountryCode	Int	False	The contact's business phone country code.
BusinessMaskFormat	String	False	The contact's business phone mask format.
MobileCountryCode	Int	False	The contact's mobile phone country code.
MobileMaskFormat	String	False	The contact's mobile phone mask format.
FaxCountryCode	Int	False	The contact's fax phone country code.
FaxMaskFormat	String	False	The contact's fax phone mask format.
HomeAddressLatitude	Decimal	False	The Home address latitude.
HomeAddressLongitude	Decimal	False	The Home address longitude.
HomeCountryCode	Int	False	The contact's home phone country code.
HomeMaskFormat	String	False	The contact's home phone mask format.
AlternateCountryCode	Int	False	The contact's alternate phone country code.
AlternateMaskFormat	String	False	The contact's alternate phone mask format.
PagerCountryCode	Int	False	The contact's pager phone country code.
PagerMaskFormat	String	False	The contact's pager phone mask format.
AemOptOut	Boolean	False	AemOptOut.
AemBounceBack	Boolean	False	AemBounceBack.
RecordManagerID	String	False	The record manager.
QuickbooksId	String	False	Quickbooks external Id to match record with quickbooks contact.
NylasContactId	String	False	Nylas contact Id to match record with a contact.
CustomFields	String	False	Custom fields.
RecordManager	String	False	Manager of the activity.

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 '<=,<,=,!=,>,>=,CONTAINS' operator.
CreateUserID supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
IsPrivate supports the '=' operator.
NoteText supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
DisplayDate supports the '<=,<,=,!=,>,>=' operator.
AttachmentDisplayName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AttachmentFileName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AttachmentLastModified supports the '<=,<,=,!=,>,>=' operator.
AttachmentPersonal supports the '=' operator.

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

SELECT * FROM ContactNotes WHERE ContactId = '12345'

Delete

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

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

Columns


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

Contacts

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

Table Specific Information

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

Select

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

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

Insert

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

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

Update

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

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

Delete

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

DELETE FROM Contacts WHERE Id = '12345'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The unique identifier (id) for this contact.
Created	Datetime	True	Created date of the contact.
FullName	String	True	The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
Company	String	False	The organization that this contact is associated with.
AltEmailAddress	String	False	A unique electronic address that an individual can be contacted.
AlternateExtension	String	False	The contact's alternate phone number extension.
AlternatePhone	String	False	The contact's alternate phone number.
Birthday	Datetime	False	The anniversary of the day on which a person was born.
BusinessAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry	String	False	A nation with its own government, occupying a particular territory.
BusinessAddressLine1	String	False	Primary street address
BusinessAddressLine2	String	False	Secondary street address
BusinessAddressLine3	String	False	Miscellaneous street address
BusinessAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState	String	False	A nation or territory considered as an organized political community under one government.
BusinessExtension	String	False	The contact's business phone number extension.
BusinessPhone	String	False	The contact's business phone number.
CompanyID	String	False	The unique identifier (companyID) for a given companies.
ContactType	String	True	The type of contact: Contact, Secondary, or User.
Department	String	False	An area of special expertise or responsibility.
Edited	Datetime	True	Edited date of the contact.
EditedBy	String	True	User who edited the contact.
EmailAddress	String	False	A unique electronic address that an individual can be contacted.
FaxExtension	String	False	The contact's fax phone number extension.
FaxPhone	String	False	The contact's fax phone number.
FirstName	String	False	A personal given name.
HomeAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry	String	False	A nation with its own government, occupying a particular territory.
HomeAddressLine1	String	False	Primary street address
HomeAddressLine2	String	False	Secondary street address
HomeAddressLine3	String	False	Miscellaneous street address
HomeAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState	String	False	A nation or territory considered as an organized political community under one government.
HomeExtension	String	False	The contact's home phone extension.
HomePhone	String	False	The contact's home phone number.
IdStatus	String	False	The relative social or professional position of an individual within a group or organization.
IsUser	Bool	True	An indicator if this contact is also a user within the system.
JobTitle	String	False	The title or position this contact holds.
LastName	String	False	The family name or surname.
LastResults	String	False	Identifies last communication or action with a contact.
Latitude	Decimal	False	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude	Decimal	False	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID	String	True	An online identifier alias.
MiddleName	String	False	A name between one's first name and surname.
MobileExtension	String	False	The contact's mobile phone extension.
MobilePhone	String	False	The contact's mobile phone number.
NamePrefix	String	False	A patronymic prefix.
NameSuffix	String	False	Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension	String	False	The contact's pager phone number extension
PagerPhone	String	False	The contact's pager phone number.
PersonalEmailAddress	String	False	A unique electronic address that an individual can be contacted.
RecordOwner	String	True	Owner user of the contact.
ReferredBy	String	False	Indiciates how this contact came about.
Salutation	String	True	A gesture or utterance made as a greeting or acknowledgment.
Website	String	False	The contact's web site.
IsFavorite	Bool	False	Assign this contact as a favorite.
IsImported	Bool	False	Identifies last communication or action with a contact.
ImportDate	Datetime	False	This readonly property displays the date that the opportunity was imported into the system.
IsPrivate	Bool	False	Identifies last communication or action with a contact.
LastEmail	String	False	Last email.
LastAttempt	String	False	Last attempt.
LastReach	String	False	Last reach.
LastMeeting	String	False	Last meeting.
LastLetterSent	String	False	Last letter sent.
AmaScore	Int	False	The contact's.
BusinessAddressLatitude	Decimal	False	Business address latitude.
BusinessAddressLongitude	Decimal	False	Business address longitude.
BusinessCountryCode	Int	False	The contact's business phone country code.
BusinessMaskFormat	String	False	The contact's business phone mask format.
MobileCountryCode	Int	False	The contact's mobile phone country code.
MobileMaskFormat	String	False	The contact's mobile phone mask format.
FaxCountryCode	Int	False	The contact's fax phone country code.
FaxMaskFormat	String	False	The contact's fax phone mask format.
HomeAddressLatitude	Decimal	False	Home address latitude.
HomeAddressLongitude	Decimal	False	Home address longitude.
HomeCountryCode	Int	False	The contact's home phone country code.
HomeMaskFormat	String	False	The contact's home phone mask format.
AlternateCountryCode	Int	False	The contact's alternate phone country code.
AlternateMaskFormat	String	False	The contact's alternate phone mask format.
PagerCountryCode	Int	False	The contact's pager phone country code.
PagerMaskFormat	String	False	The contact's pager phone mask format.
AemOptOut	Bool	False	AemOptOut.
AemBounceBack	Bool	False	AemBounceBack.
RecordManagerId	String	False	The record manager.
QuickBooksId	String	False	Quickbooks external Id to match record with quickbooks contact.
NylasContactId	String	False	Nylas contact Id to match record with a contact.
CustomFields	String	False	Custom fields.
RecordManager	String	False	Record manager.
Acl	String	True	The access control level of the record.

Documents

Query, create and delete the document history records.

Table Specific Information

Select

Id supports the '=' operator.

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

SELECT * FROM Documents
SELECT * FROM Documents WHERE Id = '12345'

Insert

To insert a document, you must specify at least one typeid, contact, start time and end time.

INSERT INTO Documents (TypeID, StartTime, EndTime, ContactsAggregate) VALUES ('-1', '2026-07-07T05:20:00.000-04:00', '2026-07-07T05:25:00.000-04:00', '[{\"id\":\"972df268-a4db-41db-bc32-4af89ab3034b\"}]')

Delete

Documents can be deleted by providing an Id and issuing a DELETE statement.

DELETE FROM Documents WHERE Id = 'f2e74900-c3a9-4dbd-89f6-3a3603d94802'

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 created the history
Details	String	False	Details of history
Duration	String	False	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
HistoryTypeActivityTypeId	Integer	True	Activity type ID of the history type
HistoryTypeActivityTypeName	String	True	Activity type name of the 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	True	Outlook Id
RecordManager	String	False	Record Manager
RecordManagerID	String	False	Id of Record Manager
Regarding	String	False	Tells what history is about
StartTime	Datetime	False	Start Time
ContactCompany	String	True	The company of the contact associated with this document.
ContactEmail	String	True	The email of the contact associated with this document.
ContactPhone	String	True	The phone number of the contact associated with this document.
ContactPhoneExt	String	True	The phone extension of the contact associated with this document.
CreateUserName	String	True	The username of the user who created this document.
EditUserID	String	True	The ID of the user who last edited this document.
EditUserName	String	True	The username of the user who last edited this document.

GroupAccessControl

Query and update the access control level for a group.

Table Specific Information

Select

GroupId supports the '=' operator.

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

SELECT * FROM GroupAccessControl WHERE GroupId = '5d940c2d-e09e-4204-ad0a-375f73a550a7'

SELECT * FROM GroupAccessControl

Update

Any column where ReadOnly=False can be updated. Specify the GroupId when updating a group access control record.

UPDATE GroupAccessControl SET Type = 'Private' WHERE GroupId = '5d940c2d-e09e-4204-ad0a-375f73a550a7'

Columns


Name	Type	ReadOnly	Description
GroupId [KEY]	String	True	The unique identifier for the group.
Type	String	False	Defines the group's access level. The allowed values are Public, Private, LimitedAccess.
AccessorsAggregate	String	False	The users or teams that have been granted limited access to the group.

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

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

SELECT * FROM GroupContacts WHERE GroupId = '12345'

SELECT * FROM GroupContacts WHERE FullName = 'Contact name'

Delete

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

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The unique identifier (id) for this contact.
GroupId [KEY]	String	False	The unique identifier (id) of the group to retrieve contacts from.
Created	Datetime	False	Created date of the group.
FullName	String	False	The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
AltEmailAddress	String	False	A unique electronic address that an individual can be contacted.
AlternateExtension	String	False	The contact's alternate phone number extension.
AlternatePhone	String	False	The contact's alternate phone number.
Birthday	Datetime	False	The anniversary of the day on which a person was born.
BusinessAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry	String	False	A nation with its own government, occupying a particular territory.
BusinessAddressLine1	String	False	Primary street address
BusinessAddressLine2	String	False	Secondary street address
BusinessAddressLine3	String	False	Miscellaneous street address
BusinessAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState	String	False	A nation or territory considered as an organized political community under one government.
BusinessExtension	String	False	The contact's business phone number extension.
BusinessPhone	String	False	The contact's business phone number.
Company	String	False	The organization that this contact is associated with.
CompanyID	String	False	The unique identifier (companyID) for a given companies.
ContactType	String	False	The type of contact: Contact, Secondary, or User.
Department	String	False	An area of special expertise or responsibility.
Edited	Datetime	False	Edited date of the group.
EditedBy	String	False	User who edited the group.
EmailAddress	String	False	A unique electronic address that an individual can be contacted.
FaxExtension	String	False	The contact's fax phone number extension.
FaxPhone	String	False	The contact's fax phone number.
FirstName	String	False	A personal given name.
HomeAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry	String	False	A nation with its own government, occupying a particular territory.
HomeAddressLine1	String	False	Primary street address
HomeAddressLine2	String	False	Secondary street address
HomeAddressLine3	String	False	Miscellaneous street address
HomeAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState	String	False	A nation or territory considered as an organized political community under one government.
HomeExtension	String	False	The contact's home phone extension.
HomePhone	String	False	The contact's home phone number.
IdStatus	String	False	The relative social or professional position of an individual within a group or organization.
IsUser	Bool	False	An indicator if this contact is also a user within the system.
JobTitle	String	False	The title or position this contact holds.
LastName	String	False	The family name or surname.
LastResults	String	False	Identifies last communication or action with a contact.
Latitude	Decimal	False	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude	Decimal	False	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID	String	False	An online identifier alias.
MiddleName	String	False	A name between one's first name and surname.
MobileExtension	String	False	The contact's mobile phone extension.
MobilePhone	String	False	The contact's mobile phone number.
NamePrefix	String	False	A patronymic prefix.
NameSuffix	String	False	Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension	String	False	The contact's pager phone number extension
PagerPhone	String	False	The contact's pager phone number.
PersonalEmailAddress	String	False	A unique electronic address that an individual can be contacted.
RecordOwner	String	False	Owner user of the record.
ReferredBy	String	False	Indiciates how this contact came about.
Salutation	String	False	A gesture or utterance made as a greeting or acknowledgment.
Website	String	False	The contact's web site.
IsFavorite	Bool	False	Assign this contact as a favorite.
IsImported	Bool	False	Identifies last communication or action with a contact.
ImportDate	Datetime	False	This readonly property displays the date that the opportunity was imported into the system.
IsPrivate	Bool	False	Identifies last communication or action with a contact.
LastEmail	String	False	Last email.
LastAttempt	String	False	Last attempt.
LastReach	String	False	Last reach.
LastMeeting	String	False	Last meeting.
LastLetterSent	String	False	Last letter sent.
AmaScore	Int	False	The contact's.
BusinessAddressLatitude	Decimal	False	Business address latitude.
BusinessAddressLongitude	Decimal	False	Business address longitude.
BusinessCountryCode	Int	False	The contact's business phone country code.
BusinessMaskFormat	String	False	The contact's business phone mask format.
MobileCountryCode	Int	False	The contact's mobile phone country code.
MobileMaskFormat	String	False	The contact's mobile phone mask format.
FaxCountryCode	Int	False	The contact's fax phone country code.
FaxMaskFormat	String	False	The contact's fax phone mask format.
HomeAddressLatitude	Decimal	False	Home address latitude.
HomeAddressLongitude	Decimal	False	Home address longitude.
HomeCountryCode	Int	False	The contact's home phone country code.
HomeMaskFormat	String	False	The contact's home phone mask format.
AlternateCountryCode	Int	False	The contact's alternate phone country code.
AlternateMaskFormat	String	False	The contact's alternate phone mask format.
PagerCountryCode	Int	False	The contact's pager phone country code.
PagerMaskFormat	String	False	The contact's pager phone mask format.
AemOptOut	Bool	False	AemOptOut.
AemBounceBack	Bool	False	AemBounceBack.
RecordManagerId	String	False	The record manager.
QuickBooksId	String	False	Quickbooks external Id to match record with quickbooks contact.
NylasContactId	String	False	Nylas contact Id to match record with a contact.
CustomFields	String	False	Custom fields.
RecordManager	String	False	Record manager.

Groups

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

Table Specific Information

Select

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

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

SELECT * FROM Groups WHERE Name = 'Group name'

Insert

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

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

Update

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

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

Delete

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

DELETE FROM Groups WHERE Id = '12345'

Columns


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

Histories

Get, Create, Update or Delete all history items.

Table Specific Information

Select

Id supports the '=' operator.

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

SELECT * FROM Histories

SELECT * FROM Histories where ID = '1234'

Insert

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

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

INSERT INTO Histories (ContactsAggregate, StartTime, EndTime) VALUES ('[{\"id\":\"a58016a6-780c-48c0-84ff-b6b6b085eb8c\",\"displayName\": \"Kristian Kate\",\"company\": \"Smith Legal Services (demo)\",\"isInvited\": true}]', '2025-02-13 10:02:00', '2025-02-14 10:02:00')

Update

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

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

Delete

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

DELETE FROM Histories WHERE Id = '12345'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Id of the history item created
AttachmentDisplayName	String	True	Name of the attachment related to a history
AttachmentFileExtension	String	True	FileExtension of the attachment related to a history
AttachmentFileName	String	True	FileName of the attachment related to a history
AttachmentFileSize	Integer	True	File Size of the attachment related to a history
AttachmentFileSizeDisplay	String	True	File Size Display of the attachment related to a history
AttachmentFileType	String	True	FileType of the attachment related to a history
AttachmentLastModified	Datetime	True	File Last Modified date of the attachment related to a history
AttachmentPersonal	Boolean	True	Attachment Personal
CompaniesAggregate	String	False	Companies associated with a history
ContactsAggregate	String	False	Contacts associated with a history
Created	Datetime	True	The datetime when history was created
CreateUserID	String	True	User Id of the user who craeted the history
Details	String	False	Details of history
Duration	String	False	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	False	Record Manager
RecordManagerID	String	False	Id of Record Msnager
Regarding	String	False	Tells what history is about
StartTime	Datetime	False	Start Time
ContactCompany	String	True	The company of the contact associated with this history.
ContactEmail	String	True	The email of the contact associated with this history.
ContactPhone	String	True	The phone number of the contact associated with this history.
ContactPhoneExt	String	True	The phone extension of the contact associated with this history.
CreateUserName	String	True	The username of the user who created this history.
EditUserID	String	True	The ID of the user who last edited this history.
EditUserName	String	True	The username of the user who last edited this history.

Notes

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

Table Specific Information

Select

Id supports the '=' operator.

The Cloud 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 Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side:

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

Insert

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

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

Update

Any column where ReadOnly=False can be updated.

UPDATE Notes SET isPrivate = false WHERE Id = 123456

Delete

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

DELETE FROM Notes WHERE Id = 123456

Columns


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

Opportunities

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

Table Specific Information

Select

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

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

SELECT * FROM Opportunities WHERE Name = 'Opportunity name'

Insert

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

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

Update

Any column where ReadOnly=False can be updated.

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

Delete

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

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

Columns


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

OpportunityAccessControl

Query and update the access control level for an opportunity.

Table Specific Information

Select

OpportunityId supports the '=' operator.

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

SELECT * FROM OpportunityAccessControl WHERE OpportunityId = '5d940c2d-e09e-4204-ad0a-375f73a550a7'

SELECT * FROM OpportunityAccessControl

Update

Any column where ReadOnly=False can be updated. Specify the OpportunityId when updating an opportunity access control record.

UPDATE OpportunityAccessControl SET Type = 'Private' WHERE OpportunityId = '5d940c2d-e09e-4204-ad0a-375f73a550a7'

Columns


Name	Type	ReadOnly	Description
OpportunityId [KEY]	String	True	The unique identifier for the opportunity.
Type	String	False	Defines the opportunity's access level. The allowed values are Public, Private, LimitedAccess.
AccessorsAggregate	String	False	The users or teams that have been granted limited access to the opportunity.

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

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

SELECT * FROM OpportunityCompanies WHERE OpportunityId = '12345'

SELECT * FROM OpportunityCompanies WHERE Name = 'Company name'

Delete

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

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

Columns


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

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

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

SELECT * FROM OpportunityContacts WHERE OpportunityId = '12345'

SELECT * FROM OpportunityContacts WHERE FullName = 'Contact name'

Delete

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

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The unique identifier (id) for this contact.
OpportunityId [KEY]	String	False	The unique identifier (id) for the opportunity to query contacts from.
Created	Datetime	False	Created date of the contact.
FullName	String	False	The first name, any middle names, and surname of a contact. This may also include name prefixes and sufixes that are defined within Act!
AltEmailAddress	String	False	A unique electronic address that an individual can be contacted.
AlternateExtension	String	False	The contact's alternate phone number extension.
AlternatePhone	String	False	The contact's alternate phone number.
Birthday	Datetime	False	The anniversary of the day on which a person was born.
BusinessAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
BusinessAddressCountry	String	False	A nation with its own government, occupying a particular territory.
BusinessAddressLine1	String	False	Primary street address
BusinessAddressLine2	String	False	Secondary street address
BusinessAddressLine3	String	False	Miscellaneous street address
BusinessAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
BusinessAddressState	String	False	A nation or territory considered as an organized political community under one government.
BusinessExtension	String	False	The contact's business phone number extension.
BusinessPhone	String	False	The contact's business phone number.
Company	String	False	The organization that this contact is associated with.
CompanyID	String	False	The unique identifier (companyID) for a given companies.
ContactType	String	False	The type of contact: Contact, Secondary, or User.
Department	String	False	An area of special expertise or responsibility.
Edited	Datetime	False	Edited date of the contact.
EditedBy	String	False	User who edited the contact.
EmailAddress	String	False	A unique electronic address that an individual can be contacted.
FaxExtension	String	False	The contact's fax phone number extension.
FaxPhone	String	False	The contact's fax phone number.
FirstName	String	False	A personal given name.
HomeAddressCity	String	False	An inhabited place of greater size, population, or importance than a town or village.
HomeAddressCountry	String	False	A nation with its own government, occupying a particular territory.
HomeAddressLine1	String	False	Primary street address
HomeAddressLine2	String	False	Secondary street address
HomeAddressLine3	String	False	Miscellaneous street address
HomeAddressPostalCode	String	False	A code of letters and digits added to a postal address to aid in the sorting of mail.
HomeAddressState	String	False	A nation or territory considered as an organized political community under one government.
HomeExtension	String	False	The contact's home phone extension.
HomePhone	String	False	The contact's home phone number.
IdStatus	String	False	The relative social or professional position of an individual within a group or organization.
IsUser	Bool	False	An indicator if this contact is also a user within the system.
JobTitle	String	False	The title or position this contact holds.
LastName	String	False	The family name or surname.
LastResults	String	False	Identifies last communication or action with a contact.
Latitude	Decimal	False	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
Longitude	Decimal	False	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
MessengerID	String	False	An online identifier alias.
MiddleName	String	False	A name between one's first name and surname.
MobileExtension	String	False	The contact's mobile phone extension.
MobilePhone	String	False	The contact's mobile phone number.
NamePrefix	String	False	A patronymic prefix.
NameSuffix	String	False	Provides additional information about the person that identifies an individual position, educational degree, accreditation, orffice, or honors.
PagerExtension	String	False	The contact's pager phone number extension
PagerPhone	String	False	The contact's pager phone number.
PersonalEmailAddress	String	False	A unique electronic address that an individual can be contacted.
RecordOwner	String	False	Owner user of the record.
ReferredBy	String	False	Indiciates how this contact came about.
Salutation	String	False	A gesture or utterance made as a greeting or acknowledgment.
Website	String	False	The contact's web site.
IsFavorite	Bool	False	Assign this contact as a favorite.
IsImported	Bool	False	Identifies last communication or action with a contact.
ImportDate	Datetime	False	This readonly property displays the date that the opportunity was imported into the system.
IsPrivate	Bool	False	Identifies last communication or action with a contact.
LastEmail	String	False	Last email.
LastAttempt	String	False	Last attempt.
LastReach	String	False	Last reach.
LastMeeting	String	False	Last meeting.
LastLetterSent	String	False	Last letter sent.
AmaScore	Int	False	The contact's.
BusinessAddressLatitude	Decimal	False	The business address latitude.
BusinessAddressLongitude	Decimal	False	The business address longitude.
BusinessCountryCode	Int	False	The contact's business phone country code.
BusinessMaskFormat	String	False	The contact's business phone mask format.
MobileCountryCode	Int	False	The contact's mobile phone country code.
MobileMaskFormat	String	False	The contact's mobile phone mask format.
FaxCountryCode	Int	False	The contact's fax phone country code.
FaxMaskFormat	String	False	The contact's fax phone mask format.
HomeAddressLatitude	Decimal	False	The home address latitude.
HomeAddressLongitude	Decimal	False	The home address longitude.
HomeCountryCode	Int	False	The contact's home phone country code.
HomeMaskFormat	String	False	The contact's home phone mask format.
AlternateCountryCode	Int	False	The contact's alternate phone country code.
AlternateMaskFormat	String	False	The contact's alternate phone mask.
PagerCountryCode	Int	False	The contact's pager phone country code.
PagerMaskFormat	String	False	The contact's pager phone mask.
AemOptOut	Bool	False	AemOptOut.
AemBouncBack	Bool	False	AemBouncBack.
RecordManagerID	String	False	The record manager.
QuickbooksId	String	False	Quickbooks external Id to match record with quickbooks contact.
NylasContactId	String	False	Nylas contact Id to match record with a contact.
CustomFileds	String	False	Custom fields.
RecordManager	String	False	Manager of the activity.
Acl	String	False	The access control level of the record.

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 '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressCity supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressCountry supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressLine1 supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressLine2 supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressLine3 supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressPostalCode supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
AddressState supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Description supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
EditedBy supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
HierarchyLevel supports the '<=,<,=,!=,>,>=' operator.
ParentID supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
RecordOwner supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM OpportunityGroups WHERE OpportunityId = '123456'

SELECT * FROM OpportunityGroups WHERE Name = 'Group name'

Delete

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

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

Columns


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

OpportunityProcesses

Query, create, update and delete opportunity process definitions.

Table Specific Information

Select

Id supports the '=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM OpportunityProcesses WHERE Id = '12345'
SELECT * FROM OpportunityProcesses WHERE Name = 'Standard Process'

Insert

To insert an opportunity process, you must specify the Name and Description.

INSERT INTO OpportunityProcesses (Name, Description) VALUES ('Standard Sales Process123', 'desc1')

Update

Any column where ReadOnly=False can be updated. Specify the OpportunityProcesses Id when updating a process.

UPDATE OpportunityProcesses SET Description = 'updateddddd' WHERE Id = '14758814-0f58-4cda-baff-1dec9be37b9b'

Delete

OpportunityProcesses can be deleted by providing an Id and issuing a DELETE statement.

DELETE FROM OpportunityProcesses WHERE Id = 'f3685d54-9148-4d73-9436-40e08169b77b'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier of the opportunity process.
Name	String	False	The name of the opportunity process.
Description	String	False	A description of the opportunity process.
Status	String	False	The status of the process (None, Active, Inactive).
StagesCount	Integer	True	The number of stages defined in this opportunity process.
StagesAggregate	String	True	The stages associated with this opportunity process.

OpportunityProcessStages

Query and create stage definitions within an opportunity process.

Table Specific Information

Select

ProcessId supports the '=' operator.
Id supports the '=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Probability supports the '<=,<,=,!=,>,>=' operator.

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

SELECT * FROM OpportunityProcessStages WHERE ProcessId = '12345'
SELECT * FROM OpportunityProcessStages WHERE ProcessId = '12345' AND Name = 'Initial Communication'

Insert

To insert a stage, you must specify the ProcessId, Name, and Description.

INSERT INTO OpportunityProcessStages (ProcessId, Name, Description) VALUES ('14758814-0f58-4cda-baff-1dec9be37b9b', 'Proposal Sent last', 'now')

Columns


Name	Type	ReadOnly	Description
ProcessId [KEY]	String	False	The Id of the parent opportunity process.
Id [KEY]	String	True	Unique identifier of the stage.
Name	String	False	The name of the stage.
Description	String	False	A description of the stage.
Probability	Integer	False	The probability percentage associated with this stage.
Number	Integer	False	The order of this stage in the process lifecycle.
ProcessName	String	True	The name of the parent opportunity process.
ProcessDescription	String	True	The description of the parent opportunity process.
ProcessStatus	String	True	The status of the parent opportunity process.

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 '<=,<,=,!=,>,>=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Cost supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
EditDate supports the '<=,<,=,!=,>,>=' operator.
ItemNumber supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Price supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Created supports the '<=,<,=,!=,>,>=' operator.

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

SELECT * FROM OpportunityProducts WHERE OpportunityId = '12345'

SELECT * FROM OpportunityProducts WHERE Name = 'Product name'

Update

Any column where ReadOnly=False can be updated.

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

Delete

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

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

Columns


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

Products

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

Table Specific Information

Select

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

The Cloud uses the Act! CRM API to process search criteria that refer to the supported fields. The Cloud processes other filters client-side within the Cloud. 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.

ProductsInternal

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

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.

RecurringTasks

Query, create, update and delete recurring task records.

Table Specific Information

Select

Id supports the '=' operator.
AttachmentLastModified supports the '<=,<,=,!=,>,>=' operator.
AttachmentPersonal supports the '=' operator.
Created supports the '<=,<,=,!=,>,>=' operator.
Edited supports the '<=,<,=,!=,>,>=' operator.
IsAllDay supports the '=' operator.
IsAlarmCleared supports the '=' operator.
IsAlarmed supports the '=' operator.
IsPrivate supports the '=' operator.
IsTimeless supports the '=' operator.
Subject supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
RecurSpecIsEndless supports the '=' operator.
RecurSpecSeriesEnd supports the '<=,<,=,!=,>,>=' operator.
RecurSpecSeriesStart supports the '<=,<,=,!=,>,>=' operator.

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

SELECT * FROM RecurringTasks WHERE Id = '12345'
SELECT * FROM RecurringTasks WHERE Subject = 'Weekly review'

Insert

To insert a recurring task, you must specify at least one contact, the subject, and the recurrence pattern details.

INSERT INTO RecurringTasks (ContactsAggregate, Subject, RecurSpecRecurType, RecurSpecSeriesStart, RecurSpecIsEndless, RecurSpecFrequency, RecurSpecDayDayAsInt, RecurSpecDayDayType, RecurSpecDayDaysOfWeek) VALUES ('[{\"id\":\"972df268-a4db-41db-bc32-4af89ab3034b\"}]', 'Weekly Team Standup', 'Weekly', '2026-07-08T09:00:00Z', true, 1, 1, 'Named', 'Monday, Tuesday')

Update

Any column where ReadOnly=False can be updated. Specify the RecurringTasks Id when updating a recurring task.

UPDATE RecurringTasks SET Subject = 'Update xyaz' WHERE Id = '3f3d27e2-dfcc-42b5-b383-d8d44f5cced0'

Delete

RecurringTasks can be deleted by providing an Id and issuing a DELETE statement.

DELETE FROM RecurringTasks WHERE Id = '8912d252-b6ce-4442-a8d9-26b619f4ccbe'

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	False	Displays the type of the activity.
AttachmentDisplayName	String	True	Gets presentation name of the attachment.
AttachmentFileExtension	String	True	Gets the extension of the attachment.
AttachmentFileName	String	True	Gets a string representing the directory full path.
AttachmentFileSize	Integer	True	Gets the size (bytes) of the attachment.
AttachmentFileSizeDisplay	String	True	Gets the displayable text representation of the attachment.
AttachmentFileType	String	True	Gets the registered system file type name of the attachment.
AttachmentLastModified	Datetime	True	Gets the last modified date of the attachment.
AttachmentPersonal	Boolean	True	Indicates whether the attachment is bound for the personal supplemental files or the workgroup supplemental files.
BannerColor	String	False	The banner color.
CalendarUid	String	False	A unique calendar uid, common for all invited users.
CompaniesAggregate	String	False	List of companies that are associated to this activity.
ContactsAggregate	String	False	List of contacts that are associated to this activity. At least one contact is required.
Created	Datetime	True	The date and time the activity was created.
Details	String	False	Additional detailed information about the activity.
Duration	String	False	The difference between the start time and end time of the activity.
Edited	Datetime	True	The date and time the activity was last updated.
ExternalId	String	False	Provides a unique identifier to an external system.
GroupsAggregate	String	False	List of groups that are associated to this activity.
IsAllDay	Boolean	False	Indicates whether the activity takes up an entire day.
IsAlarmCleared	Boolean	False	Boolean value of IsAlarmCleared.
IsAlarmed	Boolean	False	Turns on/off an alarm for this activity.
IsPrivate	Boolean	False	Indicates elevated security that only the creator/owner has access to this activity.
IsRecurring	Boolean	True	True if the activity is recurring, otherwise false.
IsTimeless	Boolean	False	Indicates whether a specified action is to be completed at a specific time-of-day.
LeadMinutes	Integer	False	The number of minutes the alarm will sound before the activity is scheduled to start.
AlarmDue	String	False	Alarm due.
Location	String	False	Describes the physical location that the activity is to take place.
OpportunitiesAggregate	String	False	List of opportunities that are associated to this activity.
ScheduledBy	String	True	The contact who created this activity.
ScheduledById	String	False	The id of the activity organizer.
ScheduledFor	String	False	The name of the activity organizer.
ScheduledForId	String	False	The id of the activity organizer.
ScheduledWithCompany	String	True	Company name of the contact with whom this activity was scheduled.
ScheduledWithEmail	String	True	Business email address of the contact with whom this activity was scheduled.
ScheduledWithPhone	String	True	Phone number of the contact with whom this activity was scheduled.
ScheduledWithPhoneExt	String	True	Phone extension of the contact with whom this activity was scheduled.
Subject	String	False	A description of the action to be completed.
RecurSpecDayDayAsInt	Integer	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 TypedDay.
RecurSpecFrequency	Integer	False	Represents how often for the recurrence patterns.
RecurSpecIsEndless	Boolean	False	Flag denoting whether an end date has been specified for the pattern.
RecurSpecMonth	Integer	False	Represents the specific month for certain recurrence patterns.
RecurSpecRecurType	String	False	Represents a unit of time used to separate recurrences (i.e. Daily, Weekly, Monthly, Yearly).
RecurSpecSeriesEnd	Datetime	False	Specifying the ending time for a given day.
RecurSpecSeriesStart	Datetime	False	Specifying the starting time for a given day.

SecondaryContacts

Query, create, update and delete secondary contacts for a contact.

Table Specific Information

Select

ParentContactId supports the '=' operator.
Id supports the '=' operator.
Created supports the '<=,<,=,!=,>,>=' operator.
FullName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Birthday supports the '<=,<,=,!=,>,>=' operator.
Edited supports the '<=,<,=,!=,>,>=' operator.
FirstName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
IsUser supports the '=' operator.
LastName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
IsFavorite supports the '=' operator.
IsImported supports the '=' operator.
ImportDate supports the '<=,<,=,!=,>,>=' operator.
IsPrivate supports the '=' operator.
LastEmail supports the '<=,<,=,!=,>,>=' operator.
LastAttempt supports the '<=,<,=,!=,>,>=' operator.
LastReach supports the '<=,<,=,!=,>,>=' operator.
LastMeeting supports the '<=,<,=,!=,>,>=' operator.
LastLetterSent supports the '<=,<,=,!=,>,>=' operator.
AemOptOut supports the '=' operator.
AemBounceBack supports the '=' operator.

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

SELECT * FROM SecondaryContacts WHERE ParentContactId = '12345'
SELECT * FROM SecondaryContacts WHERE ParentContactId = '12345' AND LastName = 'Smith'

Insert

To insert a secondary contact, you must specify the ParentContactId of the parent contact, along with the FirstName, LastName, JobTitle, and EmailAddress.

INSERT INTO SecondaryContacts (ParentContactId, FirstName, LastName, JobTitle, EmailAddress) VALUES ('972df268-a4db-41db-bc32-4af89ab3034b', 'Jane1', 'Smith1', 'Assistant', '[email protected]')

Update

Any column where ReadOnly=False can be updated. Specify the ParentContactId and Id when updating a secondary contact.

UPDATE SecondaryContacts SET AltEmailAddress = '[email protected]', BusinessAddressCity = 'Delhi' WHERE Id = 'f99d63eb-c9d1-42bf-b345-cf30183d9a80' AND ParentContactId = '972df268-a4db-41db-bc32-4af89ab3034b'

Delete

SecondaryContacts can be deleted by providing a ParentContactId and Id and issuing a DELETE statement.

DELETE FROM SecondaryContacts WHERE ParentContactId = '972df268-a4db-41db-bc32-4af89ab3034b' AND Id = '187a795f-a7dc-4681-9e4c-41b86146bb07'

Columns


Name	Type	ReadOnly	Description
ParentContactId [KEY]	String	False	The Id of the parent contact.
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 suffixes defined within Act!
Company	String	False	The organization that this contact is associated with.
AltEmailAddress	String	False	The contact's alternate email address.
AlternateExtension	String	False	The contact alternate phone number extension.
AlternatePhone	String	False	The contact alternate phone number.
Birthday	Date	False	The contact's date of birth.
BusinessAddressCity	String	False	The city of the contact's business address.
BusinessAddressCountry	String	False	The country of the contact's business address.
BusinessAddressLine1	String	False	The first line of the contact's business street address.
BusinessAddressLine2	String	False	The second line of the contact's business street address.
BusinessAddressLine3	String	False	The third line of the contact's business street address.
BusinessAddressPostalCode	String	False	The postal code of the contact's business address.
BusinessAddressState	String	False	The state or province of the contact's business address.
BusinessExtension	String	False	The contact business phone number extension.
BusinessPhone	String	False	The contact business phone number.
CompanyID	String	False	The unique identifier (id) of the company associated with this contact.
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	The contact's primary email address.
FaxExtension	String	False	The contact fax phone number extension.
FaxPhone	String	False	The contact fax phone number.
FirstName	String	False	A personal given name.
HomeAddressCity	String	False	The city of the contact's home address.
HomeAddressCountry	String	False	The country of the contact's home address.
HomeAddressLine1	String	False	The first line of the contact's home street address.
HomeAddressLine2	String	False	The second line of the contact's home street address.
HomeAddressLine3	String	False	The third line of the contact's home street address.
HomeAddressPostalCode	String	False	The postal code of the contact's home address.
HomeAddressState	String	False	The state or province of the contact's home address.
HomeExtension	String	False	The contact home phone extension.
HomePhone	String	False	The contact home phone number.
IdStatus	String	False	The relative social or professional position of an individual within a group or organization.
IsUser	Boolean	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 equator.
Longitude	Decimal	False	The angular distance of a place east or west of the meridian at Greenwich, England.
MessengerID	String	True	The contact's instant messenger ID or username.
MiddleName	String	False	A name between one first name and surname.
MobileExtension	String	False	The contact mobile phone extension.
MobilePhone	String	False	The contact mobile phone number.
NamePrefix	String	False	A title or honorific placed before the contact's name (e.g. Mr., Dr.).
NameSuffix	String	False	Provides additional information about the person identifying an individual position, educational degree, accreditation, office, or honors.
PagerExtension	String	False	The contact pager phone number extension
PagerPhone	String	False	The contact pager phone number.
PersonalEmailAddress	String	False	The contact's personal email address.
RecordOwner	String	True	The user who owns the contact record.
ReferredBy	String	False	Indicates how this contact came about.
Salutation	String	True	The greeting or salutation used when addressing this contact (e.g. Dear Mr. Smith).
Website	String	False	The contact web site.
IsFavorite	Boolean	False	Indicates whether this contact is marked as a favorite.
IsImported	Boolean	False	Indicates whether this contact was imported into the system.
ImportDate	Datetime	False	The date that the contact was imported into the system.
IsPrivate	Boolean	False	Indicates elevated security that only the creator/owner has access to this contact.
LastEmail	Datetime	False	The date of the last email sent to or from this contact.
LastAttempt	Datetime	False	The date of the last contact attempt.
LastReach	Datetime	False	The date this contact was last reached.
LastMeeting	Datetime	False	The date of the last meeting with this contact.
LastLetterSent	Datetime	False	The date of the last letter sent to this contact.
AmaScore	Integer	False	The contact AMA score.
BusinessAddressLatitude	Decimal	False	Business address latitude.
BusinessAddressLongitude	Decimal	False	Business address longitude.
BusinessCountryCode	Integer	False	The contact business phone country code.
BusinessMaskFormat	String	False	The contact business phone mask format.
MobileCountryCode	Integer	False	The contact mobile phone country code.
MobileMaskFormat	String	False	The contact mobile phone mask format.
FaxCountryCode	Integer	False	The contact fax phone country code.
FaxMaskFormat	String	False	The contact fax phone mask format.
HomeAddressLatitude	Decimal	False	Home address latitude.
HomeAddressLongitude	Decimal	False	Home address longitude.
HomeCountryCode	Integer	False	The contact home phone country code.
HomeMaskFormat	String	False	The contact home phone mask format.
AlternateCountryCode	Integer	False	The contact alternate phone country code.
AlternateMaskFormat	String	False	The contact alternate phone mask format.
PagerCountryCode	Integer	False	The contact pager phone country code.
PagerMaskFormat	String	False	The contact pager phone mask format.
AemOptOut	Boolean	False	Indicates whether this contact has opted out of Act! email marketing (AEM).
AemBounceBack	Boolean	False	Indicates whether emails to this contact have bounced back in Act! email marketing (AEM).
RecordManagerId	String	False	The unique identifier (id) of the record manager for this contact.
QuickBooksId	String	False	Quickbooks external Id to match record with quickbooks contact.
CustomFields	String	False	Custom fields.
NylasContactId	String	False	Nylas contact Id to match record with a contact.
RecordManager	String	False	The name of the record manager for this contact.
Acl	String	True	The access control level of the record.

Tasks

Query, create, update and delete the tasks.

Table Specific Information

Select

Id supports the '=' operator.
IsCleared supports the '=' operator.
IsAlarmCleared supports the '=' operator.
IsAlarmed supports the '=' operator.
OriginalTime supports the '<=,<,=,!=,>,>=' operator.
StartTime supports the '<=,<,=,!=,>,>=' operator.
EndTime supports the '<=,<,=,!=,>,>=' operator.
IsTimeless supports the '=' operator.
IsAllDay supports the '=' operator.
IsPrivate supports the '=' operator.
AttachmentLastModified supports the '<=,<,=,!=,>,>=' operator.
AttachmentPersonal supports the '=' operator.
Subject supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Created supports the '<=,<,=,!=,>,>=' operator.
Edited supports the '<=,<,=,!=,>,>=' operator.

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

SELECT * FROM Tasks WHERE Id = '12345'
SELECT * FROM Tasks WHERE Subject = 'Follow up call'

Insert

To insert a task, you must specify at least one contact, start time and end time.

INSERT INTO Tasks (ActivityTypeName, StartTime, EndTime, ContactsAggregate) VALUES ('To-Do', '2026-01-15T09:00:00', '2026-01-15T09:30:00', '[{\"id\":\"972df268-a4db-41db-bc32-4af89ab3034b\"}]')

Update

Any column where ReadOnly=False can be updated. Specify the Task Id when updating a task.

UPDATE Tasks SET Details = 'abc' WHERE Id = '2a82a5c3-c380-47a4-8b52-0eb003405def01152026'

Delete

Tasks can be deleted by providing an Id and issuing a DELETE statement.

DELETE FROM Tasks WHERE Id = 'dc75eaa5-c651-4118-93fc-0a4360458ec807022026'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A unique identifier that represents the task.
SeriesID	String	True	A unique identifier that represents the activity. The id returned is based on whether it is recurring or not: RecurSourceActivityId or ActivityID.
IsCleared	Boolean	True	Indicates that the task has been cleared.
IsAlarmCleared	Boolean	True	Boolean value of IsAlarmCleared.
IsAlarmed	Boolean	False	Turns on/off an alarm for this task.
LeadMinutes	Integer	False	The number of minutes the alarm will sound before the task is scheduled to start.
AlarmDue	String	True	Alarm due.
OriginalTime	Datetime	False	The date and time for which this task was originally scheduled.
StartTime	Datetime	False	The time the task is to begin.
EndTime	Datetime	False	The time the task is to end.
Duration	String	True	The difference between the start time and end time of the task.
ExternalId	String	False	Provides a unique identifier to an external system.
CalendarUid	String	False	A unique calendar uid, common for all invited users.
AccessorId	String	False	The unique id of the activity accessor.
Location	String	False	Describes the physical location that the task is to take place.
IsTimeless	Boolean	False	Indicates whether a specified task is to be completed at a specific time-of-day.
IsAllDay	Boolean	False	Indicates whether the task takes up an entire day.
IsPrivate	Boolean	False	Indicates elevated security that only the creator/owner has access to this task.
IsRecurring	Boolean	True	True if the task is recurring, otherwise false.
RecurSpecSeriesStart	Datetime	True	The start date and time of the recurring task series.
RecurSpecSeriesEnd	Datetime	True	The end date and time of the recurring task series.
RecurSpecIsEndless	Boolean	True	Indicates whether the recurring task series has no end date.
RecurSpecMonth	Integer	True	The month component of the recurrence schedule.
RecurSpecFrequency	Integer	True	The frequency of the recurrence.
RecurSpecRecurType	String	True	The type of recurrence (e.g., None, Daily, Weekly, Monthly).
RecurSpecDayDayAsInt	Integer	True	The day of the recurrence as an integer.
RecurSpecDayDaysOfWeek	String	True	The days of the week for the recurrence.
RecurSpecDayTypedDay	String	True	The typed day of the recurrence.
RecurSpecDayDayType	String	True	The day type of the recurrence.
RecurSpecDayOrdinal	String	True	The ordinal position of the day in the recurrence (e.g., First, Second, Last).
ActivityPriorityId	Integer	False	Activity priority value.
ActivityPriorityName	String	False	Displays the priority of the task.
ActivityTypeId	Integer	True	Displays the id of the type of the activity.
ActivityTypeName	String	False	Displays the type of the task.
AttachmentDisplayName	String	True	Gets presentation name of the attachment.
AttachmentFileExtension	String	True	Gets the extension of the attachment.
AttachmentFileName	String	True	Gets a string representing the attachment full path.
AttachmentFileSize	Integer	True	Gets the size (bytes) of the attachment.
AttachmentFileSizeDisplay	String	True	Gets the displayable text representation of the attachment.
AttachmentFileType	String	True	Gets the registered system file type name of the attachment.
AttachmentLastModified	Datetime	True	Gets the last modified date of the attachment.
AttachmentPersonal	Boolean	True	Indicates whether the attachment is bound for the personal supplemental files or the workgroup supplemental files.
BannerColor	String	False	The banner color.
Details	String	False	Additional detailed information about the task.
Subject	String	False	A description of the action to be completed.
Created	Datetime	True	The date and time the task was created.
Edited	Datetime	True	The date and time the task was last updated.
ScheduledById	String	False	The id of the task organizer.
ScheduledBy	String	True	The contact who created this task.
ScheduledForId	String	False	The id of the task organizer.
ScheduledFor	String	False	The name of the task organizer.
ScheduledWithCompany	String	True	Company name of the contact with whom this task was scheduled.
ScheduledWithEmail	String	True	Business email address of the contact with whom this task was scheduled.
ScheduledWithPhone	String	True	Phone number of the contact with whom this task was scheduled.
ScheduledWithPhoneExt	String	True	Phone extension of the contact with whom this task was scheduled.
CompaniesAggregate	String	False	List of companies that are associated to this task.
ContactsAggregate	String	False	List of contacts that are associated to this task. At least one contact is required.
GroupsAggregate	String	False	List of groups that are associated to this task.
OpportunitiesAggregate	String	False	List of opportunities that are associated to this task.

Teams

Query, create, update and delete teams.

Table Specific Information

Select

Id supports the '=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM Teams WHERE Id = '12345'
SELECT * FROM Teams WHERE Name = 'Sales Team'

Insert

To insert a team, you must specify the Name, Description, and the roster of member users.

INSERT INTO Teams (Name, RosterAggregate, Description) VALUES ('abc11', '[{\"id\": \"61a2c8b3-c52c-46ff-bb19-f26cad89c621\"}]', 'desc')

Update

Any column where ReadOnly=False can be updated. Specify the Team Id when updating a team.

UPDATE Teams SET Name = 'def update', RosterAggregate = '[{\"id\": \"e78644b3-2018-411c-a0a6-878a449b6067\"}]', Description = 'update' WHERE Id = 'ec8ba34a-9c30-42c5-b98e-f0a45185b79e'

Delete

Teams can be deleted by providing an Id and issuing a DELETE statement.

DELETE FROM Teams WHERE Id = '4eabb33c-49f5-49ae-a8e0-c91291e76194'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier of the team.
Name	String	False	The name of the team.
Description	String	False	A description of the team.
RosterAggregate	String	False	The list of users who are members of this team.

Users

Query, create and delete user accounts.

Table Specific Information

Select

Id supports the '=' operator.
Username supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
FirstName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
LastName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
FullName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM Users WHERE Id = '12345'
SELECT * FROM Users WHERE Username = 'jsmith'

Insert

To insert a user, you must specify the Username, Password, FirstName, LastName, RoleName, and Status.

INSERT INTO Users (Username, Password, FirstName, LastName, RoleName, Status) VALUES ('jdoe1', 'S13cur3P@ss!', 'John1', 'Doe1', 'Administrator', 'Inactive')

Delete

Users can be deleted by providing an Id and issuing a DELETE statement.

DELETE FROM Users WHERE Id = 'd583f44d-2013-4615-801c-b111b23b8deb'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier of the user.
Username	String	False	The login name of the user.
Password	String	False	The password for the new user.
FirstName	String	False	The first name of the user.
MiddleName	String	False	The middle name of the user.
LastName	String	False	The last name of the user.
NamePrefix	String	False	The name prefix of the user.
NameSuffix	String	False	The name suffix of the user.
FullName	String	False	The full display name of the user.
Salutation	String	False	The salutation of the user.
Email	String	True	The email address of the user.
Role	String	True	The role assigned to the user.
RoleName	String	False	The role name to assign to the new user.
Status	String	False	The status of the user (Active, Inactive, Deleted).
LastLogOnDate	Datetime	True	The date and time the user last logged on.
MustChangePassword	Boolean	False	Whether the user must change their password on next login.
CanChangePassword	Boolean	False	Whether the user is allowed to change their password.
PasswordExpires	Boolean	False	Whether the user password expires.

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.

CData Cloud - Act! CRM Views


Name	Description
ActivityCompanies	[DEPRECATED] Query the Companies participating in an Act! CRM Activity.
ActivityHistories	Get all the history related to an activity.
ActivityTaskList	Task completion analytics by user for a given date range.
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.
CompanyDivisions	Get the divisions of a company.
CompanyHistories	Get all the history related to a company.
CompanyNotes	Query the Notes for a given Act! CRM Company.
CompanyOpportunities	Query the Act! CRM Opportunities associated with a Company.
ContactCompanies	Query the Act! CRM Companies associated with a Contact.
ContactGroups	Query the Act! CRM Groups the Contact belongs to.
ContactHistories	Get all the history related to a contact.
ContactOpportunities	Query the Act! CRM Opportunities associated with 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.
GroupHistories	Get all the history related to a group.
GroupNotes	Query the Notes for a given Act! CRM Group.
GroupOpportunities	Query the Act! CRM Opportunities associated with a Group.
KPI	Key performance indicator metrics.
OpportunityCount	Generates a summary of the total number of opportunities within a stage.
OpportunityHistories	Get all the history related to an opportunity.
OpportunityNotes	Query the Notes for a given Act! CRM Opportunity.
OpportunityPipeline	The current user's opportunity pipeline analytic view settings.
OpportunityStages	Get the stages defined in the opportunity pipeline.
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.
TopOpenOpportunities	Highest-value open opportunities.
UserActivities	Generates activity sales by user data for given users.
UserActivityDetails	Generated schema file.

ActivityCompanies

[DEPRECATED] Query the Companies participating in an Act! CRM Activity.

Table Specific Information

Select

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

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

SELECT * FROM ActivityCompanies WHERE ActivityId = '12345'

SELECT * FROM ActivityCompanies WHERE Name = 'TSS Company'

Columns


Name	Type	Description
Id [KEY]	String	The unique identifier (id) for this company.
ActivityId [KEY]	String	A unique identifier that represents the activity the companies are part of.
Created	Datetime	Created date and the activity.
Name	String	The company's given name.
AddressCity	String	An inhabited place of greater size, population, or importance than a town or village.
AddressCountry	String	A nation with its own government, occupying a particular territory.
AddressLine1	String	Primary street address
AddressLine2	String	Secondary street address
AddressLine3	String	Miscellaneous street address
AddressPostalCode	String	A code of letters and digits added to a postal address to aid in the sorting of mail.
AddressState	String	A nation or territory considered as an organized political community under one government.
BillingAddressCity	String	An inhabited place of greater size, population, or importance than a town or village.
BillingAddressCountry	String	A nation with its own government, occupying a particular territory.
BillingAddressLine1	String	Primary street address
BillingAddressLine2	String	Secondary street address
BillingAddressLine3	String	Miscellaneous street address
BillingAddressPostalCode	String	A code of letters and digits added to a postal address to aid in the sorting of mail.
BillingAddressState	String	A nation or territory considered as an organized political community under one government.
Description	String	A brief written representation or account of this company.
Division	String	The name of a smaller part of a parent company tasked with overseeing different types of products or services than those offered by the parent company.
Edited	Datetime	Edited date of the activity.
EditedBy	String	User that edited the activity.
Fax	String	The company's fax phone number.
FaxExtension	String	The company's fax phone number extension.
HierarchyLevel	Int	The positional level of where this company lays within a company hierarchy.
IdStatus	String	The relative social or professional position of an individual within a group or organization.
Industry	String	Economic activity concerned with the processing of raw materials and manufacture of goods in factories.
NumberOfEmployees	Int	The total number of employees that this company operates with.
ParentID	String	The unique identifier (id) of the parent company.
Phone	String	The company's phone number.
PhoneExtension	String	The company's phone number extension.
RecordOwner	String	Owner of the activity.
ReferredBy	String	Mention or allude to.
Region	String	A part of a country, of the world, etc., that is different or separate from other parts in some way.
Revenue	Decimal	The amount of money that this company actually receives during a specific period, including discounts and deductions for returned merchandise.
ShippingAddressCity	String	An inhabited place of greater size, population, or importance than a town or village.
ShippingAddressCountry	String	A nation with its own government, occupying a particular territory.
ShippingAddressLine1	String	Primary street address
ShippingAddressLine2	String	Secondary street address
ShippingAddressLine3	String	Miscellaneous street address
ShippingAddressPostalCode	String	A code of letters and digits added to a postal address to aid in the sorting of mail.
ShippingAddressState	String	A nation or territory considered as an organized political community under one government.
SicCode	String	Standard Industrial Classification (SIC) codes are four-digit numerical codes assigned by the U.S. government to business establishments to identify the primary business of the establishment.
Territory	String	Geographical area.
TickerSymbol	String	An arrangement of characters (usually letters) representing a particular security listed on an exchange or otherwise traded publicly.
TollFreeExtension	String	The company's toll free phone number.
TollFreePhone	String	The company's toll free phone number.
Website	String	The company's website.
AddressLatitude	Decimal	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
AddressLongitude	Decimal	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
BillingAddressLatitude	Decimal	The Billing address latitude.
BillingAddressLongitude	Decimal	The Billing address longitude.
HasDivisions	Boolean	This readonly property indicates that this company has divisions.
ImportDate	Datetime	This readonly property displays the date that the opportunity was imported into the system.
IsPrivate	Boolean	Indicates elevated security that only the creator/owner has access to this company.
RootParentId	String	The unique identifier (id) of the parent company.
ShippingAddressLatitude	Decimal	The Shipping address latitude.
ShippingAddressLongitude	Decimal	The Shipping address longitude.
RecordManagerID	String	The record manager.
CustomFields	String	Custom fields.
RecordManager	String	Manager of the activity.
Acl	String	The access control level of the record.

ActivityHistories

Get all the history related to an activity.

View Specific Information

Select

ActivityId supports the '=' operator.

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

SELECT * FROM ActivityHistories
SELECT * FROM ActivityHistories WHERE ActivityId = '12345'

Columns


Name	Type	Description
Id [KEY]	String	Id of the history item created
ActivityId [KEY]	String	Id of the activity
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 created 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
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 Manager
Regarding	String	Tells what history is about
StartTime	Datetime	Start Time
ContactCompany	String	The company of the contact associated with this history.
ContactEmail	String	The email of the contact associated with this history.
ContactPhone	String	The phone number of the contact associated with this history.
ContactPhoneExt	String	The phone extension of the contact associated with this history.
CreateUserName	String	The username of the user who created this history.
EditUserID	String	The ID of the user who last edited this history.
EditUserName	String	The username of the user who last edited this history.
HistoryTypeActivityTypeName	String	The activity type name of the history type.
HistoryTypeActivityTypeId	Integer	The activity type ID of the history type.

ActivityTaskList

Task completion analytics by user for a given date range.

View Specific Information

Select

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

Start supports the '=' operator.
End supports the '=' operator.
Max supports the '=' operator.
TypeIds supports the '=' operator.
PriorityIds supports the '=' operator.

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

SELECT * FROM ActivityTaskList WHERE Start = '2023-01-01 01:00:00' AND End = '2023-12-31 01:30:00'

Columns


Name	Type	Description
Id [KEY]	String	The unique identifier of the activity task.
AccessorActivityId	String	The accessor activity identifier.
ContactCount	Integer	The number of contacts associated with the task.
StartTime	Datetime	The start time of the task.
Regarding	String	A description of what the task is about.
Type	String	The type of the activity task.
TypeId	Integer	The type identifier of the activity task.
Priority	String	The priority of the activity task.
ScheduledWith	String	The user the task is scheduled with.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
Start	Datetime	The start date-time for the analytics date-time range. Required.
End	Datetime	The end date-time for the analytics date-time range. Required.
Max	Integer	The maximum number of records to return.
TypeIds	String	Comma-separated list of activity type IDs to filter by.
PriorityIds	String	Comma-separated list of priority IDs to filter by.

AnalyticsHistory

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

Table Specific Information

Select

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

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

The Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud processes other filters client-side within the Cloud. 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 Cloud 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 Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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.

The Cloud processes other filters client-side within the Cloud. 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.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud will use the Act! CRM API to process WHERE clause conditions built with the following column and operator. The Cloud processes the rest of the filter is executed client-side within the Cloud

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

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

SELECT * FROM CloseReasonDetails where CloseReason = 'Not specified'

The StatusType is a pseudo column which can be used to filter values for Status column using the following Enum values: open,won,lost,inactive.

SELECT * FROM CloseReasonDetails where StatusType = '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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
StatusType	String	StatusType The allowed values are open, won, lost, inactive.
Date	Datetime	Date Range of Activities By User

CompanyDivisions

Get the divisions of a company.

View Specific Information

Select

CompanyId supports the '=' operator.

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

SELECT * FROM CompanyDivisions
SELECT * FROM CompanyDivisions WHERE CompanyId = '12345'

Columns


Name	Type	Description
Id [KEY]	String	The unique identifier of the division.
CompanyId [KEY]	String	The identifier of the parent company.
Name	String	The name of the division.
RootParentID	String	The identifier of the root parent company in the hierarchy.
ParentID	String	The identifier of the immediate parent in the hierarchy.
HasDivisions	Boolean	Indicates whether this division contains child divisions.
HierarchyLevel	Integer	The depth of this division in the company hierarchy.
Divisions	String	Child divisions nested under this division.

CompanyHistories

Get all the history related to a company.

View Specific Information

Select

CompanyId supports the '=' operator.

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

SELECT * FROM CompanyHistories
SELECT * FROM CompanyHistories WHERE CompanyId = '12345'

Columns


Name	Type	Description
Id [KEY]	String	Id of the history item created
CompanyId [KEY]	String	Id of the company
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 created 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
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 Manager
Regarding	String	Tells what history is about
StartTime	Datetime	Start Time
ContactCompany	String	The company of the contact associated with this history.
ContactEmail	String	The email of the contact associated with this history.
ContactPhone	String	The phone number of the contact associated with this history.
ContactPhoneExt	String	The phone extension of the contact associated with this history.
CreateUserName	String	The username of the user who created this history.
EditUserID	String	The ID of the user who last edited this history.
EditUserName	String	The username of the user who last edited this history.
HistoryTypeActivityTypeName	String	The activity type name of the history type.
HistoryTypeActivityTypeId	Integer	The activity type ID of the history type.

CompanyNotes

Query the Notes for a given Act! CRM Company.

Table Specific Information

Select

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

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

SELECT * FROM CompanyNotes WHERE CompanyId = '12345'

Columns


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

CompanyOpportunities

Query the Act! CRM Opportunities associated with a Company.

View Specific Information

Select

CompanyId supports the '=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Competitor supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Created supports the '<=,<,=,!=,>,>=' operator.
ActualCloseDate supports the '<=,<,=,!=,>,>=' operator.
DaysOpen supports the '<=,<,=,!=,>,>=' operator.
Edited supports the '<=,<,=,!=,>,>=' operator.
EditedBy supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
EstimatedCloseDate supports the '<=,<,=,!=,>,>=' operator.
GrossMargin supports the '<=,<,=,!=,>,>=' operator.
IsPrivate supports the '=' operator.
OpenDate supports the '<=,<,=,!=,>,>=' operator.
Probability supports the '<=,<,=,!=,>,>=' operator.
ProductTotal supports the '<=,<,=,!=,>,>=' operator.
Reason supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
RecordOwner supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Source supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
StageProcessName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
WeightedTotal supports the '<=,<,=,!=,>,>=' operator.
StageStartDate supports the '<=,<,=,!=,>,>=' operator.
SourceId supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
ImportDate supports the '<=,<,=,!=,>,>=' operator.
RecordManager supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM CompanyOpportunities WHERE CompanyId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c'

SELECT * FROM CompanyOpportunities

Columns


Name	Type	Description
Id [KEY]	String	A unique identifier that represents the opportunity.
CompanyId [KEY]	String	The unique identifier (id) for the company to retrieve opportunities from.
Created	Datetime	Created date of the opportunity.
Name	String	The opportunity name.
ActualCloseDate	Datetime	The date that the opportunity was closed.
CompaniesAggregate	String	List of companies that are associated to this opportunity entity.
Competitor	String	Gets the name of the competitor for this opportunity.
ContactNames	String	The names of the contacts associated with this opportunity.
ContactsAggregate	String	The names of the contacts associated with this opportunity.
Creator	String	The username of the user who created this opportunity.
DaysOpen	Integer	The number of days this opportunity has been opened.
Edited	Datetime	The date that this record was edited.
EditedBy	String	The user's name that last edited this record.
EstimatedCloseDate	Datetime	The date the opportunity is expected to be closed.
GrossMargin	Decimal	Represents the net sales less the cost of goods and services sold.
GroupsAggregate	String	List of groups that are associated to this opportunity entity.
IsPrivate	Boolean	Indicates whether this opportunity is private
Manager	String	The name of the record manager of this opportunity.
OpenDate	Datetime	The date the opportunity was opened.
Probability	Integer	The likelihood this opportunity will be won by the close date.
ProductTotal	Decimal	The total cost of the product or services.
Reason	String	Describes why the opportunity status changed.
RecordOwner	String	The user's name that owns this record.
RelatedEntitiesResolver	Boolean	If true prevents related entities from serializing.
Source	String	Describes the source of the opportunity.
StageDescription	String	Gets or sets the description of the stage.
StageId	String	Gets the unique identifier of the stage.
StageName	String	The name of the stage.
StageNumber	Integer	The ordinal number of the stage.
StageProbability	Integer	The probability of the stage.
StageProcessDescription	String	The description of the stage process.
StageProcessId	String	The unique identifier of the stage process.
StageProcessName	String	The name of the stage process.
Status	String	The status of the opportunity.
TotalPerCompany	Decimal	The average total per associated company.
TotalPerContact	Decimal	The average total per associated contact.
TotalPerGroup	Decimal	The average total per associated group.
WeightedTotal	Decimal	The weighted total value of the opportunity.
DaysInStage	Integer	The number of days this opportunity has been in a given stage.
StageStartDate	Datetime	The date the opportunity stage started.
SourceId	String	The external source id for the opportunity.
StageProcessStatus	String	Stage process status.
StageProcessStagesCount	Integer	Stage process stages count
StageProcessStages	String	Stage process stages
ImportDate	Datetime	This readonly property displays the date that the opportunity was imported into the system.
QuickBooksInvoiceId	String	Quickbooks external Id to match record with quickbooks contact.
RecordManagerId	String	The unique identifier (id) of the record manager for this opportunity.
CustomFields	String	Custom fields.
RecordManager	String	Manager of the opportunity record.
Acl	String	The access control level of the record.

ContactCompanies

Query the Act! CRM Companies associated with a Contact.

View Specific Information

Select

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

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

SELECT * FROM ContactCompanies WHERE ContactId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c'

SELECT * FROM ContactCompanies

Columns


Name	Type	Description
Id [KEY]	String	The unique identifier (id) for this company.
ContactId [KEY]	String	The unique identifier (id) for the contact to retrieve companies from.
Created	Datetime	Created date of the company.
Name	String	The company's given name.
AddressCity	String	The city of the company's address.
AddressCountry	String	The country of the company's address.
AddressLine1	String	The first line of the company's street address.
AddressLine2	String	The second line of the company's street address.
AddressLine3	String	The third line of the company's street address.
AddressPostalCode	String	The postal code of the company's address.
AddressState	String	The state or province of the company's address.
BillingAddressCity	String	The city of the company's billing address.
BillingAddressCountry	String	The country of the company's billing address.
BillingAddressLine1	String	The first line of the company's billing street address.
BillingAddressLine2	String	The second line of the company's billing street address.
BillingAddressLine3	String	The third line of the company's billing street address.
BillingAddressPostalCode	String	The postal code of the company's billing address.
BillingAddressState	String	The state or province of the company's billing address.
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 company.
EditedBy	String	User who edited the company record.
Fax	String	The company's fax phone number.
FaxExtension	String	The company's fax phone number extension.
HierarchyLevel	Integer	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	Integer	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 company record.
ReferredBy	String	The name of the person or entity that referred this company.
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	The city of the company's shipping address.
ShippingAddressCountry	String	The country of the company's shipping address.
ShippingAddressLine1	String	The first line of the company's shipping street address.
ShippingAddressLine2	String	The second line of the company's shipping street address.
ShippingAddressLine3	String	The third line of the company's shipping street address.
ShippingAddressPostalCode	String	The postal code of the company's shipping address.
ShippingAddressState	String	The state or province of the company's shipping address.
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 extension.
TollFreePhone	String	The company's toll free phone number.
Website	String	The company's website.
AddressLatitude	Decimal	The angular distance of a place north or south of the earth's equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.
AddressLongitude	Decimal	The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.
BillingAddressLatitude	Decimal	The Billing address latitude.
BillingAddressLongitude	Decimal	The Billing address longitude.
ShippingAddressLatitude	Decimal	The Shipping address latitude.
ShippingAddressLongitude	Decimal	The Shipping address longitude.
HasDivisions	Boolean	This readonly property indicates that this company has divisions.
ImportDate	Datetime	This readonly property displays the date that the company was imported into the system.
IsPrivate	Boolean	Indicates elevated security that only the creator/owner has access to this company.
RootParentId	String	The unique identifier (id) of the parent company.
RecordManagerID	String	The record manager.
CustomFields	String	Custom fields.
RecordManager	String	Manager of the company record.
Acl	String	The access control level of the record.

ContactGroups

Query the Act! CRM Groups the Contact belongs to.

Table Specific Information

Select

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

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

SELECT * FROM ContactGroups WHERE ContactId = '12345'

SELECT * FROM ContactGroups WHERE Name = 'Contact name'

Columns


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

ContactHistories

Get all the history related to a contact.

Table Specific Information

Select

ContactId supports the '=' operator.

The Cloud uses the Act! CRM API to process search criteria that refer to the supported fields. The Cloud processes other filters client-side within the Cloud. 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
ContactCompany	String	The company of the contact associated with this history.
ContactEmail	String	The email of the contact associated with this history.
ContactPhone	String	The phone number of the contact associated with this history.
ContactPhoneExt	String	The phone extension of the contact associated with this history.
CreateUserName	String	The username of the user who created this history.
EditUserID	String	The ID of the user who last edited this history.
EditUserName	String	The username of the user who last edited this history.
HistoryTypeActivityTypeName	String	The activity type name of the history type.
HistoryTypeActivityTypeId	Integer	The activity type ID of the history type.

ContactOpportunities

Query the Act! CRM Opportunities associated with a Contact.

View Specific Information

Select

ContactId supports the '=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Competitor supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Created supports the '<=,<,=,!=,>,>=' operator.
ActualCloseDate supports the '<=,<,=,!=,>,>=' operator.
DaysOpen supports the '<=,<,=,!=,>,>=' operator.
Edited supports the '<=,<,=,!=,>,>=' operator.
EditedBy supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
EstimatedCloseDate supports the '<=,<,=,!=,>,>=' operator.
GrossMargin supports the '<=,<,=,!=,>,>=' operator.
IsPrivate supports the '=' operator.
OpenDate supports the '<=,<,=,!=,>,>=' operator.
Probability supports the '<=,<,=,!=,>,>=' operator.
ProductTotal supports the '<=,<,=,!=,>,>=' operator.
Reason supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
RecordOwner supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Source supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
StageProcessName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
WeightedTotal supports the '<=,<,=,!=,>,>=' operator.
StageStartDate supports the '<=,<,=,!=,>,>=' operator.
SourceId supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
ImportDate supports the '<=,<,=,!=,>,>=' operator.
RecordManager supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM ContactOpportunities WHERE ContactId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c'

SELECT * FROM ContactOpportunities

Columns


Name	Type	Description
Id [KEY]	String	A unique identifier that represents the opportunity.
ContactId [KEY]	String	The unique identifier (id) for the contact to retrieve opportunities from.
Created	Datetime	Created date of the opportunity.
Name	String	The opportunity name.
ActualCloseDate	Datetime	The date that the opportunity was closed.
CompaniesAggregate	String	List of companies that are associated to this opportunity entity.
Competitor	String	Gets the name of the competitor for this opportunity.
ContactNames	String	The names of the contacts associated with this opportunity.
ContactsAggregate	String	The names of the contacts associated with this opportunity.
Creator	String	The username of the user who created this opportunity.
DaysOpen	Integer	The number of days this opportunity has been opened.
Edited	Datetime	The date that this record was edited.
EditedBy	String	The user's name that last edited this record.
EstimatedCloseDate	Datetime	The date the opportunity is expected to be closed.
GrossMargin	Decimal	Represents the net sales less the cost of goods and services sold.
GroupsAggregate	String	List of groups that are associated to this opportunity entity.
IsPrivate	Boolean	Indicates whether this opportunity is private
Manager	String	The name of the record manager of this opportunity.
OpenDate	Datetime	The date the opportunity was opened.
Probability	Integer	The likelihood this opportunity will be won by the close date.
ProductTotal	Decimal	The total cost of the product or services.
Reason	String	Describes why the opportunity status changed.
RecordOwner	String	The user's name that owns this record.
RelatedEntitiesResolver	Boolean	If true prevents related entities from serializing.
Source	String	Describes the source of the opportunity.
StageDescription	String	Gets or sets the description of the stage.
StageId	String	Gets the unique identifier of the stage.
StageName	String	The name of the stage.
StageNumber	Integer	The ordinal number of the stage.
StageProbability	Integer	The probability of the stage.
StageProcessDescription	String	The description of the stage process.
StageProcessId	String	The unique identifier of the stage process.
StageProcessName	String	The name of the stage process.
Status	String	The status of the opportunity.
TotalPerCompany	Decimal	The average total per associated company.
TotalPerContact	Decimal	The average total per associated contact.
TotalPerGroup	Decimal	The average total per associated group.
WeightedTotal	Decimal	The weighted total value of the opportunity.
DaysInStage	Integer	The number of days this opportunity has been in a given stage.
StageStartDate	Datetime	The date the opportunity stage started.
SourceId	String	The external source id for the opportunity.
StageProcessStatus	String	Stage process status.
StageProcessStagesCount	Integer	Stage process stages count
StageProcessStages	String	Stage process stages
ImportDate	Datetime	This readonly property displays the date that the opportunity was imported into the system.
QuickBooksInvoiceId	String	Quickbooks external Id to match record with quickbooks contact.
RecordManagerId	String	The record manager.
CustomFields	String	Custom fields.
RecordManager	String	Manager of the opportunity record.
Acl	String	The access control level of the record.

ForecastByProduct

Generates forecasted opportunity data by given products.

Table Specific Information

Select

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

ProductId supports the '=' operator.

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

SELECT * FROM ForecastByProduct

Columns


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

ForecastByProductDetails

Generates forecasted opportunity data by a given product..

Table Specific Information

Select

The Cloud 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 Cloud.

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

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

SELECT * FROM ForecastByProductDetails where Status = 'inactive'

Columns


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

ForecastByRep

Generates forecasted opportunity data by given users.

Table Specific Information

Select

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

UserId supports the '=' operator.

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

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

Columns


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

ForecastByRepDetails

Generates forecasted opportunity data by a given user.

Table Specific Information

Select

The Cloud 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 Cloud.

UserId supports the '=' operator.

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

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

Columns


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

GroupHistories

Get all the history related to a group.

View Specific Information

Select

GroupId supports the '=' operator.

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

SELECT * FROM GroupHistories
SELECT * FROM GroupHistories WHERE GroupId = '12345'

Columns


Name	Type	Description
Id [KEY]	String	Id of the history item created
GroupId [KEY]	String	Id of the group
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 created 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 Manager
Regarding	String	Tells what history is about
StartTime	Datetime	Start Time
ContactCompany	String	The company of the contact associated with this history.
ContactEmail	String	The email of the contact associated with this history.
ContactPhone	String	The phone number of the contact associated with this history.
ContactPhoneExt	String	The phone extension of the contact associated with this history.
CreateUserName	String	The username of the user who created this history.
EditUserID	String	The ID of the user who last edited this history.
EditUserName	String	The username of the user who last edited this history.
HistoryTypeActivityTypeName	String	The activity type name of the history type.
HistoryTypeActivityTypeId	Integer	The activity type ID of the history type.

GroupNotes

Query the Notes for a given Act! CRM Group.

Table Specific Information

Select

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

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

SELECT * FROM GroupNotes WHERE GroupId = '12345'

Columns


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

GroupOpportunities

Query the Act! CRM Opportunities associated with a Group.

View Specific Information

Select

GroupId supports the '=' operator.
Name supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Competitor supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Created supports the '<=,<,=,!=,>,>=' operator.
ActualCloseDate supports the '<=,<,=,!=,>,>=' operator.
DaysOpen supports the '<=,<,=,!=,>,>=' operator.
Edited supports the '<=,<,=,!=,>,>=' operator.
EditedBy supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
EstimatedCloseDate supports the '<=,<,=,!=,>,>=' operator.
GrossMargin supports the '<=,<,=,!=,>,>=' operator.
IsPrivate supports the '=' operator.
OpenDate supports the '<=,<,=,!=,>,>=' operator.
Probability supports the '<=,<,=,!=,>,>=' operator.
ProductTotal supports the '<=,<,=,!=,>,>=' operator.
Reason supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
RecordOwner supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
Source supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
StageProcessName supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
WeightedTotal supports the '<=,<,=,!=,>,>=' operator.
StageStartDate supports the '<=,<,=,!=,>,>=' operator.
SourceId supports the '<=,<,=,!=,>,>=,CONTAINS' operator.
ImportDate supports the '<=,<,=,!=,>,>=' operator.
RecordManager supports the '<=,<,=,!=,>,>=,CONTAINS' operator.

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

SELECT * FROM GroupOpportunities WHERE GroupId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c'

SELECT * FROM GroupOpportunities

Columns


Name	Type	Description
Id [KEY]	String	A unique identifier that represents the opportunity.
GroupId [KEY]	String	The unique identifier (id) for the group to retrieve opportunities from.
Created	Datetime	Created date of the opportunity.
Name	String	The opportunity name.
ActualCloseDate	Datetime	The date that the opportunity was closed.
CompaniesAggregate	String	List of companies that are associated to this opportunity entity.
Competitor	String	Gets the name of the competitor for this opportunity.
ContactNames	String	The names of the contacts associated with this opportunity.
ContactsAggregate	String	The names of the contacts associated with this opportunity.
Creator	String	The username of the user who created this opportunity.
DaysOpen	Integer	The number of days this opportunity has been opened.
Edited	Datetime	The date that this record was edited.
EditedBy	String	The user's name that last edited this record.
EstimatedCloseDate	Datetime	The date the opportunity is expected to be closed.
GrossMargin	Decimal	Represents the net sales less the cost of goods and services sold.
GroupsAggregate	String	List of groups that are associated to this opportunity entity.
IsPrivate	Boolean	Indicates whether this opportunity is private
Manager	String	The name of the record manager of this opportunity.
OpenDate	Datetime	The date the opportunity was opened.
Probability	Integer	The likelihood this opportunity will be won by the close date.
ProductTotal	Decimal	The total cost of the product or services.
Reason	String	Describes why the opportunity status changed.
RecordOwner	String	The user's name that owns this record.
RelatedEntitiesResolver	Boolean	If true prevents related entities from serializing.
Source	String	Describes the source of the opportunity.
StageDescription	String	Gets or sets the description of the stage.
StageId	String	Gets the unique identifier of the stage.
StageName	String	The name of the stage.
StageNumber	Integer	The ordinal number of the stage.
StageProbability	Integer	The probability of the stage.
StageProcessDescription	String	The description of the stage process.
StageProcessId	String	The unique identifier of the stage process.
StageProcessName	String	The name of the stage process.
Status	String	The status of the opportunity.
TotalPerCompany	Decimal	The average total per associated company.
TotalPerContact	Decimal	The average total per associated contact.
TotalPerGroup	Decimal	The average total per associated group.
WeightedTotal	Decimal	The weighted total value of the opportunity.
DaysInStage	Integer	The number of days this opportunity has been in a given stage.
StageStartDate	Datetime	The date the opportunity stage started.
SourceId	String	The external source id for the opportunity.
StageProcessStatus	String	Stage process status.
StageProcessStagesCount	Integer	Stage process stages count
StageProcessStages	String	Stage process stages
ImportDate	Datetime	This readonly property displays the date that the opportunity was imported into the system.
QuickBooksInvoiceId	String	Quickbooks external Id to match record with quickbooks contact.
RecordManagerId	String	The record manager.
CustomFields	String	Custom fields.
RecordManager	String	Manager of the opportunity record.
Acl	String	The access control level of the record.

KPI

Key performance indicator metrics.

View Specific Information

Select

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

GroupName supports the '=' operator.
Start supports the '=' operator.
End supports the '=' operator.

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

SELECT * FROM KPI WHERE GroupName = 'Opportunities'

Columns


Name	Type	Description
Label	String	The label of the KPI metric.
Value	Decimal	The value of the KPI metric.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
GroupName	String	The KPI group name to retrieve. Required.
Start	Datetime	The start date-time for the analytics date-time range.
End	Datetime	The end date-time for the analytics date-time range.

OpportunityCount

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

Table Specific Information

Select

The Cloud 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 Cloud processes other filters client-side within the Cloud. 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

OpportunityHistories

Get all the history related to an opportunity.

View Specific Information

Select

OpportunityId supports the '=' operator.

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

SELECT * FROM OpportunityHistories
SELECT * FROM OpportunityHistories WHERE OpportunityId = '12345'

Columns


Name	Type	Description
Id [KEY]	String	Id of the history item created
OpportunityId [KEY]	String	Id of the opportunity
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 created 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 Manager
Regarding	String	Tells what history is about
StartTime	Datetime	Start Time
ContactCompany	String	The company of the contact associated with this history.
ContactEmail	String	The email of the contact associated with this history.
ContactPhone	String	The phone number of the contact associated with this history.
ContactPhoneExt	String	The phone extension of the contact associated with this history.
CreateUserName	String	The username of the user who created this history.
EditUserID	String	The ID of the user who last edited this history.
EditUserName	String	The username of the user who last edited this history.
HistoryTypeActivityTypeName	String	The activity type name of the history type.
HistoryTypeActivityTypeId	Integer	The activity type ID of the history type.

OpportunityNotes

Query the Notes for a given Act! CRM Opportunity.

Table Specific Information

Select

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

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

SELECT * FROM OpportunityNotes WHERE OpportunityId = '12345'

Columns


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

OpportunityPipeline

The current user's opportunity pipeline analytic view settings.

View Specific Information

Select

All filter conditions for this view are processed client-side within the Cloud.

SELECT * FROM OpportunityPipeline

Columns


Name	Type	Description
UserId [KEY]	String	The unique identifier of the user whose pipeline settings this represents.
FiscalYearStartMonth	Integer	The month (1-12) the user's fiscal year starts.
CurrencySettingsSymbol	String	The currency symbol used in pipeline views.
CurrencySettingsDecimalSeparator	String	The character used as the decimal separator.
CurrencySettingsGroupSeparator	String	The character used as the thousands group separator.
CurrencySettingsSymbolPosition	String	The position of the currency symbol relative to the value.
FilterPreferences	String	The user's saved filter preferences for the opportunity pipeline view.

OpportunityStages

Get the stages defined in the opportunity pipeline.

View Specific Information

Select

All filter conditions for this view are processed client-side within the Cloud.

SELECT * FROM OpportunityStages

Columns


Name	Type	Description
Id [KEY]	String	The unique identifier of the stage.
Name	String	The name of the stage.
Description	String	A description of the stage.
Probability	Integer	The default probability percentage for this stage.
Number	Integer	The sequence number of this stage within the process.
ProcessId	String	The unique identifier of the process this stage belongs to.
ProcessName	String	The name of the process this stage belongs to.
ProcessDescription	String	A description of the process this stage belongs to.
ProcessStatus	String	The status of the process this stage belongs to.
ProcessStagesCount	Integer	The total number of stages in the process.

Pipeline

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

Table Specific Information

Select

The Cloud 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 Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side:

SELECT * FROM Pipeline

Columns


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

RevenueActualVsPredicted

Generates actual vs predicted opportunity revenue.

Table Specific Information

Select

The Cloud 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 Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud uses the Act! CRM API to process search criteria that refer to the supported fields. The Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud will use the Act! CRM API to process WHERE clause conditions built with the following column and operator.

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

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

SELECT * FROM RevenueWonVsLost

Columns


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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side:

SELECT * FROM SalesByProduct

Columns


Name	Type	Description
Percent	Decimal	Percent
ProductId	String	Product Id
ProductName	String	Product Name
Revenue	Decimal	Revenue

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud.

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

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

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

Columns


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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side:

SELECT * FROM SalesByRep

Columns


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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud

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

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

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

Columns


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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
Date	Datetime	Date Range of Activities By User

TopOpenOpportunities

Highest-value open opportunities.

View Specific Information

Select

All filter conditions for this view are processed client-side within the Cloud.

SELECT * FROM TopOpenOpportunities

Columns


Name	Type	Description
Id [KEY]	String	The unique identifier of the opportunity.
Name	String	The name of the opportunity.
ContactName	String	The name of the primary contact for the opportunity.
CompanyName	String	The name of the company associated with the opportunity.
StageId	String	The identifier of the current pipeline stage.
StageName	String	The name of the current pipeline stage.
EstCloseDate	Datetime	The estimated close date of the opportunity.
TotalValue	Decimal	The total value of the opportunity.
WeightedValue	Decimal	The probability-weighted value of the opportunity.
ContactCount	Integer	The number of contacts associated with the opportunity.
ProbabilityPct	Integer	The probability percentage of closing the opportunity.

UserActivities

Generates activity sales by user data for given users.

Table Specific Information

Select

The Cloud 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 Cloud processes other filters client-side within the Cloud. 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

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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 Cloud.

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

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

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

Columns


Name	Type	Description
ActivityId	String	Activity Id
ContactName	String	Contact Name
StartTime	Datetime	The start time of the user activity.
Location	String	Location
Regarding	String	Regarding
Type	String	Type
UserName	String	User Name
TypeId	String	Type Id
UserId	String	UserId

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data 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 Cloud 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.

CData Cloud - Act! CRM 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
AddOrRemoveCompaniesToContact	Associate or dissociate one or more companies to a contact.
AddOrRemoveContactToActivity	Add or remove the contact to an activity
AddOrRemoveContactToCompany	Associate the contact (if it is not already present) to a company.
AddOrRemoveContactToHistory	Associate or dissociate a contact to a history record.
AddOrRemoveGroupsToContact	Associate or dissociate one or more groups to a contact.
AddOrRemoveOpportunitiesToCompany	Associate or dissociate one or more opportunities to a company.
AddOrRemoveOpportunitiesToContact	Associate or dissociate one or more opportunities to a contact.
AddOrRemoveOpportunitiesToGroup	Associate or dissociate one or more opportunities to a group.
ClearActivity	Clears an activity.
ClearActivityAlarms	Clears an activity alarms
ClearOrUnclearTasks	Clear or unclear one or more tasks in bulk.
ClearTaskAlarms	Clear alarms for one or more tasks in bulk.
ConvertContactToUser	Convert an existing contact to a user.
DeleteAttachment	Delete an attachment from an Act! CRM entity.
DownloadAttachment	Download an attachment from an Act! CRM entity.
DuplicateContacts	Duplicate one or more contacts.
DuplicateTasks	Duplicate one or more tasks in bulk.
PromoteSecondaryContact	Promote a secondary contact to primary.
SubscribeContactToHotLeads	Subscribes a contact to hot-leads
UnclearActivity	Unclears an activity.
UpdateContactAccessLevel	Updates access level of an already existing contact.
UploadAttachment	Upload an attachment to an Act! CRM entity.

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

AddOrRemoveCompaniesToContact

Associate or dissociate one or more companies to a contact.

Stored Procedures Specific Information

Process of adding or removing companies to contact

This stored procedure associates or dissociates one or more companies to a contact. The required parameter ContactId accepts a single value. The required parameter CompanyIds accepts a comma-separated list of company Ids. The required parameter Type accepts a single value: 'add' or 'remove'. For example:

For Adding Companies To Contact

EXEC AddOrRemoveCompaniesToContact ContactId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c', CompanyIds = '5d940c2d-e09e-4204-ad0a-375f73a550a7', Type = 'add'

For Removing Companies From Contact

EXEC AddOrRemoveCompaniesToContact ContactId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c', CompanyIds = '5d940c2d-e09e-4204-ad0a-375f73a550a7', Type = 'remove'

Input


Name	Type	Description
ContactId	String	The unique identifier (id) for a given contact.
CompanyIds	String	A comma-separated list of company ids to associate or dissociate.
Type	String	Whether to add or remove the companies. Set to 'add' or 'remove'. The allowed values are add, remove.

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. The allowed values are add, remove.

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 The allowed values are add, remove.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

AddOrRemoveContactToHistory

Associate or dissociate a contact to a history record.

Stored Procedures Specific Information

Process of adding or removing contact to history

This stored procedure associates or dissociates a contact to a history record. The required parameters HistoryId and ContactId each accept a single value. The required parameter Type accepts a single value: 'add' or 'remove'. For example:

For Adding Contact To History

EXEC AddOrRemoveContactToHistory HistoryId = 'aafe05a3-3654-4ee4-8162-4ebc4f25f789', ContactId = '75e724dc-8f62-4070-bb87-38787eed09e3', Type = 'add'

For Removing Contact From History

EXEC AddOrRemoveContactToHistory HistoryId = 'aafe05a3-3654-4ee4-8162-4ebc4f25f789', ContactId = '75e724dc-8f62-4070-bb87-38787eed09e3', Type = 'remove'

Input


Name	Type	Description
HistoryId	String	The unique identifier (id) for a given history record.
ContactId	String	The unique identifier (id) for a given contact.
Type	String	Whether to add or remove the contact. Set to 'add' or 'remove'. The allowed values are add, remove.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

AddOrRemoveGroupsToContact

Associate or dissociate one or more groups to a contact.

Stored Procedures Specific Information

Process of adding or removing groups to contact

This stored procedure associates or dissociates one or more groups to a contact. The required parameter ContactId accepts a single value. The required parameter GroupIds accepts a comma-separated list of group Ids. The required parameter Type accepts a single value: 'add' or 'remove'. For example:

For Adding Groups To Contact

EXEC AddOrRemoveGroupsToContact ContactId = '75e724dc-8f62-4070-bb87-38787eed09e3', GroupIds = '49006ae2-0f89-4e92-9302-2472de8e19f1', Type = 'add'

For Removing Groups From Contact

EXEC AddOrRemoveGroupsToContact ContactId = '75e724dc-8f62-4070-bb87-38787eed09e3', GroupIds = '49006ae2-0f89-4e92-9302-2472de8e19f1', Type = 'remove'

Input


Name	Type	Description
ContactId	String	The unique identifier (id) for a given contact.
GroupIds	String	A comma-separated list of group ids to associate or dissociate.
Type	String	Whether to add or remove the groups. Set to 'add' or 'remove'. The allowed values are add, remove.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

AddOrRemoveOpportunitiesToCompany

Associate or dissociate one or more opportunities to a company.

Stored Procedures Specific Information

Process of adding or removing opportunities to company

This stored procedure associates or dissociates one or more opportunities to a company. The required parameter CompanyId accepts a single value. The required parameter OpportunityIds accepts a comma-separated list of opportunity Ids. The required parameter Type accepts a single value: 'add' or 'remove'. For example:

For Adding Opportunities To Company

EXEC AddOrRemoveOpportunitiesToCompany CompanyId = '5d940c2d-e09e-4204-ad0a-375f73a550a7', OpportunityIds = '4a6ffff1-5872-42a6-b98c-4a24a19ba585', Type = 'add'

For Removing Opportunities From Company

EXEC AddOrRemoveOpportunitiesToCompany CompanyId = '5d940c2d-e09e-4204-ad0a-375f73a550a7', OpportunityIds = '4a6ffff1-5872-42a6-b98c-4a24a19ba585', Type = 'remove'

Input


Name	Type	Description
CompanyId	String	The unique identifier (id) for a given company.
OpportunityIds	String	A comma-separated list of opportunity ids to associate or dissociate.
Type	String	Whether to add or remove the opportunities. Set to 'add' or 'remove'. The allowed values are add, remove.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

AddOrRemoveOpportunitiesToContact

Associate or dissociate one or more opportunities to a contact.

Stored Procedures Specific Information

Process of adding or removing opportunities to contact

This stored procedure associates or dissociates one or more opportunities to a contact. The required parameter ContactId accepts a single value. The required parameter OpportunityIds accepts a comma-separated list of opportunity Ids. The required parameter Type accepts a single value: 'add' or 'remove'. For example:

For Adding Opportunities To Contact

EXEC AddOrRemoveOpportunitiesToContact ContactId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c', OpportunityIds = '4a6ffff1-5872-42a6-b98c-4a24a19ba585', Type = 'add'

For Removing Opportunities From Contact

EXEC AddOrRemoveOpportunitiesToContact ContactId = 'a58016a6-780c-48c0-84ff-b6b6b085eb8c', OpportunityIds = '4a6ffff1-5872-42a6-b98c-4a24a19ba585', Type = 'remove'

Input


Name	Type	Description
ContactId	String	The unique identifier (id) for a given contact.
OpportunityIds	String	A comma-separated list of opportunity ids to associate or dissociate.
Type	String	Whether to add or remove the opportunities. Set to 'add' or 'remove'. The allowed values are add, remove.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

AddOrRemoveOpportunitiesToGroup

Associate or dissociate one or more opportunities to a group.

Stored Procedures Specific Information

Process of adding or removing opportunities to group

This stored procedure associates or dissociates one or more opportunities to a group. The required parameter GroupId accepts a single value. The required parameter OpportunityIds accepts a comma-separated list of opportunity Ids. The required parameter Type accepts a single value: 'add' or 'remove'. For example:

For Adding Opportunities To Group

EXEC AddOrRemoveOpportunitiesToGroup GroupId = '49006ae2-0f89-4e92-9302-2472de8e19f1', OpportunityIds = '4a6ffff1-5872-42a6-b98c-4a24a19ba585', Type = 'add'

For Removing Opportunities From Group

EXEC AddOrRemoveOpportunitiesToGroup GroupId = '49006ae2-0f89-4e92-9302-2472de8e19f1', OpportunityIds = '4a6ffff1-5872-42a6-b98c-4a24a19ba585', Type = 'remove'

Input


Name	Type	Description
GroupId	String	The unique identifier (id) for a given group.
OpportunityIds	String	A comma-separated list of opportunity ids to associate or dissociate.
Type	String	Whether to add or remove the opportunities. Set to 'add' or 'remove'. The allowed values are add, remove.

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

ClearOrUnclearTasks

Clear or unclear one or more tasks in bulk.

Stored Procedures Specific Information

Process of clearing or unclearing tasks

This stored procedure clears or unclears one or more tasks in bulk. The required parameter TaskIds accepts a comma-separated list of task Ids. The required parameter Type accepts a single value: 'clear' or 'unclear'. For example:

For Clearing Tasks

EXEC ClearOrUnclearTasks TaskIds = '8474041f-984d-4f15-8360-60348bfeab5207172026', Type = 'clear'

For Unclearing Tasks

EXEC ClearOrUnclearTasks TaskIds = '8474041f-984d-4f15-8360-60348bfeab5207172026', Type = 'unclear'

Input


Name	Type	Description
TaskIds	String	A comma-separated list of task ids to clear or unclear.
Type	String	Whether to clear or unclear the tasks. The allowed values are clear, unclear.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

ClearTaskAlarms

Clear alarms for one or more tasks in bulk.

Stored Procedures Specific Information

Process of clearing task alarms

This stored procedure clears alarms for one or more tasks in bulk. The required parameter TaskIds accepts a comma-separated list of task Ids. For example:

EXEC ClearTaskAlarms TaskIds = '5198476a-e209-40b8-b4ed-5d0675b1307707012026';

Input


Name	Type	Description
TaskIds	String	A comma-separated list of task ids whose alarms will be cleared.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

ConvertContactToUser

Convert an existing contact to a user.

Stored Procedures Specific Information

Process of converting contact to user

This stored procedure converts an existing contact to a user. The required parameters ContactId, ContactUsername, ContactPassword, and RoleName each accept a single value. Optional parameters include AccountStatus (accepts 'Active', 'Inactive', or 'Deleted'), MustChangePassword, CanChangePassword, PasswordExpires, and Permissions, each accepting a single value. For example:

EXEC ConvertContactToUser ContactId = 'd444d1ac-fd29-42ba-8635-4d484f62c821', RoleName = 'Standard', ContactUsername = '[email protected]', ContactPassword = 'P1a@df!';

Input


Name	Type	Description
ContactId	String	The unique identifier (id) for the contact to convert.
ContactUsername	String	A unique login id for the new user.
ContactPassword	String	The password used to login.
RoleName	String	The role name to assign to the user.
AccountStatus	String	The status of the user account. The allowed values are Active, Inactive, Deleted.
MustChangePassword	Boolean	Whether the user must change their password on next login.
CanChangePassword	Boolean	Whether the user is allowed to change their password.
PasswordExpires	Boolean	Whether the user's password expires.
Permissions	String	A JSON aggregate of permissions to assign to the user.

Result Set Columns


Name	Type	Description
Id	String	The unique identifier of the newly created user.
Username	String	The login username of the newly created user.
Role	String	The role assigned to the newly created user.
DisplayName	String	The display name of the newly created user.
Email	String	The email address of the newly created user.
FirstName	String	The first name of the newly created user.
LastName	String	The last name of the newly created user.
LastLogOnDate	Datetime	The date and time the user last logged on.
UserStatus	String	The status of the newly created user
Status	String	Stored Procedure Execution Status

DeleteAttachment

Delete an attachment from an Act! CRM entity.

Stored Procedures Specific Information

Process of deleting attachment

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 DeleteAttachment are EntityType and EntityId. For example:

EXEC DeleteAttachment EntityType = 'notes', EntityId = 'e29228c9-db9e-4b96-bd62-bfa8437a354b';

Input


Name	Type	Description
EntityType	String	The type of entity the attachment belongs to. The allowed values are activities, documents, history, notes.
EntityId	String	The unique identifier of the entity.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

DownloadAttachment

Download an attachment from an Act! CRM entity.

Stored Procedures Specific Information

Process of downloading attachment

This stored procedure downloads an attachment from an Act! CRM entity. The required parameters EntityType (accepts a single value: 'activities', 'documents', 'history', or 'notes') and EntityId (accepts a single value) identify the entity. The optional parameter LocalPath accepts a full file path to write to an exact location. If LocalPath is omitted, the optional FileStream parameter can be used to write the file data directly to an output stream; otherwise the file content is returned as Base64 in the FileData output column. For example:

Return file content as Base64

EXEC DownloadAttachment EntityType = 'notes', EntityId = '683fde56-eec0-4f13-aa51-40ff890f151d';

Write file to a local path

EXEC DownloadAttachment EntityType = 'notes', EntityId = '683fde56-eec0-4f13-aa51-40ff890f151d', LocalPath = 'D:/Logs/test.png';

Write file to an output stream

EXEC DownloadAttachment EntityType = 'notes', EntityId = '683fde56-eec0-4f13-aa51-40ff890f151d', FileStream = @outputStream;

Input


Name	Type	Description
EntityType	String	The type of entity the attachment belongs to. The allowed values are activities, documents, history, notes.
EntityId	String	The unique identifier of the entity.
LocalPath	String	The local path to write the downloaded file to. A full file path to write to an exact location. If not specified, the file data is returned as Base64.

Result Set Columns


Name	Type	Description
FileData	String	The Base64-encoded file content. Populated when LocalPath and FileStream are not provided.
LocalPath	String	The resolved local file path where the attachment was saved. Populated when LocalPath is provided as input.
Status	String	Stored Procedure Execution Status

DuplicateContacts

Duplicate one or more contacts.

Stored Procedures Specific Information

Process of duplicating contacts

This stored procedure duplicates one or more contacts. The required parameter ContactIds accepts a comma-separated list of contact Ids. The optional parameter PrimaryFields accepts a single boolean value; if true, only primary fields are copied to the duplicate. For example:

EXEC DuplicateContacts ContactIds = '430ba35a-66cf-4251-a249-15fbdb4728ba', PrimaryFields = true;

Input


Name	Type	Description
ContactIds	String	A comma-separated list of contact ids to duplicate.
PrimaryFields	Boolean	If true, the duplicated contact will only copy primary fields. Default is false.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

DuplicateTasks

Duplicate one or more tasks in bulk.

Stored Procedures Specific Information

Process of duplicating tasks

This stored procedure duplicates one or more tasks in bulk. The required parameter TaskIds accepts a comma-separated list of task Ids. For example:

EXEC DuplicateTasks TaskIds = '1089facd-46da-4b18-a2bc-7630d8b0243406222026';

Input


Name	Type	Description
TaskIds	String	A comma-separated list of task ids to duplicate.

Result Set Columns


Name	Type	Description
Status	String	Stored Procedure Execution Status

PromoteSecondaryContact

Promote a secondary contact to primary.

Stored Procedures Specific Information

Process of promoting secondary contact

This stored procedure promotes a secondary contact to primary. The required parameters ContactId and SecondaryContactId each accept a single value. For example:

EXEC PromoteSecondaryContact ContactId = 'cf83735d-43d6-4add-95af-5d2829018768', SecondaryContactId = '6664073c-4658-4511-ae4f-7371eec48679';

Input


Name	Type	Description
ContactId	String	The unique identifier (id) for the primary contact.
SecondaryContactId	String	The unique identifier (id) for the secondary contact to promote.

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. This column can only be specified as part of AccessorAggregate when using a temp table.
AccessorType	String	The type of the accessor. This column can only be specified as part of AccessorAggregate when using a temp table.
Name	String	The name of the accessor. This column can only be specified as part of AccessorAggregate when using a temp table.
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. Use this input to specify the Id, AccessorType, and Name values for the accessor.

Result Set Columns


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

UploadAttachment

Upload an attachment to an Act! CRM entity.

Stored Procedures Specific Information

Process of uploading attachment

This stored procedure uploads an attachment to an Act! CRM entity. The required parameters EntityType (accepts a single value: 'activities', 'documents', 'history', or 'notes') and EntityId (accepts a single value) identify the target entity. Specify either the optional FilePath parameter (local path of the file to upload) or the Content parameter (file content as an InputStream) to provide the file data. When using Content (file content as an InputStream), the FileName parameter (including extension) is also required. For example:

Upload from a local file path

EXEC UploadAttachment EntityType = 'notes', EntityId = 'e29228c9-db9e-4b96-bd62-bfa8437a354b', FilePath = 'D:/Logs/kpi.png';

Upload from an input stream

EXEC UploadAttachment EntityType = 'notes', EntityId = 'e29228c9-db9e-4b96-bd62-bfa8437a354b', Content = @inputStream, FileName = 'kpi.png';

Input


Name	Type	Description
EntityType	String	The type of entity to attach the file to. The allowed values are activities, documents, history, notes.
EntityId	String	The unique identifier of the entity.
FilePath	String	The local path of the file to upload.
FileName	String	The file name including extension. Required when Content is specified instead of FilePath.

Result Set Columns


Name	Type	Description
DisplayName	String	The display name of the uploaded attachment.
FileExtension	String	The file extension of the uploaded attachment (e.g. '.png').
FileName	String	The file name of the uploaded attachment.
FileSize	Long	The size of the uploaded attachment in bytes.
FileSizeDisplay	String	The human-readable size of the uploaded attachment (e.g. '12 KB').
FileType	String	The registered system file type name of the uploaded attachment (e.g. 'PNG Image').
LastModified	Datetime	The date and time the attachment was last modified.
Personal	Boolean	Whether the attachment is personal (true) or shared with the workgroup (false).
Status	String	Stored Procedure Execution Status

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 Cloud 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.
IsInsertable	Boolean	Whether the table can be inserted into.
IsDeleteable	Boolean	Whether rows can be deleted from the table.

sys_tablecolumns

Describes the columns of the available tables and views.

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

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

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.
ColumnType	String	The role or classification of the column in the schema. Possible values include SYSTEM, LINKEDCOLUMN, NAVIGATIONKEY, REFERENCECOLUMN, and NAVIGATIONPARENTCOLUMN.
ColumnCapabilities	Int32	A bit mask denoting the column's write capabilities. The value is the sum of the following: 1 if the column is required for INSERTs, 2 if the column is allowed for INSERTs, and 4 if the column is allowed for UPDATEs. A value of 0 indicates that the write capabilities of the column are unknown or that the column is read-only.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures

Columns


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

sys_procedureparameters

Describes stored procedure parameters.

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

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

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

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'StoredProc' AND IncludeResultColumns='True'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
DataTypeName	String	The name of the data type.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.
Values	String	The values you can set in this parameter are limited to those shown in this column. Possible values are comma-separated.
SupportsStreams	Boolean	Whether the parameter represents a file that you can pass as either a file path or a stream.
IsPath	Boolean	Whether the parameter is a target path for a schema creation operation.
Default	String	The value used for this parameter when no value is specified.
SpecificName	String	A label that, when multiple stored procedures have the same name, uniquely identifies each identically-named stored procedure. If there's only one procedure with a given name, its name is simply reflected here.
IsCDataProvided	Boolean	Whether the procedure is added/implemented by CData, as opposed to being a native Act! CRM procedure.

Pseudocolumns


Name	Type	Description
IncludeResultColumns	Boolean	Whether the output should include columns from the result set in addition to parameters. Defaults to False.

sys_keycolumns

Describes the primary and foreign keys.

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

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

Columns


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

sys_foreignkeys

Describes the foreign keys.

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

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns


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

sys_primarykeys

Describes the primary keys.

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

         SELECT * FROM sys_primarykeys

Columns


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

sys_indexes

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

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

          SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


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

sys_connection_props

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

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

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


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

sys_sqlinfo

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

See SQL Compliance for SQL syntax details.

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
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
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 CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData 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.

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns


Name	Type	Description
Product	String	The name of the product.
Version	String	The version number of the product.
Datasource	String	The name of the datasource the product connects to.
NodeId	String	The unique identifier of the machine where the product is installed.
HelpURL	String	The URL to the product's help documentation.
License	String	The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location	String	The file path location where the product's library is stored.
Environment	String	The version of the environment or rumtine the product is currently running under.
DataSyncVersion	String	The tier of CData Sync required to use this connector.
DataSyncCategory	String	The category of CData Sync functionality (e.g., Source, Destination).

Connection String Options

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

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

SSL

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

Logging

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Schema

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

Miscellaneous

Property	Description
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Pagesize	Specifies the maximum number of records per page the provider returns when requesting data from Act! CRM.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
ThrowsKeyNotFound	Specifies whether or not throws an exception if there is no rows updated.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.
TypeDetectionScheme	Enables scanning Act! CRM Contact entities to determine unique columns.

Authentication

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

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

URL

Identifies the URL of the ActCRM account.

Data Type

string

Default Value

""

Remarks

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

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

ActCloudRegion

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

Possible Values

US, UK, AUS, NZ, EU, InternationalEnglish

Data Type

string

Default Value

"US"

Remarks

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

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

Default Value

"Act CRM"

Remarks

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

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

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

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

SSLServerCert

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

Data Type

string

Default Value

""

Remarks

If you are using a TLS/SSL connection, use this property to specify the TLS/SSL certificate to be accepted from the server. If you specify a value for this property, all other certificates that are not trusted by the machine are 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`

Note: It is possible to use '*' to signify that all certificates should be accepted, but due to security concerns this is not recommended.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Verbosity

Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Data Type

string

Default Value

"1"

Remarks

This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.

The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.

When combined with the LogModules property, Verbosity can refine logging to specific categories of information.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

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

BrowsableSchemas

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

Data Type

string

Default Value

""

Remarks

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

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Property	Description
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Pagesize	Specifies the maximum number of records per page the provider returns when requesting data from Act! CRM.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
RowScanDepth	The maximum number of rows to scan to look for the columns available in a table.
ThrowsKeyNotFound	Specifies whether or not throws an exception if there is no rows updated.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.
TypeDetectionScheme	Enables scanning Act! CRM Contact entities to determine unique columns.

Default Value

""

Remarks

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

To specify individual pseudocolumns, use the following format:

Table1=Column1;Table1=Column2;Table2=Column3

To include all pseudocolumns for all tables use:

*=*

RowScanDepth

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

Data Type

int

Default Value

500

Remarks

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

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

ThrowsKeyNotFound

Specifies whether or not throws an exception if there is no rows updated.

Data Type

bool

Default Value

false

Remarks

Specifies whether or not throws an exception if there is no rows updated.

Timeout

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

Data Type

int

Default Value

60

Remarks

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

Timeout is set to 60 seconds by default. To disable timeouts, set this property to 0.

Disabling the timeout allows operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server.

Note: Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

TypeDetectionScheme

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

Possible Values

RowScan, None

Data Type

string

Default Value

"RowScan"

Remarks

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

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


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.

Third Party Copyrights

LZMA from 7Zip LZMA SDK

LZMA SDK is placed in the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original LZMA SDK code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

LZMA2 from XZ SDK

Version 1.9 and older are in the public domain.

Xamarin.Forms

Xamarin SDK

The MIT License (MIT)

Copyright (c) .NET Foundation Contributors

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

NSIS 3.10

Copyright (C) 1999-2025 Contributors THE ACCOMPANYING PROGRAM IS PROVIDED UNDER THE TERMS OF THIS COMMON PUBLIC LICENSE ("AGREEMENT"). ANY USE, REPRODUCTION OR DISTRIBUTION OF THE PROGRAM CONSTITUTES RECIPIENT'S ACCEPTANCE OF THIS AGREEMENT.

1. DEFINITIONS

"Contribution" means:

a) in the case of the initial Contributor, the initial code and documentation distributed under this Agreement, and b) in the case of each subsequent Contributor:

i) changes to the Program, and

ii) additions to the Program;

where such changes and/or additions to the Program originate from and are distributed by that particular Contributor. A Contribution 'originates' from a Contributor if it was added to the Program by such Contributor itself or anyone acting on such Contributor's behalf. Contributions do not include additions to the Program which: (i) are separate modules of software distributed in conjunction with the Program under their own license agreement, and (ii) are not derivative works of the Program.

"Contributor" means any person or entity that distributes the Program.

"Licensed Patents " mean patent claims licensable by a Contributor which are necessarily infringed by the use or sale of its Contribution alone or when combined with the Program.

"Program" means the Contributions distributed in accordance with this Agreement.

"Recipient" means anyone who receives the Program under this Agreement, including all Contributors.

2. GRANT OF RIGHTS

a) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free copyright license to reproduce, prepare derivative works of, publicly display, publicly perform, distribute and sublicense the Contribution of such Contributor, if any, and such derivative works, in source code and object code form.

b) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free patent license under Licensed Patents to make, use, sell, offer to sell, import and otherwise transfer the Contribution of such Contributor, if any, in source code and object code form. This patent license shall apply to the combination of the Contribution and the Program if, at the time the Contribution is added by the Contributor, such addition of the Contribution causes such combination to be covered by the Licensed Patents. The patent license shall not apply to any other combinations which include the Contribution. No hardware per se is licensed hereunder.

c) Recipient understands that although each Contributor grants the licenses to its Contributions set forth herein, no assurances are provided by any Contributor that the Program does not infringe the patent or other intellectual property rights of any other entity. Each Contributor disclaims any liability to Recipient for claims brought by any other entity based on infringement of intellectual property rights or otherwise. As a condition to exercising the rights and licenses granted hereunder, each Recipient hereby assumes sole responsibility to secure any other intellectual property rights needed, if any. For example, if a third party patent license is required to allow Recipient to distribute the Program, it is Recipient's responsibility to acquire that license before distributing the Program.

d) Each Contributor represents that to its knowledge it has sufficient copyright rights in its Contribution, if any, to grant the copyright license set forth in this Agreement.

3. REQUIREMENTS

A Contributor may choose to distribute the Program in object code form under its own license agreement, provided that:

a) it complies with the terms and conditions of this Agreement; and

b) its license agreement:

i) effectively disclaims on behalf of all Contributors all warranties and conditions, express and implied, including warranties or conditions of title and non-infringement, and implied warranties or conditions of merchantability and fitness for a particular purpose;

ii) effectively excludes on behalf of all Contributors all liability for damages, including direct, indirect, special, incidental and consequential damages, such as lost profits;

iii) states that any provisions which differ from this Agreement are offered by that Contributor alone and not by any other party; and

iv) states that source code for the Program is available from such Contributor, and informs licensees how to obtain it in a reasonable manner on or through a medium customarily used for software exchange.

When the Program is made available in source code form:

a) it must be made available under this Agreement; and

b) a copy of this Agreement must be included with each copy of the Program.

Contributors may not remove or alter any copyright notices contained within the Program.

Each Contributor must identify itself as the originator of its Contribution, if any, in a manner that reasonably allows subsequent Recipients to identify the originator of the Contribution.

4. COMMERCIAL DISTRIBUTION

Commercial distributors of software may accept certain responsibilities with respect to end users, business partners and the like. While this license is intended to facilitate the commercial use of the Program, the Contributor who includes the Program in a commercial product offering should do so in a manner which does not create potential liability for other Contributors. Therefore, if a Contributor includes the Program in a commercial product offering, such Contributor ("Commercial Contributor") hereby agrees to defend and indemnify every other Contributor ("Indemnified Contributor") against any losses, damages and costs (collectively "Losses") arising from claims, lawsuits and other legal actions brought by a third party against the Indemnified Contributor to the extent caused by the acts or omissions of such Commercial Contributor in connection with its distribution of the Program in a commercial product offering. The obligations in this section do not apply to any claims or Losses relating to any actual or alleged intellectual property infringement. In order to qualify, an Indemnified Contributor must: a) promptly notify the Commercial Contributor in writing of such claim, and b) allow the Commercial Contributor to control, and cooperate with the Commercial Contributor in, the defense and any related settlement negotiations. The Indemnified Contributor may participate in any such claim at its own expense.

For example, a Contributor might include the Program in a commercial product offering, Product X. That Contributor is then a Commercial Contributor. If that Commercial Contributor then makes performance claims, or offers warranties related to Product X, those performance claims and warranties are such Commercial Contributor's responsibility alone. Under this section, the Commercial Contributor would have to defend claims against the other Contributors related to those performance claims and warranties, and if a court requires any other Contributor to pay any damages as a result, the Commercial Contributor must pay those damages.

5. NO WARRANTY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, THE PROGRAM IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER EXPRESS OR IMPLIED INCLUDING, WITHOUT LIMITATION, ANY WARRANTIES OR CONDITIONS OF TITLE, NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Each Recipient is solely responsible for determining the appropriateness of using and distributing the Program and assumes all risks associated with its exercise of rights under this Agreement, including but not limited to the risks and costs of program errors, compliance with applicable laws, damage to or loss of data, programs or equipment, and unavailability or interruption of operations.

6. DISCLAIMER OF LIABILITY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, NEITHER RECIPIENT NOR ANY CONTRIBUTORS SHALL HAVE ANY LIABILITY FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING WITHOUT LIMITATION LOST PROFITS), HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OR DISTRIBUTION OF THE PROGRAM OR THE EXERCISE OF ANY RIGHTS GRANTED HEREUNDER, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

7. GENERAL

If any provision of this Agreement is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this Agreement, and without further action by the parties hereto, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable.

If Recipient institutes patent litigation against a Contributor with respect to a patent applicable to software (including a cross-claim or counterclaim in a lawsuit), then any patent licenses granted by that Contributor to such Recipient under this Agreement shall terminate as of the date such litigation is filed. In addition, if Recipient institutes patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Program itself (excluding combinations of the Program with other software or hardware) infringes such Recipient's patent(s), then such Recipient's rights granted under Section 2(b) shall terminate as of the date such litigation is filed.

All Recipient's rights under this Agreement shall terminate if it fails to comply with any of the material terms or conditions of this Agreement and does not cure such failure in a reasonable period of time after becoming aware of such noncompliance. If all Recipient's rights under this Agreement terminate, Recipient agrees to cease use and distribution of the Program as soon as reasonably practicable. However, Recipient's obligations under this Agreement and any licenses granted by Recipient relating to the Program shall continue and survive.

Everyone is permitted to copy and distribute copies of this Agreement, but in order to avoid inconsistency the Agreement is copyrighted and may only be modified in the following manner. The Agreement Steward reserves the right to publish new versions (including revisions) of this Agreement from time to time. No one other than the Agreement Steward has the right to modify this Agreement. IBM is the initial Agreement Steward. IBM may assign the responsibility to serve as the Agreement Steward to a suitable separate entity. Each new version of the Agreement will be given a distinguishing version number. The Program (including Contributions) may always be distributed subject to the version of the Agreement under which it was received. In addition, after a new version of the Agreement is published, Contributor may elect to distribute the Program (including its Contributions) under the new version. Except as expressly stated in Sections 2(a) and 2(b) above, Recipient receives no rights or licenses to the intellectual property of any Contributor under this Agreement, whether expressly, by implication, estoppel or otherwise. All rights in the Program not expressly granted under this Agreement are reserved.

This Agreement is governed by the laws of the State of New York and the intellectual property laws of the United States of America. No party to this Agreement will bring a legal action under this Agreement more than one year after the cause of action arose. Each party waives its rights to a jury trial in any resulting litigation.

Cloud

Act! CRM - CData Cloud

Overview

Key Features

Getting Started

Connecting to Act! CRM

Accessing Data from CData Cloud Services

Establishing a Connection

Connecting to Act! CRM

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

Views

Tables

CData Cloud - Act! CRM Tables

Activities

Table Specific Information

Select

Insert

Update

Delete

Columns

Companies

Table Specific Information

Select

Insert

Update

Delete

Columns

CompanyAccessControl

Table Specific Information

Select

Update

Columns

CompanyContacts

Table Specific Information

Select

Delete

Columns

ContactNotes

Table Specific Information

Select

Delete

Columns

Contacts

Table Specific Information

Select

Insert

Update

Delete

Columns

Documents

Table Specific Information

Select

Insert

Delete

Columns

GroupAccessControl

Table Specific Information

Select

Update

Columns

GroupContacts

Table Specific Information

Select

Delete

Columns

Groups

Table Specific Information

Select

Insert

Update

Delete

Columns

Histories

Table Specific Information