Help Centre

Choose a category to find the help you need

API Access

Reading data from agileBase into external systems

There are a number of ways to get data from agileBase and integrate it with other systems, including use of our API, direct connection to the database with ODBC etc. The easiest is to use Zapier but in cases where we want to integrate with a system not supported by them, the first stop is often our JSON API. Here are details on how to use it.

Querying agileBase over HTTP

Connections to agileBase are made using standard HTTP calls. This makes it easy for developers in any language, so PHP, Java, even JavaScript in a browser can be used.

The first step is to create a view in agileBase that contains the data you want to make available to other apps. You can use the standard view creation features of the administrator interface, including adding fields and calculations, sorts and filters.

Secondly, turn on authenticated access for third party apps. In the view’s manage tab, under ‘sync’, tick ‘allow access from third party software’.

api turn on

You’ll get a number of items of information back, all of which are necessary to read information from that particular view.

The first is an access URL, if you’re hosting on our cloud service this will be

https://appserver.gtportalbase.com/agileBase/Public.ab

If you host on your own cloud or on premises, this may be different. Requests can contain a number of parameters, the one which must be supplied to use the JSON API is

get_report_json=true

Other parameters to control various options are detailed below.

HTTP requests can be submitted as GET or POST.

Three parameters for the view also need to be submitted as parameters to the request. These are ‘c’, ‘t’ and ‘r’, the values are displayed in the administrator interface (see screenshot above). Together, these identify the view from which to extract data.

Finally, the API key provided needs to be submitted not as a HTTP request parameter, but as a header named ‘Authorization’. This is to reduce the change of it being cached or used in a browser string where it can be easily retained and read by unauthorised users.

Here’s a complete example of making a request, using PHP in WordPress

[php]

$url = 'http://appserver.gtportalbase.com/agileBase/Public.ab?get_report_json=true&simple_format=true&t=mytablecode&r=myreportcode&c=mycompanycode&json_format=json';

$args = array('headers' => array( 'Authorization' => 'myauthorisationkey'));

$response = wp_remote_get( $url, $args );

var_dump($response);

[/php]

Sample output would look like

[
  {
    forename: "Oliver",
    surname: "Kohll",
    email_address: "oliver@

agilebase.co.uk

"
  },
  {
    forename: "Simon",
    surname: "Minton",
    email_address: "simon@

agilebase.co.uk

"
  },
  {
    forename: "Cliff",
    surname: "Calcutt",
    email_address: "cliff@

agilebase.co.uk

"
  }
]

of course the fields being those returned for the particular view chosen.

Request options

Here are the options that can be provided as parameters to the request, along with the c, t and r identifiers and in addition to get_report_json=true.

  • simple_format=true/false (default false)
    This option controls the JSON format returned. With simple_format=true, the response will be a simple array of objects, each containing keys for the field namesm as above. Keys are basically lowercase field names with spaces replaced by underscores, so “Email address” would become “email_address”
    With simple_format=false, a more complex but more robust format it used. Rather than field names, internal agileBase field identifiers are used as keys. This has the advantage that if the field names are changed (easy for an agileBase administrator to do), the JSON will remain the same. The first object in the JSON will be a dictionary mapping internal identifier to field name
  • [json_format=json]
    With this option, just the JSON will be returned. This is the usual case required. Without it, complete Javascript will be returned defining the JSON as an object, i.e.var abJson=[…]which can be useful if you want to run the result as Javascript
  • [return=posted_json]
    Optional, if this is included, the return content will be JSON including the ID of the record as well as the full content of the data
  • cache_seconds=[seconds]
    How long to cache the results for (default 600 seconds or 10 minutes). Only un-filtered results are cached
  • unencode_html=true
    Causes characters which would normally be encoded as HTML entities (e.g. & -> &) to be returned unencoded
  • exact_filters=true/false (default false)
    If true, filters (see below) will be need to match results exactly instead of using the default ‘contains’ filtering

Filtering

By specifying exact_filters=true, filters can be supplied to search for a particular record or set of records. For HTTP parameter names, just use the internal agileBase field identifiers. The easiest way of finding these is to right click on a field in the administrator interface using a browser like Chrome, Safari or Firefox and select ‘inspect element’. The identifier will look something like ‘a467b03e93435a25e’.

Standard agileBase filtering techniques can be used for preparing complex criteria on multiple fields, for example in each field using “?” to find blank records, > and < for numbers and date ranges, phrases like “last month” for dates, “=[word]” for exact matches, “:word” for starts with, and “!word” for doesn’t contain.

Response codes

200: request successful

401: unauthorised: the API key is missing or incorrect

404: not found: an object identifier (company ID, table ID or report ID) was supplied that wasn’t found in the system

