Contract and expand take 2

Last month we introduced a way to simplify data entry screens by contracting blocks of fields under each heading on the page. The user can expand and contract the blocks to suit them and see just the information they’re interested in.


It’s been gratifying to hear that many customers love that functionality, so we’ve spent a bit more time finessing it to make it more useful for more people.

An administrator can now configure whether each block (i.e. section) should be initially expanded or contracted based on rules. So for example you could set different blocks on a form to expand based on where the record is in a process. So now, the state of a block is set by

  1. Whether the user has manually expanded or contracted a block. This always takes precedence, blocks stay expanded/contracted for all records the user opens until they log out
  2. What an administrator has set as the default state – see below
  3. If no state is set, all blocks will normally be expanded, unless there are 10 or more blocks in the table, in which case all will contract

Setting up the rules

As an agileBase administrator, it’s easy to set up rules for whether to expand or contract each block the first time a record is loaded. It uses exactly the same mechanism as for most other agileBase user interface rules, such as hiding or showing fields or tabs, locking or unlocking fields etc. Here are the steps:

  1. Create a view from the table you’re working on. Add filters to it so any records which should be initially expanded are included in the view, any which should be contracted are excluded.
    For example, in a sales enquiry, you may want to expand an ‘outcome’ section if the status is set to ‘closed’. Create a view ‘closed enquiries’ with the filter status=closed.
  2. In the fields tab of the table, find the separator field that marks the start of the block, ‘outcome’ in our example
  3. Click ‘field options’ and for the ‘expand if record in view’ option, select the view you just created

Please carry on sending feedback, we look forward to hearing from you




Rugged tablets for agileBase

As a modern cloud-based web application, the agileBase platform has always worked on all manner of devices, from laptops to tablets to phones.

However there’s no doubting that for relatively complex apps built on the platform, the experience on a computer is the best – on a phone for example, there’s more scrolling, swiping and pinching, not to mention reduced typing speed, making tasks a bit slower to complete. That’s fine when looking up info or adding a comment to a record out in the field, but you wouldn’t want to use it for everything all day.

We’ve been mulling over various options for improving the mobile experience for a long time. Recently, we were delighted to get the opportunity to try out some ideas in the real world due to a customer request from Lewis Pies. To go with the introduction of a new purchase ordering system built on agileBase, the Goods In dept. wanted to be able to book in incoming stock quickly and efficiently.

Following some brainstorming and meetings with Lewis Pies staff and partner Little House Consulting, we came up with a solution that’s gone down really well and which MD Wilf Lewis is ‘really excited about’.

The system in use in the Lewis Pies warehouse

In brief, the system replaces the standard ‘form’ interface with popup prompts designed specifically for mobile use. These new prompts mean that only one tap on the screen is necessary to kick off data entry for a number of fields, which pop up one after the other. Each prompt has large text and a specific keyboard or data entry type used on the tablet (for dates, numbers etc.). A massive amount of time is saved compared to having to scroll around, tap into fields, hide the keyboard in order to find other fields etc. Help text is shown for each data entry prompt.

How does this fit into their working day? Neil George, Company Platform Developer describes the process:

Goods in will operate by replacing the current paper system with the tablet solution:

  1. use the agenda calendar view to action each goods in record which needs to be assigned to a booking in slot or not.

  2. use the tablet interface to complete technical and vehicle checks of overall order.

  3. book in each delivery line by using field prompts of quantity, quality check, expiry date, batch code and sign off.

  4. each line has a unique ID which is used throughout the technical traceability system.

  5. sign off delivery by photographing and uploading the delivery note provided.

All delivery data is now accessible at a click of a button!

How can this be applied to other situations?

Facilities like these can be used by many other customers and applications that may require tablet devices.

As an administrator, to try it out, just tick the ‘required’ or ‘prominent’ tickboxes for any fields that you want prompted under a tab. Then, when a user clicks on a row under the tab, all those fields will be prompted for immediately. This feature appears only when a tablet or other mobile device is used, and only when a row is selected in a tab.

