The Value of Integrating with Endole

I don’t think anyone would be surprised to hear that, at agileBase, we use our platform to build our own back office (which we call OBO).

Basically we eat our own dog food.

Yesterday however, I was really surprised by the value I got from a very small integration we did the previous Friday. So I thought I would share this “discovery” with you.

In the past we used Duedil as our source of financial data on prospects and clients to help us make better decisions during our sales lead qualification process.

Unfortunately, Duedil increased their charges 10 fold and we parted company.

We found a replacement provider in a company called Endole and on Friday got around to setting up OBO to pull data over from them via our API into OBO.

Each company record costs 70p and they seemed to have digitised around the last 8 years worth of financial records from Companies House.

So far so good, I am guessing everyone in a sales role would agree that having rich financial data about prospects and clients can never be a bad thing.

What was different for me was that, this time around, I also imported data for our five biggest competitors. Taking a look at the charts of their financial progress over the last decade, I could see two of the five had experienced major financial events in the last couple of years.

With a small amount of searching using Google I found they had both been acquired by US companies and I hadn’t heard anything at all about this.

Everyone in the company is now aware that we need to track these two competitors far more closely than we had in the past so we can see what impact these takeovers may have on ourselves.

One small inexpensive and simple integration has paid back in my eyes within days.

If you are interested in how we did this, please take a look at this documentation page which runs through the setup.

Calling freelance JS / Python programmers

We’re looking for a couple of people to help with some specific projects we’ve got on at the moment. Ideally we’d like to build further ties with developers in the South West (Bristol & surrounds, UK), especially.

NB we’re looking to deal directly with freelancers, no agencies please.

The two projects below are part of the continuous improvement and development of the www.agilebase.co.uk platform. Below are some brief descriptions.

For either of these please contact

Oliver Kohll
CTO, agileChilli
oliver@agilechilli.com / https://twitter.com/okohll / https://www.linkedin.com/in/oliverkohll/

1) Card web interface

Language / Skills – Javascript + CSS

The agileBase platform displays rows from a database in various formats, e.g. in a calendar / timeline, an image gallery, charts and most commonly, in tabular format, like a spreadsheet.

We’d like to add another display option – ‘cards’ arranged in columns, as used in Kanban boards. Cards will be able to be dragged between columns. In other words, similar to the Trello interface, or https://webtcards.com/

Our existing application should be able to call a new Javascript function, which accepts a Json parameter, which will be an array of records each representing a card. The format is to be determined, but amongst the data will be for example

  • a title (and perhaps subtitle)
  • an optional colour
  • a set of field names and values for display on the card (content can be text/images/document links/HTML)
  • additional metadata for each field e.g. field type, tooltip / help text, display option flags
  • the name of a column in which to place this card

Cards should then be rendered to a specific container element on screen.

The cards should respond to events

  • click – run a JS function supplied by us
  • various other element clicks, e.g. delete and expand icons – run similar functions
  • drag – on dragging to a different column, an API call (HTTP POST) should be made to register the move – we’ll supply the details

The application front end currently uses a few libraries e.g. https://sortablejs.github.io/Sortable/, but we use no larger frameworks apart from jQuery. Ideally we’d not add a large framework just for this purpose, so would be looking for vanilla JS, but will happily discuss all options / suggestions.

Although we’ll be able to update stylesheets ourselves afterwards, a good design sensibility would also be appreciated. The UI & UX should fit well into the existing system.

2) Apache Superset configuration

Language / Skills – Python (Flask framework) + Linux

We’ve installed https://superset.apache.org/ on a server for reporting. However as I’ve never worked with Python apps, some help with configuration would be appreciated.

Specifically, we’d like to install a production web server for it – at https://superset.apache.org/docs/installation/configuring-superset they recommend Gunicorn, but instructions are bare-bones. For other apps we use nginx and ideally we’d be able to reverse proxy from our primary nginx server. At any rate we’d like a webserver of some sort set up with Letsencrypt TLS certificates.

Mandating Two Factor Authentication

As many of you may know, we’ve been championing the use of Two Factor Authentication (2FA) in agileBase for a number of years and gradually increasing the ‘nudges’ towards this – prompting and requiring 2FA for tasks such as exporting or bulk editing data.

We also keep an eye on what are the best technologies – for example we recently removed the ability to use SMS text messages to receive 2FA codes, as that mechanism has been found to be insecure.

Today, we continue that trend by adding an option for a company administrator to require the use of 2FA for all users.

If 2FA is required, any user who logs on will be asked to set up 2FA, if they haven’t done so already. If they don’t do that, they will not be able to go on to use the application.

A couple of common questions are

  • what about people who can’t use 2FA because they can’t use a phone at their workplace?
  • what happens if someone loses or breaks their phone, that they were using for 2FA?

Firstly, phones aren’t the only devices that can be used as the ‘second factor’ to authenticate with. There are actually many desktop applications that can serve the same purpose. In fact, here at agileChilli, we use 1password.com – this password wallet also generates your 2FA codes.

Secondly, if someone does lose a device and therefore loses the ability to authenticate, it is possible for an administrator to disable 2FA for their account. If 2FA is required by the company (with this new setting), that means the next time they log in, they will be prompted to set it up again.

Note: administrators need to be careful to make sure they’ve verified the identity of anyone requesting their 2FA be disabled.

Remember, with 2FA on, people will only be prompted for their code when they log in from a new device or location.

Turning on the option to require 2FA

We do recommend that every customer considers doing this. In the administration interface, edit the company and tick ‘mandate 2FA’.

A new filtering option

An option to include blank values has been added, which can be applied to any new or existing view filters. This greatly simplifies many view filtering tasks.

Normally filters such as ‘date is up to 7 days ago’ exclude blank values, which is often not wanted as it will exclude any records where the date field has not been filled in yet. Now you can just tick ‘or is blank’ for the filter to include them. Previously accomplishing the same thing would have required creating a calculation such as

{date field} is null or {date field} > (now() – ‘7 days’::interval)

then filtering on that, which is not something that’s particularly user friendly for new learners, and is annoying even for seasoned developers.

This type of filter is commonly required when e.g. sending email notifications. You often need to send a notification for events, tasks etc. which either haven’t had notifications send in the past X days, or have never had a notification send (i.e. the ‘last notified’ field is blank).

Efficiency measures 

Finally one other update is more behind the scenes, but is worth mentioning because it could possibly affect some users.

An efficiency improvement measure has been added, so that when a record update is requested, that update won’t actually be done if the data to save is exactly the same as the data already in the database.

That wouldn’t usually happen in everyday use of course, but it can in particular situations where automated updates are done, either by a workflow or from API requests. When that’s the case, this change can reduce the workload on the server – not only due to the work necessary to update the records themselves, but also related things such as

  • adding log messages – logs can grow very large!
  • kicking off workflows
  • forcing views to refresh caches, as they think that underlying data has changed

This should have no effect on the vast majority of people, beyond some general performance improvements, however the one case it’s possible could be affected is when you set a workflow or API call to update a record specifically to update the ‘last changed’ date of that record, e.g. in order to kick off a workflow. If you do that, you’ll now need to ensure that you include data that actually does change the record, otherwise it will have no effect.

Thanks go to The Safeguarding Company for requesting this month’s 2FA update. If any customer has any request for further functionality in any area, please do get in touch too.

Learner progression and diagnostics in agileBase

Introduction

We’ve talked before about ‘learner progression’ for developers using agileBase. The idea is to help people increase their developer abilities at their own pace, up to the level they want to get to, whilst still allowing control and oversight over the direction of development by the organisation.

For example, once someone has been using agileBase for a while, they may like to start making small changes to particular views, e.g. adding in fields they need to see for their work or creating new charts. These changes will only be visible to them.

Once comfortable with making changes, some people may want to progress to develop functionality in a more substantial way, to make changes visible across the whole organisation.

There are a number of learner levels we have in mind, which we’ll discuss with customers and in public in future posts.

