Business Intelligence reporting

One thing that customers have started asking for recently is the ability to create dashboards, to bring charts from various parts of the system together in one place, to share with colleagues and senior management.

Sometimes data from agileBase will be merged or shown alongside data from other places, such as accounting systems.

To allow this, we’ve added a new facility which allows Business Intelligence (BI) reporting applications to connect to agileBase and extract selected data. Bear in mind that when data is exported outside of agileBase, data privacy implications need to be understood.

If your organisation already uses a BI tool such as Microsoft’s Power BI, it can easily be connected. Just let us know and we’ll supply the details you need.

Alternatively, you can utilise the BI tool we provide and host, based on Apache Superset. This is currently in beta testing – if you’d like to join the beta programme please let us know.

Whichever tool you use, you can send the data from any agileBase view to it, by selecting an option in the view’s charting screen. Bear in mind that if you then change a field name, remove a field, or rename the view used in the BI tool, you may then need to re-do any BI charts based on that view.

For more information, please see our charting documentation, which contains an introduction to this topic.

BI reporting can be enabled for a fixed charge of £50/month, covering up to 10 views and an unlimited number (subject to fair use) of BI users (who don’t need to be agileBase users).

agileBase’s new developer interface

This feature for agileBase administrators and developers allows massive improvements in usability and capabilities. In the long run it will also allow us to tailor the system to suit developers of different levels, doing different types of jobs.

So a beginner (called a ‘superuser’) will be able to see a simple, user friendly array of tools to let them customise the experience for themselves, whereas certified architects will be able to use much more functionality. These advanced users will be able to see much more about how the system is put together, seeing how different parts of a system are connected, with relations, workflows etc. The processes of constructing, updating and deconstructing large scale applications can be visualised, planned and executed more easily.

The first step being released in January is the framework for all of the above, introducing some really useful ways of doing things.

As a developer, you may be used to using the ‘developer mode’ toggle at the top right of the screen. That stays the same and in fact all the features it enables stay the same, but the UI is a bit better.

There are three places the developer toggle can be used

  1. when looking at a view, to edit that view
  2. when looking at a record, to edit the table that record belongs to
  3. on the homepage, to see structure and lists of objects, and edit anything

Editing a view

When a view is open, toggling developer mode on will initially simply show pencil icons next to each field’s column header. Clicking on a pencil will edit that field, allowing you to sort the view by that field, edit the field properties, set a filter on that field [coming soon] or remove it from the view.

There is also a plus button at the end of the columns, letting you add a new field or calculation to the view.

When a pencil is clicked, a panel opens allowing details to be edited. The panel can be moved around on the screen and the data remains visible underneath. Any change immediately updates the view underneath – the results can be seen in real time as you work.

Editing a field

As well as the fields, the other view constituents like joins and filters can be edited by clicking the large pencil icon at the top left, next to the view name:

Editing a view

As you can see, you can also see and edit the complete set of view options – API settings, workflow options etc.

Editing a table

Similarly to view editing, when a record is open, toggling the developer mode on will show pencils next to every item that can be edited. For a table, this is each field, tab and the table properties/options (with the large pencil at the top left).

Editing a table

On the homepage

When on the homepage, toggling the developer mode on will by default show a map of all the tables in the system, with connections between them where one table references another (such as contacts belonging to companies).

You can select one or more types of objects, i.e. tables, views, tiles, roles and users to show the connections between them.

The display will change according to what the most useful output is. For example, if you tick just views, all the views will be displayed with connections representing the joins between views. However if you tick both tables and views, the connections will then link each view to its parent table. You can select multiple options if you wish, e.g. tables, roles and users will show the roles which have privileges on each table, then the users which belong to each role.

Using these visualisations is particularly useful if you’re coming to a system that is new to you – you can build up a picture of how it works and what the main parts are (objects are sized depending on how many connections they have to other objects).

Here’s a map of the connections between tables in a CRM system.

Clicking an object will show it to the left of the map. Objects can be kept in place with the pin icon, so you can build up a list of objects to work on, mixing tables, views, tiles etc. Each can be edited with the relevant button, e.g. EDIT TABLE.

You may also want to see the objects as a list – to do that, use the ‘list’ selector at the top.

Here’s a list of some views from a sample application, including a chain of workflows:

Scrolling right, you can also see and filter on other properties, such as whether the view is used for a workflow or API.

By the way, pinning views can be done from anywhere in the system, not just from the homepage. Whenever you see a view when developer mode is on, you’ll be able to pin it, then when you return to the homepage it will be visible on the left.

A new drag and drop cards interface

The first in our list of three major features being released is the drag and drop ‘cards’ interface – also described as T-cards, or for use as Kanban boards. In essence you can set any view to use the cards interface and it will display each record as a card, within columns. The columns are generated from the list of options in a dropdown field of your choice from the table.