Ruggedised tablets

Finally, being used in a warehouse environment means a certain amount of protection is necessary for the devices themselves. Lewis Pies are grateful to Tetratab for providing devices to help when developing and testing the system.

Addendum – options for mobile apps

Here are some of the options we considered before building the above. Although this time we went for the third option below, all options are always under consideration for future developments in our roadmap and we may well revisit them for further developments in future.

Creating a native mobile ‘app’


  • Apps are typically seen as providing the best possible performance and user experience, if well designed


  • Expensive and time consuming to develop
  • The need to create and test two variants, one for Apple iOS and one for Google Android devices
  • Reduced flexibility – agileBase’s three core characteristics are ‘fast’, ‘friendly’ and ‘flexible’. Flexibility is the main killer here. In agileBase, adding a field, altering a view or tweaking business rules is as easy as pie, but those changes could break any app which relies on a fixed way of working

Building a bespoke web app for a particular purpose

In this case, that would be building a bespoke ‘goods in’ user interface, separate to the standard agileBase interface, perhaps using the API to communicate with the main system


  • Relatively quick development
  • The ability to tailor the user experience for the particular needs of the users


  • Lack of re-use. The app would have no life beyond it’s relatively narrow use case
  • The app would need maintenance and support in addition to the the main system

Adding mobile-only features and changes

In the end, this is what we went for, as described above.


  • Very quick development
  • Can potentially benefit many use cases, not just the one app under consideration


  • The initial solution may not fit all needs, further development may be needed for some other applications.

We look forward to working with other customers on mobile interfaces – please get in touch if you have any ideas you’d like to try out!


Two factor authentication – upgrade your security

A while ago we introduced two factor authentication (2FA) to the agileBase platform, to improve data security. This helps to secure your account even in the case when you password is stolen, guessed or hacked.

We used SMS text messages to send codes to you when logging in from a new device or location, as this technology is widely used and understood. However, we now have an improved way of doing this using a two-factor app.

The main advantages of using an app are

  • it’s more secure
  • you don’t have to have phone reception to use it. That makes things like logging in from abroad easier
  • it’s more reliable – the phone network can sometimes delay or block messages

How do I set this up?

Really easily. Just log in, click on your user icon at the top right of the screen and select ‘edit profile’, then tick ‘use two factor authentication. A three step wizard will take you through the process.

In brief:

  1. Download an authenticator app if you don’t already have one. Luckily the technology is all standard and interoperable. Any app which works for one service, such as agileBase, will also work for others like Google or Twitter, so you can manage 2FA for all your logins from one app.If you don’t have one yet, we recommend as they backup things for you so you’re not stuck if you lose your phone.
  2. agileBase will show a QR code (barcode) to scan. From your authenticator app, press the plus button or choose ‘add account’.
  3. That’s it, your app will now show a code. To confirm everything’s working, you’ll be asked to enter the code shown.


Why’s that again?

If unconvinced that setting up 2FA is a really good idea, here are a couple of cases to highlight:

Russia hacked the Clinton campaign in large part because John Podesta didn’t bother to turn on two-factor authentication

Bitcoin users who lacked 2FA have been hacked

Administrator notes

As an administrator, you can’t set up two factor for someone – the user has to do that themselves as they need their own personal phone or device. However, if something goes wrong, such as their phone breaks or is lost, you can disable it for them. In the settings for a user with 2FA enabled, you’ll be able to un-tick the option.

A word of warning – as the administrator, you are responsible for data security in that situation. Two factor authentication is of no use if a hacker can just contact an administrator and request it be turned off for a particular user. Please follow your internal procedures and at least use common sense, e.g. if you know the user well, speak to them  rather than rely on an email.


Contract and expand blocks

Today we launch a new feature that turns complex data entry pages into simple and easy to grasp screens.

Why do we need to do that? Simply put, the power and complexity of the applications customers (and ourselves) are building on the agileBase platform has increased so much over the years. As more features are added, screens become bigger.