To make this fully a reality, there are a number of strands we’ve been working on and we’re excited to show the fruits of some of those developments today.

The three primary strands are

  1. for new users, making it easy to ‘dip a toe in the water’ of development, starting them on a potential path to further progression
  2. for developers, general usability improvements to make that path a smooth one
  3. for advanced developers (architects), adding features to allow easy diagnostics and deconstruction abilities – we’ll explain this below

We are tackling this work from both directions – focussing on both 1 and 3 and working inwards to 2 from either end.

On number 1, some great design work has been done, which will see the light of day in a future release.

Today’s new features are actually from the more advanced end of the spectrum and are about diagnostics.

What does that mean? 

Diagnostics

After any application has had a significant amount of development, the app builders will often need to go back and ‘refactor’ it – change how it works, make it more efficient, remove unused features etc. as well as adding further functionality. This applies to any software development but arguably even more so to apps built on a ‘low code / no code’ platform like agileBase, as barriers to entry are so low and it’s easy to prototype things.

Often when there’s a need to be able to look into an application and see how it works – it may be quite a while since the initial development was done, different people may now be involved, or it’s just very big and you need to remind yourself how everything fits together!

So that’s the background, let’s get on to how we’re addressing these issues.

Today’s updates

Firstly, the history/search for developers has been massively beefed up.

Previously, you could just see recently edited views and tables. This was really useful for flipping back and forth when making a few changes to related views, but finding and managing lots of views was still not as easy as it could be. You had to know the name of a view to search for it for example.

Now, there are some added tools to make larger scale projects a lot more consumable. We’ll go through each new option in turn, there are quite a few.

The view/table filter

Here’s a screenshot of the history menu, which appears when you click the history icon in the toolbar at the top (the clock icon) if you’re in build mode, i.e. editing a table or view.

The top parts are exactly the same as before – showing the search box and a graph of recently edited tables and views.

Below that is a new selector, so you can filter down the list of items when looking for something. The options are

  • tables – show tables only, exclude views
  • views – show all views
  • workflows – show only workflow views. When this is selected, a second row of options appears, to let you select the type of workflows you’d like to see. In the screenshot above, you can see that email notifications are selected
  • apis – show tables and views which have APIs enabled i.e. which allow third party software to send data into agileBase via a table, or extract data via a view
  • more – additional options appear allowing you to select views which are used for other purposes, e.g. to control field visibility or locking, referenced fields or tabs

Any search you enter will be filtered by this selection, or you can leave the search blank to see all.

So it should now be easier to find what you’re looking for if you know what a view does, even if you can’t quite remember what you called it.

Sorting

To the right of the filter selection, there’s a dropdown which lets you choose which order to show results in. The options are

  • recent first – the default, show recently edited views and tables at the top, followed by all others
  • group by tile – shows a heading for each tile, with views underneath. This is another good way of finding something if you don’t know the exact name
  • least used first – this is not an obvious one, but can be really useful when trying to work out which areas may be candidates for removal

The ‘least used first’ option requires some further explanation. When you select it, you’ll see a count of how many times each view has been accessed per day, on average.

A view count includes not just direct accesses, but also accesses of any views which depend on it. So for example, you may have a chain of joined views

monthly sales report -> daily sales by category -> all daily sales

Whenever someone opens the ‘monthly sales report’ view, that will increment the count for all three of those views. This then gives you a way of seeing which views are important to the system, even if they’re not directly opened by everyday users.

Pinning

Any view or table in the history list can be pinned to the top of the list. Just hover over it and press the pin button.

That can be useful when you’re planning to make a large change to a part of the system, involving a number of different views and tables. You can line them all up, ready to work on.

Field uses

The second of today’s major updates is related but is in another area of the system. When editing a table, clicking on a field will show the properties. In those properties, you can see the views that field is used in. You can now filter that list in a similar way to the filtering above, i.e. to show only workflow views, only API views etc.

Note only the uses the field actually has are shown as options to be ticked.

Other updates

