We are happy to announce about a very important and interesting feature called Formula field.

New formula field features:

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

How to calculate two column values from different tables

How to show data from several table columns in the same column of the other table

 

Using which 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:

 

Also, please, see Record's detailed View:

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.

Using Formula for generating Google Maps in MTH

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.
We hope you'll like this new Formula Field! Letэs go and calculate your first formula using Subtraction, Addition, Multiplication or Division!

New formula field features

In this update I will explain how to use formula fields in different situations.

 

How to show data from several database table columns in the same column of the other table.

 

For example, we have two forms Contact (with columns: Name, Address, Zip, City) and Payment. For the second table we've created the following columns: Amount, Select contact (it's a related field from the  Contact form, in our case it's Name), Contact Address (formula field). We need to add data from the first table to the second table (contact, contact address). In the Contact Address column we will show all the information (Address, Zip, City) from the first table. 

 

Relationships feature and Formula field will help us to relate these forms/tables. 

 

1. Create Contact and Payment forms: add to the Contact form Name, Address, Zip, City fields. On the Payment form we need to have the following fields: Amount, Select contact, Contact Address.

 

 

 

 

2.  To relate forms Use Relationships type Has many. More detailed information about Relationships read in this blog post.

 

 

Now on a Payment form we have column Select Contact. It's a Name field from the Contact form. In our case we can select contact Igor or Andrew.

 

 

3. Click on Edit Formula link to see and edit all formula field settings. Use "+" option to paste together data from Address, Zip, City fields

 

 

So, we have two related tables. Data from the three Contact table columns is shown in the Contact address column in the Payment table.

 

 

How to calculate two column values from different tables

 

For example, we need a table with the user's data (name, source the user migrated from to MyTaskHelper, price considering discount rate i.e. NET price). In our case we need to multiply Amount (standart MyTaskHelper price) and Discount rate. These values are located in different tables. So in this example, using Relationship and Formula field, I will show you how to calculate these values.

See step by step instructions below:

1. Create Source form with fields Name, Discount rate and User form. Click on button Relationships to create relationship between the two forms: Source and User.

 

2. Use Has many relationship type (source has many users). More detailed instructions about relationships you can find here.

 

 

 

3. On the User form we have Name, Amount, Select Source (Name field from Source form), NET fields. To calculate NET click on Edit formula link. Select the needed fields and operations.

 

 

 

 

We have two tables. We've added service names and discount rate to the first table Source.

 

 

We've added users' data: Name, Amount, Select Source (it's a name field from the Source web form) to the second form.

NET (it's a formula field). NET = Amount (User form)*Discount rate (Source form).

 

 

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. 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).

 

 

26.08.2012 Move to top