As you may know, you can group a collection of fields together into a block by adding ‘separator’ fields. This new feature simply allows a user to expand or contract a whole block of fields by clicking on the heading.

Blocks which have been contracted will ‘remember’ they’re contracted throughout the whole session, until the user logs out, whichever record the user loads.

This is what a page can look like with all blocks contracted


compared with the same page with everything expanded:


With blocks contracted, it’s much easier to see the structure of the page – the person looking at it can then expand out any blocks they’re interested in.

Now as the first release of this feature, the implementation is reasonably basic – everything starts expanded as normal and it’s down to the user which blocks they’d like to contract (the system does remember their selection as long as they’re logged in). We’re sure there’s plenty of scope for making even more improvements and we’d like to hear what you have to say.

Please let us know at what ideas you have!

Agenda view

Today’s release is an example of the continuous improvement we’ve been doing in the agileBase platform over the past 10 or so years!

In this case, we’ve added a new view to the built in calendar – a weekly agenda. You’ll see the new button at the right of the calendar, next to ‘day grid’.

agenda view

It’ll show all events in the selected calendars in the current week. To switch weeks, use the arrows at the top right.

Please let us know what you think and if there are any tweaks you’d like to see.

Remember, calendar events can also be sync’d to and from an external calendar, like Google, Microsoft Office or Apple – if you’re interested in getting that set up please get in touch. There’s a small charge per user to cover the third party costs necessary.

PostgreSQL performance improvements

As we mentioned last week we’ve upgraded some of the server software the agileBase platform uses. The most significant upgrade is a move from PostgreSQL version 9.5 to version 10. That’s the database that stores all of agileBase’s data.

Now that it’s been running for more than a week, we can see whether there are any differences in performance. It’s common to see benchmarks which are designed to replicate real-world scenarios, but not so common to see actual speed changes for live production systems.

So here’s a simple before and after comparison for our own system. The method we used is to look at the top 30 queries from before the upgrade which were taking the most time in total over a 7 day period. That’s not necessarily the slowest queries, we’re measuring the total cumulative time spent running a particular query over those 7 days. So many of them will be the slowest queries but some will be faster ones which are just used very frequently. We then compared the average times with those for the same queries in a 7 day period after the upgrade.

The takeaway is that for us, SQL querying has become significantly faster, in fact the top 30 most time consuming queries are faster by an average of 35%. Of course this can’t be generalised to anyone other than us and even then the nature of a live system means multiple factors could be at play – see below!

To find these stats we used the JavaMelody monitoring package.


Please note that this analysis is only meant to inform us internally, it’s not necessarily rigorous enough to draw general conclusions from. For example, the system may be under different usage patterns from one week to the next (different users going on holiday etc.). It’s also possible the data in the system has changed – for example if someone’s done a large data import. No doubt there are many other possible differences. One advantage of an artificial benchmark is that it’s more repeatable!

Another thought – if we wanted to spend more time on it, we could also compare the top 30 most time consuming on the new with the old, which may be a different set of queries.  We’ve done some spot checks to confirm that many of the top 30 queries do overlap though.

With those things in mind, here are some general stats and the results.

Overview stats

Just to give some background, some general statistics from the first 7 day period are:

  • there were 2,769 unique SQL queries (counting the same query with different parameters as one query, i.e. counting the ‘prepared statement’ versions)
  • there were 7,052,363 calls to those queries in total
  • the average time to process was just 5ms (five thousandths of a second) for 98% of those calls
  • the slowest 1% of calls took on average 302ms each

The stats for the second 7 day period were very similar apart from one big difference -there were about 3 million calls less in total. That’s partly because some external apps were previously calling the system APIs in bursts every 5 minutes 24hrs a day (reduced a couple of days before the upgrade) and also because the need for another type of internal query was reduced due to a cache implementation. It’s possible this could have had an effect on results but I think a material change is unlikely due to the fact the server isn’t CPU-core-bound (there are plenty of unused cores most of the time), backed up by the fact the other average time stats remain the same. Furthermore, some of the query times were manually spot checked before the upgrade at times of low activity and remained similar. It’s impossible to know for certain though.


