Help Centre

Choose a category to find the help you need

Views - agileChilli

A view shows data from tables. It can include data from the main ‘parent’ table and optionally any other related table or report. You can choose which fields to display, add filters and calculations.

Views can be grouped together into modules (the user sees them as tiles). For example, reports such as opportunities, contacts and organisations can be put together to form a Customer Relationship Management module.

Note: to be able to see a view, a user must have VIEW privileges on the parent table plus any other tables from which data is taken.

 

View builder quick start

To create a view, you must have MANAGE privileges on a table.

Firstly, ensure you’re in the admin interface. If you’re in the tiles view, click the logo at the top left to start administration.

Click ‘build’ then the table that has the data you’re interested in.

Note, you may need to use the toolbar buttons view_buttons to switch to editing view, so you see the view/edit/visualisations/fields/manage tabs at the bottom of the screen. 

 

Manage tab

 

Select the manage tab for a table then press the the ‘create a new view’ button.

Give the view a name and description. Choose a module to put it in.

The view will be created to contain all fields from the table initially, but you may not want all of them, especially if there are a lot. To prune them down, click the ‘view fields’ tab and press the delete button next to each field you want to get rid of.

To add fields back in, or add more fields

  • Click the view fields tab.
  • Choose a field from the middle drop down list, and optionally a sort direction. Click ‘add field’.

Fields can be added from the table the report is based on, or any directly related table. To add in fields from somewhere else, see Advanced below.

Note: There are a few special fields you can add that are automatically available in addition to the table fields

  • Date Created: the date that a record was created
  • Last Modified: the date that a record was last modified
  • Created By and Modified By: name and username of the user who modified the record
  • Record locked: whether the record can be edited or not (see record locking)
  • Comment Stream: Shows the most recent comments against any field in the record

To add a filter, click the Filters button and choose options from the bottom of the screen

 

Advanced

To add a calculation, see Calculations

To draw in data from a table other than the report’s parent table or one directly related, you must first add a join to it: see Relations and Joins

Relations and joins

Understanding relations

Every table in agileBase has a primary key field as its first field. This is an integer field that automatically increments for each new record, (starting with 1 for the first record). The value of a primary key field uniquely identifies a record within a table. By default, this value is not displayed but can be seen to exist by looking at a table’s ‘fields’ tab.

 

Fields

 

Using primary keys, we can relate the data in one table to that stored in another by means of a ‘Relation’ field. The ‘Relation’ field creates what is known as a ‘foreign key’ by storing the primary key value of the record from the related table.

 

Relation

 

As an example, employees can be related to their employers by storing the employer primary key value within a Relation field in the employee record. As can be seen in the table below, the employee Sergey Brin is related to the employer Google by storing id 2 in the Employer relation field.

 

3 related data

 

Joins

 

When you create a view in agileBase you immediately have access to all the fields of the parent table. However, when you need to bring together data from various tables or views, (for example, you may want a view that lists details of employers and their employees,) you first need to ‘join’ to the related tables/reports.

To add a join to a view select the ‘Joins’ tab of Pane 3 whilst viewing a report.

agileBase will automatically sense what joins may be useful and present them as links. For example, if you are editing a report of employees, portalBase may offer to join to the employers table. Clicking a link will immediately create the relevant join, so you don’t need to get involved in any complex details. It chooses the right fields and the most relevant type of join. This is a really quick way to build up reports when prototyping an application and is useful even for advanced users.

In some cases, though, administrators with a knowledge of SQL principles may want to customise their joins, for example joining on a calculation or creating a full outer join. If you need this capability, click ‘add complex join’ and read on…

 

Joins

 

You can only join from tables/reports already included within the report, (this includes the parent table and any previously joined tables or reports). As can be seen in the image above, the relation field will be listed by table name and display name, (in our example this is ‘Organisations: organisation name’).

The ‘Join Type’ column allows you to specify how records should be returned when there is unrelated data in either table. The options are:

