Regular expression checks

We’ve just added a useful new feature – regular expressions.

In other words, rather than just using simple rules to limit the ability for people to enter duff data, such as ’email addresses must contain an @ sign’, an administrator can now in addition add arbitrarily complex checks, as long as they know how to write regular expressions.

They can also be used to create advanced filters in views.

This might be something that people who’ve done a little programming might be excited by, but it perhaps others may also like to expand their skillsets with a bit of learning.

regular_expressions
xkcd.com

For full documentation, please see

https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

To set up a data check in agileBase, go to a table in the admin interface, visit the ‘manage’ tab and click ‘checks’. Then from the dropdowns at the bottom of the screen, select a field and either ‘matches regular expression’ or ‘does not match regular expression’. Fill in the expression you’d like to check against to the right, e.g. [a-z] to match any data containing a letter of the alphabet.

regex checks

regex_golf_2x

Advertisements

Integration how-to: Power BI

A few customers have been interested in powering up their analytics with tools such as Tableau, Qlik or Power BI.

agileBase has built in charting which is great for quickly analysing and answering questions about data in your system, but specialist business intelligence apps can be particularly useful if you want to join data from multiple sources (perhaps an accounting package or marketing automation system too) in one place.

Thanks to one customer, Lewis Pies, we now have a great how-to guide on integrating with Power BI using our easy to use API. They’ve done all the work of investigating the Power BI side of things! It’s a great example of how to use the API in general to integrate with third party software.

powerbi

Setting up

Without further ado, here’s how an admin can set up the integration. It looks like lots of steps but that’s just because we’ve split it up into very small, simple parts to make it as easy as possible to follow.

Step 1: set up a view in agileBase to use as a data source

Tip: use a view bringing in as many possible variables into one place. For example, if you want to analyse sales, you might include totals by customer, product, salesperson, region, category, price banding and date. That way you have plenty of dimensions to analyse in multiple ways later on.

Step 2: turn on the API for the view to make data available

1 ab

Under the admin interface of AB select the manage option on the view and then the ‘send’ option.

Select ‘send data to a third party system using the API’ in the dropdown list and make sure you tick ‘allow access from third party software using an API key’.

Refresh the view to show your API key needed for the next step.

1 ab

Step 3: within Power BI press ‘get data’ and select ‘Web’ as the data source

2 pbi

Step 4: enter the sync details provided by AB into Power BI

Select the advanced option and fill in the following URL parts provided from the AB view setup in the first point. These are the parameters to add:

?get_report_json=true
&c=[your ‘c’ value here from AB]
&t=[your ‘t’ value here from AB]
&r=[your ‘r’ value here from AB]
&json_format=json
&simple_format=true
&row_limit=250000 (or however many rows you need)

3 pb

Step 5: enter the authorisation key into Power BI

Select ‘authorization’ from the dropdown options under ‘HTTP request hear parameters (optional)’ and press OK. The authorisation key is provided in the Agilebase view.

4 pb api key

Step 6: make data visible in Power BI

Once the data has pulled through and the connection is set up you will need to convert the dataset into a table.

5 pbi convert to table

Step 7: expand the colums

Once converted to a table you will then need to expand the data into columns and your view will be presented to manipulate.

6 pbi expand

Step 8: go wild with visualisations!

powerbi

 

 

 

 

 

New chart view setting

chart default

Sometimes, views are created purely as sources of data for charts – where the chart is the interesting thing and what you want to see first.

There’s now an option you can tick to show the charts for a view by default whenever you open that view.

In the view’s manage tab, just tick ‘Show charts by default’.

p.s. we’ll be at the Food & Drink Summit in Coventry on the 7th, launching our new Supplier Approval product!

 

See us in Coventry

We’re excited to tell you that not only will we be exhibiting at the Food & Drink Business Europe ‘IT Summit’, a week next Tuesday on the 7th November, but our Director of Food, Mark Garrett, is a guest speaker.

http://www.itfoodsummit.com/speaker/mark-garrett-product-director-food-agilechilli/

He’ll be talking about some of the heart-pounding experiences of his past life managing a food manufacturing company whilst being involved in a food-safety product recall across the supply chain. The insights from that prompted Mark to start developing software specifically tailored to food manufacturers.

Please come and say hello, or let your friends and colleagues know. If you can’t make it yourself, we’ll be posting proceedings on twitter, so please do follow us @agilefood.

agilechillilogobig

 

API update

A quick update for developers using agileBase to integrate with other web based software using the API:

You can now see internal object IDs in the agileBase admin interface just by hovering over any field names, view names or table names.

To see field or calculation IDs, hover over over a field in the ‘fields’ tab of a table or view. To see table IDs, hover over the table name in the manage tab and similarly to see view IDs, hover over the view name in the view’s manage tab.

You’ll often need these values when integrating with third party systems, either to post data into agileBase or to retrieve data in a JSON feed – see the documentation linked above.

Thanks to our friends at Logic Software for suggesting that handy feature.

By the way, we’ll soon by publishing a example tutorial on how to use the API to connect to Power BI, so keep your eyes peeled for that if you’re interested in mastering advanced data analysis and visualisation.

powerbi

Finally, on the 7th November, we’ll be exhibiting and presenting at the Food & Drink Business Europe ‘IT Summit’, at the Ricoh Arena, Coventry. Our director Mark Garrett will be speaking about his experiences that drove him to get involved with IT for food manufacturers. If you’re in that industry, please come along and say hello!

More info on that to come soon…

 

Traffic lights

agileBase has long had the ability to colour unusual values, for numbers and dates. This is based on standard deviation from the mean.

These colours have recently been tweaked to better follow the agileBase colour palette, but along with that, we’ve also added the ability to colour text fields, based on the words in them.

To turn it on for a text field, edit the field options in the admin interface and tick ‘colour words’.

The words can produce green, orange, red or blue, as per this screenshot of ‘taste test’ results:

word colours

The complete list of words that display green is, at time of writing:

“green”,”good”, “robust”, “high”, “yes”, “complete”, “completed”, “finished”, “done”, “success”, “successful”, “excellent”, “high value”, “live”, “active”, “new”, “won”, “accepted”, “confirmed”, “invoice”, “approved”, “launched”, “low risk”

Orange: “amber”, “warm”, “orange”

Red: “red”,”low”, “no”, “poor”, “fragile”, “bad”, “dead”, “on hold”, “inactive”, “lost”, “rejected”, “denied”, “credit note”, “not low risk”, “high risk”, “security”,”hot”

Blue: “cold”, “cool”, “blue”

Of course, this is at the moment quite a rudimentary method of colouring cells and may evolve, but it’s nonetheless a nice quick way to produce a ‘traffic lights’ style report.

 

 

Tile colours

When each user adds a tile, the system picks a colour for it based on what’s already on the screen. It tries to create a variety of colours with little repetition. The colours themselves are from a specially designed palette that creates a friendly, interesting look that we try to differentiate from the boring blue and white/grey of typical business software applications.

tile colours

However, this has meant that different users might get different colours for the same tile.

We’ve now tweaked this so that administrators can globally set the colour of a tile across the whole organisation. That should make training and support easier – support staff can now say “open sales analysis – that’s the purple one” and any printed material can match.

It also means you can customise your own company designs – maybe make a two-tone checkerboard effect, or colour tiles by dept. so e.g. all finance is blue and all sales green.

To set this up, go in to the admin interface, expand out ‘Administration’ and click tiles.

colours admin

p.s. you may notice some of the colour names might not exactly match the actual colours – don’t worry about that, it’s the colours you see that matter, the names may be tweaked in future.