So for example, you could have a list of sales enquiries, each column representing a different stage in the sales process. Dragging a card will move a sales enquiry from one stage to another. Or you could use it for issue tracking or todo lists. Each card would then be an issue/todo, moving between states like ‘todo, doing, done, tested’ for example.

This is something you can do outside of agileBase with a dedicated third party tool, but the benefit of having the capability inside aB is that the data is (or can be created as) your core business data inside the system. There’s no need for complex integration and you can configure the system to suit you quickly, as with any other development in agileBase. Other platform functionality, such as the ability to run a workflow when a card moves columns (e.g. to inform someone or chase for documents), works out of the box.

This is a paid feature, as it opens up a whole new way of working, similar to various third party products, but keeping the data centralised and shared. The cost is a simple £10/month per user for access to any cards view. As many card views can be created as necessary for the business, by an agileBase developer. Any user who hasn’t been set up with access will still see the data in those views, but will fall back to seeing them in the standard rows and columns.

What’s coming in January 2022?

Things have been ramping up towards the end of the year here at agileChilli HQ. Alongside a lot of marketing and product development, there have been some major improvements and new features for the agileBase platform.

On that front, there are three major things we can announce, which will be going live in January 2022.

  1. Drag and drop cards interface
  2. New developer interface
  3. BI dashboard facilities

Each of these is so substantial that it requires its own post – click the links above [check back here in a mo] to go through to full details of each.

Alongside these major additions, a number of smaller tweaks and changes have been made:

  • When a field is marked ‘mandatory’, people will still get the option of leaving the record without filling it in (after being prompted) – the vast majority of feedback is that most times a value is left out, it’s because it’s impossible to fill it in at that point in time
  • RSS functionality has been removed – the facility to generate public RSS feeds is not used by anyone any more. Removing it makes the system more maintainable and reduces the potential attack surface, i.e. increases platform security.
  • The codebase has been updated to take advantage of new Java 16 features – the use of this new Java version also results in a higher performance and more secure system.
  • More robust views – views which were particularly slow could sometimes break due to timeouts during processing. This won’t happen any more – of course if a view is particularly slow, you should still address that using performance optimisation measures

Finally, one change which is not in this release but will be coming soon afterwards is that simultaneous logins will be disallowed. When someone logs in with a particular username, anyone already logged in with that username elsewhere will be automatically logged out.

Exceptions will be allowed for

  1. administrators, who often need multiple sessions, sometimes on different machines or browsers
  2. separate sessions in the same browser on the same machine – it’s often useful to keep two tabs open using a tool like SessionBox or in private browsing tabs for example.

Remember that to allow access from lots of people from outside your organisation cost effectively, you can use the new Community Users mechanism

The Log4j Vulnerability

I’m not sure how much this has permeated outside of the tech world, but at the end of last week there was a major security ‘zero-day’ vulnerability in a software library used by lots of enterprise software, potentially including agileBase.

[Update: it is now in the national news]

This post is to inform customers, who may be concerned and want to know what we’ve done about it.

A little background – the issue is with a Java library called log4j, used for writing messages to log files. If an attacker can force certain messages to be logged, they can cause the server to execute any code they like. For those interested in a more technical explanation, here’s some info: https://www.lunasec.io/docs/blog/log4j-zero-day/

As soon as we became aware of this, on Friday morning, we researched mitigation techniques and put in place the recommended measure at the time, disabling the problematic log4j option on our primary server.

By the afternoon, it was clear what the most robust mitigation measure was, so we implemented this across all servers (primary, development, test etc.) as well as reaching out to customers who host agileBase on their own infrastructure. Where we had access to these, we proactively patched those servers too.

After taking these immediate actions, we also looked into what our level of risk was. Whilst most of agileBase’s server-side code is written in Java, our code doesn’t directly call the log4j library, so many potential avenues of attack would be closed.

However, we do use many third party elements in our stack, any of which could use the library and indeed we did find a vulnerable version of log4j present in the list of dependencies, so at least one of those elements does potentially use log4j, opening up possible lines of attack (now closed due to the above mitigations).

Searching our logs shows no suspicious activity and the protections put in place ensure that no hack will take place due to this in future. Additionally, www.greynoise.io hosts a searchable list of IP addresses affected by this issue and we can confirm that none of our servers’ addresses show up in it.

Additional learning

With any security incident, there’s the opportunity to look at our existing tools and processes to see where they may be improved.

All of the third party dependencies we use are already automatically updated, when new minor versions are released, so we expect a new version of log4j to roll through soon.

However we have now also implemented vulnerability scanning of every agileBase development update using tools from Anchore, which will create a software bill of materials (SBOM) for serverside code and carry out vulnerability scanning, to catch any other software supply chain attacks like this.

This is also a good opportunity to remind customers to enable two factor authentication on their accounts. Since most agileBase functionality is behind a login, the easiest way for an attacker to take advantage of any new vulnerabilities is to appropriate someone’s username and password. Two factor authentication helps protect against that.

If anyone has any further questions, please do contact support@agilechilli.com

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.