How to use formulas and calculated fields

You can use a formula to set the default value of a field as a calculated value. Formulas can include the values of fields in your form plus a variety of supported functions. This article presents a few common examples of useful formulas plus a list of supported functions.

Common formula examples

(Tip before beginning: When using quotations within formulas, use only the ' symbol and not ")

Include the Formotus user name in a form

Set a field value to Username() to capture the email the mobile user enters to log into Formotus Pro. (This function will not work in Formotus Now because there is no user login.)

Include today's date in a form

Set a field value to Today() to capture the date when a form is filled.

Give each form submission a unique filename

When you add a data connection to your form that submits the form as a document, there is a field where you can specify the name of the file that is submitted. You can use the function Now() to include a timestamp to make each filename unique.

Give each form submission a meaningful and unique filename

You can use the Concat() function to combine (concatenate) multiple strings, field names and functions in order to generate a unique filename that also contains meaningful info. The following formula, for example, generates a name that includes the form name as text, the customer name as a field value, the user name as a function, and a timestamp function to assure uniqueness. (There must be a field in the form named CustomerName for this formula to work.)

=concat("Safety Inspection", CustomerName, username(), now())

Note that in the form name field the formula starts with an equal sign, while in field properties it does not.)

Perform arithmetic calculations in forms

You can use formulas on fields to design forms that automatically calculate totals. This is common in scenarios such as timecards, invoices, job estimates, etc. For example, if an invoice form has several ItemCost fields and a Total field, you can use a formula like this to add up all the item costs:

number(ItemCost1)+number(ItemCost2)+number(ItemCost3)

Note: When you use the formula builder menu to insert some functions, such as Sum(), the formula will include handy placeholder ellipses (...) that you can double-click to enter a field name. If you need more field names than there are ellipses, simply enter the additional fields manually.

Another Addition Example:

../textbox1 + ../textbox2

This formula would be applied to a third field, for example textbox3, where the total would be displayed.  In other words, textbox3 would hold the total of textbox1 + textbox2. Rather than textboxes, dropdowns and radio buttons can have numeric values that you can use to get a total.

Example formoula on Total Cost: ../Item1 + ../Item2

 formula1.png

Multiplication Example:

number(../dropdown4) mul number(../dropdown5)

This formula will multiply the two dropdown values together.  In this example, you could have a dropdown with a display value of 'Computer', but the value being a price.  Then the other dropdown would be a quantity, and you can multiple the two to get a total/sum.

Example formula on Subtotal:  number(../Quantity) mul number(../Price)

formula2.png

Calculation in Repeating:

sum(/myFields/repeating_section1/repeating_section1group/Subtotal)

This formula can add up the sum of a value in a repeating section. For example, you can add rows and values that will continually increase the sum. As you add values, the sum will increase accordingly. This formula is set on the Total textbox and will continue to add from the Subtotal as rows and values are added.

 formula3.png

 

Substring() examples

You can show specific values from a formula using substring():

      =substring(today(), 1, 7)

This will show:  2019-08 

The substring takes  the value of today and only shows the first through seventh character, explaining the 1, 7 within the formula. The formula can also have more functions within it, or ex:

 

       =concat("Time Sheet", " ", substring(today(), 1, 7), " ", textbox1)

 

This will show: Time Sheet 2019-08 Value

The " " within the formula adds spaces between the functions, and textbox1 will retrieve whatever information is entered in that textbox.

 

Note: When using a Substring() formula, be sure to add an equals sign in front of it as shown above.  If there is no equal '=' sign, it will not work as expected.  

List of Available Functions

Math Functions

Sum - Adds the values of a list of fields

Subtract - Subtracts one field value from another

Multiply - Multiplies the values of a list of fields

Divide - Divides one field value by another

Round - Rounds a field value to the nearest integer

Floor - Rounds a field value down to the nearest integer

Ceiling - Rounds a field value up to the nearest integer

Text Functions

Username - Returns the email address of the logged in Formotus Pro user

Concat - Combines strings, field values and functions in a string

Contains - Checks whether one string contains another string

Substring-after - Returns the part of one string that occurs after another string

Substring-before - Returns the part of one string that occurs before another string

Starts-with - Checks whether one string begins with another string

Ends-with - Checks whether one string ends with another string

String-length - Returns the number of characters in a string

Translate - Converts a text string by replacing one list of characters with another list of characters

Compare - Checks if one string is the same as another

Time Functions

Today - Returns the current date

Now - Returns the current date and time

AddDays - Returns a date by adding days to a date

AddSeconds - Returns a time by adding seconds to a time

 

 

Tags: formulas, calculations, FormDesigner, logic
Pages/Eforms/FormDesigner.aspx

Have more questions? Submit a request

0 Comments

Article is closed for comments.