Screen Shot 2017-08-08 at 19.05.55

We’ve recently upgraded our map visualisations, in a few ways:

  • data points can now have different sizes as well as colours, depending on fields of your choice
  • address recognition has been improved
  • the map background has been made grayscale so data points can be more easily seen

How to set up a map

  1. Open or create a view with address data in it. If mapping more than a couple of dozen data points, the view needs to contain a column for postcode, currently only UK postcodes are supported (though other data can be mapped – see below).
  2. Open the view and click the visualisations button (chart icon) on the left
  3. Under ‘map by postcode field’ on the right, choose the field in the view that contains the postcode (it will usually be called ‘postcode’)
  4. Optionally, select a text field to ‘colour by’. Each separate value in this field will get a different colour, so status fields are a good option for example
  5. Optionally, select a numeric field to ‘size by’. Larger numbers will get larger diameter circles. The system will automatically normalise the sizes so that none come out too massive or tiny

Address recognition

Some addresses in your system may not contain postcodes, either because they’re overseas or the address is incomplete. That’s no problem for a few cases, the system will query an address service ‘nominatim’, which powers

If your view contains other address fields, e.g. address 1, address 2, town, city or country, the system will build up the address to query by joining these fields together, from left to right in the view. The service has strict limits on the number of queries, so we can only do 15 or 20 at a time (exact limits may be subject to change), with a short delay inbetween each. However, we do cache the results in our database, so future calls to the same information won’t need this querying, each address will only ever need to be looked up once.

Note: we used to use Google Maps for this querying, but Open Street Maps has proved more accurate for the UK.

If you want to exactly specify which fields your ‘fallback address’ is built from, you can create a calculation and use that as the ‘postcode’ field. It could look something like

coalesce({postcode}, {city} || ', ' || {country})

if for example you wanted to fall back to city and country if postcode was blank.




Many thanks to all of the many UX testers and customers who’ve recently helped us improved our platform and products.

There have been more updates than usual this month, that’s just because we’re being very productive implementing all the suggestions coming our way.

Without further ado, here are a few ways in which the new charting upgrade, which has been in beta testing for the past month, has been recently improved:

Chart editing

Previously, once a chart was saved, it wasn’t editable any more. That was because there wasn’t much point – charts were so easy to recreate, in most cases just needing you to specify the x-axis and y-axis functions. Now that there are more optional settings to customise, such as chart type, editing becomes more useful.

To edit a chart, there’s a new ‘edit’ option in the chart menu.


That will let you update the chart type, filters, breakdown etc.

Chart height

There’s a new option to let the user specify the height of a chart, useful for where there are lots of rows vertically which might be squashed – available under ‘advanced settings’ when editing a chart.


Editing feedback

The charting system is now more user friendly when making changes, so it’s clear for example when actions are in progress if they’re going to take a couple of seconds.

Please carry on telling us any new improvements you can think of!

Today we add a rather heavyweight feature to the workflow capabilities of the agileBase platform – recursion.

If you’re interested in a bit of maths and want to know what that means, there’s a great tutorial about the concept at the Khan Academy.

If not, never mind, the takeaway is it allows an administrator to set up workflows that weren’t possible before, particularly where you have networks of people or things. For example, if your CRM links people who know each other, you could create a list of all people 1 step removed, 2 steps etc.

More generally, any calculation with a repetitive step that depends on the results of the previous repetition is recursive. For example, solving sudokus or to some extent crosswords. In fact, we have a sudoku solver as one of our demos.

Worked example – an ingredients declaration

Here’s an example that may be a useful pattern for reference. We have a recipe with quantities of each ingredient. However, an ingredient may itself also be a recipe containing other ingredients. We wish to calculate an ‘ingredients declaration’ which has a list of all ‘raw ingredients’ at the end of the chain i.e. those with no recipe and their percentages in the initial recipe.

This can (and has) been done with a some linked workflows including a couple of recursive steps. Here’s the logic (not including technical implementation details).

Step 1

Firstly, with for our main recipe (let’s call it Tom Yum Soup), look at all the direct ingredients and make a copy list, we’ll call it the ‘exploded ingredients’. After this step it will just be exactly the same ingredients as the main list, but later on we’ll recurse and add ingredients of ingredients.

In agileBase parlance, a workflow will copy all the items from an ‘ingredient lines’ table to an ‘exploded ingredient lines’ table. Each exploded ingredient line will have

  • a link to the main recipe, i.e. Tom Yum Soup
  • a link to it’s corresponding ingredient line e.g. 10 grams of chilli paste
  • a link to the ‘parent exploded line’, which will just be empty in this first step

Step 2

Go through each of the exploded ingredient lines. For each one, if the ingredient is itself a recipe, add lines for each of it’s ingredients, setting

  • main recipe = the same main recipe as the existing exploded line, i.e. Tom Yum Soup
  • link to parent ingredient line = the line we’re looking at e.g. 10 grams chilli paste
  • link to ingredient line = e.g. 7 grams crushed chillies

