In this article, we will help you to understand about the Advanced Field Types in Form 2.0.

Skip Ahead to:

Introduction

Master List formatting

Lookups

Formula field

Configuring a Formula in the Formula Builder

Functions in Formula Builder

Operators in Formula Builder

Conditions in Formula Builder

Introduction

As the names suggest the advanced field types have advanced capabilities to define a filed type in Form 2.0. There are three advanced field types in Form 2.0.

Master List

Form 2.0 Configuration allows you to use the master list as the field value in a form. You can use the project master list as well as the organization master list in the form to use as the values of a drop-down list.
Details

Fields  Description
Display Label Enter the name that you want to give to the field. It will be visible to the users in the form, such as First Name, Last Name, etc.
Field Name Enter a unique name that will be only used by the system to identify this field. It will not be visible to a user.
Helper text Enter a helper text that will be visible to the user inside the field so that the user can enter the relevant information. For example, you can enter helper text for a name such as – Enter a name without a title.
Dropdown type  It has two options – Single-select and Multi-select. Select the appropriate option to specify whether you want the user to select one value at a time or multiple values at a time.
Enable search-select  Turn this toggle On to allow the user to search the values in the drop-down by entering the initials of the value. For example, in the list of Countries, entering the character “in” will list all the countries having the letters “in” in it such as India, Indonesia, etc.
Search Master List Select a master list from the drop-down list that you want to use in your field.

Validations

Mandatory Set the toggle on if you want to make your field mandatory.
Hide Field Set the toggle on if you want to hide this field.
Field Type Set whether you want to set this field as read-only or editable.

Lookups

Form 2.0 configuration allows you to use a field from an existing form to your form. For example, if you have a form named Order and a field named Name and want to use the same Name field in a new form called Purchase Order, then you can do that with the help of Lookups.  A Lookup field has the following settings –
Details 

Fields  Description
Display Label Enter the name that you want to give to the field. It will be visible to the users in the form, such as First Name, Last Name, etc.
Field Name Enter a unique name that the system will only use to identify this field. It will not be visible to a user.
Helper text Enter a helper text that will be visible to the user inside the field so that the user can enter the relevant information. For example, you can enter helper text for a name such as – Enter a name without a title.
Dropdown type  It has two options – Single-select and Multi-select. Select the appropriate option to specify whether you want the user to select one value at a time or multiple values at a time.
Enable search select  Turn this toggle On to allow the user to search the values in the drop-down by entering the initials of the value. For example, in the list of Countries, entering the character “in” will list all the countries having the letters “in” in it such as India, Indonesia, etc.
Search Master List Select a master list from the drop-down list that you want to use in your field.
Lookup form Select the form which field you want to use in your form.
Lookup field When you select the form, all the fields of that form along with its block name appear in this list. Select the field that you want to select. If a segment of a form has two fields with the same name, then both fields will be listed in this list. The field names are followed by the segment name in the list so that it is easy to select the right field.
Add fields of Lookup Form Based on the Lookup field chosen by the configurator list of fields would appear in this section. With the help of this property, the configurator would be able to set the associated field for the lookup. These fields are read-only fields whose values appear based on the value set on the main lookup field. For example, In the purchase order form, there is a lookup field Vendor name, and the associated field is Vendor Location which is referenced from the Vendor Form. End users when they create a purchase order form by selecting the value of the Vendor Name field, the Vendor Location field value would get the value.     

Validations

Mandatory Set the toggle on if you want to make your field mandatory.
Hide Field Set the toggle on if you want to hide this field.
Field Type Set whether you want to set this field as read-only or editable.

While selecting a lookup field from a form, you can also select the associated field of a lookup field by simply selecting the check boxes of the relevant fields. The associated fields are non-editable/read-only.  In the given image, The Order Number is the Lookup field from the Item form while the Price, Amount, Discount, etc are the associated field.
Lookup

Formula