Other recent changes to make life easier for advanced developers/architects are

  • When editing a table, you can now see any workflows which act on that table. I.e. any workflows which create or update records in the table, generate documents for a file field in it etc. These are displayed at the top right, in the table properties sidebar.
  • When editing a calculation, you can search for fields to include in the calculation and drag them into the editing space. Hovering over an available field will show what type of field it is and any relevant properties. In particular, for dropdown or tags fields, it will show a list of the available options, which you may want to use in the calculation

We hope you find all of these updates useful and stay tuned, more work in this area will become available soon.

agileBase June updates

Welcome to June’s update of recent improvements in the agileBase platform. Below are a couple of features released this week, but firstly a massive thanks to customers who’ve taken part in workshops recently to help steer the direction of the administrative and app building features of agileBase. A lot came out of those which we’re looking forward to working on. We’ll keep you in the loop.

Features for users

Comment mentions

When typing out a comment to log against a record, you can now type the @ symbol to pop up a dropdown list of potential recipients, in a way that will be familiar from your favourite social media platform. Typing or selecting a name will then send a direct message to that person.

There’s more coming soon. We also plan to allow users to specify in their profile whether they want to receive notifications by email whenever they’re mentioned in a comment.

Searchable dropdown menus

Dropdown menus now include a search box, which can be useful when there are many values, for example a list of countries of the world.

Features for developers

Custom help menus

It’s now possible to add custom menu items into the user menu (the one at the top right that appears if you click your user icon). That means if your company has specific help pages or process documentation relevant to you, you can make that info available from within agileBase.

Each custom menu has a title, description and URL, which will be opened in a new tab when the menu item’s chosen.

Custom menus can be defined using roles.

Custom branding options

Two new options allow system emails (password reset notification etc.) to come from a domain name of your choice and be branded with a particular ‘app’ name. This can be useful if you’re creating a public-facing app using agileBase, rather than applications for your own staff.

API creation of users

The API capabilities of agileBase have been extended with the introduction of an API for creating user accounts, which can be used for example to let customers purchase accounts for an aB application using an e-commerce shopping cart. Please see the documentation for further details.

Launching audITapp

Today we’re proud to announce the launch of a new application built on the agileBase platform. 

A collaboration between Techni-K and agileChilli, here is audITapp: www.myauditapp.co.uk

Techni-K are ‘the food industry’s most trusted provider of technical expertise’. That expertise, along with the powerful software features built into the agileBase platform, has led the development of audITapp – the future of internal auditing.

To take a look at the application itself and find out more, check out the video on the audITapp website: https://myauditapp.co.uk/learn-more-about-audit-app/

Change and large systems

Generally, as systems get larger, it is more work to make large changes to them.

Building apps with an agile platform such as agileBase is very quick. However over the years, as new members of staff join, they may find it hard to work out how the system has been put together, which parts see a lot of use and which less.

Over the past months we’ve been working on addressing these things and we’re in the midst of designing some exciting further developments in that area too, but today I want to talk about the more technical aspects of making changes to very large and complex systems – what happens under the hood.

To understand why this is important let’s look at an example. Many businesses create calculations of component cost, VAT or other financial values, which then feed into many places of the system. Some customers have many hundreds of views, all referencing the same base calculation view.

What happens if we want to make a major change to that calculation view, such as adding or removing a field (gross and net figures as well as VAT perhaps)? 

What you may not know is that when you do this, the system automatically checks and rebuilds every dependent view. While this is happening, users and even the API are blocked from reading or writing data. Usually everything wraps up in a second or two and no one notices the time lag but sometimes, when there are hundreds of dependent views, it can take longer, causing a queue of waiting requests to build up and eventually time out, resulting in people seeing error messages.

Incremental view rebuilding

As of today, if the system works out that it’s going to be too slow to update everything in one shot, it will switch to updating dependent views one by one, in the background, allowing people to go on working.

Now, while a view is waiting to be updated, people will see a message like this:

