Help Centre

Choose a category to find the help you need

Troubleshooting - agileChilli

agileBase allows extreme flexibility in letting you set up views of information, combining data from multiple tables and adding your own complex calculations.

With that power comes the potential to get yourself in a pickle every now and again. In this document, we explain some of the more common types of issue you may encounter and give tips for resolving them.

A general rule is that 99% of the time, any view errors will at the root be a problem to do with a calculation. That’s because calculations are the one area where the user (administrator) has complete control over what to enter. When adding a filter for example,  you can pick from a set list of options, but in the calculation editor you can type anything.

Here are some common symptoms and explanations:

 

Errors from data

Usually, if you submit a calculation with an error in it, the system will reject it immediately and there’ll be no long-lasting error. However, sometimes things are less clear cut. A calculation can work with some data but not all. For example, casting data to different types can exhibit this behaviour. Say you have a calculation like

 

 cast(left({my field}, 1) as int)

 

This will take the leftmost character of a text field and convert it into a number. If the leftmost character is always a digit (0 – 9), this will work fine, however if for some reason a letter or other character appears in that field one day, the view will no longer work. Instead an error something like

 

Error

Invocation of method 'getReportDataRows' in class com.gtwm.pb.model.manageData.ViewMethods threw exception org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "U" at gui/reports_and_tables/report_data.vm[line 57, column 32]

 

will appear.

That’s why it’s a good idea to make sure your system has a fair amount of data in it before adding any complex calculations – errors will appear and can be fixed more quickly.

If your view has this problem, re-submit the offending calculation to fix it. If that isn’t possible (see below), add a filter to the view to remove any problem data first, then work on the calculation and remove the filter.

 

‘View Broken’ messages

If there’s a serious error, the view will display ‘view broken’ messages in each column instead of data.

This isn’t very common but it is possible for it to arise in certain situations – when a schema change action creates an error but the system can’t roll back to a previous state because of further errors – possibly due to a combination of different errors at the same time, like ‘errors from data’ above and ‘field or table name changes’ below.

In any case, the possible options are to 

  1. Fix all the errors. Some tactics are:
    1.  Editing each calculation individually and re-submitting, correcting any errors. Often, you won’t be able to submit many calculations without error the first time, as the other errors still prevent the view from re-building. You have to go through each in turn until you find one which submits ok, then re-do the others.
    2. If a calculation has no changes, add a space to the end of it to force the server to recognise it as changed and try re-building it
    3. If that fails, try copying all existing calculation definitions to a text editor (for backup) then replace them with really simple definitions that can contain no errors, e.g. 0 for number fields, now() for dates or  for text fields. Once the view contains no errors, paste back in the original definitions one at a time to see which ones have errors
    4. Check all calculations are properly detected as aggregates or not (see ‘aggregates’ below) and change if not
  2. Delete the view. Often, if the system blocks you from making any changes at all, the view can usually still be deleted from the ‘manage’ tab. I suggest making screenshots of the view setup (filters, fields, calculations etc.) first so you can re-create it afterwards.

 

Calculations dependant on each other

Some views may contain ‘chains’ of calculations dependent on each other. In this case, when one link in the chain is corrected, all other calculations which reference it have to be re-submitted too (e.g. by adding a space as above), even if they don’t have any problems, otherwise they will internally still use the old definition of the corrected calculation.

 

Field or table name changes

Over time, field, table or view names may of course be changed. If calculations reference those names, they won’t automatically update (though that may be a feature to arrive in future). The next time you make changes to a view with a calculation referencing an old name, it will need to be updated. This can be sometimes be a source of confusion, if for example you’re making a change completely unrelated to the calculation such as adding a field or filter and it complains about another calculation.

Again, if in doubt of the sources of any errors, check the calculations!

 

Aggregates

Some calculations are treated slightly differently in SQL – aggregates. An aggregate is a calculation that condenses many rows down to one value, things like totals, averages etc. For example if you have invoice records joined down to invoice lines, you may have an aggregate calculation ‘sum({invoice lines.value})’ which calculates the invoice total.

The system just needs to know which calculations are aggregates and which aren’t – it does it’s best to work it out itself but every now and again can’t. If you get error messages talking about ‘aggregate functions’ or ‘group by’ then this is likely the cause.

To fix it, simply go to the calculation editor, tick ‘aggregate’ next to the calc. which is an aggregate but which hasn’t been detected as such and submit it.

Note – the system will usually detect aggregates properly anyway and is guaranteed to if the view contains enough data that there are multiple values to be rolled up into one.

 

Inter-dependent views

It’s possible for views to be dependant on others. For example, one view ‘A’ may calculate a tax rate per country and many other views B, C and D may join to that one to use the result of the calculation.

If you make a significant change to view A, the system may need to re-build B, C and D in order to update them too. If one of them, say ‘C’ has a calculation error as described above, this can’t be done. The system will tell you that it can’t make the change to A because of an error in C. In this case, the solution is to fix the error in C, edit C to remove the link or delete C altogether (maybe re-creating it afterwards).

 

Timeouts

When views get complex (many calculations, joins etc.) and the amount of data in the system grows, views can slow down.

Don’t worry, there are many things that can be done to improve the situation and some things the system does automatically.

First of all, a couple of basic ideas. When you create a view or add a join, it by default contains *all* the data rows from the relevant table. Views are in general quicker with less data, so is it all necessary? In many cases, especially when systems have been in use a number of years, working views only need to contain recent data, with perhaps a separate ‘archive’ view for old data.

So one way to speed up a view with lots of data is to add a filter to hide data older than a certain number of days/months/years.

Indexes can also be added to speed up views with specific filters or joins. We’ll need to look at doing that so please contact us.

For views using lots of rows, memory settings can be expanded. Again it’s best if we look at doing this so please get in touch.

Once a view’s finished, if it’s still slow, caching can be turned on so the view contents are only update daily and the user gets an immediate view of the last update. This can be useful for stats views e.g. monthly sales, that don’t need to update minute by minute. However, don’t turn on caching while building a view, it can cause extra work which will make view changes slower.

Troubleshooting view cloning

If cloning a view fails with an error, there are two main possibilities:

  1. there’s a problem with one of the calculations in the view (or a joined view). See view troubleshooting.
  2. There’s an ordering issue, see below
  3. There’s a join on a calculation, see below

 

Calculation ordering issues when cloning

Fields and calculations are cloned from top to bottom. If a calculation depends on another calculation below it (to the right when looking at the view), the order will have to be swapped before cloning. Drag the calculations using the fields editor in the view to change their ordering.

 

Joins on calculations

When cloning a view, agileBase first copies all joins to the new copy view, then it adds fields, calculations and filters.

The only problem with this is if the view contains a join on one of it’s own calculations, i.e. a join on a calculated value. That join can’t be added because the calculation doesn’t yet exist. AB isn’t yet clever enough to break out of adding joins, add the calculation then come back and finish the joins. This view will have to be copied manually.


Not found what you’re looking for?

Please email us at [email protected]