Air Forms for iPad

Revolutionise your business!

Air Forms API Description

If you've chosen to create cloud based tables, you'll will have entered a data source URL telling Air Forms where it should send its requests for data to. This section outlines the API you'll need to implement on your webserver to allow Air Forms to access your database systems.

Air Forms uses a simple API over HTTPS (or HTTP, however this is STRONGLY discouraged). Air Forms uses HTTP POST method to post a JSON payload describing the action your server should take. The server should then reply with a JSON response.

The API consists of eight functions:
  • authenticate
  • record_count
  • records_at_offset
  • new_record
  • delete_record
  • update_record
  • values_for_list
  • data_for_media_column

Function Descriptions

authenticate

Verifies that the user has permission to access this database.

Request Parameters

usernameRequired. A string specifying the users username.
passwordRequired. An string specifying the users password. This string is not encrypted or hashed, meaning that it can be used to authenticate against external directory services to determine a users access. If the Data Source URL you have specified is NOT secured using SSL, users passwords will be exposed in clear text to other network users. This request should ALWAYS be secured using SSL.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" if the user has permission to access.

Request & Response Example

Request Payload:
{	
  "function"    :"authenticate",
  "username"    :"Johnny",
  "password"    :"apple"
}
Response Payload:
{
  "result_code"    :"0"
}

record_count

Returns the number of records in a table for the given search predicate.

Request Parameters

tableRequired. A string specifying the table whose record count should be returned.
search_predicateOptional. An array specifying the column name and value pairs from which to build the search query. See the Entities section for information on the format of this array.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
record_countRequired. A string specifying the number of records in the table, given the search predicate supplied.
total_record_countRequired. A string specifying the number of records in the table, ignoring the search predicate supplied.

Request & Response Example

Request Payload:
{	
  "function"    :"record_count",
  "table"       :"invoices"
}
Response Payload:
{
  "result_code"        :"0",
  "record_count"       :"2124",
  "total_record_count" :"2124"
}

records_at_offset

Returns the specified maximum number of ordered records for a given search predicate, beginning at a specified offset.

Request Parameters

tableRequired. A string specifying the table to fetch records from.
recidRequired. A string specifying the column name of the row identifier. This should be the primary key column name.
offsetRequired. A string specifying the offset within the ordered set of records in the table.
max_recordsRequired. A string specifying maximum number of records to return. You may return fewer records in the response.
columnsRequired. An array of column names to fetch from the table.
summary_columnsRequired. An array of summary column objects to fetch from the table.
search_predicateOptional. An array specifying the column name and value pairs from which to build the search query. See the Entities section for information on the format of this array. Used to return correct record counts.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
recordsRequired. An ordered array of column name value pair dictionaries for every record returned. Media column values return a "Media Value Description" entity encoded as a string, which does not contain actual file data.

Request & Response Example

Request Payload:
{	
  "function"    :"records_at_offset",
  "table"       :"customers",
  "recid"       :"recid",
  "offset"      :"50",
  "max_records" :"3",
  "columns"     :["customer_name","customer_contact","phone","fax","email","photo"]
}
Response Payload:
{
  "result_code"    :"0",
  "records"   :[
                 {"recid":"50", "customer_name":"", "customer_contact":"", "phone":"",
                   "fax":"", "email":"", "photo":"{\"mime_type\":\"image\/jpeg\",
                        \"file_md5\":\"86b1c9fe5e8a3b48f26a50e86b9498b8\"}"}, 
                           
                 {"recid":"51", "customer_name":"", "customer_contact":"", "phone":"", 
                   "fax":"", "email":"", "photo":""}, 
                   
                 {"recid":"52", "customer_name":"", "customer_contact":"", "phone":"",
                   "fax":"", "email":"", "photo":"{\"mime_type\":\"image\/jpeg\",
                        \"file_md5\":\"a6b7764fe5e8a3b48f26a50e86ca42363\"}"}
               ]
}

new_record

Inserts a new record into a table.

Request Parameters