429: too many requests: the daily API request limit has been exceeded for the view being queried

500: some other server error

Generate API descriptions

For developers who use the agileBase API to create and update data within agileBase from a third party product, there’s now a way to further automate the process.

agileBase will now generate a swagger.io compatible API description of any table you want to post to – that means if you deal with accounts for many agileBase customers, you can with a common query get the API details needed to interact with them.

Just make a POST request to

/agileBase/Public.ab with parameters

  • c = the company identifier (the table options screen will show this)
  • t = the table identifier (similarly shown by the options screen)
  • describe_table = true

and the ‘Authorization’ header set to the API key (if the table requires one).

This also makes testing with Postman easier, since Postman can import a Swagger API description – so you don’t have to write a single line of code to test out an API.

 

postman

Posting data into agileBase from external systems

To read data from agileBase to external apps, you can use our JSON API. To get data in, it can be posted in as follows. Note there are other alternatives such as direct ODBC access or Zapier integration.

 

Preparing a form for input

Firstly, in the agileBase administrator interface, turn on the option to allow data to be posted from public sources, e.g. a web form.

Under ‘build’, choose the relevant table, go to the manage tab, press ‘options’ and tick ‘public data entry’.

 

api_in

 

Optionally, you can add an email address to notify when a new item is added and an autoresponse text which will be sent to any email addresses found in the posted data.

To protect the system from spam input, we also suggest you tick ‘Require API key’ and reload the table to show the API key generated. This then has to be submitted with every POST request as the value of the HTTP Authorization header.

 

Creating new records

Clicking the ‘sample form’ link will then show a sample form which can be embedded into a website. Here’s an example:

 

The Push API

When integrating agileBase with third party systems, the API is really useful. Programmers can use it to send data to websites, to other software such as PowerBI, a  Business Intelligence tool, or to external systems such as label/barcode printers for example. The opportunities are endless.

We now have a way to ‘push’ data from agileBase, rather than ‘pulling’ it from a third party system.

What’s the difference? Simply put, if pulling, a third party system has to regularly ask agileBase whether there’s any new data. It may do this once an hour, once every few minutes or however often it needs. That means extra work for both systems – new data may be reasonably infrequent but when it is there you want it to be transferred quickly, which means polling often even when there’s nothing to send.

Conversely, with ‘push’, agileBase sends a message to the third party system only when there’s relevant data to send.

Not only is this more efficient, it’s less costly too. Each agileBase API call costs a small amount, as set by the capacity that’s been purchased. Third party systems may also have capacity limits or costs which build up over a number of calls. In the ‘pull’ scenario, each call would use server resources and generate costs even when there’s no data to send.

When using push, calls are made only when necessary. Further, you can specify in the settings the maximum number of calls to make per day, so you can control the maximum possible cost. You can set it to anything from once every 5 minutes to once a day – different integrations may require more or less timely data. For example invoice totals may be ok to push to an accounting system once a day but individual customer orders may be best sent as soon as they’re received.

 

Llama with 2 heads facing opposite directions

 

Setting up the push API

  1. Firstly, set up a view to operate as a standard ‘pull’ API
  2. Enter a URL into the ‘Push URL (optional)’ area below the other details on that screen. This is the URL that agileBase will POST to when there’s new data.
  3. In the table that the API view was created from, add a new date/time field, accurate to the second, for the system to record when the API push was last used
  4. Go to the ‘workflow’ section under the view’s ‘manage’ tab.
    1. for ‘workflow action’, select ‘send data to a third party system using the API’
    2. for ‘recording the time of the last action’, choose the date/time field created above
    3. choose a minimum interval to wait between pushes

The system will now make a POST to the URL specified in step 2 whenever there’s new data to send. It will contain one parameter, ‘json’, which is a JSON representation of all the data in the view.

 

Selecting data to push

How do we know which data is new and wants to be sent out via the API?

That’s up to you – any filters can be added to the view to select data you want. Commonly, you’d use the date/time field set up in step 3. When a push successfully completes, every record in the view has this field set to the current time.

Note: if the push encounters an error for any reason, say the third party system returns a HTTP error code rather than the expected ‘200’ code for success, the timestamp won’t be set.

A few common scenarios would be:

 

Pushing any data modified since it was last pushed

To do this, add a boolean calculation to your view, something like

needs pushing = {last modified [auto]} > {last pushed}

where ‘last pushed’ is the name of the timestamp field you added in step 3 above.

Then add a filter on the view ‘needs pushing equals true’

 

Pushing any new rows that have never been pushed before

For this scenario, simply add a filter to the view ‘last pushed is empty’

 

Pushing only rows not yet marked as received

In some cases, you may want to be even more prudent than using the internal timestamp. The third party system could make a separate API call to agileBase for every row that’s received, telling it to update the row with an ID to prove receipt. That’s more API calls of course, but some situations may warrant it.

