English Español Deutsch Italiano Polski Nederlands Português Français 中文 日本語 हिन्दी اللغة العربية Русский Українська עִברִית Ελληνικά Türk Latvietis Dansk
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)

Tips & Tricks

Records sorting

Complex calculations

Counting days between dates

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

 

Tips & Tricks

Here is some examples of how to simplify your work using functions.

We have a Job candidates table:

The task is to divide candidates into three groups: a suitable candidate, a reserve candidate and an unsuitable candidate.

We can use the Search Form, but then we'll have to use all criteria:

A Formula field with a function comes to the rescue. Let's create a new "Selection" formula.

We've to divide the candidates into three conditional groups.

If the candidate has:

- Age less than 45 years old;

- Higher education;

- Work experience over 5 years;

- A recommendation from a previous employer, then such a candidate receives the "Suitable" status.

For the "Reserve" group a candidate needs less than 50 years old, higher or tertiary education and more than 5 years of work experience.

All other candidates receive the status "Unsuitable ".

 

 

Let's write the following code for the function:

if age<45 && "education" == "Higher" && exp>5 && "recommend" == "Yes"
   "Suitable"
elsif age<50 && "education" == "Higher" || "Tertiary" && exp>5
   "Reserve"
else
   "Unsuitable "
end

Please note: numeric values (including html field names) are not quoted. Quotes are used for string values only.

As you can see in the following screenshot, each candidate was assigned a status based on the criteria:

It is much easier to search on a single field instead of multiple criteria:

The same with Conditional formatting in a table:

We use the "Selection" field to format records in the table:

It is much more convenient to generate a Report after searching or grouping by one field:

Which contains all candidates by selected criteria:

Don't forget about Conditional Actions and the Action field.

For example, create a button with the action "Send Email", which is available in the record with the "Suitable" candidates only.

You can see an example of how to create an Action button by this link.

A function field can be used as a criterion in Field Rules.

As an example, enable the "Selection" field on the form:

Next, let's create a new field "Add to queue" (Radio button) with a choice of two values:

Create a rule.

If the "Selection" field consists "Reserve", then show the "Add to queue" field:

This is how it looks on the form:

The functions will allow not only more efficient data management, but also more complex calculations.

For example, consider a Profit definition table:

The task is to calculate total profit.

The formula looks like this: total profit = revenue - total cost

If we carry out such a calculation using standard formulas, then we would have to divide the calculation into several formulas (separately calculate revenue and total cost).

Using Ruby functions, you can do this calculation in one field:

(sell_price*quantity)-(buy_cost*quantity)

Total profit in table:

Please note: for correct calculation, you must use the "Decimal" field type.

 

Counting days between dates

For example, we'll use a table with contracts.

Task: show how many days the contract will end.

Create the following script:

exp_date.mjd - DateTime.now

Where:

exp_date - html field name.

.mjd method - converts the date to the Julian calendar format.

DateTime.now - object whose value is the current local date and time.

As a result, we get a column with the number of days until the end of the contract.


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: