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<>field3is 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":"[email protected]","subject":"delivery","body":"Your file is attached."} is equivalent to [email protected]&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]