Form 2.0 configuration allows you to add a formula in a field so that it automatically calculates the values depending upon the formula and shows the calculated value in the form. This is useful when you want to design a form that automatically calculates values and saves a lot of human effort and chances of mistakes in manual calculation.
Details 

Fields  Description
Display Label Enter the name that you want to give to the field. It will be visible to the users in the form, such as First Name, Last Name, etc.
Field Name Enter a unique name that the system will only use to identify this field. It will not be visible to a user.
Helper text Enter a helper text that will be visible to the user inside the field so that the user can enter the relevant information. For example, you can enter helper text for a name such as – Enter a name without a title.
Field icon Select an icon from the available list of icons to display with the filed name in the form.
Formula  Enter a unique name for the formula that you are configuring in the formula builder.

Validations

Mandatory Set the toggle on if you want to make the field mandatory.
Hide Field Set the toggle on if you want to hide the field.
Field Type Set whether you want to set this field as read-only or editable.

Configuring a Formula in the Formula Builder

The Formula Builder in Form 2.0 allows you to configure a formula and apply it to the formula field so that the value of the field is automatically calculated based on the given formula. It has some predefined functions, conditions, and operators to help you configure a formula. The left panel in the formula builder contains all the conditions and functions while the bottom bar contains all the available operators.

Formula Builder

To configure a formula, perform the following steps –

  1. Insert the formula field in the required field segment of the form.
  2. Clock the formula field. The Reposition, Settings, and Delete icons are enabled.
  3. Click the Settings icon.
    Formula Settings
  4. On the Details tab, click Configure Formula. The Formula Builder appears.
    Formula Builder
  5. On the left pane, click the Block Fields tab and expand the required field segment.
  6. Drag the required field name to the Configure area. You can also enter # and select the required field name from the list.
  7. Click the required operator at the bottom bar. You can also enter the required operator with the keyboard.
  8. To insert a function in the field or formula, click the Functions tab and drag the required function. You can also enter @ and select the required function.
    Formula Builder
  9. Once the formula is configured, click the Save button.

The value of the field will be calculated automatically depending on the formula defined in the formula builder.

Functions in Formula Builder

The Formula Builder supports the Date and Time, Numeric, and String functions as mentioned in the given table.
Please note, for all the users, the date is returned in the format selected by the user in the User Preferences.

Function Description

Date and Time Functions

DATEADD

The DateAdd function adds a specified number of units to the Date/Time value. Users can also subtract a specified number of units from a date or time value by specifying a negative value. Units can be years, months, weeks, days, hours, minutes, seconds.
Syntax:
DATEADD[dateTime, number, units(ex days, hours)]
Example:
DATEADD[17-Mar-2024 09:57:29, 02, days]
The function adds 2 days to the dateTime and returns 19-Mar-2024 09:57:29 as the output.
DATEADD[17-Mar-2024 09:57:29, 02, hours]
The function adds 2 hours to the dateTime and returns 17-Mar-2024 11:57:29 as the output.

DATE

The DATE function returns the date value from the year, month and day values the user entered.
Syntax:
DATE[year, month, day]
Example:
DATE[2021, 01, 26]
It returns 26-Jan- 2023 as the output.

DATEDIFF

The DATEDIFF function returns the difference between two date/time values. The Units can be years, months, days, hours.
Syntax:
DATEDIFF[date1, date2, units(ex days, hours)]
Example:
DATEDIFF[BlockName.DOB, TODAY, years]
Here BOB is a field name in the form.
The function will return the age/number of years between DOB and today.
DATEDIFF[17-March-2024, 19-March-2024, days]
The function will return 2 as the output.

DATEVALUE

The DATEVALUE function converts a date string to a date value. The Date string should be in DD-Mon-YYYY format only.

DATETIMEVALUE

The DATETIMEVALUE function converts a date and time string to a date and time value. The date and time string should be in DD-Mon-YYYY hh:mm:ss format only.

