Active Campaign Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 23.0.8804 of the connector.
Get Started¶
ActiveCampaign Version Support
The connector leverages version 3 of the ActiveCampaign API to enable bidirectional access to ActiveCampaign data.
Establish a Connection¶
Connect to ActiveCampaign¶
ActiveCampaign supports authenticating with the API Key. To connect to ActiveCampaign, in your account navigate to the Developer
page and set the following:
URL
: Navigate to theMy Settings
page to set this property. For example:https://<yourAccountName>.api-us1.com
APIKey
: Navigate to theSettings
page to set this property. Each user in your ActiveCampaign account has their own unique API key.
Make sure to distinguish between My Settings
and Settings
when setting these properties.
Important Notes¶
Configuration Files and Their Paths¶
- All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.
Data Model¶
Overview
The connector models ActiveCampaign resources like Contacts, Accounts as relational tables, allowing you to write SQL to query ActiveCampaign data.
Live connectivity to these objects means any changes to your ActiveCampaign account are immediately reflected when using the connector.
Views
Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.
Dynamic Views
Along with the default static views, the connector also allows querying on dynamic views.
These are views that are created based on the "lists" (also called "audiences") in your ActiveCampaign account.
For example, suppose you have these 3 lists in your account: Master Contact List, New Audience, VIP List.
The connector lists 3 views based on them: List_MasterList, List_New Audience, List_VIP List.
The views return data about each contact that is a member of that specific list.
Tables
Tables describes the available tables. Tables are statically defined to model resources like Accounts, Deals, DealGroups, DealStages, Tasks, etc.
The connector also supports querying tables like AccountCustomFieldData, DealCustomFieldData, ContactCustomFieldData which represent custom field data information.
Additionally, the connector supports updating, inserting custom fields data from the main resource (available for the Accounts, Contacts, Deals tables) where custom fields are dynamically appended as main resource columns. See the specific table section for more information.
Tables¶
The connector models the data in ActiveCampaign as a list of tables in a relational database that can be queried using standard SQL statements.
Jitterbit Connector for ActiveCampaign Tables¶
Name | Description |
---|---|
AccountContacts | Usage information for the operation AccountContacts.rsd. |
AccountCustomFieldData | Create, modify, delete and query the available deal custom field data in ActiveCampaign. |
Accounts | View many or all contacts by including their ID's or various filters. |
ContactCustomFieldData | Create, modify, delete and query the available contact custom field data in ActiveCampaign. |
Contacts | View many or all contacts by including their ID's or various filters. |
DealCustomFieldData | Create, modify, delete and query the available deal custom field data in ActiveCampaign. |
DealGroups | Lists all existing accounts. |
Deals | Usage information for the operation Deals.rsd. |
DealStages | Usage information for the operation DealStages.rsd. |
Tasks | Lists all existing accounts. |
AccountContacts¶
Usage information for the operation AccountContacts.rsd.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM AccountContacts WHERE Id = 10003
SELECT * FROM AccountContacts WHERE AccountId = 1 AND ContactId = 100
Insert¶
Inserting an account-contact association requires specifying a value for the AccountId and ContactId columns.
INSERT INTO AccountContacts (AccountId, ContactId) VALUES (2, 300)
INSERT INTO AccountContacts (AccountId, ContactId, Jobtitle) VALUES (2, 300, 'nurse')
Update¶
The driver supports updating account-contact information.
UPDATE AccountContacts SET ContactId = 3, JobTitle = 'updatedJobTitle' WHERE Id = 1
UPDATE AccountContacts SET JobTitle = 'updatedJobTitle' WHERE AccountId = 1
Delete¶
Remove all account-contacts association or an association by specifying the ID of the association.
DELETE FROM AccountContacts WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID of the account-contact association. |
AccountId | String | False | The account Id. |
ContactId | String | False | The contact Id. |
CreatedTimestamp | Datetime | True | Datetime value which indicates when the assosiaction was created. |
JobTitle | String | False | Job Title of the contact at the account. |
AccountLink | String | True | Account link. |
ContactLink | String | True | Contact link. |
UpdatedTimestamp | Datetime | True | Datetime value which indicates when the assosiaction was last updated. |
AccountCustomFieldData¶
Create, modify, delete and query the available deal custom field data in ActiveCampaign.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM AccountCustomFieldData WHERE Id = 10003
SELECT * FROM AccountCustomFieldData WHERE AccountId = 1
Insert¶
Inserting an account custom field data requires specifying a value for the AccountId, CustomFieldId, FieldValue columns.
Additionally, the connector supports inserting account custom fields data from the Accounts table. See Accounts for more information.
INSERT INTO AccountCustomFieldData (FieldValue, CustomFieldId, AccountId) VALUES ('custom field value', 1, 5)
Update¶
The connector supports updating account custom field data information. Updating an account custom field data requires specifying a value for the ID and CustomFieldId columns.
Additionally, it is possible to update account custom fields data from the Accounts table.
UPDATE AccountCustomFieldData SET FieldValue = 'Custom Field Value' WHERE ID = 13 AND CustomFieldId = 1
Delete¶
The connector supports removing accounts' custom field data.
DELETE FROM AccountcustomFieldData WHERE ID = 10
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID of the account custom field data. |
AccountCustomFieldMetumId | Integer | True | The custom field metum Id. |
AccountId | Integer | False | The ID of the account the field value relates to. |
CreatedTimestamp | Datetime | True | Datetime value that indicates when the custom field value was set. |
CustomFieldId | Integer | False | The ID of the custom field metum the field value relates to. |
CustomFieldName | String | True | The name of the custom field metum the field value relates to. |
FieldValue | String | False | Values for the field. (For currency field only, this needs to be in cents: eg, 10050 = 100.5). |
AccountLink | String | True | Account link. |
AccountCustomFieldMetumLink | String | True | AccountCustomFieldMetum link. |
UpdatedTimestamp | Datetime | True | Datetime value which indicates when the custom field data was last updated. |
FieldCurrency | String | False | Value of the custom field currency type for a specific contact. This field is required when inserting currency custom fields. |
Accounts¶
View many or all contacts by including their ID's or various filters.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM Accounts WHERE Id = 10003
SELECT * FROM Accounts WHERE Name = 'testAccount'
As a note, due to server-side performance issues, even when the property IncludeCustomFields is set to TRUE, the custom fields will appear only in the table schema with null values. To retrieve the values of the custom fields, please refer to the AccountCustomFieldData table.
Insert¶
Inserting an account requires specifying the account name.
INSERT INTO Accounts (Name) VALUES ('testAccount2')
INSERT INTO Accounts (Name, AccountUrl) VALUES ('account', 'http://accountUrl.com/')
Additionally, the connector supports inserting account and account custom fields data from the Accounts table. All custom field columns start with CF_.
INSERT INTO Accounts (Name, CF_Description, CF_Hidden, CF_Number) VALUES ('testAccount3', 'custom field value', 'custom field value', 123)
Update¶
The connector supports updating account information.
Additionally, it is possible to update account custom fields data from the Accounts table.
UPDATE Accounts SET Name = 'updatedName', AccountUrl = 'http://updatedAccountUrl.com/' WHERE Id = 1
UPDATE Accounts SET Name = 'updatedName', CF_Description = 'custom field value', CF_Hidden = 'custom field value' WHERE ID = 1
Delete¶
Remove all accounts or an account by specifying the ID of the account.
DELETE FROM Accounts WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID column of the Accounts table. |
AccountUrl | String | False | Account's website. |
ContactCount | Integer | True | The ContactCount column of the Accounts table. |
CreatedTimestamp | Datetime | True | The CreatedTimestamp column of the Accounts table. |
DealCount | Integer | True | The DealCount column of the Accounts table. |
AccountContactsLink | String | True | The AccountContactsLink column of the Accounts table. |
AccountCustomFieldDataLink | String | True | The AccountCustomFieldDataLink column of the Accounts table. |
ContactEmailsLink | String | True | The ContactEmailsLink column of the Accounts table. |
EmailActivitiesLink | String | True | The EmailActivitiesLink column of the Accounts table. |
NotesLink | String | True | The NotesLink column of the Accounts table. |
Name | String | False | Account's name. |
UpdatedTimestamp | Datetime | True | The UpdatedTimestamp column of the Accounts table. |
ContactCustomFieldData¶
Create, modify, delete and query the available contact custom field data in ActiveCampaign.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM ContactCustomFieldData WHERE Id = 10003
SELECT * FROM ContactCustomFieldData WHERE FieldValue = 'custom field value'
Insert¶
Inserting a contact custom field data requires specifying a value for the ContactId, FieldId, FieldValue columns.
Additionally, the connector supports inserting contact custom fields data from the Contacts table. See Contacts for more information.
INSERT INTO ContactCustomFieldData (FieldValue, FieldId, ContactId) VALUES ('custom field value', 1, 5)
Update¶
The connector supports updating contact custom field data information. Updating contact custom field data requires specifying a value for the Id, ContactId and FieldId columns.
Additionally, it is possible to update contact custom fields data from the Contacts table.
UPDATE ContactCustomFieldData SET FieldValue = 'custom field value' WHERE ID = 8 AND ContactId = 3 AND FieldId = 2"
Delete¶
The connector supports removing contact custom field data.
DELETE FROM ContactCustomFieldData WHERE ID = 10
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | False | The ID column of the ContactCustomFieldData table. |
CDate | Datetime | False | The CDate column of the ContactCustomFieldData table. |
ContactId | String | False | The ContactId column of the ContactCustomFieldData table. |
CustomFieldName | String | True | The name of the custom field metum the field value relates to. |
FieldId | String | False | ID of the field the value belongs to. |
FieldLink | String | False | The FieldLink column of the ContactCustomFieldData table. |
OwnerLink | String | False | The OwnerLink column of the ContactCustomFieldData table. |
Owner | String | False | The Owner column of the ContactCustomFieldData table. |
Udate | Datetime | False | The Udate column of the ContactCustomFieldData table. |
FieldValue | String | False | Value of the custom field for a specific contact. |
Contacts¶
View many or all contacts by including their ID's or various filters.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM Contacts WHERE Id = 10
SELECT * FROM Contacts WHERE Udate >= '2019-06-30T10:44:34-05:00'
SELECT * FROM Contacts WHERE Udate <= '2021-06-30T10:44:34-05:00'
SELECT * FROM Contacts WHERE Cdate >= '2019-06-30T10:44:34-05:00' AND Cdate <= '2021-06-30T10:44:34-05:00'
As a note, due to server-side performance issues, even when the property IncludeCustomFields is set to TRUE, the custom fields will appear only in the table schema with null values. To retrieve the values of the custom fields, please refer to the ContactCustomFieldData table.
Insert¶
Inserting a contact requires specifying the contact email.
INSERT INTO Contacts (Email) VALUES ('testcontact@test.com')
INSERT INTO Contacts (Email, FirstName, LastName, Phone) VALUES ('test@test.com', 'test', 'test', 'contactphonenumber')
Additionally, the connector supports inserting contact and contact custom fields data from the Contacts table. All custom field columns start with CF_.
INSERT INTO Contacts (Name, CF_Description, CF_Hidden, CF_Number) VALUES ('testcontact3', 'custom field value', 'custom field value', 123)
Update¶
The connector supports updating contact information.
Additionally, it is possible to update contact custom fields data from the Contacts table.
UPDATE Contacts SET FirstName = 'updatedName' WHERE Id = 1
UPDATE Contacts SET CF_CustomField1 = 'custom field value', CF_CustomField2 = 'custom field value' WHERE ID = 1
Bulk Import Contacts¶
The connector supports uploading large numbers of contacts into your account. This operation includes:
- Creating new contacts
- Updating existing contacts
- Subscribing and unsubscribing from lists
Contacts are imported in batches of up to 250 items. The importer is not recommended to be used for a small number of contacts. You can import contacts as below:
INSERT INTO Contacts#TEMP (Email) VALUES ('email1@test.com')
INSERT INTO Contacts#TEMP (Email) VALUES ('email2@test.com')
UPSERT INTO Contacts (Email) SELECT Email FROM Contacts#TEMP
Additionally, the connector supports BULK INSERT for the Contacts table. Please note this operation will cause a cost in performance as the connector validates if the submitted contacts exist before importing them.
INSERT INTO Contacts#TEMP (Email) VALUES ('email1@test.com')
INSERT INTO Contacts#TEMP (Email) VALUES ('email2@test.com')
INSERT INTO Contacts (Email) SELECT Email FROM Contacts#TEMP
To subscribe and unsubscribe contacts from a specific list set a value for the SubscribeTo and/or UnsubcribeFrom columns which accept a comma-separated list of list ids. For example:
INSERT INTO Contacts#TEMP (Email, SubscribeTo, UnsubcribeFrom) VALUES ('email1@test.com', '1,2', '6,12')
INSERT INTO Contacts#TEMP (Email, SubscribeTo, UnsubcribeFrom) VALUES ('email2@test.com', '3,7', '6,1')
UPSERT INTO Contacts (Email, SubscribeTo, UnsubcribeFrom) SELECT Email, SubscribeTo, UnsubcribeFrom FROM Contacts#TEMP
Contacts must meet all the following criteria in order to be created or updated with this API:
- The imported contacts may not exceed your account limit
- The contact must have an email address
- The contact's email address must not be on an exclusion list
- The contact's email must not be on a list of bounced email addresses
- The contact must not have unsubscribed to a list that the import would add them to
If contacts do not meet all of these requirements, they will be skipped by the importer.
Delete¶
Remove all contacts or a contact by specifying the ID of the contact.
DELETE FROM Contacts WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID column of the Contacts table. |
Adate | Datetime | True | The Adate column of the Contacts table. |
Anonymized | String | True | The Anonymized column of the Contacts table. |
BouncedDate | String | True | The BouncedDate column of the Contacts table. |
BouncedHard | String | True | The BouncedHard column of the Contacts table. |
BouncedSoft | String | True | The BouncedSoft column of the Contacts table. |
Cdate | Datetime | True | The Cdate column of the Contacts table. |
CreatedBy | String | True | The CreatedBy column of the Contacts table. |
CreatedTimestamp | Datetime | True | The CreatedTimestamp column of the Contacts table. |
CreatedUtcTimestamp | Datetime | True | The CreatedUtcTimestamp column of the Contacts table. |
Deleted | String | True | The Deleted column of the Contacts table. |
DeletedAt | String | True | The DeletedAt column of the Contacts table. |
Edate | Datetime | True | The Edate column of the Contacts table. |
Email | String | False | The Email column of the Contacts table. |
EmailDomain | String | True | The EmailDomain column of the Contacts table. |
EmailEmpty | Boolean | True | The EmailEmpty column of the Contacts table. |
EmailLocal | String | True | The EmailLocal column of the Contacts table. |
FirstName | String | False | The FirstName column of the Contacts table. |
Gravatar | String | True | The Gravatar column of the Contacts table. |
Hash | String | True | The Hash column of the Contacts table. |
Ip | String | True | The Ip column of the Contacts table. |
LastName | String | False | The LastName column of the Contacts table. |
AccountContactsLink | String | True | The AccountContactsLink column of the Contacts table. |
AutomationEntryCountsLink | String | True | The AutomationEntryCountsLink column of the Contacts table. |
BounceLogsLink | String | True | The BounceLogsLink column of the Contacts table. |
ContactAutomationsLink | String | True | The ContactAutomationsLink column of the Contacts table. |
ContactDataLink | String | True | The ContactDataLink column of the Contacts table. |
ContactDealsLink | String | True | The ContactDealsLink column of the Contacts table. |
ContactGoalsLink | String | True | The ContactGoalsLink column of the Contacts table. |
ContactListsLink | String | True | The ContactListsLink column of the Contacts table. |
ContactLogsLink | String | True | The ContactLogsLink column of the Contacts table. |
ContactTagsLink | String | True | The ContactTagsLink column of the Contacts table. |
DealsLink | String | True | The DealsLink column of the Contacts table. |
FieldValuesLink | String | True | The FieldValuesLink column of the Contacts table. |
GeoIpsLink | String | True | The GeoIpsLink column of the Contacts table. |
NotesLink | String | True | The NotesLink column of the Contacts table. |
OrganizationLink | String | True | The OrganizationLink column of the Contacts table. |
PlusAppendLink | String | True | The PlusAppendLink column of the Contacts table. |
ScoreValuesLink | String | True | The ScoreValuesLink column of the Contacts table. |
TrackingLogsLink | String | True | The TrackingLogsLink column of the Contacts table. |
Organization | String | True | The Organization column of the Contacts table. |
Orgname | String | True | The Orgname column of the Contacts table. |
Phone | String | False | The Phone column of the Contacts table. |
RatingTStamp | String | True | The RatingTStamp column of the Contacts table. |
SegmentioId | String | True | The SegmentioId column of the Contacts table. |
Sentcnt | String | True | The Sentcnt column of the Contacts table. |
SocialdataLastcheck | String | True | The SocialdataLastcheck column of the Contacts table. |
Ua | String | True | The Ua column of the Contacts table. |
Udate | Datetime | True | The Udate column of the Contacts table. |
UpdatedTimestamp | String | True | The UpdatedTimestamp column of the Contacts table. |
Updated_by | String | True | The Updated_by column of the Contacts table. |
SubscribeTo | String | False | Comma-separated list of list Ids to subscribe a contact to. |
UnsubscribeFrom | String | False | Comma-separated list of list Ids to unsubscribe a contact from. |
DealCustomFieldData¶
Create, modify, delete and query the available deal custom field data in ActiveCampaign.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM DealCustomFieldData WHERE Id = 10003
SELECT * FROM DealCustomFieldData WHERE DealId = 1
Insert¶
Inserting a deal custom field data requires specifying a value for the DealId, CustomFieldId, and FieldValue columns.
Additionally, the connector supports inserting deal custom fields data from the Deals table. See Deals for more information.
INSERT INTO DealCustomFieldData (FieldValue, CustomFieldId, DealId) VALUES ('custom field value', 1, 5)
Update¶
The connector supports updating deal custom field data information. Updating deal custom field data requires specifying a value for the Id, DealId and CustomFieldId columns.
Additionally, it is possible to update deal custom fields data from the Deals table.
UPDATE DealCustomFieldData SET FieldValue = 'custom field value' WHERE ID = 8 AND DealId = 3 AND CustomFieldId = 2
Delete¶
The connector supports removing deal custom field data.
DELETE FROM DealCustomFieldData WHERE ID = 10
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | False | The ID column of the DealCustomFieldData table. |
CreatedTimestamp | Datetime | False | The CreatedTimestamp column of the DealCustomFieldData table. |
CustomFieldName | String | True | The name of the custom field metum the field value relates to. |
CustomFieldId | Integer | False | The CustomFieldId column of the DealCustomFieldData table. |
DealCustomFieldMetumId | Integer | False | The DealCustomFieldMetumId column of the DealCustomFieldData table. |
DealId | Integer | False | The DealId column of the DealCustomFieldData table. |
FieldValue | String | False | The FieldValue column of the DealCustomFieldData table. |
DealLink | String | False | The DealLink column of the DealCustomFieldData table. |
DealCustomFieldMetumLink | String | False | The DealCustomFieldMetumLink column of the DealCustomFieldData table. |
UpdatedTimestamp | Datetime | False | The UpdatedTimestamp column of the DealCustomFieldData. |
FieldCurrency | String | False | Value of the custom field currency type for a specific deal. This field is required when inserting currency custom fields. |
FieldType | String | False | Value of the custom field currency type for a specific deal. This field is required when inserting currency custom fields. |
DealGroups¶
Lists all existing accounts.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM DealGroups WHERE Id = 10003
SELECT * FROM DealGroups WHERE Title = 'title'
Insert¶
Inserting a pipeline/dealgroup requires specifying a value for the Currency, Title columns.
INSERT INTO DealGroups (Currency, Title) VALUES (2, 300)
INSERT INTO DealGroups (Currency, Title, AutoAssign, AllUsers, AllGroups) VALUES ('USD', 'title', 1, 0, 1)
Update¶
The connector supports updating pipeline/dealgroup information.
UPDATE DealGroups SET AllUsers = 0, AllGroups = 1, Currency = 'EUR' WHERE Id = 1
Delete¶
Remove all pipelines or a pipeline by specifying the ID of the dealgroup.
DELETE FROM DealGroups WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The pipeline Id. |
AllGroups | String | False | The AllGroups column of the DealGroups table. Indicates whether all user groups have permission to manage this pipeline. Can be either 1 or 0. If 1, all user groups can manage this pipeline. If 0, only user groups in dealGroup.groups parameter can manage this pipeline. |
AllUsers | String | False | The AllUsers column of the DealGroups table. Indicates whether new deals get auto-assigned to all users. Can be either 1 or 0. If 1, new deals are auto-assigned to all users unless auto-assign is disabled. If 0, new deals are auto-assigned to only the users in dealGroup.users parameter. |
AutoAssign | String | False | The AutoAssign column of the DealGroups table. Deal auto-assign option. Can be one of 0, 1, and 2. If 0, auto-assign is disabled. If 1, Round Robin method is used to auto-assign new deals. If 2, deals are distributed based on deal values. |
Cdate | Datetime | True | The Cdate column of the DealGroups table. |
Currency | String | False | The Currency column of the DealGroups table. |
DealGroupGroupsLink | String | True | The DealGroupGroupsLink column of the DealGroups table. |
DealGroupUsersLink | String | True | The DealGroupUsersLink column of the DealGroups table. |
StagesLink | String | True | The StagesLink column of the DealGroups table. |
Stages | String | True | The Stages column of the DealGroups table. |
Title | String | False | Pipeline's title. |
Udate | Datetime | True | The Udate column of the DealGroups table. |
Deals¶
Usage information for the operation Deals.rsd.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM Deals WHERE Id = 10
SELECT * FROM Deals WHERE GroupId = 1
SELECT * FROM Deals WHERE OwnerId = 1
SELECT * FROM Deals WHERE Stage = 1 AND Title = 'title'
As a note, due to server-side performance issues, even when the property IncludeCustomFields is set to TRUE, the custom fields will appear only in the table schema with null values. To retrieve the values of the custom fields, please refer to the DealCustomFieldData table.
Insert¶
Inserting a deal requires specifying a value for the GroupId, Stage, OwnerId, Title, Value, Currency columns and a value for AccountId or ContactId column.
INSERT INTO Deals (AccountId, GroupId, Stage, OwnerId, Title, Value, Currency) VALUES (1, 1, 1, 1, 'title', 100, 'USD')
INSERT INTO Deals (Email, FirstName, LastName, Phone) VALUES ('test@test.com', 'test', 'test', 'dealphonenumber')
Additionally, the connector supports inserting deal and deal custom fields data from the Deals table. All custom field columns start with CF_.
INSERT INTO Deals (AccountId, GroupId, Stage, OwnerId, Title, Value, Currency, CF_CustomField1, CF_CustomField2, CF_CustomField3) VALUES (1, 1, 1, 1, 'title', 100, 'USD', 'value', 'value', 'value')
Update¶
The connector supports updating deal information.
Additionally, it is possible to update deal custom fields data from the Deals table.
UPDATE Deals SET Title = 'updatedTitle' WHERE Id = 1
UPDATE Deals SET CF_CustomField1 = 'custom field value', CF_CustomField2 = 'custom field value' WHERE ID = 1
Delete¶
Remove all deals or a deal by specifying the ID of the deal.
DELETE FROM Deals
DELETE FROM Deals WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID column of the Deals table. |
AccountId | String | False | The AccountId column of the Deals table. |
ActivityCount | String | True | The ActivityCount column of the Deals table. |
Cdate | Datetime | True | The Cdate column of the Deals table. |
ContactId | String | False | Deal's primary contact's Id. |
Currency | String | False | Deal's currency in 3-digit ISO format, lowercased. |
CustomerAccount | String | True | The CustomerAccount column of the Deals table. |
Description | String | False | The Description column of the Deals table. |
Edate | String | True | The Edate column of the Deals table. |
Group | String | False | Deal's pipeline Id. Required if deal.stage is not provided. If deal.group is not provided, the stage's pipeline will be assigned to the deal automatically. |
Hash | String | True | The Hash column of the Deals table. |
IsDisabled | String | True | The IsDisabled column of the Deals table. |
AccountLink | String | True | The AccountLink column of the Deals table. |
ContactLink | String | True | The ContactLink column of the Deals table. |
ContactDealsLink | String | True | The ContactDealsLink column of the Deals table. |
CustomerAccountLink | String | True | The CustomerAccountLink column of the Deals table. |
DealActivitiesLink | String | True | The DealActivitiesLink column of the Deals table. |
DealCustomFieldDataLink | String | True | The DealCustomFieldDataLink column of the Deals table. |
GroupLink | String | True | The GroupLink column of the Deals table. |
NextTaskLink | String | True | The NextTaskLink column of the Deals table. |
NotesLink | String | True | The NotesLink column of the Deals table. |
OrganizationLink | String | True | The OrganizationLink column of the Deals table. |
OwnerLink | String | True | The OwnerLink column of the Deals table. |
ScoreValuesLink | String | True | The ScoreValuesLink column of the Deals table. |
StageLink | String | True | The StageLink column of the Deals table. |
TasksLink | String | True | The TasksLink column of the Deals table. |
Mdate | Datetime | True | The Mdate column of the Deals table. |
NextTask | String | True | The NextTask column of the Deals table. |
NextDate | Datetime | True | The NextDate column of the Deals table. |
NextDealId | String | True | The NextDealId column of the Deals table. |
NextTaskId | String | True | The NextTaskId column of the Deals table. |
Organization | String | True | The Organization column of the Deals table. |
Owner | String | False | Deal's owner Id. Required if pipeline's auto-assign option is disabled. |
Percent | String | False | The Percent column of the Deals table. |
Stage | String | False | Deal's stage Id. Required if deal.group is not provided. If deal.stage is not provided, the deal will be assigned with the first stage in the pipeline provided in deal.group. |
Status | String | False | The Status column of the Deals table. |
Title | String | False | The Title column of the Deals table. |
Value | String | False | Deal's value in cents. |
WinProbability | Integer | True | The WinProbability column of the Deals table. |
WinProbabilityMdate | Datetime | True | The WinProbabilityMdate column of the Deals table. |
DealStages¶
Usage information for the operation DealStages.rsd.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM DealStages WHERE Id = 10003
SELECT * FROM DealStages WHERE Title = 'title' AND GroupId = 1
Insert¶
Inserting a stage requires specifying a value for the GroupId, Title columns.
INSERT INTO DealStages (GroupId, Title) VALUES (2, 'title')
INSERT INTO DealStages (Title, GroupId, OrderId, CardRegion3, Color, Width) VALUES ('TestStage', 1, 2, 'hide-avatar', '434343', '280')
Update¶
The connector supports updating stage/dealstage information.
UPDATE DealStages SET Title = 'updatedTitle' WHERE Id = 1
Delete¶
Remove all stages or a stage by specifying the ID of the stage.
DELETE FROM DealStages WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID column of the DealStages table. |
CardRegion1 | String | False | The CardRegion1 column of the DealStages table. Indicates what to show in upper-left corner of Deal Cards. |
CardRegion2 | String | False | The CardRegion2 column of the DealStages table. Indicates what to show in upper-right corner of Deal Cards. |
CardRegion3 | String | False | The CardRegion3 column of the DealStages table. Indicates whether to show the avatar in Deal Cards. Can be one of show-avatar and hide-avatar. If set to show-avatar, deal cards will show the avatars. If set to hide-avatar, deal cards will hide the avatars. |
CardRegion4 | String | False | The CardRegion4 column of the DealStages table. Indicates what to show next to the avatar in Deal Cards. |
CardRegion5 | String | False | The CardRegion5 column of the DealStages table. Indicates what to show in lower-right corner of Deal Cards. |
Cdate | Datetime | True | The Cdate column of the DealStages table. |
Color | String | False | Deal Stage's color. 6-character HEX color code without the hashtag. e.g. 434343 to assign the hex color value #434343. |
DealOrder | String | False | Option and direction to be used to sort deals in the deal stage. The option and direction should be delimited by a space. Direction can be either. |
GroupId | String | False | The GroupId column of the DealStages table. |
GroupLink | String | True | The GroupLink column of the DealStages table. |
OrderId | String | False | The OrderId column of the DealStages table. |
Title | String | False | The Title column of the DealStages table. |
Udate | Datetime | True | The Udate column of the DealStages table. |
Width | String | False | Deal stage's width in pixels, without px unit. |
Tasks¶
Lists all existing accounts.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM Tasks WHERE Id = 10003
SELECT * FROM Tasks WHERE DealTaskType = 1
SELECT * FROM Tasks WHERE DueDate >= '2019-06-30T10:44:34-05:00'
SELECT * FROM Tasks WHERE DueDate <= '2021-06-30T10:44:34-05:00'
SELECT * FROM Tasks WHERE OutcomeId = 1 AND RelId = 1 AND Status = 1
Insert¶
Inserting a stage requires specifying a value for the DealTaskType, DueDate, Relid columns.
INSERT INTO Tasks (DealTaskType, DueDate, Relid) VALUES (2, '2021-06-30T10:44:34-05:00', 7)
Update¶
The connector supports updating stage/dealstage information.
UPDATE Tasks SET Title = 'updatedTitle' WHERE Id = 1
Delete¶
Remove all stages or a stage by specifying the ID of the stage.
DELETE FROM Tasks WHERE ID = 10003
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | String | True | The ID column of the Tasks table. |
Assignee | String | False | The Assignee column of the Tasks table. |
Automation | String | True | The Automation column of the Tasks table. |
Cdate | Datetime | True | The Cdate column of the Tasks table. |
DealTaskType | String | False | The type of the task based on the available Task Types in the account. |
DoneAutomation | String | False | The DoneAutomation column of the Tasks table. |
DueDate | Datetime | False | Due date of the task. |
Edate | Datetime | True | The Edate column of the Tasks table. |
ActivitiesLink | String | True | The ActivitiesLink column of the Tasks table. |
AssigneeLink | String | True | The AssigneeLink column of the Tasks table. |
AutomationLink | String | True | The AutomationLink column of the Tasks table. |
DealTasktypeLink | String | True | The DealTasktypeLink column of the Tasks table. |
DoneAutomationLink | String | True | The DoneAutomationLink column of the Tasks table. |
NotesLink | String | True | The NotesLink column of the Tasks table. |
OwnerLink | String | True | The OwnerLink column of the Tasks table. |
TaskNotificationsLink | String | True | The TaskNotificationsLink column of the Tasks table. |
UserLink | String | True | The UserLink column of the Tasks table. |
Note | String | False | The content describing the task. |
OutcomeId | Integer | False | The OutcomeId column of the Tasks table. |
OutcomeInfo | String | False | The OutcomeInfo column of the Tasks table. |
OwnerId | String | True | The OwnerId column of the Tasks table. |
OwnerType | String | False | The name of the relating object. Valid values are contact or deal. |
Relid | String | False | The ID of the relational object for this task. |
Reltype | String | True | The Reltype column of the Tasks table. |
Status | Integer | False | The Status column of the Tasks table. 1 is complete and 0 is incomplete.. |
Title | String | False | The Title column of the Tasks table. |
Udate | Datetime | True | The Udate column of the Tasks table. |
User | String | True | The User column of the Tasks table. |
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.
Jitterbit Connector for ActiveCampaign Views¶
Name | Description |
---|---|
Addresses | Usage information for the operation Addresses.rsd. |
Automations | Usage information for the operation Automations.rsd. |
Campaigns | Query the campaigns information in ActiveCampaign. |
ContactAutomations | View many or all contacts by including their ID's or various filters. |
ContactLists | Retrieve ContactList for each contact Group or for the specific Contact group. |
ContactTags | Retrieve tags added for each contact or for the specific Contact Id |
DealActivities | Retrieve Deal Activities for each Deal or for the specific Deal Id. |
Lists | Usage information for the operation Lists.rsd. |
Scores | View many or all contacts by including their ID's or various filters. |
Tags | View many or all tags by including their ID's or various filters. |
TrackingLogs | Retrieve trackingLogs for the specific contact or all contacts |
Users | Usage information for the operation Users.rsd. |
Addresses¶
Usage information for the operation Addresses.rsd.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following query is processed server side:
SELECT * FROM AccountContacts WHERE Id = 10003
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The ID column of the Addresses table. |
Address1 | String | The Address1 column of the Addresses table. |
Address2 | String | The Address2 column of the Addresses table. |
Allgroup | String | The Allgroup column of the Addresses table. |
City | String | The City column of the Addresses table. |
CompanyName | String | The CompanyName column of the Addresses table. |
Country | String | The Country column of the Addresses table. |
District | String | The District column of the Addresses table. |
isDefault | String | The isDefault column of the Addresses table. |
AddressGroupLink | String | The AddressGroupLink column of the Addresses table. |
AddressListLink | String | The AddressListLink column of the Addresses table. |
FormsLink | String | The FormsLink column of the Addresses table. |
State | String | The State column of the Addresses table. |
Zip | String | The Zip column of the Addresses table. |
Automations¶
Usage information for the operation Automations.rsd.
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The ID column of the Automations table. |
Cdate | Datetime | The Cdate column of the Automations table. |
DefaultScreenshot | String | The DefaultScreenshot column of the Automations table. |
Entered | String | The Entered column of the Automations table. |
Exited | String | The Exited column of the Automations table. |
Hidden | String | The Hidden column of the Automations table. |
BlocksLink | String | The BlocksLink column of the Automations table. |
CampaignsLink | String | The CampaignsLink column of the Automations table. |
ContactAutomationsLink | String | The ContactAutomationsLink column of the Automations table. |
ContactGoalsLink | String | The ContactGoalsLink column of the Automations table. |
GoalsLink | String | The GoalsLink column of the Automations table. |
SiteMessagesLink | String | The SiteMessagesLink column of the Automations table. |
SmsLink | String | The SmsLink column of the Automations table. |
Mdate | Datetime | The Mdate column of the Automations table. |
Name | String | The Name column of the Automations table. |
Screenshot | String | The Screenshot column of the Automations table. |
Status | String | The Status column of the Automations table. |
UserId | String | The UserId column of the Automations table. |
Campaigns¶
Query the campaigns information in ActiveCampaign.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following query is processed server side:
SELECT * FROM Campaigns WHERE Id = 10003
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID column of the Campaigns table. |
ActiveRssInterval | String | The ActiveRssInterval column of the Campaigns table. |
ActiveRssItems | String | The ActiveRssItems column of the Campaigns table. |
ActiveRssUrl | String | The ActiveRssUrl column of the Campaigns table. |
AddressId | String | The AddressId column of the Campaigns table. |
AnalyticsCampaignName | String | The AnalyticsCampaignName column of the Campaigns table. |
Automation | String | The Automation column of the Campaigns table. |
BaseMessageId | String | The BaseMessageId column of the Campaigns table. |
BaseTemplateId | String | The BaseTemplateId column of the Campaigns table. |
BounceId | String | The BounceId column of the Campaigns table. |
CanSkipApproval | String | The can_skip_approval column of the Campaigns table. |
Cdate | Datetime | The Cdate column of the Campaigns table. |
CreatedBy | String | The Created By column of the Campaigns table. |
CreatedTimestamp | Datetime | The Created Timestamp column of the Campaigns table. |
DeleteStamp | String | The DeleteStamp column of the Campaigns table. |
EmbedImages | String | The EmbedImages column of the Campaigns table. |
Facebook | String | The Facebook column of the Campaigns table. |
FormId | String | The FormId column of the Campaigns table. |
Forwards | String | The Forwards column of the Campaigns table. |
HardBounces | String | The HardBounces column of the Campaigns table. |
HasPredictiveContent | Bit | The HasPredictiveContent column of the Campaigns table. |
HtmlUnsub | String | The HtmlUnsub column of the Campaigns table. |
HtmlUnsubdata | String | The HtmlUnsubdata column of the Campaigns table. |
Ip | String | The Ip column of the Campaigns table. |
Ip4 | String | The Ip4 column of the Campaigns table. |
LastStep | String | The LastStep column of the Campaigns table. |
Ldate | String | The Ldate column of the Campaigns table. |
LinkClicks | String | The LinkClicks column of the Campaigns table. |
AutomationLink | String | The AutomationLink column of the Campaigns table. |
CampaignAggregateRevenuesLink | String | The AggregateRevenues column of the Campaigns table. |
CampaignListsLink | String | The CampaignListsLink column of the Campaigns table. |
CampaignMessageLink | String | The CampaignMessageLink column of the Campaigns table. |
CampaignMessagesLink | String | The CampaignMessagesLink column of the Campaigns table. |
LinksLink | String | The LinksLink column of the Campaigns table. |
LinksSegment | String | The Segment column of the Campaigns table. |
UserLink | String | The UserLink column of the Campaigns table. |
MailCleanup | String | The MailCleanup column of the Campaigns table. |
MailSend | String | The MailSend column of the Campaigns table. |
MailTransfer | String | The MailTransfer column of the Campaigns table. |
MailerLogFile | String | The MailerLogFile column of the Campaigns table. |
ManageText | String | The ManageText column of the Campaigns table. |
Mdate | Datetime | The Mdate column of the Campaigns table. |
Name | String | The Name column of the Campaigns table. |
Opens | String | The Opens column of the Campaigns table. |
Public | String | The Public column of the Campaigns table. |
RealCid | String | The RealCid column of the Campaigns table. |
Recurring | String | The Recurring column of the Campaigns table. |
ReminderField | String | The ReminderField column of the Campaigns table. |
ReminderFormat | String | The ReminderFormat column of the Campaigns table. |
ReminderLastCronRun | String | The ReminderLastCronRun column of the Campaigns table. |
ReminderOffset | String | The ReminderOffset column of the Campaigns table. |
ReminderOffsetSign | String | The ReminderOffsetSign column of the Campaigns table. |
ReminderOffsetType | String | The ReminderOffsetType column of the Campaigns table. |
ReminderType | String | The ReminderType column of the Campaigns table. |
Replies | String | The Replies column of the Campaigns table. |
ReplySys | String | The ReplySys column of the Campaigns table. |
ResponderExisting | String | The ResponderExisting column of the Campaigns table. |
ResponderOffset | String | The ResponderOffset column of the Campaigns table. |
ResponderType | String | The ResponderType column of the Campaigns table. |
Schedule | String | The Schedule column of the Campaigns table. |
ScheduledDate | String | The ScheduledDate column of the Campaigns table. |
Screenshot | String | The Screenshot column of the Campaigns table. |
Sdate | String | The Sdate column of the Campaigns table. |
SegmentId | String | The SegmentId column of the Campaigns table. |
SegmentName | String | The SegmentName column of the Campaigns table. |
SendAmt | String | The SendAmt column of the Campaigns table. |
SendId | String | The SendId column of the Campaigns table. |
SeriesId | String | The SeriesId column of the Campaigns table. |
Series Send Lock Time | String | The SeriesSendLockTime column of the Campaigns table. |
SocialShares | String | The SocialShares column of the Campaigns table. |
SoftBounces | String | The SoftBounces column of the Campaigns table. |
Source | String | The Source column of the Campaigns table. |
SplitContent | String | The SplitContent column of the Campaigns table. |
SplitOffset | String | The SplitOffset column of the Campaigns table. |
SplitOffsetType | String | The SplitOffsetType column of the Campaigns table. |
SplitType | String | The SplitType column of the Campaigns table. |
SplitWinnerAwaiting | String | The SplitWinnerAwaiting column of the Campaigns table. |
SplitWinnerMessageId | String | The SplitWinnerMessageId column of the Campaigns table. |
Status | String | The Status column of the Campaigns table. |
SubscriberClicks | String | The SubscriberClicks column of the Campaigns table. |
Survey | String | The Survey column of the Campaigns table. |
TextUnsub | String | The TextUnsub column of the Campaigns table. |
TextUnsubData | String | The TextUnsubData column of the Campaigns table. |
ThreadId | String | The ThreadId column of the Campaigns table. |
TotalAmt | String | The TotalAmt column of the Campaigns table. |
TrackLinks | String | The TrackLinks column of the Campaigns table. |
TrackLinksAnalytics | String | The TrackLinksAnalytics column of the Campaigns table. |
TrackReads | String | The TrackReads column of the Campaigns table. |
TrackReadsAnalytics | String | The TrackReadsAnalytics column of the Campaigns table. |
Tweet | String | The Tweet column of the Campaigns table. |
Type | String | The Type column of the Campaigns table. |
UniqueForwards | String | The UniqueForwards column of the Campaigns table. |
UniqueLinkClicks | String | The UniqueLinkClicks column of the Campaigns table. |
UniqueOpens | String | The UniqueOpens column of the Campaigns table. |
UniqueReplies | String | The UniqueReplies column of the Campaigns table. |
UnsubReasons | String | The UnsubReasons column of the Campaigns table. |
Unsubscribes | String | The Unsubscribes column of the Campaigns table. |
Updated By | String | The UpdatedBy column of the Campaigns table. |
Updated Timestamp | String | The UpdatedTimestamp column of the Campaigns table. |
Updates | String | The Updates column of the Campaigns table. |
User | String | The User column of the Campaigns table. |
UserId | String | The UserId column of the Campaigns table. |
WaitPreview | String | The WaitPreview column of the Campaigns table. |
WillRecur | String | The WillRecur column of the Campaigns table. |
ContactAutomations¶
View many or all contacts by including their ID's or various filters.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following query is processed server side:
SELECT * FROM ContactAutomations WHERE Id = 10003
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The ID column of the ContactAutomations table. |
Adddate | Datetime | The Adddate column of the ContactAutomations table. |
Automation | String | The Automation column of the ContactAutomations table. |
Batchid | Unknown | The Batchid column of the ContactAutomations table. |
CompleteValue | Integer | The CompleteValue column of the ContactAutomations table. |
Completed | Integer | The Completed column of the ContactAutomations table. |
CompletedElements | String | The CompletedElements column of the ContactAutomations table. |
ContactId | String | The ContactId column of the ContactAutomations table. |
Lastblock | String | The Lastblock column of the ContactAutomations table. |
Lastdate | Datetime | The Lastdate column of the ContactAutomations table. |
LastLogId | String | The LastLogId column of the ContactAutomations table. |
AutomationLink | String | The AutomationLink column of the ContactAutomations table. |
ContactLink | String | The ContactLink column of the ContactAutomations table. |
ContactGoalsLink | String | The ContactGoalsLink column of the ContactAutomations table. |
Remdate | Datetime | The Remdate column of the ContactAutomations table. |
SeriesId | String | The SeriesId column of the ContactAutomations table. |
StartId | String | The StartId column of the ContactAutomations table. |
Status | String | The Status column of the ContactAutomations table. |
TimeSpan | String | The TimeSpan column of the ContactAutomations table. |
TotalElements | String | The TotalElements column of the ContactAutomations table. |
ContactLists¶
Retrieve ContactList for each contact Group or for the specific Contact group.
Table Specific Information¶
Select¶
Retrieving information from ContactLists requires specifying a ContactId.
SELECT * FROM ContactLists WHERE ContactId = 1000
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID column of the ContactLists table. |
ContactId | String | The ContactId column of the ContactLists table. |
AutomationId | String | The AutomationId column of the ContactLists table. |
AutosyncLog | String | The AutosyncLog column of the ContactLists table. |
Campaign | String | The Campaign column of the ContactLists table. |
CreatedBy | String | The CreatedBy column of the ContactLists table. |
CreatedTimestamp | Datetime | The CreatedTimestamp column of the ContactLists table. |
FirstName | String | The FirstName column of the ContactLists table. |
Form | String | The Form column of the ContactLists table. |
Ip4Sub | String | The Ip4Sub column of the ContactLists table. |
Ip4Unsub | String | The Ip4Unsub column of the ContactLists table. |
Ip4Last | String | The Ip4Last column of the ContactLists table. |
LastName | String | The LastName column of the ContactLists table. |
AutomationLink | String | The AutomationLink column of the ContactLists table. |
AutosyncLogLink | String | The AutosyncLogLink column of the ContactLists table. |
CampaignLink | String | The CampaignLink column of the ContactLists table. |
ContactLink | String | The ContactLink column of the ContactLists table. |
FormLink | String | The FormLink column of the ContactLists table. |
ListLink | String | The ListLink column of the ContactLists table. |
MessageLink | String | The MessageLink column of the ContactLists table. |
UnsubscribeAutomationLink | String | The UnsubscribeAutomationLink column of the ContactLists table. |
ListId | String | The ListId column of the ContactLists table. |
Message | String | The Message column of the ContactLists table. |
Responder | String | The Responder column of the ContactLists table. |
Sdate | Datetime | The Sdate column of the ContactLists table. |
SeriesId | String | The SeriesId column of the ContactLists table. |
Status | String | The Status column of the ContactLists table. |
SourceId | String | The SourceId column of the ContactLists table. |
Sync | String | The Sync column of the ContactLists table. |
Unsubreason | String | The Unsubreason column of the ContactLists table. |
UnsubscribeAutomation | String | The UnsubscribeAutomation column of the ContactLists table. |
UpdatedBy | String | The UpdatedBy column of the ContactLists table. |
UpdatedTimestamp | Datetime | The UpdatedTimestamp column of the ContactLists table. |
ContactTags¶
Retrieve tags added for each contact or for the specific Contact Id
Table Specific Information¶
Select¶
Retrieving information for ContactTags requires specifying a ContactId.
SELECT * FROM ContactTags WHERE ContactId = 1
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID column of the ContactTags table. |
ContactId | String | The ContactId column of the ContactTags table. |
Cdate | Datetime | The Cdate column of the ContactTags table. |
CreatedBy | String | The CreatedBy column of the ContactTags table. |
CreatedTimestamp | Datetime | The CreatedTimestamp column of the ContactTags table. |
ContactLink | String | The ContactLink column of the ContactTags table. |
TagLink | String | The TagLink column of the ContactTags table. |
Tag | String | The Tag column of the ContactTags table. |
UpdatedBy | String | The UpdatedBy column of the ContactTags table. |
UpdatedTimestamp | Datetime | The UpdatedTimestamp column of the ContactTags table. |
DealActivities¶
Retrieve Deal Activities for each Deal or for the specific Deal Id.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID of the activity. |
DealId | String | The ID of the Deal to which the activity belongs to. |
StageId | String | The ID of the Stage to which the parent Deal of the activity belongs to. |
UserId | String | The ID of the User which triggered the activity. |
DataId | String | The ID for the related object (e.g. a Task or Note or similar), if relevant. |
DataType | String | The type of the activity. Examples include 'userid' or 'subscriberid' or 'task' or 'note' or empty quotes ('') for the deal's initial creation. |
DataAction | String | The action taken by this activity. Examples include 'add' or 'complete' or the new value ('100') if the value is changing. |
DataOldVal | String | The previous value, if the value has changed (see DataAction); otherwise, empty quotes ('') |
CreatedDate | Datetime | The creation date of the activity. |
SortDate | Datetime | |
IsAddTask | Boolean | Indicates whether the activity was adding a task to the deal. |
Deleted | Boolean | Indicates whethere the activity was deleted or not. |
SeriesId | String | |
DealLink | String | The API endpoint to the Deal related to this activity. |
StageLink | String | The API endpoint to the Stage related to this activity. |
UserLink | String | The API endpoint to the User related to this activity. |
AutomationLink | String | The API endpoint to the Automation related to this activity. |
ActivityLink | String | The API endpoint to the Activity. |
Deal | String | The related to this Activity. |
Stage | String | The Stage column of the DealActivities table. |
User | String | The User column of the DealActivities table. |
Automation | String | The Automation which triggered this activity. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
Exclude | String | Include this param to exclude 'email', 'connection_email', and 'LinkData' activity types. The allowed values are email. |
Lists¶
Usage information for the operation Lists.rsd.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following queries are processed server side:
SELECT * FROM Lists WHERE Id = 1
SELECT * FROM Lists WHERE Name = 'list1'
In addition the connector supports retrieving information about members of lists in your account by supporting them dynamically as VIEWS. Selecting from a dynamic list will return information about all the contacts that are members of that list. Lists names always start with "List_".
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID column of the Lists table. |
AnalyticsDomains | String | The AnalyticsDomains column of the Lists table. |
AnalyticsSource | String | The AnalyticsSource column of the Lists table. |
AnalyticsUa | String | The AnalyticsUa column of the Lists table. |
CarbonCopy | String | The CarbonCopy column of the Lists table. |
Cdate | Datetime | The Cdate column of the Lists table. |
Udate | Datetime | The Udate column of the Lists table. |
DeleteStamp | String | The DeleteStamp column of the Lists table. |
FacebookSession | String | The FacebookSession column of the Lists table. |
FullAddress | String | The FullAddress column of the Lists table. |
UnsubscribeReason | String | The UnsubscribeReason column of the Lists table. |
AddressListsLink | String | The AddressListsLink column of the Lists table. |
ContactGoalListsLink | String | The ContactGoalListsLink column of the Lists table. |
UserLink | String | The UserLink column of the Lists table. |
Name | String | The Name column of the Lists table. |
OptinMessageId | String | The OptinMessageId column of the Lists table. |
OptinOptout | String | The OptinOptout column of the Lists table. |
OptoutConf | String | The OptoutConf column of the Lists table. |
EmbedImage | String | The EmbedImage column of the Lists table. |
UseAnalyticsLink | String | The UseAnalyticsLink column of the Lists table. |
UseAnalyticsRead | String | The UseAnalyticsRead column of the Lists table. |
UseCaptcha | String | The UseCaptcha column of the Lists table. |
UseFacebook | String | The UseFacebook column of the Lists table. |
UseTracking | String | The UseTracking column of the Lists table. |
UseTwitter | String | The UseTwitter column of the Lists table. |
Private | String | The Private column of the Lists table. |
RequireName | String | The RequireName column of the Lists table. |
SendLastBroadcast | String | The SendLastBroadcast column of the Lists table. |
SenderAddr1 | String | The SenderAddr1 column of the Lists table. |
SenderAddr2 | String | The SenderAddr2 column of the Lists table. |
SenderCity | String | The SenderCity column of the Lists table. |
SenderCountry | String | The SenderCountry column of the Lists table. |
SenderName | String | The SenderName column of the Lists table. |
SenderPhone | String | The SenderPhone column of the Lists table. |
SenderReminder | String | The SenderReminder column of the Lists table. |
SenderState | String | The SenderState column of the Lists table. |
SenderUrl | String | The SenderUrl column of the Lists table. |
SenderZip | String | The SenderZip column of the Lists table. |
StringId | String | The StringId column of the Lists table. |
SubscriptionNotify | String | The SubscriptionNotify column of the Lists table. |
ToName | String | The ToName column of the Lists table. |
TwitterToken | String | The TwitterToken column of the Lists table. |
TwitterTokenSecret | String | The TwitterTokenSecret column of the Lists table. |
UnsubscriptionNotify | String | The UnsubscriptionNotify column of the Lists table. |
ListsUser | String | The ListsUser column of the Lists table. |
ListsUserid | String | The ListsUserid column of the Lists table. |
Scores¶
View many or all contacts by including their ID's or various filters.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following query is processed server side:
SELECT * FROM Scores WHERE Id = 10
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The ID column of the Scores table. |
Cdate | Datetime | The Cdate column of the Scores table. |
Descript | String | The Descript column of the Scores table. |
Mdate | Datetime | The Mdate column of the Scores table. |
Name | String | The Name column of the Scores table. |
RelType | String | The RelType column of the Scores table. |
Status | String | The Status column of the Scores table. |
Tags¶
View many or all tags by including their ID's or various filters.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following query is processed server side:
SELECT * FROM Tags WHERE Id = 10003
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID column of the Tags table. |
Tag | String | The Tag column of the Tags table. |
TagType | String | The TagType column of the Tags table. |
Description | String | The Description column of the Tags table. |
Cdate | Datetime | The Cdate column of the Tags table. |
ContactGoalTagsLink | String | The ContactGoalTagsLink column of the Tags table. |
TrackingLogs¶
Retrieve trackingLogs for the specific contact or all contacts
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | String | The ID column of the TrackingLogs table. |
ContactId | String | The ContactId column of the TrackingLogs table. |
Hash | String | The Hash column of the TrackingLogs table. |
ContactLink | String | The ContactLink column of the TrackingLogs table. |
SubscriberidLink | String | The SubscriberidLink column of the TrackingLogs table. |
TstampLink | Datetime | The TstampLink column of the TrackingLogs table. |
TypeLink | String | The TypeLink column of the TrackingLogs table. |
ValueLink | String | The ValueLink column of the TrackingLogs table. |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
UDateLink | Datetime |
Users¶
Usage information for the operation Users.rsd.
Table Specific Information¶
Select¶
The connector uses the ActiveCampaign API to process some of the filters.
For example, the following query is processed server side:
SELECT * FROM Users WHERE Id = 10003
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The ID column of the Users table. |
Email | String | The Email column of the Users table. |
FirstName | String | The FirstName column of the Users table. |
LastName | String | The LastName column of the Users table. |
ConfigsLink | String | The ConfigsLinks column of the Users table. |
DealGroupTotalsLink | String | The DealGroupTotalsLink column of the Users table. |
DealGroupUsersLink | String | The DealGroupUsersLink column of the Users table. |
ListsLink | String | The ListsLink column of the Users table. |
UserGroupLink | String | The UserGroupLink column of the Users table. |
Phone | Unknown | The Phone column of the Users table. |
Signature | Unknown | The Signature column of the Users table. |
Username | String | The Username column of the Users table. |
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 ActiveCampaign:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Contacts table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Contacts'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the SelectEntries stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the Contacts table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Contacts'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:activecampaign:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discovering the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
APIKey | The APIKey obtained from the ActiveCampaign UI. |
URL | Base URL which is used to access the API. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Property | Description |
---|---|
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from ActiveCampaign. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
APIKey | The APIKey obtained from the ActiveCampaign UI. |
URL | Base URL which is used to access the API. |
APIKey¶
The APIKey obtained from the ActiveCampaign UI.
Data Type¶
string
Default Value¶
""
Remarks¶
API key can be found in your account on the Settings page under the "Developer" tab. Each user in your ActiveCampaign account has their own unique API key.
URL¶
Base URL which is used to access the API.
Data Type¶
string
Default Value¶
""
Remarks¶
URL can be found in your account on the My Settings page under the "Developer" tab. For example: https://{yourAccountName}.api-us1.com
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\ActiveCampaign Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\ActiveCampaign Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from ActiveCampaign. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Pagesize¶
The maximum number of results to return per page from ActiveCampaign.
Data Type¶
int
Default Value¶
100
Remarks¶
The Pagesize
property affects the maximum number of results to return per page from ActiveCampaign. ActiveCampaign allows a maximum of 100 records to be returned in each page.
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Contacts WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json
Note that the specified path is not embedded in quotation marks.