Now recurse and do step 2 again, for any of the new exploded ingredient lines that are themselves recipes. Repeat 2 as many times as are necessary until there are no new lines which are recipes. This is the key step – after this you’ll have a complete list of raw ingredients.

To make agileBase do that, simply tick the ‘recursive’ checkbox in the view’s workflow section. It will repeat the step until the view contains no records.

Step 3

The only thing left is to fill in the percentages. This involves a similar logic – bootstrap by first filling in percentages copied from the direct ingredients of the main recipe, then in a new workflow calculate the percentage of sub-ingredients by multiplying the proportion of the main ingredient by the percentage in the sub-ingredient. So for example if our Tom Yum is 2% chilli paste, which contains 70% crushed chillies, the percentage of crushed chillies in the Tom Yum is 0.02 x 70 = 1.4%.

When implementing, there are a couple of other details to take into consideration, e.g. how to mark each exploded line as ‘processed’ so you don’t keep on re-calculating them. We do that with a counter. You also have to deal with the mechanics of things like what to do if a recipe changes e.g. an ingredient is deleted – another recursive workflow will go through and delete all exploded lines including sub-ingredients.

If you’d like to see a full implementation details for this example, please let us know and we can take you through it on our demo system.

Step 4

Finally, the ingredient declaration to e.g. go on a label can be calculated by joining together the names of all the raw ingredients i.e. the exploded ingredients that aren’t themselves recipes, along with their percentages.

Visualising linked workflows

We’ve added one more thing to the system to make working with these multi-step workflows easier. When you click on a workflow step in the admin interface, it will colour all preceding steps (if any) in green – fading to lighter green the further back they go. Similarly all future chained steps are marked in blue, fading to light blue the further forward they are in the chain.



An update for system administrators today – calculations now automatically update throughout the system whenever a field, view or table name is changed.

Before this, if a field name was changed in a table, calculations on that field would still show the old name. They’d need manually updating the next time each calculation was edited.

Now, you don’t have to worry about any of that when changing names.

Due to this, cloning views will work first time more regularly – previously any out of date calculations would have to be updated beforehand.


100x58_1When you’ve been developing one piece of software for a number of years there are inevitably one or two decisions you make early on that with hindsight you’d make differently. However, there’s one major decision that we at agileBase get happier with every day: choosing PostgreSQL as our data store.

As a customer, you may not know the details of which database is used to store your data, or the technicalities of what a database is exactly, but it’s worth highlighting a few points that have a direct impact on your business with us. The database forms such a large part of our offering – it’s what keeps your data safe and means however many users you add, the system remains lightning fast. Our own ‘fast, friendly and flexible’ agileBase platform built on top of the database takes advantage of that.

Besides robustness and performance, there are a few other business benefits:

Open source

The database is open source – one could talk for a long time about what that means, but for you and I there are three main benefits

  1. It’s free. There are no license fees for customers to pay. That way we can remain extremely good value.
  2. There’s a large body of active developers improving it all the time. That often manifests itself in security and performance improvements that bring great benefits to all customers without us lifting a finger. For example, recently parallel processing has been added that will let us speed up many parts of the system massively.
  3. There’s a great community of users and developers. When our programmers want to find out the best way to do something, there’s loads of help out there.

Some stats

  • 67 people form the current team of developers, including 5 from the core team and 39 major contributors
  • 1.5 million lines of code form the database, from a starting point of just 100,000 in 1996
  • There are approx. 6,000 mailing list (support, developer and community) messages per month

While agileBase is at the moment a relatively small startup, it’s nice to know that some larger outfits such as Instagram, Sony, the Met Office and many large enterprise, financial and government/defence organisations also see the benefits. As Amazon say,

PostgreSQL has become the preferred open source relational database for many enterprise developers and start-ups, powering leading geospatial and mobile applications



Lots of people have been UX (user experience) testing agileBase recently and given us lots of valuable ideas. Despite quite a few staff holidays recently, we’ve managed to incorporate a lot of their suggestions into the product and can release them to everyone today! 28 improvements have been made since last month’s release. Many are ‘behind the scenes’ but here are some of the visible highlights.

Notification ‘badges’

Comments and notifications are one of the most heavily used features of the platform. If you mention a particular person in a comment, that person gets a ‘direct message’ that appears at the top of their homepage – they can then easily reply to it if they want to.

With this release, not only will the message appear on the homepage, but if the user isn’t on the homepage at the time, a ‘notification badge’ with a count of messages will appear at the top right of the screen, allowing quick access to direct messages. This helps in the case where the user may not visit the homepage very often, but remains in one tile or app for long periods of time.

direct comment

Thanks to Lewis Pies for suggesting that improvement.

New & improved user interface controls

Date picker

date picker

Modern browsers include their own date pickers, so we now take advantage of that to provide a better experience. Google Chrome’s version is pictured. Dates are quicker to enter, either with the keyboard or mouse and the layout’s more compact.

Older browsers such as Internet Explorer will retain the old-style picker.