TIMEVALUE

The TIMEVALUE function converts a time string to a time value. The time string should be in hh:mm:ss format only.

TIMENOW

Returns a time value in GMT representing the current moment. This function is useful if you only want to track time, without a date. Note: The NOW function returns the date and time while the TIMENOW function only returns the time.
Syntax:
TIMENOW
Example:
TIMENOW
If the current moment is 10:23:54 and the date is 17-Mar-2024 then it returns 10:23:54 (only time) as output.

TODAY

The TODAY function returns the current date as a date data type.
Syntax:
TODAY
Example:
TODAY
If today is 17-Mar-2024 then it returns 17-Mar-2024 as output.

WEEKDAY

The WEEKDAY function returns the weekday of a Date/Time value. By default, the result ranges from 1 (Sunday) to 7 (Saturday).
Syntax:
WEEKDAY[dateTime]
Example:
WEEKDAY[17-Mar-2024 09:57:29]
Since 17-Mar-2024 is Sunday, it returns 1 as the output.
WEEKDAY[NOW]
If the current day is Friday, it returns 6 as the output.

DAY

The Day function returns the day component of a Day/Time value ranging from 1 to 31.
Syntax:
DAY[dateTime]
Example:
DAY[17-Mar-2024]
It returns 17 as the output.

HOUR

The Hour function returns the hour component of a Date/Time value ranging from 0 (12:00 AM) to 23 (11:00 PM).
Syntax:
HOUR[dateTime]
Example:
HOUR[17-Mar-2024 09:57:29]
It returns 09 as the output.
HOUR[NOW]
It returns the hour component of the current time.

MINUTE

The Minute function returns the minute component of a Date/Time value ranging from 0 to 59.
Syntax:
MINUTE[dateTime]
Example:
MINUTE[17-Mar-2024 09:57:29]
It returns 57 as the output.
MINUTE[NOW]
It returns the minute component of the current time.

MONTH

The Month function returns the month component of a Date/Time value ranging from 1 to 12.
Syntax:
MONTH[dateTime]
Example:
MONTH[17-Mar-2024]
It returns 3 as the output.
MONTH[NOW]
It returns the number of the current month.

NOW

Returns the date and time representing the current moment.
Syntax:
NOW
Example:
NOW
If the current moment is 10:23:54 and the date is 17-Mar-2024 then it returns 17-Mar-2024 10:23:54 as output.

SECOND

The Second function returns the second component of a Date/Time value ranging from 0 to 59.
Syntax:
SECOND[dateTime]
Example:
SECOND[17-Mar-2024 09:57:29]
It returns 29 as the output.
SECOND[NOW]
It returns the second component of the current time.

YEAR

The Year function returns the year component of a Date/Time value starting from 1900.
Syntax:
YEAR[dateTime]
Example:
YEAR[17-Mar-2024]
It returns 2024 as the output.
YEAR[NOW]
It returns the current year.

Numeric Functions

ABS

The ABS function returns the absolute (positive) value of a number.

Syntax:

ABS[number]

Example:

ABS[-5]

Output – It returns 5 as the output.

AVERAGE

The Average function calculates the arithmetic mean of a set of numbers by adding them up and dividing by the count of numbers in the set.

Syntax:

AVERAGE[number1, number2, number3]

Example:

AVERAGE[10, 20, 30]

Output – It returns 20 as the output.

CEILING

The Ceiling function rounds a number up to the nearest integer. It always returns the smallest integer greater than or equal to a given number.

Syntax:

CEILING[number]

Example:

CEILING[80.239]

Output – It returns 80 as the output.

EXP

The Exp function Returns the value of Euler’s number (approximately 2.71828) raised to the power of a specified number.

Syntax:

EXP[number]

Example:

EXP[2]

Output – It returns 7.3890561 as the output.

FLOOR

