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

Skip Ahead to: Introduction Master Listformatting 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 in it named Name and you 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, 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.

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.
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.
4. On the Details tab, click Configure Formula. The Formula Builder appears.
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.
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 (+): The Plus operator is used to combine strings concatenate fields together or add numeric values.
2. Minus (-): The minus operator is often used in functions or expressions to denote subtraction or to remove elements from a field.
3. Divide (/): The Divide operator is used to split or divide strings or fields based on a delimiter or pattern.
4. Multiply (*): The multiply or repetition operator is used to duplicate strings or fields a certain number of times.
5. Greater than (>): The Greater than comparison operator is used to evaluate if one string or field comes after another in alphabetical order or some other defined sequence.
6. Less than (<): The Less than comparison operator is used to evaluate if one string or field comes before another in alphabetical order or some other defined sequence.
7. Greater than or equals to (>=): The Comparison operator is used to evaluate if one string or field comes after or is equal to another in alphabetical order or some other defined sequence.
8. Less than or equals to (<=): The Comparison operator is used to evaluate if one string or field comes before or is equal to another in alphabetical order or some other defined sequence.
9. Equals to (==): The equals to Comparison operator is used to check if two strings or fields are the same.
10. Not equals to (!=): The Not equals to Comparison operator is used to check if two strings or fields are not the same.
11. AND: The AND logical operator is used to combine conditions or filters to narrow down results based on multiple criteria.
12. OR: The OR logical operator is used to combine conditions or filters to broaden results based on multiple criteria.
13. NOT: The NOT logical operator is used to negate a condition or filter, excluding certain results from consideration.
14. Parenthesis (): The () operator is used to group conditions or functions together to control the order of evaluation or to define parameters for functions.

Formatters in Formula Builder

The following formatters 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
<=2000	30%
> 2000 and <= 5000	25%
>5000 and <=7500	20%
>7500 and <= 10000	15%
> 10000 and <= 15000	13.5%
>15000 and <= 20000	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 Settingsicon.
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 Functionstab 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.
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.