Using Expressions for Formula-fields and Validations

iFreeTools Creator supports defining expressions for use with formula fields and validations of input values. The formula expressions can be provided for any data type and the result of the expression needs to be in the required data-type.

Apart from providing formulas to derive computed values for fields, you can also define validation expressions to match required expressions.

The Formula Expression, Alias and Validation Expression configurations are available for all attributes under the Advanced section of the add/edit attribute form, as shown below.

The following sub-sections provide more details on using expressions with iFreeTools Creator.

Accessing field values within expressions

Within the expressions, one can access the field values from the record which is to be added / updated into the database. One can refer to those values using aliases (if you have provided them) or directly using the display names.

For example, for a text-field named Full Name or just Name, the formula expression can be val ("First Name") +" "+val ("Last Name") where First Name and Last Names are display names for text fields within the same record.

If those fields were provided with aliases, like say First_Name and Last_Name, then the expression can be First_Name + " " + Last_Name.

Accessing user input value in validation expressions

Within validation expressions, the record values will be validated even before it is saved into the database. In such expressions, you will have to use the inputValue variable name to access the validated field's current value, as entered by the user in the form.

To validate that the value entered by user is exactly 10 characters length, the expression will be len(inputValue) == 10. You can also access the values of fields listed before the validated field by providing their alias or by using the val function, but if the fields listed below the validated field are accessed, the value returned will be None (in case of adding a new record) or the old-value (in case of editing an existing record).

The different operators and functions available for use with expressions are detailed below.

Accessing old-value in Criteria Expression of Workflow

When defining criteria expressions to check if the workflow is to be executed, you may sometimes want to check the old value with the new value. You can do this by using the old_val function.

For example, if one were to check if the date value has changed, the criteria expression can be..
old_val ("Attribute_Alias_Or Display_Name") != val ("Attribute_Alias_Or Display_Name")

Arithmetic & Concatenation Operators

+Addition, works for numeric, text and list values.

For numeric values adds them to provide the sum.
Example :
5 + 2 = 7,
5 + 2.0 = 7.0

For text, concatenates them to form a new joined text.
Example :
"big" + "ger" = "bigger"
"big" + str(123) = "big123"

For list, makes a bigger list by adding both the lists items.
Example :
[11, 22, 33] + [44, 55] = [11, 22, 33, 44, 55]
["list", "values"] + ["more" , "values"] = ["list", "values", "more", "values"]
Subtraction, applicable for numeric fields alone.
Example :
5 - 2 = 3,
5 - 2.0 = 3.0
Multiplication, applicable for numeric values, text-with-integer and list-with-integer values.

For numeric values, provides a product of those values.
Example :
5 * 2 = 10,
5 * 2.0 = 10.0

For text-with-integer, concatenates the text the specified number of times .
Example :
"big" * 2 = "bigbig"

For list-with-integer, makes a bigger list by concatenating the list items the specified number of times.
Example :
["and" ,"again"] * 3 = ["and" ,"again", "and" ,"again", "and" ,"again"]
Division, applicable for numeric fields alone.
Example :
5 / 2 = 2,
5 / 2.0 = 2.5
Modulo, applicable for numeric fields alone.
Example :
5 % 2 = 1,
5 % 2.0 = 1.0

Comparison Operators

< Less-than
<= Less-than or equal
>=Greater-than or equal
!= Not equal
inContainment check, applicable for list values.

Example :
33 in [11, 22, 33, 44, 55] = True,
"orange" in ["apple", "orange", "grape"] = True,
"potato" in ["apple", "orange", "grape"] = False

Logical Operators

and Logical AND

Usage : expr1 and expr2
Result will be True when both expr1 and expr2 are True, else False.
or Logical OR

Usage : expr1 or expr2
Result will be True when either expr1 or expr2 is True, else False.
notLogical NOT

Usage : not expr1
Result will be True when expr1 is False, else True.

Data Type Conversion Functions

strReturns the string (text) equivalent of the parameter. If your text can contain unicode (non-English) characters, it is recommended that you use the unicode / uc function, when working with those text values.
Returns the unicode value of the parameter.
intReturns the integer value of the parameter.
floatReturns the float value of the parameter.
join(listV [, sep])Returns the string value with all the items in the list concatenated with the separator param, sep, which when not supplied will be ", " (a comma and a space).

Example :
join(["item1", "item2", "item3"]) returns "item1, item2, item3"
join(["item1", "item2", "item3"], "--") returns "item1--item2--item3"

String Functions

ltrimReturns the text with additional spaces (if any) on the left being trimmed out.
Example : ltrim(" some text ") ==> "some text "
rtrimReturns the text with additional spaces (if any) on the right being trimmed out.

Example : rtrim(" some text ") ==> " some text"
trimReturns the text with additional spaces (if any) on the left and right being trimmed out.

Example : trim(" some text ") ==> "some text"
uppercaseReturns the text with all letters converted to uppercase.