tableRequired. A string specifying the table to insert the record into.
recidRequired. A string specifying the column name of the row identifier. This should be the primary key column name.
defaultsOptional. An array of column name and value pairs used to populate the new record.
search_predicateOptional. An array specifying the column name and value pairs from which to build the search query. See the Entities section for information on the format of this array. Used to return correct record counts.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
recid_valueRequired. A string specifying the value of the newly created record unique row identifier. This should be the value of the primary key column of the new record.
record_countRequired. A string specifying the number of records in the table, given the search predicate supplied.
total_record_countRequired. A string specifying the number of records in the table, ignoring the search predicate supplied.

Request & Response Example

Request Payload:
{	
  "function"    :"new_record",
  "table"       :"invoices",
  "recid"       :"recid"
}
Response Payload:
{
  "result_code"        :"0",
  "recid_value"        :"1023412",
  "record_count"       :"23412",
  "total_record_count" :"23412"
}

delete_record

Removes a single record from a table.

Request Parameters

tableRequired. A string specifying the table to delete from.
recidRequired. A string specifying the column name of the row identifier. This should be the primary key column name.
recid_valueRequired. A string specifying the value of the unique row identifier column of the record to delete.
search_predicateOptional. An array specifying the column name and value pairs from which to build the search query. See the Entities section for information on the format of this array. Used to return correct record counts.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
record_countRequired. A string specifying the number of records in the table, given the search predicate supplied.
total_record_countRequired. A string specifying the number of records in the table, ignoring the search predicate supplied.

Request & Response Example

Request Payload:
{	
  "function"    :"delete_record",
  "table"       :"employees",
  "recid"       :"recid",
  "recid_value" :"93"
}
Response Payload:
{	
  "result_code"        :"0",
  "record_count"       :"121",
  "total_record_count" :"121"
}

update_record

Sets the value of a single column in a record.

Request Parameters

tableRequired. A string specifying the table to update.
recidRequired. A string specifying the column name of the row identifier. This should be the table's primary key column name.
recid_valueRequired. A string specifying the value of the unique row identifier column of the record to update.
column_typeRequired. A string specifying the type of data which is stored in this column. (Text, Number, Media etc)
columnRequired. A string specifying the column name to update.
valueRequired. A string specifying the value to set the column to. If column_type is set to 'Media' this value is set to a 'Media Value' entity encoded as a string.
search_predicateOptional. An array specifying the column name and value pairs from which to build the search query. See the Entities section for information on the format of this array. Used to return correct record counts.
autoupdateOptional. An array specifying columns which require autoupdating based on this column value being changed. See the Entities section for information on the format of this array.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
record_countRequired. A string specifying the number of records in the table, given the search predicate supplied.
total_record_countRequired. A string specifying the number of records in the table, ignoring the search predicate supplied.

Request & Response Example

Request Payload:
{	
  "function"    :"update_record",
  "table"       :"customers",
  "recid"       :"recid",
  "recid_value" :"1242",
  "column"      :"phone",
  "value"       :"417-555-5555"
}
Response Payload:
{	
  "result_code"        :"0",
  "record_count"       :"12982",
  "total_record_count" :"12982"
}

values_for_list

Retrieves distinct values from the specified column in a table.

Request Parameters

tableRequired. A string specifying the table to gather distinct values from.
recidRequired. A string specifying the column name of the row identifier. This should be the table's primary key column name.
columnRequired. A string specifying the column name to gather distinct values from.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
recordsRequired. An array of recid and value dictionaries of each distinct value from the specified table column.

Request & Response Example

Request Payload:
{	
  "function"    :"values_for_list",
  "table"       :"employees",
  "column"      :"name"
}
Response Payload:
{
  "result_code"    :"0",
  "records"        :[
                     {"recid":"1", "value":"Layton"}, 
                     {"recid":"2", "value":"Sam"}, 
                     {"recid":"3", "value":"John"}
                    ]
}

data_for_media_column

Retrieves the file data for the specified media column.

Request Parameters

tableRequired. A string specifying the table to gather media data from.
recidRequired. A string specifying the column name of the row identifier. This should be the table's primary key column name.
recid_valueRequired. A string specifying the value of the row identifier.
columnRequired. A string specifying the column name to fetch the file data for.

Response Parameters

result_codeRequired. A string specifying the outcome of the request. Set to "0" on successful request.
file_md5Required. A string specifying the MD5 hash of the raw, non Base64 encoded file data.
file_base64_dataRequired. A string specifying the Base64 encoded file data.

Request & Response Example

