English Español Deutsch Italiano Polski Nederlands Português Français 中文 日本語 हिन्दी اللغة العربية Русский Українська עִברִית Ελληνικά
Log In
English

Log In

 

Functions and scripts (Formula field)

How to use the Minimum/Maximum, Ceil up/Ceil down (round) functions and Ruby scripts

Contents

Minimum/Maximum functions

Ceil up/down functions

Scripts (Ruby)

 

Minimum/Maximum functions

Let's see how the functions work, using the product table as an example.

This table contains the following columns:
Brand name and model;
Image;
Price for June, July and August.

The task is to show the lowest price for all three months.

The first step is to add the Formula field to the form.

Select "Function" in the first drop-down list:

Select "Minimum" in the second drop-down list:

Next, you need to select the fields for comparison. In this case, these are three price fields.
And click on the "Create" button.

Now the field "Lowest price" has appeared in our table, which compares the three values in the record and shows the minimum of them:

The "Maximum" function works exactly the opposite.

 

Ceil up/down functions

Consider the "Ceil up" and "Ceil down" functions. Here is a table with clients and their orders.

The task is to round the value in the "Payable amount" column.

Create a new Formula field and select the function "Ceil up" (or "Ceil down");

Next, you need to select the required field from the dropdown list and specify the significance.

As a result, we get a column with a rounded amount:

 

Scripts (Ruby)

Scripts allow you to perform logical and arithmetic operations. 

We'll use the "Summer sales" table as an example. This table contains a column with a managers, their work duration and the sum of sales for the summer.

Let's create a new Formula field, select the Function option and the Script (Ruby) item in the following list.

You can use HTML field names as operands.

Click on the link under the function field "How to insert record data":

A list of available fields will appear. Select a field from the list and copy HTML name.:

Also, you can change the HTML field name right here. It is enough to enter a new name and click on the "Update" button:

The following arithmetic operators are available:


(+) addition

(-) subtraction

(*) multiplication

(/) division

(>) true, if the left operand is greater than the right

(<) true, if left operand is less than right

(%) modulo division

(==) true, if the values of the two operands are the same.


Conditional operators:

(if) using to test if a condition is true. Condition values false and nil are false, while all others will be True.

(elsif) using for additional conditions.

(||) using to test multiple conditions.


Let's go back, for example. The task is to calculate the bonus for managers.

Assume the bonus ratio depends on several factors. The first one is work experience in the company.

Let's create the following script:

 

if work_months > 12

  300

 

This means, if an employee has worked in the company for more than 12 months, he will receive a bonus of  300 $.

Let's create another condition. If the manager made more than 7000 sales, then he will receive 150 $ + 25%.

The script should look like this:

elsif sales > 7000
 (150*0.25)+150

In all other cases, the bonus will be 150 $

else
 150

It is necessary to specify end, for the script's correctly work.

 

 

Now we can calculate the bonus for each manager:

Available methods and operators: to_f, to_i, to_s, round, floor, ceil, ceiling_up, ceiling_down, include?, if, elsif, else, end.

Also, you can use two variables: a and b


Please, login to QuintaDB to be able to post comments

anils@telia.com

String operations? Have not found such in quinta documentation, yet.


Dmitry Shirokonis

Scripts allow you to perform logical and arithmetic operations.
If you have some questions about formula field, please add a new topic here: Support

Share the discussion in the social networks: