V2 API Usage

Basic Usage:

 

- API endpoints begin with https://[your_domain]/api/

- Most endpoints require the parameter “userkey”, which should contain a valid user guid.  This can be found on your user profile as "Key:"

- All endpoints also support the parameter “action” which specifies which of the available actions to perform.

- Each endpoint has a default action(s) which will be assumed if no action is specified. Defaults may be different for POST requests than they are for REST requests.

- If an endpoint only supports a single action, or a single action for each request type, these are default actions (and will not be re-listed in this document).

- Note: Any special characters will need to be escaped before being added to the URL. A list of special characters and the escaped equivalents can be found HERE.

EX:

https://[your_domain]/api/tables 

is equivalent to

https://[your_domain]/api/tables?action=list

- All params may be either GET or POST params.

- Some parameters can contain a list of pipe (|) separated values. All endpoints support the parameter “split”. If used, any list that would normally use pipes as a delimiter should use the value of the “split” param.

EX:

split=<>&fields=field1<>field2<>field3

is equivalent to

fields=field1|field2|field3

 

Filtering using a Json Object

 js_filter is a JSON encoded array of objects specifying all filtering options. (Note, w_fields, w_conditions, and w_values will be ignored if js_filter is present.)

Requirements:

- type

  type is the formatting of the field such as text, number, etc.

- field

  This is the name of the field that will be used to filter on.  Valid options: any field in the table (case insensitive). Do not use Alias names for this.

- cond

 The condition to match using.

 Valid options:

  - eq (equal to)

  - ne (not equal to)

  - bw (begins with)

  - ew (ends with)

  - cn (contains)

  - bn (does not begin with)

  - en (does not end with)

  - nc (does not contain)

  - in (in [comma seperated list])

  - ni (not in [comma seperated list])

  - nu (NULL)

  - nn (Not NULL)

  - lt (less than)

  - le (less than or equal to)

  - gt (greater than)

  - ge (greater than or equal to)

- val

 The value you are searching for

EX1:

In this example we want to search a table's Make Key which is a text field for any occurrences of Chevy Camaro

js_filter=[[{"type":"text","field":"Make Key","cond":"eq","val":"Chevy Camaro"}]]

 

EX2:

In this example we want to search a table's Make Key which is a text field for any occurrences of Chevy Camaro and Chevy Impala.

Notice how the two filter's are in the same scope making it an and statement being used

js_filter=[[{"type":"text","field":"Make Key","cond":"eq","val":"Chevy Camaro"},{"type":"text","field":"Make Key","cond":"eq","val":"Chevy Camaro"}]]

 

EX3:

In this example we want to search a table's Make Key which is a text field for any occurrences of Chevy Camaro and Chevy Impala OR Dodge Challenger and Dodge Charger.

Notice how the filter for the Dodge Challenger and Dodge Charger are in a different scope than the Chevy Camaro and Chevy Impala

js_filter=[[{"type":"text","field":"Make Key","cond":"eq","val":"Chevy Camaro"},{"type":"text","field":"Make Key","cond":"eq","val":"Chevy Camaro"}],[{"type":"text","field":"Make Key","cond":"eq","val":"Dodge Challenger"},{"type":"text","field":"Make Key","cond":"eq","val":"Dodge Charger"}]]

Table Endpoints

/tables - Represents a list of tables in the system.

Actions:

GET:

- list

   Display information about tables in the system.

Params:

- userkey

- custkey

EX:

http://[your_domain]/api/tables?action=list&userkey=[your_userkey]&custkey=[your_customer_key]

/tables/[table_guid] - Represents a single table

/tables/[table_guid] - Select Records

Actions:

GET OR POST:

- select - Returns records from a table

Params:

- userkey  - Required

- custkey - Required

- fields - Optional

   A pipe separated list of fields to include in the response. If not present, all fields will be returned.

   Valid options: any field in the table (case insensitive).

- formats - Optional

   A pipe separated list of formats, corresponding in order to the list of fields.

   Valid options: value(default), count, count_dis, min, max, sum, avg