Query no. Old PG9.5 (ms) New PG10 (ms) Speedup* Query description
1 62 31 1.0 retrieve comments for a field
2 898 768 0.2 retrieve a filtered list of sales jobs
3 382 371 0.0 retrieve the newest customer leads
4 230 193 0.2 retrieve a filtered list of trade jobs
5 255 154 0.7 retrieve contact details for a lead
6 794 555 0.4 search the list of allowed products for an order
7 324 232 0.4 find duplicate orders for a customer on the same day
8 53 40 0.3 retrieve sales contacts for a company
9 223 234 0.0 retrieve the account info for a lead
10 2234 1694 0.3 total up the gluten values in all recipes
11 75 48 0.6 retrieve the list of lead activities requiring an email to be sent
12 204 202 0.0 retrieve a filtered list of active trade jobs
13 614 545 0.1 retrieve a filtered list of current sales jobs
14 605 414 0.5 retrieve a filtered list of all leads
15 56 21 1.7 search the products list
16 673 557 0.2 retrieve the list of order lines to lock
17 91 71 0.3 calculate finance costs for a job
18 127 127 0.0 calculate the message for a product sample
19 817 667 0.2 retrieve a filtered list of all jobs
20 119 120 0.0 retrieve a filtered list of all calls
21 19 15 0.3 retrieve customer account info for a sale
22 208 347 -0.4 total up the quantities in a recipe
23 203 172 0.2 create activities for expired memberships
24 202 173 0.2 create activities for 30 day renewals
25 24 20 0.2 retrieve a filtered list of accounts
26 1100 594 0.9 retrieve the newest wholesale customer orders
27 730 298 1.4 get the audit trail change history for a record
28 13789 11122 0.2 refresh the materialized view of confirmed invoices
29 320 267 0.2 retrieve a particular user’s list of filtered leads
30 1361 959 0.4 retrieve a list of invoices to auto-generate

*The speedup column is calculated as the ratio of the old over the new, minus 1. Hence a speedup of 0 means the query performs exactly the same after the upgrade. 1 means it’s twice as fast, 2 means it’s three times as fast. A negative figure means it’s slower, as you can see there are one or two which are slower, but the vast majority are faster, often significantly so.

Here the query descriptions are only vague and massively simplified explanations of a particular query. Many of these are the slower queries and are typically very complex, with lots of joins to multiple layers of views, aggregate calculations etc. A couple are simple but just called very frequently. Some may work on many millions of records, some a few thousand – there’s a good mixture!

Charting the actual average query times before and after didn’t work very well because of the massive difference of speed between some queries. A log scale might have helped but that would make before and after comparisons less readable.

Instead here’s a chart of the speedup factors (x-axis, see above for definition) for each query after the upgrade. The y-axis is just the query number from the table above.


The average query speedup is 0.35, i.e. queries are 35% faster after the upgrade. That’s a pretty significant speedup, all without a single hardware change. Thanks to the PostgreSQL project team for continuing to work hard on performance enhancements as well as many other areas – it’s a vibrant community. These improvements are certainly appreciated.

Configuration notes

You may be wondering about configuration – as we say, no hardware was changed. This machine has 64GB RAM and 16 CPU cores. The only difference in PostgreSQL configuration was the addition of three new parameters for the newer software to allow the system to take advantage of multiple cores while processing a single query, i.e.


It’s possible that future configuration changes, e.g. allowing greater parallelism could improve things even further.


This weekend we completed a second set of upgrades to our systems, which should result in faster performance able to cope with growing customer data. A couple of weeks ago our hardware was upgraded to add more processor cores, memory and space and now the software stack has had a full refresh, importantly including moving to the latest version of the PostgreSQL database. That brings with it a whole host of speedups, so we look forward with anticipation to performing some before and after comparisons.

But never mind speeding up your data processing, we’ve also produced some gorgeous new summer login screens.

Enjoy a colourful summer!

summer login