Calendar synchronisation

If a view contains a date field, the data in it can by synchronised with an external calendar application, e.g. Google Calendar, a mobile phone calendar, Outlook or the iPad.

If it contains two date fields, the first will be used for the start date/time, the second for the  finish.

Once set up, the link will be maintained and the calendar will update whenever the data in agileBase is updated. The synchonisation is one way, i.e. updating agileBase will change the calendar view but you can’t edit the calendar to update agileBase. In other words, agileBase is the ‘master’ copy.

To set up a link, in the admin interface, go to a view’s manage tab, tick the checkbox to allow synchronisation and follow the instructions.

Note: for Google calendar synchronisation, we have found that a Zapier link is the most reliable method.

 

Screenshot of training calendar

Direct Google Calendar synchronisation

When people want to synchronise events to their Google calendars, we usually use the third party http://www.zapier.com who we have a built in integration with. They have a great service which allows you to push data to hundreds of other cloud apps, including Google calendars.

The only downside is that it’s a one-time sync only: once an event has gone across, any changes made in agileBase won’t be reflected in Google, say if the date/time or any other details of the event change.

To combat that, we’ve now made a more fully featured direct integration between the two services. With the new system, not only will changes made in agileBase synchronise to Google whenever an event is updated, but the event in Google will also contain a link back to the record that created it in agileBase. Just clicking on the event will take you to the right meeting, lead, sale, document or whatever created the calendar entry.

Updating an event in agileBase will trigger a push notification to Google calendar. This can happen between ‘immediately’ and up to ten minutes after the event (for demand levelling) but should take no longer than that.

 

How to set up a direct calendar integration

Firstly, contact us to enable the service. Although you’ll no longer need to pay Zapier integration fees, you will need to agree a contract that covers your usage as regular push notifications require resources and potentially Google fees. You will also need a ‘Google Apps for my domain’ account.

Once enabled, the configuration has a number of steps but is reasonably straightforward. If you like, we’d be happy to set up your calendars on your behalf as consultancy/support, please contact us if you’d like us to do this. If you’d like to crack on yourselves, here are the steps:

  1. Add some necessary fields to the table
    1. In the table that the events come from, create a text field called “Google Event ID” (the name needs to be exact). This will be used by the system to store internal IDs to help with the synchronisation process.
    2. Create a date/time field (with accuracy SECOND) to store the synchronisation time. Call it something like “Last synchronised”. This will be used by the system to record when the event was last sync’d, also to help with the details of the process.
  2. Set up a new view
    1. Create a new view from the table. This view will be configured to show all events that need to be synchronised with a single Google calendar
      We recommend creating a new module e.g. “Google Calendars” to store views that sync with Google.
    2. Add a date field or calculation into the view. The first date field in the view will be used as the event’s start date
    3. Optionally, add a second date/time field to be used as an end date. If you don’t, the end date will be set to the start date for whole day events, or the start date plus an hour for events with a time
    4. Add a text field / calc to be used as the event title in Google
    5. Optionally, add a text field / calc for the location. Any postal address recognisable to Google can be used. The simplest option is just to use a postcode.
    6. Optionally add a text field for the event description
    7. Finally, add the field “Google Event ID” that you created (see above) to the view
  3. Add some filters to choose the events to sync
    1. Typically, add a filter to hide events in the past. There’s not often any point in updating events which have already come and gone. Excessive updates may incur additional fees from Google.
      e.g. Start Date newer than 0 days ago
    2. Only show events which need to be created or updated in Google. Reference the “Last Notified” field you created in step 1. The easiest way is to create a calculation likeTo Sync (boolean) = {last notified} IS NULL OR {last modified [auto]} > {last notified}Then add the filter “To Sync = true”
      This will ensure only newly created events, or events which have been modified since the last sync, will be included
  4. Connect to a Google calendar
    1. In the view’s manage tab, click Sync
    2. Under Google Account Integration, type in your email address (this address must be associated with a “Google Apps for my domain” account. Refresh the screen
    3. Under your email address, a dropdown list of your Google calendars will appear. Select the one you want to send events to and refresh the screen.
    4. For the setting “Synchronisation time field”, select the field that you set up in step 1, e.g. “Last Synchronised”
    5. Refreshing the screen will now show a list of which fields will be used for which parts of the event, e.g. start, location, description etc. so you can check all the fields are in the right order in the view

Finally, create a new record, wait up to 10 minutes then take a look at your Google calendar to check that it’s gone across! Note, when looking at the event details in Google, you’ll be able to click the agileBase source link which will take you directly to the original record.


Not found what you’re looking for?

Please email us at support@agilechilli.com