Forms Expression Builder

Learn to build expressions

Last published at: May 2nd, 2024

This function uses the Forms Expression Builder feature to build simple expressions. 

The expression builder (JQuery-based expression computed on client-side code) will display variables and globals based on the context. Note: The functions/variables/globals should be invoked with a double click.

On the Forms Designer page, you'll need to navigate to the Page - Functions menu option. 

 

Click on the (fx) image to invoke the Forms Expression Builder.

 

The Forms Expression Builder popup window is displayed for configuration. 

 

You must use the user interface to build and validate an expression. You may click the Verify button to validate this expression in the Result textbox.  The Use & Close button shall copy this expression to the form text field before closing the popup window. 

 

Parts of the user interface

1)    Expression – Placeholder to build the expression. This can be done via manual entry type or by picking an operators (7), function (8).

2)    Verify – Select this function to verify the logic of the expression. It will give you the option to enter a value to the defined input fields and simulate the expression. Example Expession: = CONCAT(“Dear, “ + variable.username + “, we’re today : “ +DAYTEXT(TODAY())

3)    Use & Close: The expression is use in the process or form control, depending on the starting point. Window gets closed.

4)    Save As – Saves the expression as a new function to re-use. Will be displayed in the functions overview (8) – Section Custom Expression

5)    Send to Clipboard : copies the expression into the clipboard, so it can be paste for later purpose.

6)    Clear Expressions: erases any definition in the expression builder

7)    Available Operators

8)    Available Functions (See below)

9)    Description and example of the function

 

Functions Aggregate Table

AVERAGE

Description: Returns the average of the given numbers
Example: AVERAGE(1,2,3) // returns 2

 

COUNT

Description: Counts the number of numerical items.
Example: COUNT(1, 2, "a") // returns 2

 

MAX

Description: Returns the largest value from a set of data
Example: MAX(3,15,27) // returns 27

 

MIN

Description: Returns the smallest value from a set of data
Example: MIN(3,15,27) // returns 3

 

SUM

Description: Sums up all the given numbers
Example: SUM(2,3,8) // returns 13

 

 

Functions Numeric Table

ABS

Description: Returns the absolute value of a given number
Example: ABS(-324) = 324

 

FLOOR

Description: Round a number down to the nearest specified multiple

Example: FLOOR(36,7) // returns 35

 

ISEVEN

Description: check if a numeric value is an even number.

Example: ISEVEN(7) // returns False

LOG

Description: Gets the logarithm of a number
Example: LOG (16,2) // returns 4

 

MOD

Description: Returns the remainder of the division of the given number in the divisor
Example:MOD(10,3) // returns 1

QUOTIENT

Description: Returns the result of integer division without the remainder
Example:MOD(10,3) // returns 3

 

POWER

Description: A number raised to a power
Example: POWER(2,3) // returns 8

 

ROUND

Description: Rounds a number to a specific number of digits
Example: ROUND(14.67891, 2) // returns 14.68

 

ROUNDUP

Description: Always rounds a number up
Example: ROUNDUP(21.6,0) // returns 22

 

ROUNDDWN

Description: Always rounds a number down
Example: ROUNDDOWN(1.1,0) // returns 1

 

SQRT

Description: Positive square root of a positive number
Example:SQRT(9) // returns 3

 

TRUNC

Description: Truncate a number to a given precision

Example: TRUNC(4.9) // returns 4

TRUNC(-3.5) // returns -3

TRUNC(PI(), 3) // returns 3.141

 

 

Function Date Time Table

ADD_DAYS

