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
- Accessing user input value in validation expressions
- Accessing old-value in Criteria Expression of Workflow
- Arithmetic & Concatenation Operators
- Comparison Operators
- Logical Operators
- Data Type Conversion Functions
- String Functions
- Mathematical Functions
- Flow-Control Functions
- Date-Time Functions
- Raising Errors (for use with Validation Expressions)
- Data Access & Cache Functions
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")
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
Operator | Details |
---|---|
+ | 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
Operator | Details |
---|---|
< | Less-than |
<= | Less-than or equal |
> | Greater-than |
>= | Greater-than or equal |
!= | Not equal |
== | Equal |
in | Containment 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
Operator | Details |
---|---|
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. |
not | Logical NOT
Usage : not expr1 Result will be True when expr1 is False, else True. |
Data Type Conversion Functions
Function | Details |
---|---|
str | Returns 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. |
unicode,
uc | Returns the unicode value of the parameter. |
int | Returns the integer value of the parameter. |
float | Returns 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
Function | Details |
---|---|
ltrim | Returns the text with additional spaces (if any) on the left being trimmed out.
Example : ltrim(" some text ") ==> "some text " |
rtrim | Returns the text with additional spaces (if any) on the right being trimmed out.
Example : rtrim(" some text ") ==> " some text" |
trim | Returns the text with additional spaces (if any) on the left and right being trimmed out.
Example : trim(" some text ") ==> "some text" |
uppercase | Returns the text with all letters converted to uppercase.
Example : uppercase("Some Text") ==> "SOME TEXT" |
lowercase | Returns the text with all letters converted to lowercase.
Example : lowercase("Some Text") ==> "some text" |
capitalize | Returns the text with the first letter capitalized.
Example : capitalize("some text") ==> "Some text" |
titlecase | Returns the text with the first letter of all words converted capitalized.
Example : titlecase("some text") ==> "Some Text" |
swapcase | Returns the text with all letters swapped from the current case.
Example : lowercase("Some Text") ==> "sOME tEXT" |
len | Returns 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 |
find | Usage : 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 |
rfind | Usage : rfind (stringVal, stringToFind) Returns the last index of the stringToFind within stringVal or -1 Example : rfind("Strawberry", "r") ==> 8 rfind("Strawberry", "Berry") ==> -1 (case-sensitive) |
sub_string | Usage : 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" |
replace | Usage : 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
Function | Details |
---|---|
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 e |
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
Function | Details |
---|---|
iif | Usage : iif(expr1, exprIfTrue, exprIfFalse)
Evaluates and returns one of exprIfTrue or exprIfFalse, based on the result of evaluation of expr1 |
switch | Usage : 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. |
choose | Usage : 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
Function | Details |
---|---|
now | Usage : now()
Returns a datetime object having the current time. |
date | Usage : date(year, month, day)
Returns a date object for the given year, month and day values. |
datetime | Usage : 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). |
timedelta | Usage : 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_microseconds | Usage : td_days(timedelta), td_seconds(timedelta), td_microseconds(timedelta) Returns a days / seconds / microseconds components of a given timedelta value. Example: [ cache("diff_value", datetime (2012, 1, 16, 15, 30) - datetime (2012, 1, 15, 14)), str(cached_val("diff_value")), td_days(cached_val("diff_value")), td_seconds(cached_val("diff_value")), td_microseconds(cached_val("diff_value")) ] will return [ datetime.timedelta(1, 5400), '1 day, 1:30:00', 1, 5400, 0 ] |
year | Usage : 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 |
month | month(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
|
day | day(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
|
today | Usage : today()
Returns a date object having the current date. |
Raising Errors (for use with Validation Expressions)
Function | Details |
---|---|
error | Instead 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
Function | Details |
---|---|
user | Usage : user()
Returns the record instance of the currently logged in user. |
fetch_one | Usage : fetch_one(entity_name, criteria)
Returns a record for the given entity_name and filtering criteria, where
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_from | Usage : val_from(instance, prop_name)
Returns a value of a property from the given instance.
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_all | Usage : fetch_all(entity_name, criteria)
Returns a list of records for the given entity_name and filtering criteria, where
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_from | Usage : values_from(instances, prop_name)
Returns a list of values of a property from the given list of instances.
values_from(fetch_all("Car", {"Company":"Honda"}), "Price") will return a list of price values for all Honda cars in the database. |
key_id | Usage : 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. |
cache | Usage : 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. Example: 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_val | Usage : 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_record | Usage : 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.