#inner: Only returns records where the join fields have matching values in both tables/reports.
#left outer: Returns all records from the left table. Records from the right table will only be returned if they match with left table records on the join fields. Where a record from each table matches on the join fields, they will be returned as one row.
#right outer: Returns all records from the right table. Records from the left table will only be returned if they match with right table records on the join fields. Where a record from each table matches on the join fields, they will be returned as one row. This is obviously the reverse of a left outer join.
#full outer: Returns all records from both tables. Where a record from each table matches on the join fields, they will be returned as one row.
#none: This join type is equivalent to a ‘cross outer’ or ‘cartesian product’ in SQL. It is sometimes but rarely useful.

The default join type is ‘left outer’ as this is appropriate in most cases and ensures data isn’t made ‘invisible’ to users. For example, usually in a CRM you’ll want to see all organisations even if some of them don’t have contacts.

Calculations

Calculations can be added to any view in agileBase.

Note: Before adding a calculation, it’s best if the view contains some data. agileBase is better able to check for calculation errors if there’s visible data.

 

Adding a calculation

  1. Load the view you want to add the calculation to
  2. Select the ‘report fields’ tab at the bottom of pane 3 then the calculations button on the left
  3. Fill in the details required and press Create Calculation

For the calculation definition, text is entered in SQL format, the only addition being that fields are referenced in agileBase format, i.e. surrounded by curly braces

 

{field name}

or if you need to reference a field in a particular table or report that’s joined on to the current one

 

{report name.field name}

 

or

 

{table name.field name}

 

An example calculation definition is shown in the screenshot.

 

Calculations

 

All SQL calculation operations can be entered, for example addition, subtraction, multiplication and division are performed with + – / and *.

The full list of functions available can be seen at http://www.postgresql.org/docs/current/interactive/functions.html

Some example calculations:

 

Calculate VAT {item cost}*0.175
Work out a person’s age age({contacts.date of birth})
Show a person’s age in years only extract(year from age({contacts.date of birth})
Calculate the estimated completion date of a job, given a estimate number field that is the number of months to complete ((‘1 month’:: interval * {jobs.estimate}) + {jobs.start date})
or if feeling pessimistic ((‘3 months’:: interval * {jobs.estimate}) + {jobs.start date})
Generate a random dice throw ceiling(random() * 6)

 

Aggregate calculations

If you have joins in your report, then you can add in aggregate calculations such as totals and averages into the report.

Note: aggregate calculations can always be added in the report summary in pane 3 which is often an easier way to do so (and you get a nice chart).

 

Implementation notes

  • Unlike standard SQL, you can reference another calculation in the report by name, you don’t have to rewrite it
  • Field names aren’t case sensitive
  • In fact, all calculation content will be converted to lower case on submission. The functions upper(), lower() and initcap() can be used to transform text if necessary
  • Division by zero errors will be caught by agileBase. When this happens, null will be returned for the value

 

Removing a calculation

Once a calculation’s been entered, it’s treated just like any other report field. To remove it or change it’s position in the report, keep the ‘view fields’ tab selected and press the ‘fields’ button

 

Helpful Hints

Just a small pointer to help… rather than use “IF / ELSE” statements use “CASE WHEN”.

 

Aggregate Calculations

Introduction

For background on what aggregate calculations are, please see http://www.postgresql.org/docs/current/interactive/tutorial-agg.html

In agileBase, a quick and easy way of viewing aggregate data is to use charts, which create them behind the scenes.

However, if you have relations (joins) in the view, then aggregate calculations can be added directly into the view like any other calculation, which is more powerful in some situations.

 

Example

Say there’s a timesheet table that records hours spent on work on particular client jobs. The time sheet table has a relation field linking it to an organisations table that holds client details. We want to total up hours spent per client. To do this, you’d perform the following steps:

  • Create a new view under the organisations table
  • Add a join in the view to the timesheet table
  • Add a calculation such as sum({timesheet.hours})

There should now be one row per organisation, with the total timesheet hours listed for each.

 

Explanation

When you add an aggregate such as a sum or average, agileBase automatically groups rows by the parent table’s record identifier (technically, the ID plus all the other report fields). So to usefully add an aggregate, you should join in another report B that the current report A has a one to many relationship with. In the above example, when the sum calculation was added, portalBase automatically added a grouping by the organisation ID.

This means that you shouldn’t add in any fields from B to the report, otherwise the grouping will include them and the aggregate calculation won’t work as intended.

The example report could of course be joined to others and further calculations done, such as multiplying the hours by a standard rate to get a chargeable amount.

 

Going further

Many useful aggregates exist, beyond summing data, for example text in many rows can be merged into one. For further details, please see http://www.postgresql.org/docs/current/static/functions-aggregate.html

Aggregate reports

A common wish, once people have a system flowing with lots of data, is to query it in aggregate. In other words, summarising the data by counting things or totalling up values of items in different categories.

Here’s an example, a report of the total value of sales in each stage of a manufacturer’s pipeline.

 

Pipeline

 

Other examples might be an accounts summary showing profit and loss by year, or sales by salesperson by month.

Often also, you want many figures for the same grouping, e.g. for each month, you could see total sales, total costs, total upsells, number of unique customers, profit, etc. etc.

One way to see these is to use agileBase’s charting features, but often spreadsheet-type reports are better suited to the task at hand. Creating these has also been possible but it often involves quite complex report creation including lots of joins and calculations.

Now, it’s literally been condensed down to ticking one tick-box. In the ‘manage’ tab for a view is a new option ‘use groupings’. Ticking this has a simple effect on the view. Rather than showing one line per record in the database, It will group the view by all the fields visible (excluding any aggregate calculations like totals and counts). So for example, if you create a view containing the field ‘sale status’ (as per the screenshot above), tick the ‘use groupings’ option and add the calculation

sum({sale value})

the outcome will be the example report above. Add as many other calculations or other fields as you like. Each other field will become a sub-grouping.

It couldn’t be any easier. This will not only save hours of work and cost, but let customers take over even more of the build process of an app themselves.

UNION views

agileBase support the SQL ‘UNION ALL’ construct allowing you to combine the results of two views into one list.

http://www.postgresql.org/docs/current/static/queries-union.html

One example of this would be if you have a calendar view e.g. appointments for a user and you want to include events from a separate table such as holidays.

To set this up, go to the manage tab of a view and click ‘advanced options’. Then under ‘amalgamate with’, select a second view to join in data from. The other view must of course have the same number of fields, of the same data types, in the same order.

Activity history in a sparkline

When looking at a sales-related data such as leads or opportunities, it’s often useful to see not just the data itself but how it’s changed over time. You want to know which opps. are being actively worked, which haven’t had any activity for some time and what conversations are going on with prospects.

Now you can at a glance see not just the last update time, but the ‘shape’ of activity over a period of time. It may look like

 

A sparkline with a spike of activity to the right

 

a spike of activity at the right, which represents a new record just added to the system,

 

Sparkline with a spike of activity a while ago

 

a record which had a large spike of activity a while ago with only minor updates since, or

 

A record with lots of activity over its lifetime

 

which represents a record with a large amount of activity over it’s whole lifetime.

With sparklines next to every record on screen, you can quickly get an idea of the relative activity of each…

 

Sparklines

 

The default length of a sparkline is 6 months.

 

Setting up

To add this sparkline, simply go to the view fields tab for a view in the admin section and add the field called Activity Histogram [Auto] to the view. It often looks best when moved to be the first field in the view.

Colour-based record highlighting

Responding and adapting to the needs of our clients and co-workers whilst taking the opportunity to brighten up our interface, we’re welcoming a new agileBase feature to our platform. This new “traffic light” feature will allow you to visually mark records with a custom colour for your attention, based entirely on tailored criteria that you specify.

 

Sales pipeline highlighting won and lost sales
a sales pipeline highlighting won and lost sales in green or red

 

For example, your sales team could use the feature to mark sales opportunities “Hot or Cold” (red or blue, orange or purple) based on criteria you’ve set, like how long they’ve been on the system, when the last comment was added or frequency of contact. Or you could use the colour coded feature to mark companies who are unreliable or late paying invoices.

 

Issue list
an issue list marking high priority issues in red

 

This ability to apply colour to personalised criteria makes your interface more easily identifiable, saving time and effort whilst prioritising your workload. It also allows you to personalise your CRM to suit you and your goals, adapting to your workload and making the system more efficient for your needs.

To set this up, simply create a calculation in a view that generates the name of a colour based on criteria you specify. Then in the ‘manage’ tab of a table, under ‘options’, type the name of this calculation in to the ‘flag records with colour’ field.

 

Setting colour options
setting colour options

Making ‘assigned to me’ views and other uses of your username

Using a text field option, it’s possible to assign system users to records, so for example if you have a ‘todo’ list, items can be parcelled out to individual people.

There’s now an easy way of creating a view that filters based on the currently logged in user, a ‘my items’ view if you like. The facility that allows this is actually a bit more generic – it’s the ability to use the username of the logged in user in any calculation. Just add the code

 

ab_userid()

 

and when the view is looked at, the name of the current user will be used instead, in the format ‘Firstname Surname (username)’, the same format used when assigning users to records.

 

New: you can also return a list of your roles (groups) in a similar way:

 

ab_userroles()

 

So to create the ‘my list’ example, you could add a boolean calculation like

 

assigned to me = {assigned to} ilike ab_userid()

 

or if a record can be assigned to more than one person via a tags field,

 

assigned to me = {assigned to} ilike '%' || ab_userid() || '%'

 

which will find any records containing the currently logged in user. Then just add a filter ‘assigned to me = true’ and the list will auto-filter whenever a user looks at it.

 

Using ‘materialized views’ to cache data

Last week’s update was about helping system administrators manage large accounts. This week’s is about improving the performance for users when those accounts contain very complex reports that may slow the system down if action isn’t taken.

The PostgreSQL database that underlies agileBase is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. It has a sophisticated ‘query planner’ built in to optimise complex reports and many other ways in which reporting can be speeded up. Today, we add support for one of these, called ‘materialized views‘.

A materialized view is basically a ‘cache’ of view data. Say you have a view which contains many calculations over lots of rows. An example could be a profit and loss report for the last decade that totals up monthly costs and income over millions of purchases and sales per year. This report only changes slowly over time. Usually in agileBase, this data will be re-generated every time you view it, which may take a number of seconds.

Now, the view can be set to cache the data. The database only does the calculations once a day, then results can be viewed  immediately as soon as someone looks at the report.

 

Setting up

In a view’s manage tab, click ‘advanced options’

For the ‘cache view rows’ option, select a value. You can either choose to update the view daily or every ten minutes.

Note that if no one looks at the view, it won’t be updated, to save unnecessary work for the server.

 

Notes

This is a feature that should’t be over-used. In fact, it should only be used once other speedup strategies have also been implemented or tried such as

  • adding filters to the view to only show the subset of data that’s needed
  • adding indexes (ask us)
  • increasing the memory available to the view
  • altering filters and calculations to speed them up

The reason is that even when you use materialised views, the system can spend a lot of time refreshing them. If a view takes longer than a certain amount of time to refresh, a database timeout will occur and no results at all will be returned.

So if you’re worried that a view seems slow, please get in touch and we can analyse it. The agileBase monitoring system also proactively highlights the views taking the most time to process so we can investigate.

 

SQL mean times


Not found what you’re looking for?

Please email us at [email protected]