The Floor function rounds a number down to the nearest integer. It always returns the largest integer less than or equal to a given number.

Syntax:

FLOOR[number]

Example:

FLOOR[2.0902]

It returns 2 as the output.

FLOOR[-4.623]

Output – It returns -5 as the output.

LOG

The Log function calculates the logarithm of a number. Common logarithms are base 10, and natural logarithms are base e.

Syntax:

LOG[number]

Example:

LOG[10]

Output – It returns 1 as output.

MAX

The Max function returns the largest number in a set.

Syntax:

MAX[number1, number2, number3]

Example:

MAX[100, 160, 500, 40]

Output – It returns 500 as output.

MIN

The Min function returns the smallest number in a set.

Syntax:

MIN[number1, number2, number3]

Example:

MIN[100, 160, 500, 40]

Output – It returns 40 as output.

MOD

The Mod function returns the remainder of a division operation.

Syntax:

MOD[number, divisor]

Example:

MOD[17,5]

Output – It returns 2 as output.

POWER

The Power function raises a number to a specified power.

Syntax:

POWER[base, exponent]

Example:

POWER[5,3]

Output – It returns 125 as output.

ROUND

The Round function rounds a decimal number to the nearest digit. If the fractional part is exactly halfway between two digits, the result is the nearest to the even digit.

Syntax:

ROUND[number, decimalPlaces]

Example:

ROUND[221.8799, 2]

Output – It returns 221.88 as output.

SUM The Sum function calculates the total of a set of numbers by adding them together.
Syntax:
SUM[number1, number2]
Example>:
SUM[150,26]
Output – It returns 176 as output.
SQRT

The SQRT function calculates the square root of a number.

Syntax:

SQRT[number]

Example:

SQRT[25]

Output – It returns 5 as output.

String Functions
CONCAT The CONCAT function concatenates the string arguments and returns a new string.
Syntax:
CONCAT[string1, string2, string3]
Example:
CONCAT[Hello, World, !!]
Output – Hello World !!
CONCAT[BlockName.FirstName,  , BlockName.LastName]
It returns the first name and last name with a space between them.
The CONCAT function uses string/alphanumeric values as its parameters. If you want to use a number or date value as its parameter then you can use the TEXT function.
Example:

CONCAT[TEXT[Serial No.], Name]

CONTAINS The CONTAINS function compares two arguments of string (case sensitive) and returns TRUE if the first segment contains the second argument. If not, returns FALSE.
Syntax:
CONTAINS[string, pattern]
Example:
CONTAINS[Useful, use]
Output – It returns true as the word useful contains “use” in it.
CONTAINS[Useful, color]
Output – It returns false as the string “color” is not part of the word useful.
ENDSWITH The ENDSWITH function tests whether the string (first argument) ends with the pattern (Second argument). The test is case-insensitive.
Syntax:
ENDSWITH[string, pattern]
Example:
ENDSWITH[Hello world, world]
Output – It returns true as the word “Hello world” ends with the word “world”.
LEFT The LEFT function returns the specified number of characters from the beginning of a string.
LEN The LEN function returns the number of characters in a specified string.
LOWER The LOWER function converts all letters in the specified string to lowercase.
MID The MID function returns the specified number of characters from the middle of a string given the starting position.
NOTCONTAINS
RIGHT The RIGHT function returns the specified number of characters from the end of the string.
STARTSWITH The STARTSWITH function tests if the string (first argument) starts with the pattern (second argument).
Syntax:
STARTSWITH[string, pattern]
Example:
STARTSWITH[Hello world, Hello]
Output – It returns true as the word “Hello world” starts with the word “Hello”.
SUBSTITUTE The SUBSTITUTE function replaces all the occurrences of searchString(second argument) in the string (first argument) with the given repalceString (third argument) value. This function is case-sensitive.
TEXT The TEXT function converts any value and formats a number or date/time value to a string of text.
TRIM The TRIM function removes all the spaces in the string except for the single space between the words.
UPPER The UPPER function converts all letters in the specified string to uppercase.
FIND The FIND function looks for a string within another string and is case-sensitive. FIND returns the starting position of the string that was found. It returns blank if the string in which you are searching doesn’t contain the string for which you are searching.