Request Payload:
{ 
  "function"      :"data_for_media_column",
  "table"         :"contacts",
  "recid"         :"recid",
  "recid_value"   :"46",
  "column"        :"photo"
}
Response Payload:
{
  "result_code"       :"0",
  "file_md5"          :"86b1c9fe5e8a3b48f26a50e86b9498b8",
  "file_base64_data"  :"iVBORw0KGgoAAAANSUhEUgAAAbgAAAEcCAYAAAC1R6FLAAAgAEl..."
}

Result Codes

Every function should return a result code as part of the response payload. Valid response codes are:

0The request was completed successfully.
1The request failed. You may provide a description of the error to be displayed to the user by adding an "error_description" key and value into the response payload.
2Authentication failed. This will close the database on the users device, and they will be forced to re-enter their username and password.

Entities

The Search Predicate entity is an array of objects representing columns and values in a single table which are logically ANDed together to form a query.

columnRequired. A string specifying the column name in the table for which to search.
valueRequired. A string specifying the value to search for within the column.

The Media Value entity is a dictionary of objects representing a media value.

mime_typeRequired. A string specifying the MIME type of the file.
file_md5Required. A string specifying the MD5 hash of the raw, non Base64 encoded file data.
file_base64_dataRequired. A string specifying the Base64 encoded file data.
Media Value Entity Example:
{ 
  "mime_type"        :"image/png",
  "file_md5"         :"86b1c9fe5e8a3b48f26a50e86b9498b8"
  "file_base64_data" :"iVBORw0KGgoAAAANSUhEUgAAAbgAAAEcCAYAAAC1R6F..."
}

The Media Value Description entity is a dictionary of objects describing a media value, but without its associated file data. This entity is returned with the records_at_offset function to minimise data transfer. It gives Air Forms enough information about the value to manage caching of media column data on the device. Air Forms uses the file_md5 to determine whether the cached file on device is valid, if it's not, the data_for_media_column function is called as necessary.

mime_typeRequired. A string specifying the MIME type of the file.
file_md5Required. A string specifying the MD5 hash of the raw, non Base64 encoded file data.
Media Value Description Entity Example:
{ 
  "mime_type" :"image/png",
  "file_md5"  :"86b1c9fe5e8a3b48f26a50e86b9498b8"
}

The Autoupdate entity specifies the columns which may require automatic updating as a result of a particular record being updated.

trigger_columnsRequired. A array of trigger columns entities specifying which columns trigger autoupdating.
trigger_relationshipsRequired. A dictionary of trigger relationship entities with relationship_recids as the key value.
Autoupdate Entity Example:
"autoupdate":{
  "trigger_columns":
    [
      {"column":"product","relationship_recid":"2"}
    ],
  "trigger_relationships":
   {
    "2":
     {
      "columns":[{"source_column":"unit_price","destination_column":"unit_price"}],
      "relationship":
       {
        "name":"Order Items - Products",
        "id":"2",
        "criteria":
          [
            {
             "table_1_column":"product",
             "comparison_operator":"=",
             "table_2_column":"product_name"
            }
          ],
        "table_1":"order_items",
        "table_2":"products"
       }
     }
   }
  }

The Trigger Column entity specifies the columns which may require automatic updating as a result of a particular record being updated.

columnRequired. The name of the column which triggers autoupdating of other columns.
relationship_recidRequired. An identifier specifying the key used in the autoupdate's entity trigger_relationships dictionary.

The Trigger Relationship entity specifies the columns which may require automatic updating as a result of a particular record being updated.

columnsRequired. A array of columns names, and relationship identifiers specifying which columns trigger autoupdating.
relationshipRequired. The relationship entity used to fetch the new field value.

The Relationship entity is an object which represents a relationship between two tables.

idRequired.
nameRequired.
table_1Required.
table_2Required.
criteriaRequired. An array of relationship criteria entities defining the conditions of the relationship.

The Relationship Criteria entity.

table_1_columnRequired.
table_2_columnRequired.
comparison_operatorRequired.

Important Security Considerations

This API should ALWAYS be implemented over SSL secured HTTP connections. Username and password strings are transmitted unobscured in the request payloads. If you are considering implementing this API over HTTP instead of HTTPS, you will be exposing usernames and passwords to the world.

Twitter

Follow @PolarBearFarm on Twitter!