Description: Adds days to the given date. Returns the new date.  
Examples: ADD_DAYS("2021-04-15", 5)
ADD_DAYS(TODAY(), 5)
FORMAT_DATE(ADD_DAYS("2021-04-15", 5) // returns 2020-04-20

 

DATE

Description: Returns the value for the given parameters
Example: DATE(2021,4,15) // returns Wed April 15 2021 

 

DAYNUMBER

Description: Returns the day of the month of a given date.
Example: DAY(variable.startdate) // returns 31

 

DAYTEXT

Description: Returns the day of the month of a given date in long format.
Example: DAY(variable.startdate) // returns Monday

 

DAYS

Description: Returns the number of days between the two dates
Example:DAYS(variable.startdate, variable.enddate) // returns diff between dates in days

 

FORMAT_DATE

Description: Returns a formatted date
Examples: FORMAT_DATE(TODAY()) // returns "Apr 15, 2021" (using default format)
FORMAT_DATE(TODAY(), "YYYY-MM-DD") // returns "2021-04-15"
FORMAT_DATE(TODAY(), "dddd, MMMM Do YYYY") // returns Wednesday, April 15th 2021

 

HOUR

Description: Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Example: HOUR(NOW()) // returns 23 (in case it's 23:00 - 23:59 o'clock now)

 

HOURS_DIFF

Description: Returns the difference between two hours columns
Example: HOURS_DIFF("13:00", "07:00") // returns 05:00

 

WORKDAYS

Description: Returns the number of working days between the two dates
Example: WORKDAYS(variable.startdate, variable.enddate) // returns working days between dates in days

 

MINUTE

Description: Returns the minute as a number from 0 to 59.
Example: MINUTE(NOW()) // returns 42 (in case it's 11:42 now)

 

MONTH

Description: Returns the month of a given date.
Example: MONTH(variable.startdate) // returns 4

 

MONTHTEXT

Description: Returns the month of a given date in long format.
Example: DAY(variable.startdate) // returns March

 

NOW

Description: Gets the current time in 24hour format
Example: NOW () // returns 11:50:14

 

NOWUTC


 

Description: Gets the current time in 24hour format (UTC)
Example: NOWUTC () // returns 11:50:14


 

QUARTER

Description: Returns the quarter of a given date.

Example: MONTH(variable.startdate) // returns 2

 

SECOND

Description: Returns the second as a number from 0 to 59.
Example: SECOND(NOW()) // returns 1 (in case it's 11:42:01 now)

 

SUBTRACT_DAYS

Description: Subtract days from the given date. Returns the new date.  
Examples: SUBTRACT_DAYS("2021-04-15", 5)
SUBTRACT_DAYS(TODAY(), 5)
FORMAT_DATE(SUBTRACT_DAYS("2021-04-15", 5) // returns "2021-04-10

 

TODAY

Description: Gets the current date in current timezone format
Example: TODAY() // returns 14th Wednesday April 2021.

 

TODAYUTC


 

Description: Gets the current date in UTC Format (UTC)
Example: TODAYUTC() // returns 14th Wednesday April 2021.


 

WEEKNUM

Description: Returns the yearly week number of a given date.
Example: WEEKNUM(variable.startdate)// returns 17

 

YEAR

Description: Returns the year of a given date.
Example: YEAR(global.startyear) // returns 2017

 

 

Functions String / Text

CONCAT

Description: This operator concatenate text values into a single text value
Example: CONCATENATE(“I”,”love”,”FlowWright.com”) // returns “IloveFlowWright.com”

 

EXACT

Description: compares two strings and returns TRUE if both values are the same

Example: EXACT(variable.username, global.username) // returns TRUE – if the value of both variable and global are the same

 

LEFT

Description: Extracts a given number of characters from the left side.
Example: LEFT("flowwright.com", 4) // returns “flow”

 

LEN

Description: Returns the amount of characters of a given text string.
Example: LEN("hello") // returns 5

 

LOWER

Description: Converts a specified string to lowercase
Example: LOWER("Have a Nice Day") // returns “have a nice day”

 

MID

Description: Extracts a given number of characters from the middle of a supplied text string.

Example: MID(“I Like FlowWright”, 3,4) // returns “Like”

REPLACE

Description: Replaces a part of a string with the new string.
Example: REPLACE("Goat",1, 2,"Fl") // returns “Flat”

 

REPT

Description: Repeats a string a given number of times.
Example: REPT("Flow",3) // returns “FlowFlowFlow”

 

RIGHT

Description: Extracts a number of characters from the right side of a given text string.
Example: RIGHT("FlowWright", 4) // returns “right”

 

SEARCH

Description: Searches a string within another string
Example: IF(SEARCH("love", "I love FlowWright", 1) > 0, "Exist", "Not") // returns “exist”

 

SUBSTITUTE

Description: Replace text in a given text string by matching.
Example: SUBSTITUTE("goodmorning", "morning", "night") // returns “goodnight”

 

TEXT

Description: Formats the given value based on the given text format
Example: TEXT(9340.1,"#,##0.00€") // returns “9,340.10€”

 

TRIM

Description: Removes all spaces from a string except for single spaces between words.
Example: TRIM(" I love FW ") // returns “I love FW”

 

UPPER

Description: Convert a specified string to uppercase
Example: UPPER(“www.flowwright.com”) // returns “WWW.FLOWWRIGHT.COM”