Operators in Formula Builder

The following operators are available in the Formula Builder to configure a formula.

  1. Plus (+): Adds two numbers together.
  2. Minus (): Subtracts the second value from the first value.
  3. Divide (/): Divides the first value by the second value.
  4. Multiply (*): Multiplies two values.
  5. Greater than (>): Compares if the first value is greater than the second value.
  6. Less than (<): Compares if the first value is less than the second value.
  7. Greater than or equals to (>=): Compares if the first value is greater than or equal to the second value.
  8. Less than or equals to (<=): Compares if the first value is less than or equal to the second value.
  9. Equals to (==): Checks if the first value is equal to the second value.
  10. Not equals to (!=): Checks if the first value is not equal to the second value.
  11. AND: it returns true if both conditions are true.
  12. OR: It returns true if at least one condition is true.
  13. NOT: It negates the result of a condition.
  14. Parenthesis (): Used for grouping expressions or operations to dictate the order of evaluation.

Formatters in Formula Builder

The following formats are available in the Formula Builder to format a formula.

  1. \n: It represents a newline character, used to format text or fields by indicating the start of a new line.
  2. \t: It represents a tab character, used to format text or fields by indicating horizontal spacing or indentation.

IF ELSE Conditions in Formula Builder

The IF-ELSE condition helps you set a field’s value based on a condition you define for the formula. Here’s how it works:
IF Section: Define a condition. If it’s true, the formula returns True and returns the value you specify here.
ELSE Section: If the condition is not true, the formula returns False and returns the value you specify in this section.
Multiple Conditions: You can have multiple IF-ELSE conditions or nest them within an IF condition for more complex formulas.
For example, a company is giving discounts on its electronic products in different ranges as shown in the below table. So we can define such kind of condition using the If Else condition of the formula builder for a formula field.

Price Discount
<=5000 5%
 >= 5000 10%

Here the value in the Discount field is calculated automatically as per the value in the Price field and the conditions defined in the Discount field.
To define the condition for a field, perform the following steps –

  1. Click on the field whose field type is defined as the Formula field.
  2. Click the Settings icon.
  3. Click Configure Formula.
  4. From the left panel, on the Functions tab, click Condition.
  5. Drag the IF THEN ELSE condition to the right pane.
  6. To enter a function in the IF box, from the left plane, click the Functions tab and drag the required function. You can also type @ and select the required function.
  7. To enter a field in the IF box, from the left plane, click the Block Fields tab, click the required Form and drag the required field. You can also type # and select the required field.
  8. To enter an operator in the IF box, click the operator at the bottom of the screen. You can also type & and select the required operator.
  9. To define a condition for the discount field according to the Price field, type # and select the Price field from the list.
  10. Define the conditions as shown in the table.
    If Else
  11. Click the Save button.

Once you publish the form, the value of the Discount field is calculated automatically as per the value in the Price field. Similarly, you can define conditions including functions and operators in the IF box.

Defining a Formula Manually

You can manually define a formula using the shortcuts/ special characters to include the functions, form fields, and operators with the help of given symbols –

  1. Use the @ symbol to include a function in a formula. As soon as you type or click the @, a list of functions appears. You can select the required function and define its parameters.
  2. Use the # symbol to include a form field in a formula. As soon as you type #, a list of forms appears. Clicking a particular form shows the list of available fields in it. Click the field which you want to insert in your formula.
  3. Use the & symbol to insert an operator in your formula. As soon as you enter the & symbol, it shows the categories of the operators, clicking a particular category shows all the operators in it. Click the operator that you want to select.
  • Was this helpful?
  • Yes   No