Adding items to drop-downs

Similarly, the old method of adding a new item to a dropdown list confused some testers – this UI has been improved, using a popup prompt.

Less ‘jumping’

When navigating between different parts of the screen, the system now smoothly scrolls, so the user always knows where they are.

Easier to see chaser emails

As you may know our ‘chaser’ facility can be set up to automatically email customers or suppliers when their input is needed e.g. if they need to supply a document to you. The visualisation of these messages is now easier to look at, for example repetitions of the same message are reduced.

Administrator features

On the admin side, cloning views is now a lot more reliable, working with particularly complex views as well as basic ones.

Some people had an issue using the facility to download a spreadsheet of all recent changes. That’s now fixed and should work for everyone.



We have new charts – histograms, bubbles, ranges – you name it, a veritable smorgasboard of charting functionality has arrived!

This is one of the largest feature upgrades that we’ve released in the past few years. We’re not American, and not usually given to hyperbole, but this beta* release is awesome!

*Since this is such a major feature boost, we’re releasing it in beta form, that means although it’s been internally tested, there are almost certainly bugs, so please bear that in mind, try it out as much as you can and report any issues or general requests to us.

Firstly, the chart builder now gives you a lot more control, in an intuitive fashion. A three step process lets you choose data to chart, set various options if necessary and select a chart type – showing a visual representation of what your data looks like for each type.

chart selection

There are more than a handful of features and categories of chart that just weren’t possible to make previously. Here’s a quick roundup, focussing purely on what’s new.

New charts

Note: a lot of these screenshots are from the demos but you can test out the features in your own systems to see similar results. Highcharts is the excellent library we use to create our charts.

Stacked percentages

For area, column and bar charts, you can now display data as stacked percentages, letting you see how values relate to each other relatively.

stacked percentage area

Breakdowns with multiple series

The ability to break a bar chart or area chart down is great, but previously worked with only one series. Now you can add multiple series, or calculations, and break each down.

multiple stacks

Multiple series from a breakdown

As you may know, you can add multiple series (lines, sets of bars etc.) to a chart by adding different calculations, if you want to see different things on the same chart, like say total net worth and annual turnover.

However, now you can also create multiple lines from just one calculation – simply choose a breakdown field and a ‘line’ or similar chart type. Here’s a chart where the calculation is total number of employees and the breakdown is sector.

breakdown lines

Bubble charts

Show more information than a plain scatter graph by specifying the size of each point. To create this, add one calculation for the placement of the bubble on the y-axis, then a second to be used as the relative size of the bubble.



Many’s the time I’ve wanted to look at the distribution of data and had to export to a spreadsheet, then look up help on the web and fiddle around with data analysis options.

Now, you can simply enter a ‘histogram bin width’ under the second ‘options’ step of the chart creation wizard. For example, if you’re looking at past sales and want to see how many of each you have in value ranges of £1000 (0 – 999, 1000 – 1999 etc.), just

  • set the chart grouping to ‘value’
  • add a count calculation
  • set the ‘histogram bin width’ to 1000

and if you like, choose a suitable-looking chart type like ‘packed columns’. Here’s a histogram with a bin width of 10:


Note: this is one area you may notice needs a little work if you try it out in agileBase – the placement of the numbers on the x-axis can be slightly confusing still, but we’re working on it.


Useful for showing max. and min. values on the same chart. Like bubble charts above, add two calculations to the chart – one for the min and a second for the max.

area range

Adding multiple filters

Perhaps saving the best until last, this feature applies to all chart types and is probably one of the additions that will make the most difference to everyday workflows.

Previously, you could add simple date filters on charts, to limit the data shown to say the last month or year.

Now, you can add filters on any fields to a chart permanently. That makes charting much more useful in general, as you can add multiple charts to the same view with different filters. Say you were looking at a view of all customer support requests, open and closed. You could add one chart summarising the closed tickets by category and a different one showing the open ones i.e. the current workload.

You can even use all the ‘special’ filter commands as per filtering a view, i.e. a question mark means blank values, a colon means ‘starts with’ etc.

Under ‘advanced options’ (step 2), enter your filters in the format fieldname=value,fieldname2=value2,…

What’s next?

Well, no doubt we’ll be fixing any bugs that come up in beta testing, helpfully reported by you! Further development will also be guided by customers, so let us know what you’d like to see. Here are some initial questions open to anyone, answers on a postcard please.

  1. Would you use trend lines? They’d be relatively easy to add to the system but how many people have a use for them?
  2. One of the more advanced chart types we’d like to get working is box plots but we need some concrete examples of how they might be used, so if one of those plots would be useful to you, please do drop us a line and let us know the details.
  3. Would you like to be able to add different series types on the same chart (e.g. bar and line)? What for?

We hope you’ll agree this all adds up to a massive jump in capabilities. We still don’t pretend to be a full ‘Business Intelligence’ solution though, for users with particularly advanced analytics needs we recommend integrating with a third party product such as Microsoft’s Power BI, Tableau or Amazon QuickSight.