Generate dynamic data using Formulas to Optimise Test Data

Formulas in the Test Data module allow for dynamic data creation, reducing the need for manual input and enabling efficient test case execution. It help boost performance and optimise test scenarios by generating valid data on the go with minimal user effort.

How to use formulas to generate dynamic data?

• To add formula for a parameter, go to the Test Data module in the test case.

• Select the parameter where you want to use the formula. To do so, simply add '=' which will show a dropdown of all formulas. Select or type the formula as shown in the table below and add the required parameters or strings required by the formula.

How to parameterise a formula?

• To do so, call the equation and add double curly brackets { { inside the Parentheses. A dropdown will appear showing all available parameters, including Local, Global, and Runtime parameters.

• Select the parameter from the list to data drive the formula by locally defining the value or to pick the data during the execution from a runtime parameter.

List of Formulas

Arithmetic Formulas

FORMULA	DESCRIPTION	EXAMPLE	PARAMETERS
SUM(number1, [number2], ...)	Returns the sum of all given numbers	=SUM(10, 20, 30) returns 60	number1: Numeric arguments to sum. number2: Numeric arguments to sum.
AVERAGE(number1, [number2]	Calculates the average of the given numbers.	=AVERAGE(10, 20, 30) returns 20	number1: Numeric arguments for which you want the average. number2: Numeric arguments for which you want the average.
SUBTRACT(number1, [number2], ...)	Subtract the given numbers together.	=SUBTRACT(30,20) returns 10	number1: Numeric arguments to subtract. number2: Numeric arguments to subtract.
MULTIPLY(number1, [number2], ...)	This function multiplies all the numbers provided as parameters.	=MULTIPLY(1, 2, 3) returns the product as 6	number1: The first number or range to be multiplied. [number2]: Additional numbers or ranges to be multiplied.
MODULUS(number, divisor)	Returns the remainder after dividing one number by another. It is often used to determine if a number is even or odd, or to perform cyclic operations.	=MOD(10, 3) returns 1, because when 10 is divided by 3, the remainder is 1	number: The number to be divided (the dividend) divisor: The number by which to divide (the divisor)

String Manipulation

FORMULA	DESCRIPTION	EXAMPLE	PARAMETERS
CONCATENATE(“text1”, [text2], ...)	Joins several text strings into one.	=CONCATENATE("Hello", " ", "World")	text1: string to be joined into a single string text2: string to be joined into a single string.
LEFT(text, [num_chars])	Returns the first character(s) from a text string, starting from the left.	=LEFT("Hello World", 5) returns "Hello". If num_chars is not specified, =LEFT("Hello World") returns "H".	text: The text string from which to extract the characters. [num_chars]: (Optional) The number of characters to extract from the left side of the text. If omitted, it defaults to 1.
RIGHT(text, [num_chars])	Returns the specified number of characters from the end of a text string.	=RIGHT("Hello World", 5) returns "World".	text: The string from which you want to extract the characters. [num_chars]: (Optional) The number of characters to extract from the end. Default is 1.
MID(text, start_num, num_chars)	Extracts a substring from a text string, starting at a specified position.	=MID("Hello World", 7, 5) returns "World"	text: The string from which you want to extract the characters. start_num: The position of the first character to extract (1-based). num_chars: 'The number of characters to return from text.
LOWER(text)	Converts a text string to lowercase.	=LOWER("Hello World") returns "hello world"	text: The text string which you want to convert text to lowercase.
UPPER(text)	Converts a text string to uppercase.	=UPPER("Hello World") returns "HELLO WORLD".	text: The text string which you want to convert text to uppercase.
PROPER(text)	Capitalises the first letter of each word in a text string	=PROPER("hello world") returns "Hello World"	text: The text string in which you want to capitalise the first letter in each word
SPLIT(text, delimiter, index)	Splits a text string into multiple parts based on a specified delimiter.	=SPLIT("Hello,World,Excel", ",") splits the text "Hello,World,Excel" into three parts: "Hello", "World", and "Excel"	text: The text string to split. delimiter: The character or string used to divide the text. index: The position of the part to return (1-based). For example, 1 returns the first part, 2 returns the second part, and so on.

Date & Time

FORMULA	DESCRIPTION	EXAMPLE	PARAMETERS
NOW()	Returns the current date and time based on your system's date & time format.	=NOW() would return 08/23/2024 12:20 pm based on your system's date time format
TODAY()	Returns the current date as per local system date format.	=TODAY() would return 08/08/2024 if today’s date is 8th August 2024.
DAY()	Returns the day of the month, a number from 1 to 31.	=DAY("01/08/2024") returns 1
MONTH()	Returns the month, a number 1 (January) to 12 (December).	=MONTH("01/08/2024") returns 8.
YEAR()	Returns the year from a date.	=YEAR("01/08/2024") returns 2024.
HOUR()	Extracts the hour from a time value.	=HOUR("12:30:45 PM") returns 12.
MINUTE()	Extracts the minute from a time value.	=MINUTE("12:30:45 PM") returns 30.
SECOND()	Extracts the second from a time value.	=SECOND("12:30:45 PM") returns 45.

Synthetic Data

FORMULA	DESCRIPTION	EXAMPLE	PARAMETERS
GENDER()	Generates gender/sex information.	=GENDER() would return "Male"
PREFIX(sex)	Returns a prefix (Mr., Mrs., etc.) based on the specified sex.	=PREFIX("Male") returns "Mr."	sex: The gender for which to get the prefix ("Male" or "Female")
FIRST_NAME(country name, sex, min, max)	Generates a first name based on the specified country and sex.	=FIRST_NAME("United States", "Male", 3, 10) would return "John"	country name: The name of the country sex: The gender for the name ("Male" or "Female") min: The minimum length of the name max: The maximum length of the name
LAST_NAME(country name, sex, min, max)	Generates a last name based on the specified country and sex.	=LAST_NAME("United States", "Male", 3, 10) would return "Smith"	country name: The name of the country sex: The gender for the name ("Male" or "Female") min: The minimum length of the name max: The maximum length of the name
FULL_NAME(Country, Sex, MinLength, MaxLength)	Generates a full name based on the specified country and sex.	=FULL_NAME("United States", "Male", 3, 10) would return "Smith".	country name: The name of the country. sex: The gender for the name ("Male" or "Female") min: The minimum length of the name. max: The maximum length of the name.
MIDDLE_NAME(Country, Sex, MinLength, MaxLength)	Generates a middle name based on the specified country and sex.	=MIDDLE_NAME("United States", "Male", 3, 10) would return "Smith".	country name: The name of the country. sex: The gender for the name ("Male" or "Female") min: The minimum length of the name. max: The maximum length of the name.
DATE_OF_BIRTH(dd/MM/yyyy, MinAge, MaxAge)	Generates a date of birth within the specified range.	=DOB("dd/mm/yyyy", 5, 25) would return "15/06/1985".	dd/mm/yyyy: The format for the date of birth. MinAge: The start date of the range. MaxAge: The end date of the range.
COUNTRY_NAME	Returns the name of a country randomly.	=COUNTRY() would return "United States".
CREDIT_CARD_CVV(“Country name”)	Generates a credit card CVV code based on the specified country.	=CREDIT_CARD_CVV("US") would return "123"
CREDIT_CARD_NUMBER(“Country Name)	Generates a credit card number based on the specified country.	=CREDIT_CARD_NUMBER("US") would return "123".	Country Name: The country code for which to generate the card number
CURRENCY_CODE(“country name”)	Returns the currency code for the specified country.	=CURRENCY_CODE("United States") returns "USD"	Country Name: The name of the country for which to get the currency code.
CURRENCY_NAME(“country name”)	Returns the currency name for the specified country.	=CURRENCY_NAME("United States") returns "Dollar".	Country Name: 'The name of the country for which to get the currency code.
CURRENCY_SYMBOL(“country name”)	Returns the currency symbol for the specified country.	=CURRENCY_SYMBOL("United States") returns "$".	Country Name: 'The name of the country for which to get the currency code.
PHONE_NUMBER("country name")	Generates a phone number based on the specified country.	=PHONE_NUMBER("United States") would return "[phone]".	Country Name: The name of the country for which to get the phone number.
COUNTRY_CODE(“country name”)	Returns the country code for the specified country.	=COUNTRY_CODE("United States") would return"123"	Country Name: The name of the country for which to get the country code.
STATE(“country name”)	Returns a postal code for the specified country.	=STATE("United States") might would "California"	Country Name: 'The name of the country for which to get the state.
CITY("country name")	Returns a name of a city randomly based on the entered country name. Parameter: country name - The name of the country for which you want to retrieve city information	city("france") might return the value as Paris
STREET(“country name”)	Returns a street address for the specified country.	=STREET("United States") would return "123 Main St	Country Name: The name of the country for which to get the street.
ADDRESS(“country name”)	Returns a complete address for the specified country.	=ADDRESS("United States") would return "123 Main St, Springfield, IL, 62701"	Country Name: The name of the country for which to get the full address.
ZIPCODE(“country name”)	Returns a state/province for the specified country.	=ZIPCODE("United States") would return "62701".	Country Name: The name of the country for which to get the zipcode.
TIMEZONE(“country name”)	Returns the time zone for the specified country.	=TIME_ZONE("United States") would return "Central Standard Time".	Country Name: The name of the country for which to get the timezone.
ONCE(Formula)	Ensures that a dynamic formula is evaluated only once per test case execution. The result remains constant throughout the execution, and a new value is generated on subsequent executions. =ONCE() is invalid for static values (e.g., =ONCE("John")). It supports various dynamic formulas like FIRSTNAME(), LASTNAME(), EMAIL() etc and parameterization with {{...}}.	=ONCE(=FIRSTNAME("India")) generates a first name once during the test run and retains the value until the test is executed again.	Formula: The dynamic formula to be evaluated. Can include functions like FIRSTNAME(), EMAIL(), etc. Supports parameterization with {{...}}.' }] } }

Excel

FORMULA	DESCRIPTION	EXAMPLE	PARAMETERS
READ_FROM_EXCEL ([file-path], [sheet-name], [cell-reference])	Fetches data from the specified Excel file during test execution. The fetched value is used as test data for the parameter.	=ReadFromExcel("C:\\Data\\Test.xlsx", "Sheet1", "A1")	File path: The path to the Excel file.an be a local file path or a repository path. Supports Sheet Name: The name of the sheet in the Excel file to read from. Supports Cell Reference: The reference to the cell (e.g., A1, B2) whose value needs to be fetched. Supports parameterisation.

Note: When you preview data in the Test Data popup, the dynamically generated values are visible. Additionally, every execution generates fresh, unique data, ensuring realistic and varied test inputs each time your tests run.