TyBase is all about simplifying data management. Whether it’s at home, in a small business environment, or in enterprise- people always have the need to collate, process, and format information. A traditional database system is often expensive and technically challenging to deploy- meaning that we often fall back to unsuitable techniques such as spreadsheets or text editors when we really need something a little more structured. TyBase provides that ‘something’.
Table Columns as Calculations
Any table column can be configured as a “Calculation”. This means that the contents of that column are calculated automatically. (This is similar to an Excel spreadsheet formula, for example.) Unlike in a spreadsheet, calculations are not set on a single cell or a range of cells, but rather on an entire column. This is configured in the “Edit Table” window.
An example of a table which makes use of calculations is shown in the image below. This example comes from our Accounting template. The calculations are summing the “SalePrice” and “SaleCost” columns respectively from the “SalesInventoryItems” table, multiplied by the “Quantity”, filtered by the “ProfitCenter” column. In short, this shows the total income and outgoings for each profit center defined in this table. Read on for a detailed explanation.
Calculations within TyBase follow a common syntax, regardless of where they are used. Columns within the current table can be referenced by name (with one restriction- column names containing spaces or special symbols cannot be used in this manner.) If the column is a reference, it should be followed with a period character (‘.’) and then the name of a column within the referenced table. This can be repeated as desired.
To access any data field, you must uniquely identify the table, row, and column of that field. For a calculation column defined in a table, the calculation is evaluated separately for each row in the table, and the scope is always set to the row being evaluated. This means that you can simply specify a column name, and TyBase will understand that to refer to a data field in your current row. For example, in a table of temperature where each row has “date”, “location”, “high” and “low” columns, you could add an “average” column which calculates “(high + low) / 2”. This would display the average temperature alongside each row of the table. You don’t need to specify which date or location to use- this is implied by the scope. The inputs (high, low) and output (average) are all present on the same row of the table.
When you need to pull in data from another table, or from different rows in the same table, you need to provide some context about which rows you are referring to. There are two approaches to this: References, and Queries.
A reference is a special type of data column which directly refers to a single row in a given table.
To extend the temperatures example above, let’s add a table which lists all of the locations for which we are tracking weather. This table (named “Locations”) will start with a single column: “location”. Each row represents a single location where a weather station is located.
In the “Temperatures” table, instead of leaving the “location” column as free-form text, we’ll specify it as “Reference > Locations”. When you edit a field in this column, you are simply selecting an existing row from the “Locations” table.
By itself, this doesn’t achieve much- it does ensure that we don’t make any spelling mistakes when typing in the locations, and can help remind us what the proper name of a given weather station is, but that’s about it. The benefit of this approach is that it reveals a relationship between the two tables, which we can utilise in our calculations.
For example, consider what happens if we add a “historicalAverageTemperature” column to the “Locations” table. This allows each station to have a single temperature recorded which lists the overall average temperature at that station. Now in the “Temperatures” table, we could add a new column “differenceFromAverage” which uses the following calculation: “average – location.historicalAverageTemperature”. Since the ‘location’ field is know to refer to a specific row in a specific table, all we had to do is specify a column name in that table to uniquely identify the data field.
A query allows us to take this one step further- instead of accessing a single data field from another table, we can summarise data from a selected range of fields. Continuing the above example, let’s take a look at how we can express the “historicalAverageTemperature” column as a calculation instead of requiring the user to enter it manually for each location:
sum(Temperatures, location==this, average) / count(Temperatures, location==this)
There are a few important concepts introduced in this calculation. Both “sum” and “count” are query functions. Each query is run independently. The first parameter, “Temperatures” in this example, is the table over which the query runs. A query steps through each row in the specified table, and evaluates the second parameter, know as a condition. In this example, both queries use “location==this” as their query condition. The condition is a calculation in its own right, performed in the scope of the table row being queried. This means that any column names used in the query condition are accessed from the query’s scope in preference to the parent calculation’s scope.
If you need to access the parent calculation’s scope, you can preface the field access with ‘this’. For example, to access the location name from the parent table, you could use ‘this.location’. In our calculation, we don’t need to do that, but we do need to check that the ‘location’ reference field matches the location we care about. Since the ‘location’ field is a reference back to a row in our parent table, and ‘this’ also represents a row in our parent table, we can directly compare them using the equality (‘==’) operator. If the queried row’s location matches our own row’s location, we know to include the row into the result. If the row doesn’t match, we skip over it without changing the result.
For the ‘count’ query, the result is simply the number of rows in the target table which match the condition. This may be 0, 1, or any other positive integer. (In this example, if it ends up being zero we’ll get a division-by-zero error.)
For the ‘sum’ query, one extra parameter is present. This is a calculation which is evaluated and added to the result. In our case, we simply use the ‘average’ field from the query’s target table. Any rows in the target table which match the condition will have their average temperature added to our total, which we then divide by the number of rows, giving an overall average at our location.
Standard mathematical and logical operators are used in a similar manner to other programming languages. For example, the following expression is valid syntax:
- 15 + 13 * (12 – 3)
Simple mathematical operators work as expected on numeric data. Date columns can be treated as numeric, keeping in mind that they are stored internally as millisecond counts. The following numeric operators are available:
- “+” – Adds the left and right values.
- “-” – Subtracts the right value from the left value.
- “*” – Multiplies the left value by the right value.
- “/” – Divides the left value by the right value. If the right value is zero, the string “nan” is returned.
- “<” – Returns true if the left value is less than the right value.
- “<=” – Returns true if the left value is less than or equal to the right value.
- “>=” – Returns true if the left value is greater than or equal to the right value.
- “>” – Returns true if the left value is greater than the right value.
- “#” – Returns a string which is the concatenation of right value after the the left value.
- “==” – Returns true if the left value and right value are equal strings.
- “!=” – Returns true if the left value and right values are NOT equal strings.
- “&&” – Returns true if both input values are true.
- “||” – Return true if at least one input value is true.
- “-” – Returns the negative of the numeric input value.
- “!” – Returns the boolean inverse of the boolean input value.
- Unary operators: “!”, “-“
- Multiplication and division operators: “/”, “-“
- Addition and subtractions operators: “+”, “-“
- String concatenation: “#”
- Comparison operators: “<“, “<=”, “==”, “!=”, “>=”, “>”
- Boolean operators: “&&”, “||”
- sum(table, condition, value) – Performs a query, evaluating ‘condition’ on each row of the named table. If the condition results in a boolean true, ‘value’ is added to the sum. The sum is returned.
- count(table, condition) – Performs a query, evaluating ‘condition’ on each row of the named table. Returns the number of rows for which ‘condition’ returned boolean true.
- max(…) – Evaluates the numeric maximum of the parameters.
- min(…) – Evaluates the numeric minimum of the parameters.
- round(value, precision) – Rounds ‘value’ to the nearest multiple of ‘precision’. ‘precision’ can be any non-zero positive real number.
- formatnumber(format, number) – Formats a number according to a format string (see formatting, below.)
- formatdate(format, date) – Given a format string and an integer (millisecond timestamp) representation of a date, this will return a formatted text string representing the date (see formatting, below.)
- if(condition, iftrue, iffalse) – Evaluates ‘condition’ as a boolean, and returns the value of ‘iftrue’ or ‘iffalse’ as appropriate.
- searchstring(body, search) – Performs a human-style search within the “body” text. Returns a boolean value indicating whether the search string was present. Primarily used for implementing search fields for table view elements.
- this – Refers to the row on which the calculation is occurring.
- iterator – Refers to the current row of the execution query (where relevant.)
- null – The empty string.
- true – A boolean true (“1”).
- false – A boolean false (“0”).
- form – Specific to calculations used in a form, this allows direct access to form field values without requiring them to be written to database first. The typical syntax is ‘form.fieldName.value’ where ‘fieldName’ is the name of the form element. This is primarily used to implement search fields for table view elements.
- “Fred Jones”
- “Martha \”The Magician\” Jones”
A number of mechanisms exist to display and parse data in specific formats.
The formatnumber(format, number) function can be used to format a number value based on a format string. The format string rules are as follows. Unspecified characters are reserved for future use and should not be used:
- “-” emits a minus sign if the number is negative, or is ignored if the number is zero or positive.
- “+” emits a minus sign if the number is negative, or a plus sign if the number is zero or positive.
- “0” (zero) without following numerals emits at least one digit.
- “0” (zero) followed by numerals emits at least the specified number of digits. If after the decimal points, emits exactly the specified number of digits.
- “.” emits a decimal point.
- “d” followed by any character emits the following character as a decimal point.
- “D” followed by any character emits the following character as a decimal point IF the number is not integer.
- “$” emits a literal.
- “#” emits a literal.
- “(” and “)” are emitted literal if the number is negative, or are ignored if the number is zero or positive.
- ‘”‘ (quote) used where any single character is expected allows a quoted string of characters to be used instead of the single character.
- ‘”‘ (quote) used elsewhere emits a literal string.
The formatdate(format, number) function can be used to format a numeric date value based on a format string. The format string rules are as per the Java date formatters, documented here. Unspecified characters are reserved for future use and should not be used.
The “display format” property of each table column uses calculation syntax to determine what to display. This calculation is only for display formatting purposes and does not affect the cell’s data value or the user’s editing format.
As an alternative to complex format calculations, some shorthand formatters are available for common usage:
- @number “XXX” – given a literal format string, this formats the value as per the formatnumber() function.
- @date “XXX” – given a literal date string, this formats the value as per the formatdate() function.
In addition to being simpler to understand, these formatters also apply to the user’s editing.