Act-On Connector

Act-On Connector

Connecting to Act-On

Please read the instructions to obtain your API Key and API call limitations here:  https://developer.act-on.com/provision/
Please supply the username, password and API Key for Act-On in the Connector window:



Contacts

To read from Contacts, it seems that you have to pass in the list id and/or(?) contact id. Please check out the documentation to get this to work. If you think it should work differently, please let us know.

Filters

“Criteria” is passed directly to the REST API. ‘createdafter’ is in Epoch/Unix millisecond timestamp format.
Act-On Origin Filter Criteria.png


SELECT Statements

A SELECT statement can consist of the following basic clauses.

  • SELECT
  • INTO
  • FROM
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY
  • LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {
  TOP <numeric_literal> | DISTINCT ]
  {
    *
    | {
        <expression> [ [ AS ] <column_reference> ]
        | { <table_name> | <correlation_name> } .*
      } [ , ... ]
  }
  INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  {
    FROM <table_reference> [ [ AS ] <identifier> ]
  } [ , ... ]
  [ [ 
      INNER | { { LEFT RIGHT FULL } [ OUTER ] }
    JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
  ] [ ... ]
  WHERE <search_condition> ]
  GROUP BY <column_reference> [ , ... ]
  HAVING <search_condition> ]
  UNION ALL ] <select_statement> ]
  [
    ORDER BY
    <column_reference> [ ASC DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [
    LIMIT <expression>
    [
      { OFFSET | , }
      <expression>
    ]
  ]
}
 
<expression> ::=
  | <column_reference>
  | @ <parameter>
  | ?
  COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG MAX MIN SUM COUNT } ( <expression> )
  NULLIF ( <expression> , <expression> )
  COALESCE ( <expression> , ... )
  CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    ELSE { <expression> | NULL } ]
    END
  | <literal>
  | <sql_function>
 
<search_condition> ::=
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT_LIKE | IN | NOT_IN | IS_NULL | IS_NOT_NULL | AND OR CONTAINS BETWEEN } [ <expression> ]
  } [ { AND OR } ... ]

Examples

  1. Return all columns:
    SELECT * FROM Images
  2. Rename a column:
    SELECT [Name] AS MY_Name FROM Images
  3. Cast a column's data as a different data type:
    SELECT CAST(Size AS VARCHAR) AS Str_Size FROM Images
  4. Search data:
    SELECT * FROM Images WHERE FolderName = 'New Folder';
  5. Return the number of items matching the query criteria:
    SELECT COUNT(*) AS MyCount FROM Images
  6. Return the number of unique items matching the query criteria:
    SELECT COUNT(DISTINCT Name) FROM Images
  7. Return the unique items matching the query criteria:
    SELECT DISTINCT Name FROM Images
  8. Summarize data:
    SELECT Name, MAX(Size) FROM Images GROUP BY Name
    See Aggregate Functions for details.
  9. Retrieve data from multiple tables.
    SELECT Channels.Name, Users.FullName FROM Channels, Users WHERE Channels.Creator=Users.Id
    See JOIN Queries for details.
  10. Sort a result set in ascending order:
    SELECT Id, Name FROM Images  ORDER BY Name ASC
  11. Restrict a result set to the specified number of rows:
    SELECT Id, Name FROM Images LIMIT 10
  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    SELECT * FROM Images WHERE FolderName = @param
See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from Act-On.

SELECT * FROM Images WHERE Pseudo = '@Pseudo'

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7655
 

Parameter
Description
API User Key




    • Related Articles

    • Act! Connector

      ACT! Premium Cloud: StarfishETL supports two-way integration. ACT! On-Prem: StarfishETL does not have a native Act! connector for versions less than Act v18 so we read directly from the database. Configuration Options Connect Act Premium (v18+) using ...
    • Hubspot Connector

      Additional Documentation This Connector is based on the Hubspot ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/DHG/ado/ Hubspot Connector Note: As of September 8, 2020 ...
    • Pardot Connector

      Additional Documentation This Connector is based on the Salesforce Pardot ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/GPG/ado/ Overview The Starfish Pardot Connector ...
    • Salesforce Connector

      Additional Documentation This Connector is based on the Salesforce ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/RFG/ado/ Salesforce Origin Case as Origin Table You ...
    • SugarCRM REST Connector

      SugarCRM REST Origin Use JSONLint to validate JSON: https://jsonlint.com/. Sample Origin Filters See the GET /<module> filterList in the SugarCRM REST Help: https://SERVER/rest/v10/help/ Note the [{...}] surrounding the filter. This is required. ...