Blog

One of the new features we added in Backendless is called “Generated columns”. If you are familiar with Excel and have ever used Excel cell functions, you will understand Backendless Generated columns in no time. In short, it is a special column you can declare in a data table that has a “formula”, or our lingo, an expression.

The expression is responsible for generating a value for the columns. What can you do in an expression? A LOT! And that’s what makes Backendless Generated Columns super powerful! Read this article for an overview of the new feature.

In this post, we will go over some examples so you can see how awesome this functionality is and can start using it for your application. Let’s start with something very basic.

Suppose we have a table called `Math`

. The table has two columns `operand1`

and `operand2`

, both are of the `INT`

type. Here’s the schema for the columns:

Let’s say we want to be able to calculate the following values:

- the sum (
`operand1 + operand2`

) - the difference (
`operand - operand2`

) - the product (
`operand1 * operand2`

) - the dividend (
`operand1 / operand2`

) `operand1`

raised to the power of`operand2`

, which is basically`(operand1)`

^{operand2}

And not only we want to calculate these values, but also have dedicated columns that contain each of the results. Generated columns to the rescue!

Here’s what a declaration of the column containing the sum would look like:

As you can see all it takes is to select the **Generated Column** checkbox and then enter the **Expression**. Make sure to select the **Type** you expect the expression to produce. In our case, the sum of two integers will be an integer. **The Expression field is the most important part**. It is where you put the formula to calculate the column value. Once the column is created, let’s see it in action:

As you can see the generated columns have a special visualization in Backendless Console – there is a light yellow background, which is meant to indicate that this is a special column. Indeed, you cannot enter values into the cells of that column and you cannot update that column’s values through the API. However, as you can see the result is right there – the column displays the value according to the provided expression – it is the sum of the `operand1`

and the `operand2`

values.

Let’s add more columns for other math operations. The subtraction, multiplication, and division are going to be easy since Backendless supports all basic math operations. But how to go about calculating the power of `operand1`

to `operand2`

? This is where expression functions come in. And we support a lot of them, including one to calculate the power (and Sine, Cosine, Tangent, Log, Square root, Random number generator to name a few). See below for the examples of what the expression should look like when you configure the generated columns the example we’re reviewing:

- difference –
`operand - operand2`

- product –
`operand1 * operand2`

- dividend –
`operand1 / operand2`

- power –
`POWER( operand1, operand2 )`

This is what my data table looks like after I added the columns with the expressions shown above:

Of course, being able to define a column that calculates the value according to your own formula/expression is fantastic, but what makes this infinitely more useful is the API handles the columns just like any other ones. What this means is:

- The where clause to retrieve data objects can reference generated columns;
- A generated column and its value is returned by the server as any other column;
- A generate column expression can reference other generated column;

Below is a list of some common mathematical functions you can use in your expressions. Where it says `value`

it can be:

- a scalar value
- name of a column in the same table
- another function whose result will be used as the input

**ABS( value )**– returns absolute value. Example:

Example:`ABS( operand1 )`

**MOD( value1, value2 )**– Modulo operation. Returns the remainder of`value1`

divided by`value2`

.

Example:`MOD( operand1, operand2 )`

**FLOOR( value )**-Returns the largest integer value not greater than`value`

.

Example:`FLOOR( operand1 )`

**RAND()**-Returns a random floating-point value in the range 0 <= value < 1.0.To obtain a random integer R in the range i <= R < j, use the expression`FLOOR(i + RAND() * (j − i))`

. For example, to obtain a random integer in the range 7 <= R < 12, use the following expression:

Example:`FLOOR(7 + (RAND() * 5))`

Suppose there is a table with two columns of the `STRING`

type. The column names are `firstName`

and `lastName`

:

Here are some examples of what you can do with generated columns and these string values:

**CONCAT( value1, value2, …, valueN )**– concatenate values.

Example:`CONCAT( firstName, ' ', lastName )`

produces`"Spong Bob"`

**CONCAT_WS( separator, value1 value2, …, valueN )**– concatenate values with separator.

