One of the recent charting improvements is the ability to add filters to a chart, however it’s not a very well known feature, so here’s a little guide on how to use it.

In a view, you can add ‘ad-hoc’ filters using the filter inputs at the top of the screen. In this screenshot for example, we’re searching for all organisations in the food and drink sector, who are distributors and customers.

view_screenshot

If you switch to see charts based on that view, they’ll have the same filters applied. However, what’s new is that you can also ‘hard-code’ whatever filters you like into charts when editing them. This particularly becomes useful when adding multiple charts to a view.

So working on the example of the above view, you could add two charts, one a chart of the number of ‘food and drink’ customers added over time, another of ‘education’.

Adding a filter to a chart

When editing a chart, click ‘advanced settings’ and type in a filter into the ‘additional filters’ box, in the format fieldname=value.

simple_filter

The value can be anything that you’d type into a filter box at the top of the screen, when narrowing down records. It can contain advanced filter commands, so e.g. a colon means ‘starts with’, an exclamation mark means ‘not’. Just remember that the equals sign separating the field from the filter value is not part of the filter value. So you can use the following formats:

  • fieldname=value : field contains ‘value’
  • fieldname=:value : field starts with ‘value’
  • fieldname=!value : field does not contain ‘value’ (fieldname=not value can also be used)
  • fieldname=? : field is empty, no value has been set
  • fieldname=!? : field has a value (it’s not empty)
  • fieldname==value : field is exactly equal to value (case insensitively)
  • fieldname=>value : field is greater than or equal to value (useful to numbers and dates, but also applies to text)
  • fieldname=<value : field is less than value
  • fieldname=value1 OR value2 OR value3 : field contains any of the specified values
  • fieldname=value1 AND value2 AND value3 : field contains all of the specified values, in any order

Multiple filters

Multiple filters can be added by separating them with commas. For example, the filter

  • sector serviced=food and drink, industry=distributor, date added=>12 months ago

would apply all three of those filters to the chart.

Ad-hoc filtering

What happens when you type in searches into the filter boxes at the top of the screen, when looking at a chart?

The filters get applied in addition to any filters saved into the chart as above. If you filter on a field which is already included in a chart, the filter that you type will temporarily override the filter saved to the chart.

Advertisements

You might have heard about our recent calendar sync setup – the ability to two-way synchronize calendars in agileBase with your cloud calendar provider such as Google or Office 365.

We’ve now leveraged this technology to use comments in the system as reminders to yourself. If you see a message in your comment stream that you want to remind yourself about later, just click it and hit one of the buttons.

Screen Shot 2017-09-11 at 11.10.22

The system will immediately add a 10 minute entry into your external calendar, for either

  • the same time tomorrow
  • the same time in seven days time
  • the same time in 30 days time

An alarm will be set on the event so it will remind you at the time. (We may in future add the option to specify a custom date).

Note that for this to work, the logged in user has to have a calendar sync set up – to do that, click your user icon at the top right of the screen, select ‘edit profile’ and choose ‘connect calendar’.

We use a third party utility to enable the calendar sync and there is a small charge per user associated with it. Please contact us to turn the system on for you.

 

 

For a few customers, we’ve recently been manually creating reports containing charts and data, that are automatically emailed to managers regularly (once a month or week). This has proved so successful that we’re now introducing a feature to let users create their own reports by choosing charts and data.

report

It’s really simple to set up –

  • view charts as normal (open a tile, select a view and click the charts button)
  • select a period – monthly, weekly, or daily

agileBase will then gather together all the charts you’ve chosen to receive monthly and send them to you in a PDF document once a month on the first day of the month, similarly for weeklies and dailies.

Including data

Sometimes, you may want to see actual data instead of (or as well as) charts. To do that, select an option next to the ‘period’ chooser. There are a couple of options to email the top 5 or top 20 rows of data in the view, but if the view includes date fields, the system will also offer to send new records since the last report.

So for example if you wish to see a list of all new customers added to the system every week, find or create a view containing the ‘creation time’ of customer records and the system will offer the following options:

weekly

If there are charts in the view, they’ll be included too, if not, just the data will be sent for that view.

Lucky Dip

Many people may not even be aware of what charts exist in the system. Once a month, a random chart will be chosen for each user (from the data that they have access to) and sent to them, whether or not they’re subscribing to any. You never know, that may serendipitously be just what they’re looking for. This will also help to introduce the feature to the many users who may not be reading this blog.

Note: users with data filters are currently excluded, though this may be changed in future.

Other charting improvements

Choosing charts is one thing, but creating them is another. We’ve also been continuously improving this area, increasing the power of the system whilst also improving user friendliness. Some recent updates include:

  • the chart creation/editing screen is now separate from chart viewing, cutting down on clutter
  • there’s a new option in the menu for saved charts to ‘expand’ it. This will show it full screen – many times, users wanted to concentrate on one chart when presenting or interrogating data

As test users have said “Charts creation looks so much better.”

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 www.openstreetmap.org

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.

chart_edit

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.

chart_height

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.

workflow

 

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.

9659165098_cea5b9b59b_k