The system will generally rattle through rebuilding all views necessary, so even when there are many hundreds, people should have to wait no more than a minute or so. And parts of the system which aren’t affected can carry on being used as normal.

Most importantly, these background updates will take a lot less resources while running.

We know that some administrators have previously found it necessary to wait until evening time to change views which many others are dependent on, to avoid the danger of overloading the system as lots of users tried to use a system at the same time as it was being modified.

Hopefully with this update, hard working system administrators can regain some of their work-life balance!

What else has changed?

A number of other improvements have taken place recently – here are some notable ones:

Comment fields usability improvements

The user interface for adding and viewing comments has been much improved, in particular comment boxes are now more differentiated from normal editable text boxes, making it obvious which is which.

Secondly, we’ve introduced ‘comment tags’. When a comment is imported from an email someone sends the system (e.g. to attach an email to a contact sales enquiry automatically by forwarding it or BCC’ing the system), it will get tagged with the sender/recipient of the email. Chaser responses will also get tagged and in future, we may use tags for other things, or let people add their own.

Dates in spreadsheet exports

When a view is exported to a spreadsheet, any date fields will now become proper dates in Excel, rather than text representations of dates, so you can quickly sort and filter them without having to do extra work to transform the cell type.

Record merging improvements

The record merging feature has seen a lot of use and has had some usability improvements and bug fixes. In particular, when a comment is produced due to one unique value overwriting another, that comment won’t be included in the comment stream on the homepage. These comments were polluting the comment stream when heavily used.

Inline editing improvements

Relation fields can now be edited when a view is in inline editing mode. Some customers are finding this indispensable for speeding up particular data entry tasks.

Major database performance improvements for agileBase

I always enjoy the chance to do some performance optimisation work on agileBase – especially when you figure out how to improve the performance and responsiveness of the system in a way that customers will definitely notice.

Now is a good time for us to be looking at this. Over the past year, more customers have started doing more complex things with greater amounts of data. Here’s how our database workload has increased over that time:

So here are a couple of updates that will be released next week:

1) Auto-updating cached views used for calculations

View caching is a feature first introduced in 2015. It can improve the responsiveness substantially – views which previously would have taken many seconds to search now return data in milliseconds.

A ‘primary’ view, that is one which a user looks at directly such as a sales report for example, is often a good candidate for caching. It can be set to refresh the cache whenever data changes so the user always sees up to date information.

However until now,  ‘underpinning’ views which a user doesn’t directly query, but which are used by those primary views (e.g. in joins) haven’t benefited as much from caching. Until this update, they wouldn’t automatically refresh when data changes, only updating once a day, meaning data that users see could be out of date – that’s not always an issue but sometimes can be.

An example from an actual customer is a complex and relatively slow VAT calculation (when run over many records), defined in a view ‘vat calculation’, which is then used in dozens of other views.

With this new feature, that underpinning VAT calculation view can be cached and set to update whenever data it depends on changes. Those multiple other views that reference VAT now have massively improved performance at a stroke!

This also works wherever a view is used in a chain. So if View 1 references View 2 which itself references View 3, View 3 can be cached and will update on demand whenever a user looks at View 1.

This one change should have a positive impact for many customers.

Once again, we couldn’t do this on our own, this was a real team effort. We have to thank two customers, Beacon Foods and Britannia Windows along with our partner Richard Wilson of Little House Consultancy for the time they spent in conversation with us as we progressed this idea.

2) Query Optimisation

Our second update is more niche. The feature will be enabled automatically, so administrators don’t need to configure anything. Technically it’s to do with re-writing the WHERE clause in the SQL that agileBase generates to take advantage of primary keys in certain situations – please contact us if you’d like to know the full details.

This change will only affect a few views, but one of those which will be improved significantly is currently taking about 20% of total query processing time over the whole system!

Our testing shows that each call to this view (and there are tens of thousands per day), which currently takes about 0.7 seconds, will reduce to a few microseconds, hence freeing a lot of server capacity for all users to benefit from.

Thanks must go to Oliver Humpage of Entuplet for being a part of the conversation which initiated this.