Example : uppercase("Some Text") ==> "SOME TEXT"
lowercaseReturns the text with all letters converted to lowercase.

Example : lowercase("Some Text") ==> "some text"
capitalizeReturns the text with the first letter capitalized.

Example : capitalize("some text") ==> "Some text"
titlecaseReturns the text with the first letter of all words converted capitalized.

Example : titlecase("some text") ==> "Some Text"
swapcaseReturns the text with all letters swapped from the current case.

Example : lowercase("Some Text") ==> "sOME tEXT"
lenReturns the total number of characters in the text. This also works list, returning the number of items in the list.
Example :
len("Some Text") returns 9
len(["item1", "item2"]) returns 2
findUsage :  find (stringVal, stringToFind) 
Returns the first index of the stringToFind within stringVal or -1.

Example :
find("Strawberry", "berry") ==> 5
find("Strawberry", "r")  ==> 2
find("Strawberry", "melon") ==> -1
rfindUsage : rfind (stringVal, stringToFind) 
Returns the last index of the stringToFind within stringVal or -1

Example :
rfind("Strawberry", "r") ==>
rfind("Strawberry", "Berry") ==> -1  (case-sensitive)
sub_stringUsage : sub_string (stringVal, startIndex, endEndex) 
Returns the sub-string of the stringVal from the startIndex character up to endIndex character

Example :
sub_string("Strawberry", 2, 5) ==> "raw"
sub_string("Strawberry", 0, find("Strawberry", "berry")) ==> "Straw"
sub_string("Strawberry", find("Strawberry", "berry"), len("Strawberry")) ==> "berry"
replaceUsage : replace (stringVal, old, new) 
Returns a copy of the stringVal with all the old replaced by new.

Example :
replace("Strawberry", "Straw", "Blue") ==> "Blueberry"

Mathematical Functions

pow(x,y)Returns x raised to power of y
floor(x)Returns a float value of the smallest integral value less than or equal to x
ceil(x)Returns a float value of the largest integral value greater than or equal to x
exp(x)Returns ex, equivalent to pow(e, x)
sgn(x)Returns the sign as -1, 0 and 1 for negative, zero and positive values of x, respectively.
sqrt(x)Returns the square-root of x
round(x[, n])Returns the float value of x rounded to n decimals digits
abs(x)Returns the absolute value of x. Return type based on the parameter.
fabs(x)Returns the absolute value of x in float.
sin(x)Returns the sine of x radians
cos(x)Returns the cosine of x radians
tan(x)Returns the tangent of x radians
asin(x)Returns the arc sine of x radians
acos(x)Returns the arc cosine of x radians
atan(x)Returns the arc tangent of x radians
degrees(x)Returns the degrees for given x in radians
radians(x)Returns the radians for given x in degrees

Flow-Control Functions

iifUsage : iif(expr1, exprIfTrue, exprIfFalse)

Evaluates and returns one of exprIfTrue or exprIfFalse, based on the result of evaluation of expr1
switchUsage : switch(expr1, value1 [, expr2, value2,.., exprN, valueN])

Evaluates expressions from exprX and valueX sets and if exprX is True, evaluates and returns the valueX.
Returns None is none of the expressions evaluated to True.
chooseUsage : choose (expr, choice1, choice2, choice3,...)

Evaluates the expression expr and based on its result, evaluates and returns one of the values corresponding to the result treated as the index.
That is, if expr evaluates to 1, choice1 is returned; if it is 2, choice2 is returned and so on.
Returns None if the expr evaluates to value less than 1 or greated than the number of choices supplied.

Date-Time Functions

nowUsage : now()

Returns a datetime object having the current time.
dateUsage : date(year, month, day)

Returns a date object for the given year, month and day values.
datetimeUsage : datetime (year, month, day, hours=0, minutes=0, seconds=0)

Returns a datetime object for the given parameters. If hours, minutes and seconds are not provided, they will be taken to be 0 (zero).
timedeltaUsage : timedelta (days=0, seconds=0, microseconds=0)

Returns a timedelta object representing a difference in time, for the given days, seconds and microseconds.

Can be used to add or subtract date/datetime values.

Example now() + timedelta(1) will give tomorrow's date and time.
td_days, td_seconds, td_microsecondsUsage : td_days(timedelta), td_seconds(timedelta), td_microseconds(timedelta)

Returns a days / seconds / microseconds components of a given timedelta value.

cache("diff_value", datetime (2012, 1, 16, 15, 30) - datetime (2012, 1, 15, 14)), 


will return