- rename - Optional

   A pipe separated list of names to display instead of the fields’ actual name, corresponding in order to the list of fields (leave a blank entry for fields not renamed

   Valid options: Any string.

- m_values - Optional

   A pipe separated list of mashup values used to calculate fields’ values, corresponding in order to the list of fields (leave a blank entry for fields that are not mashups).

   Valid options: any valid mashup string.

- w_fields - Optional

   A pipe separated list of fields to use to filter the results set (a “WHERE” query)

   Valid options: Name of any field in the table OR created mashup field.

- w_conditions  Optional

   A pipe separated list of comparison operands, corresponding in order to the list of w_fields.

   Valid options: eq, ne, bw, ew, cn, bn, en, nc, in, ni, nu, nn, lt, le, gt, ge

- w_values  Optional

   A pipe separated list of comparison values, corresponding in order to the list of w_fields.

   Valid options: Any integer or string.

- js_filter  Optional

   A JSON encoded array of objects specifying all filtering options. (Note, w_fields, w_conditions, and w_values will be ignored if js_filter is present.)

EX:

js_filter=[[{"type":"text","field":"field1","cond":"eq","val":"val1"}, {"type":"text","field":"field2","cond":"ni","val":"a,b,c"}]] is equivalent to w_fields=field1|field2&w_conditions=eq|ni&w_values=val1|a,b,c 

- limit  Optional

   The maximum number of records to return.

    Valid options: Any positive integer.

- page  Optional

   If a limit is specified, which record to begin the return with (based on multiples of the limit). Example: limit=10&page=2 will return records 11-20.

    Valid options: Any positive integer.

- order  Optional

   The field who’s values should be used to determine the order of the results (assumes ascending order.)

    Valid options: Any field in the table.

- js_order  Optional

   A JSON encoded array of objects representing any number of fields to order the results by, specifying direction.

EX:

    [{"field":"field1","dir":"ASC"},{"field":"field2","dir":"DESC"}] 

- delivery  Optional

   Method by which to deliver the results.

    Valid Options:

   - print: Simply respond with the results as text.

   - file: Write the results to a file and respond with the file.

   - grid: Respond with an HTML formatted grid displaying the results.

   - con: Send the results using a connection set up previously within the tool.

   - email: Write the results to a file and send it by email.

- orientation  Optional

   If the delivery is “grid”, specifies a vertical vs. horizontal grid.

    Valid options: vert, hort

- connection  Optional

   If the delivery is “con”, the ID of the connection which should handle delivery.

- file_type  Optional

   The format that should be used to display the results.

    Valid options: txt, xls, xlsx, csv, pipe, semicolon, xml, json

    Defaults: For “print” delivery: json. For other deliveries, csv.

- file_name  Optional

   For “file”, “email”, and some “con” deliveries, the name the file should be given. If not provided, a name will be generated.

    Valid options: Any string.

- file_ext  Optional

   The extension that the file should be given. If not provided, it defaults to the file type.

    Valid options: Any string.

- header  Optional

   Text to place above your results.

    Valid options: Any string.

- footer  Optional

   Text to place below your results.

    Valid options: Any string.

- compress  Optional

   When a file is created, method of compression to use. If not provided, file will be delivered uncompressed.

    Valid options: zip, rar, gzip, bzip2, 7zip, lzip

- email_to  Optional

   When the delivery is “email”, specifies the recipient address.

    Valid options: Any valid email address.

- email_subject  Optional

   When the delivery is “email”, specifies the subject line.

    Valid options: Any string.

- email_body  Optional

   When the delivery is “email”, specifies the body text.

    Valid options: Any string.

- js_email  Optional

   A json encoded object specifying all email options.

EX:

js_email={"to":"jd1@site.net","subject":"delivery","body":"Your file is attached."} is equivalent to email_to=jd1@site.net&email_subject=deliver&email_body=Your file is attached 

EX:

http://[your_domain]/api/tables/[your_table_guid]?action=select&fields=Location|Division&delivery=print&userkey=[your_user_key]&custkey=[your_customer_key]

/tables/[table_guid] - Insert Records

Actions:

GET OR POST:

  • insert - Add a new record to a table (can only do one insert call at a time)

Params:

- userkey  - Required

- custkey - Required

- fields - Required

   A pipe separated list of fields that will be populated in your new record.

   Valid options: any field in the table (case insensitive).

- values - Required

   A pipe separated list of mashup values used to calculate fields’ values, corresponding in order to the list of fields (leave a blank entry for fields that are not mashups).

   Valid options: any value valid for its corresponding field

EX:

http://[your_domain]/api/tables/[your_table_guid]?action=insert&fields=Location|Division&values=Philipsburg|Data%20Management&userkey=[your_user_key]&custkey=[your_customer_key]