Example:`CONCAT_WS( ', ', firstName, lastName )`

produces`"Sponge, Bob"`

**TO_BASE64( value )**– encodes value using the base-64 encoding.

Example:`TO_BASE64( firstName )`

produces`U3Bvbmdl`

**FROM_BASE64( value )**– decodes a base-64 encoded value.

Example:`FROM_BASE64( TO_BASE64( firstName ) )`

produces`"Sponge"`

**LENGTH( value )**– returns the length of value measured in bytes.

Example:`LENGTH( firstName )`

produces 6**LOWER( value )**– returns value in lower case.

Example:`LOWER( firstName )`

produces`"sponge"`

**UPPER( value )**– returns value in upper case.

Example:`UPPER( CONCAT( firstName, ' ', lastName ) )`

produces`"SPONGE BOB"`

There are many more date/time functions supported by Backendless. For a complete list, see the MySQL documentation.

Suppose there is a table with a column of type `DATETIME`

. The column name is `deliveryDate`

:

Here are some examples of what you can do with generated columns and this DATETIME column:

**ADDDATE( value, days )**– adds the specified number of days to the value.

Example:`ADDDATE( deliveryDate, 15 )`

returns`05/15/2020 00:00:00`

**DATEDIFF( value1, value2 )**– returns the difference between value1 and value2 where both values are of the DATETIME type.

Example:`DATEDIFF( created, deliveryDate )`

– returns the number of days between the date when the order had been created and the delivery date.**DAYNAME( value )**– returns the name of the weekday for value.

Example:`CONCAT( 'Your delivery will be on ', DAYNAME(deliveryDate) )`

produces`"Your delivery will be on Thursday"`

**DAYOFMONTH( value )**– returns the day of the month for date, in the range 1 to 31.

Example:`DAYOFMONTH( deliveryDate )`

produces 30**DAYOFWEEK( value )**– returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday)

Example:`DAYOFWEEK( deliveryDate )`

produces 5**DAYOFYEAR( value )**– returns the day of the year for date, in the range 1 to 366.

Example:`DAYOFYEAR( value )`

produces 121**HOUR( value ), MINUTE( value ), SECOND( value )**– return hour, minute and second elements of the datetime value. All elements are zero-based.

There are many more date/time functions supported by Backendless. For a complete list, see the MySQL documentation.

It is possible to create a generated column with an expression that includes some conditional logic. Consider the following example, it is a table called `Order`

. Suppose we need to determine the priority of delivery depending on the day of the week of the delivery date. If the delivery date is between Monday through Friday, the priority is `"HIGH"`

and if it is on the weekend (Saturday or Sunday), the priority is `"MEDIUM"`

. Here’s what the expression for the generated column could look like:

CASE WHEN DAYOFWEEK( deliveryDate ) IN (2,3,4,5,6) THEN 'HIGH' WHEN DAYOFWEEK( deliveryDate ) IN (1,7) THEN 'MEDIUM' END

When creating a generated column, the screen will look as shown below:

And the magic happens automatically:

`"HIGH"`

priority – the date lands on a weekday:

`"MEDIUM"`

priority – the date lands on the weekend:

As you can see this can get extremely powerful and be very useful for your applications. If you have any questions or need help with composing your generated column expressions, please contact us on our support forum.

Cheers and Happy Coding!

Is there a way to auto increment the ID INT column by initially specifying the start value and increment value?

Yes, there is a way to do it, but not with generated columns. An expression used in a generated column operates in the context of the values of the row that is being created or updated. The auto-increment is based on the knowledge of some other field located elsewhere. To implement auto-increment in Backendless, you can use a combination of “beforeCreate” or “beforeUpdate” event handler in Business Logic (https://backendless.com/docs/bl-js/bl_event_handlers.html) and atomic counters API: https://backendless.com/docs/js/ut_atomic_counters_api_js.html

Your business logic would use an atomic counter value, increment it and inject the value into the object being saved.

How to run an aggregator function in Generated Column for Child/Parent objects? Like, Count, Sum, Max etc.