[ datetime.timedelta(1, 5400), 
  '1 day, 1:30:00', 

yearUsage : year(dateVal) or year(datetimeVal)

Returns a year value for the date / datetime value supplied to the function.

Example :
year (date(2012, 12, 24)) returns 2012
year (now()) returns 2012 assuming you accessed the function in 2012
monthmonth(dateVal) or month(datetimeVal)

Returns a month value for the date / datetime value supplied to the function.

Example :
month (date(2012, 11, 24)) returns 11
month (datetime(2012, 10, 24, 9, 45, 0)) returns 10 
dayday(dateVal) or day(datetimeVal)

Returns a day of the month for the date / datetime value supplied to the function.

Example :
day (date(2012, 12, 24)) returns 24
day (datetime(2012, 10, 24, 9, 45, 0)) returns 24
todayUsage : today()

Returns a date object having the current date.

Raising Errors (for use with Validation Expressions)

errorInstead of returning False in case of validation, you can instead raise an error and the error message will be shown in the form for the corresponding attribute.

Usage : error(error_msg)

Example : To check if the entered value is exactly 5 characters in length, it should suffice to have len(inputValue) == 5. This will show a message that the value entered did not meet the validation conditions, which you might have detailed as part of the attribute's description.

If in this case, you want to provide some specific error message, you can use the following format..

iif( len(inputValue) == 5, True, error("Please enter a 5-letter code"))

You can also check for multiple conditions and show an appropriate error message for each such case. In the following example, the input value is validated to be at least 5 characters and at most 10 characters..

[ iif( (len(inputValue) < 5), error("Code should contain at least 5 characters"), 1), iif( (len(inputValue)> 10), error("Code can contain at most 10 characters"), 1) ]

In case both the conditions are met, the result of the expression will be [1, 1], which is treated to be non-False value (zero, empty-strings "", empty-lists & empty-dicts are treated as False). If either of these expressions fail, it would result in an error which would be shown in the form.

Data Access & Cache Functions

userUsage : user()

Returns the record instance of the currently logged in user.
fetch_oneUsage : fetch_one(entity_name, criteria)

Returns a record for the given entity_name and filtering criteria, where
  • entity_name is the Display Name of the entity (singular) and
  • criteria is a dictionary of filtering conditions of the form

    {"key1":"value1", "key2":["value21", "value22"], "key3":33},

    where the keys are the Alias or the Display Name of the attribute and the values are the condition to be matched.
Example :
fetch_one("Car", {"Company":"Honda", "Name":"City"})

will fetch a record from the entity having Display Name as Car and for the filtering condition to get the record for Honda City.
val_fromUsage : val_from(instance, prop_name)

Returns a value of a property from the given instance.
  • instance is a record from the database, which may be from a reference attribute or fetched from database using fetch_one or a user record returned from the user() function.
  • prop_name is the Alias or the Display Name of the attribute for which the value is required.
Example :
val_from(user(), "emailId") will return the current user's email-id.

val_from(fetch_one("Car", {"Name":"City"}), "Company") will return "Honda", if a record for the "Car" entity matching the given condition is present in the database.
fetch_allUsage : fetch_all(entity_name, criteria)

Returns a list of records for the given entity_name and filtering criteria, where
  • entity_name is the Display Name of the entity (singular) and
  • criteria is a dictionary of filtering conditions of the form

    {"key1":"value1", "key2":["value21", "value22"], "key3":33},

    where the keys are the Alias or the Display Name of the attribute and the values are the condition to be matched.
Example :
fetch_all("Car", {"Company":"Honda"})

will fetch all records from the entity having Display Name as Car and for the filtering condition having the Company value as Honda.

Note: In the free multi-tenant deployment, fetch_all will return up to 10 records matching the criteria, while the paid single-tenant deployment will, by default, allow up to 500 records, which can also be increased on request.
values_fromUsage : values_from(instances, prop_name)

Returns a list of values of a property from the given list of instances.
  • instances is a list of records from the database, which may be fetched from database using fetch_all.
  • prop_name is the Alias or the Display Name of the attribute for which the values are required.
Example :
values_from(fetch_all("Car", {"Company":"Honda"}), "Price") will return a list of price values for all Honda cars in the database.
key_idUsage : key_id(instance=None)

Returns a key-id for the currently added / modified record, if instance parameter is not supplied (if not supplied, instance is assumed to be None). If instance parameter is supplied, returns the key-id for that instance.

Example :
key_id() returns the currently added / updated records key-id key_id (val("Customer")) returns the key_id for the Customer field, assuming Customer is a Lookup (Reference) attribute.
cacheUsage : cache(name, expr)

To caches the result of evaluation of the expression expr, with the given name, so that it can be accessed again using the cached_val function. This function also returns the value which is cached. Useful to cache results of repeating sub-expressions.

iif(cache("car1", fetch_one("Car", {"Name":"City"})), val_from(cached_val("car1"), "Company"), None)

Here the car instance is cached as car1 and accessed again in val_from function, instead of doing a fetch_one again.

The cached value has scope only within the currently evaluated expression alone.
cached_valUsage : cached_val(name)

Returns the value which was added to cache previously with the given name. Example is available in the cache function details provided above.

The cached valued has scope only within the currently evaluated expression alone.
current_recordUsage : current_record()

Returns the current record used in expression evaluation.

If you would like to see support for more functions, use the feedback form in the app to let us know.