We always want to keep the system running like a well oiled machine for whatever scale of use customers need, so our work doesn’t stop there. Next on the list is looking at, amongst other things, scaling across multiple servers. We will keep you posted.

Retiring SMS-based two factor authentication

Keeping customers’ data secure has always been a primary concern here at agileBase. Following best practices such as automatically checking passwords against known leaks and enabling two factor authentication is a big part of that.

Since secure app-based two-factor authentication was released on the agileBase platform in 2018 we’ve been heavily promoting its use and gradually deprecating the older, text-message based authentication codes.

For example, for a number of months, app based authentication has been the only two factor authentication mechanism available for new users.

Information has come to light in the last couple of days that the older SMS based authentication mechanism is fatally flawed from a security perspective. Although it’s been known for a while that SMS codes have certain issues, using them has been seen as better than nothing. Exploiting the issues has required a certain amount of work and expertise. However recently, there have been cases where a newly discovered exploitation technique allows customers’ authentication codes to be easily stolen without them even noticing anything’s amiss.

Given this new vulnerability example, we feel that the time is right to retire SMS codes entirely, as they increasingly provide more of a sense of false security than actual security.

As of the next release, agileBase will stop sending authentication codes by text message. Anyone using this mechanism will revert to having no 2FA, so they will be prompted to set up app-based two factor authentication after logging in.

Due to past initiatives this change will only affect the 12 percent of people using the old SMS mechanism.

User interface makeover

From Monday, when you log in to the system, you may notice some changes in the user interface. It’s had a bit of a makeover to make it easier to use, more modern and make navigating  around much quicker.

Here’s a quick rundown of the main updates, with a couple of annotated screenshots.

Here’s a tile which has been opened – ‘Ingredients’.

Firstly, there’s a toolbar at the top of the screen, which remains visible wherever you are in the system. It will stay there as you scroll up and down through data, so will always be easily accessible.

At the top left, are three navigation options

  • The agileChilli logo will always take you to the homepage, wherever you are
  • The back arrow to the right of it, will take you back one step to the previous screen you were in (finally taking you back to the homepage)
  • The history icon (the clock) next to that is a quick way of navigating anywhere else in the system without needing to go back to the homepage. People testing the system have loved this, it’s shown in more detail below

The menus to the right are specific to the tile that’s open. The system can cope with any number. On a typical laptop, there’s space for eight to ten (larger screens may see more) to be seen initially. If there are more than that, the toolbar will automatically expand to show them when you hover over the area.

Near the top right is an actions panel, with actions relevant to the view or record on screen. In a view, the buttons allow you to

  • show and add charts
  • export to spreadsheet, or share a link to the view with other users (this is a new feature)
  • add a new record

The filtering button shows or hides the filter boxes. The current state will stay when you switch between tiles, so you won’t have to keep clicking it if you do a lot of filtering. Hiding the filter boxes is also a quick way of clearing all filters applied to the view.

In general, there’s more space given over to content – so you can see more rows and columns on screen at once.

Finally, Windows users will find a welcome change – you can now scroll quickly right and left using scrollbars, rather than using the old buttons.

History

Here’s a look at the history feature mentioned earlier.

When you click the history icon, a large, searchable menu appears.

At the top is a list of the recently opened records, which you can navigate to quickly.

Below that are the recently opened views. Again, you can click on them to jump directly back to them.

The search box lets you look at not just recently opened views and records, but any tile or view in the system. For example, if you want to look at an allergens report for your ingredients, you can now type ‘allergens’ into the search, rather than navigating to the homepage, opening the tile which contains the view (which you may not be sure of) and then selecting the right view.

That’s a quick run-down – everything should be reasonably intuitive but it is different to before, so we thought you’d appreciate this heads up.

There are plenty more improvements coming down the line following this visual refresh, we have some exciting things in the roadmap which should really shake things up, particularly making building apps easier. We look forward to launching those.

In the meantime, please let us know what you think when you use your system the next time. Feedback is really useful and we will always try to react quickly to any good suggestions.