Log In

Log In



Not registered yet?


Reset password Activation link

Formula field, calculate Net, Total using Subtraction, Addition, Multiplication and Division

Formula field can be added to any web-form to allow calculations in your database. Formula field allows all basic math operations and other advanced features to use while linking forms

Using formula you'll be able to calculate Subtraction, Addition, Multiplication and Division of several columns.

In this blog post we will create a web form for counting the people's money. We will need two 'Single line' fields, First Name and Last name. We will need three Decimal fields: At home, Paypal, On Credit card.

Also we will create two Formula fields, one is called Full name and another one is Total.

As you understand this is for getting a full person' name and his total money amount.

Let's fill our form several times and here is what we will see on the Records page:

How to create Formula field

How to show multiple columns from linked table

In order to show several columns from linked table just use Formula field as a Link column.

Please, see below how to do it.

How to show multiple columns from linked table

 

If you creating formula for the form which is linked to other forms then you'll be able to use other form's fields as your formula operands.

Update existing records when key data matches

Other good news is that updating big data sets will become much better now, because you will be able to create 'more unique' fields now using Formula field. For example, you may have a database with first name and last name columns, and it's impossible to use these fields as a unique field for mass update. That is why it is so great to have Formula in place now.

Use formula as a key field during import

Using Formula for generating Google Maps in QuintaDB

Also you can use the Formula field for showing a unique map points title.



Sorting, Filtering, Grouping and Searching using Formula fields

Formula fields are just the same as all other form fields. But it will allow you to perform better Sorting, to sort by name including Last name. The same is with Filtering and Grouping. You can perform Search for Formula fields, so you don't need to select both First name and Last name search criteria when you have one Full name formula field.
 

How to show the total amount from other tables using Formula field

For example, we have two tables (football teams: Dynamo, MU) with one column Income. We have added incomes to these tables, also you should choose "Show Table Total at the bottom of the column" from "Defaut" menu. We need the third table with the following columns: Team names and Total income of each team. Also we need to be able to add new income and see all incomes of the selected team using this third table. 

We use Formula field to solve this problem.

Create form All teams and add formula field. Click on link Edit formula to activate needed options. All records of Team name column must be the same as table names (Dynamo, MU).

Add formula field Total Income. Use option Table Total. We can see the total amount of each team in this column.

Also if you need to have a link to submit new income add to the form Formula field with name Enter team income and use the following options.

If you need a link to the table with all team incomes add to the web form Formula field with name Team incomes and use the following option.

So, in this table we have 4 columns: Team name (all records of this column must be the same as a table names), Enter team income (formula field) - you can see a link to the form to submit new team income, Team incomes (formula field) - you can see a link to the table with all team incomes, Total income (in this column we have a total amount of all the team incomes).

 

Formula options

 

Concatenate strings

When you use formula to concatenate string then between data from strings field formula add space automatically.
You can use option "Do not add space between operands" to turn off this. This option you can find in edit formula when hover cursor on formula field.

Do not add spaces between operands


Please, login to QuintaDB to be able to post comments
Share the discussion in the social networks: