Introduction
Spreadsheet applications (like MS Excel or Google Sheets) are very popular as productivity tools, as they allow us to implement arithmetical operations in a more simple way.
Usually, you build the formula from existing columns and arithmetical operations.
In ERPAG, we enabled that each operator in every table can add a custom column using formula. We think we made this process even more simple compared to spreadsheet applications.
Note: Custom column is not the same as a custom field. The custom column is just a Read-Only column for every operator in the table. Find more information about custom fields on the below link:
Adding Custom column:
Adding the column option is in the Column settings. On the last tab in the Column list is option “Formula”, marked with a red arrow on the picture below.
The green arrow indicates the option to edit an existing custom column.
Once again, we mention that settings are separate for each operator, e.g each operator adds columns for only their account.
Setting up formula
The process of setting up is split into a couple of sections.
The first section contains a heading where we enter the Caption of Column, Decimals, and Sum of the entire column if needed.
Bottom left section contains the formula “preview”. That section is read-only. You can create a formula by using arithmetical operators (marked with red border pictured above) and available columns (right list on the picture above). Only numerical columns will be shown because it is only possible to perform arithmetic operations on them.
By clicking on the operator or column, we are creating a formula from left to right. Columns are marked with green color for easier visual recognition.
In operators, there is “back” and “clear” options, like on every calculator (marked on picture below with green and blue arrow).
There is another option (“123” icon marked with a red arrow on the picture above) that allows us to add a fixed amount.
Fixed amounts in “previews” of the formula shown in blue color.
Formula validation
While creating the formula, we should keep an eye on syntax (e.g the number of open and closed brackets should be the same). In ERPAG there is basic validation, and the formula needs to be syntax correct to implement the formula.
Note: One of the common mistakes is “divide by zero”. Mathematically, when you divide a number by “0” you get an infinite number. The reason being, infinite numbers can’t be shown in ERPAG, those results will have a value of “0”.
Custom column in table
The custom column is like every other column, the only difference is, it is always “read-only”.
Value is automatically calculated (like in other spreadsheet applications) when we change an entry in any other column.
Note: to prevent recursive invocation of formulas, custom columns will not be offered when building a formula in column list.
Formula in the Report Designer
Custom columns are separate for each operator and like so, it is not possible to forward them in report designer.
In the report designer, there is an “insert formula” option. That makes it simpler to insert formulas if needed for printing or making reports.
The Principle of creating a formula is the same as with a custom column.
You can find more information about Report Designer on this link:
Recent Comments