Define Calculated Fields and Visibility/Enabled formulas
By setting a field to be calculated, you have to define the calculation formula for the field values.
Moreover, you can use formulas in the "Visibility formula" and "Enabled formula" properties.
Whereas in the Calculation formula should return the actual value that must be applied to the control, the Visibility and Enabled formulas should return a boolean value (true or false) that should indicate whether the control should be visibile or enabled.
The formulas use VB like syntax and have many available functions you can use:
now()
today()
day(DateTime date)
month(DateTime date)
year(DateTime date)
adddays(DateTime date, int days)
addmonths(DateTime date, int months)
addyears(DateTime date, int years)
rnd(int number) : Returns a random number
Code
format(rnd(10000), "#,##0")
mid(string str, int index) : gets a part of a string
mid2(string str, int index, int length) : gets a part of a string
left(string str, int length) : gets the left part of a string
Code
left(FieldValue("c_Customer"), 10)
right(string str, int length) : gets the right part of a string
len(string str) : returns the length of the string
trim(string str) : removes leading and trailing spaces
ifn(bool condition, double TrueValue, double FalseValue)
ifd(bool condition, DateTime TrueValue, DateTime FalseValue)
ifs(bool condition, string TrueValue, string FalseValue)
format(string str, string style)
ucase(string str) : converts to uppercase
lcase(string str) : converts to lowercase
wcase(string str) : converts string to mixed case
replace(string original, string SearchValue, string ReplaceValue)
date(int year, int month, int day)
int(double value) : converts to integer
round(double value, int decimals) : rounds a number
nn(object value) : Converts an object to a number
nnf(object value, string format) : Converts a value to a number expecting a specific country format (values = en-US, el-GR, etc)
nnus(object value) : converts an object to a number expecting the us format (i.e. 34.2)
part(string str, string delimeter, int index) : returns a part of the string after splitting the string with the delimeter provided
FieldValue(string fieldname) : returns the field value of a specific control
FieldDisplayValue(string fieldname) : returns the display field value of a specific control
FieldValue_Value(string controlname) : returns the id value from lookup field
FieldValue_Name(string controlname) : returns the name value from lookup field
PropertyValue(string propertyname) : returns the property value of a specific property
LoginName() : returns the login name of the current user
UserID() : returns current user's ID and can be set as a default value to PeoplePicker Control
UserInGroup(string groupName) : returns true if the current user belongs to the specified group
Code
ifs(UserInGroup("Admins") or UserID() = "1", "Is Admin", "Is Guest")
UserInGroupById(string groupId) : returns true if the current user belongs to the specified group
ColumnDescription(string columnInternalName) : returns the description of the column from the SP metadata
ColumnTitle(string columnInternalName) : returns the column title
ColumnType(string columnInternalName) : returns the type of the column
ColumnIsRequired(string columnInternalName) : returns a boolean value indicating wether the columns is required or not
For complex expressions the following operators can be used :
"(" left parenthesis
")" right parenthesis
"<" less than
">" greater than
"<=" less or equans
">=" greater or equal
"+" plus sign
"-" minus sign
"*" multiply
"/" division
"and", "or", "not", "true", "false", "yes", "no" for boolean operations
For string comparison, single or double quotes may be used. If a double quote needs to be included in the string, then it should be enclosed in single quotes and vise-versa.
For example to check a field value against a string containing double quotes (i.e. TEST" STRING) then the users should use something like the following :
Code
FieldValue("FieldName") = 'TEST" STRING'
NOTE : All function names are case sensitive.
EXAMPLES
Using values from controls inside the calculation formula
In order to include values from controls inside the formula, there are 2 options.
Either use the functions FieldValue (FieldValue), FieldDisplayValue (Field Display Value) as a normal function inside the formula or use the format {CONTROL_NAME} inside the formula.
The different is that the FieldValue and FieldDisplayValue functions are calculated by the expression evaluator whereas the {control_name} text is replaced with the field value prior to the calculation, so the remaining text has to be a valid formula.
For example in order to concatenate 2 fields :
Code
FieldValue("c_c1") & " " & FieldValue("c_c2")
or
Another example to multiply a field value by 4 :
Code
nn(FieldValue("c_c1")) * 4
or
Inside the calculation formula 3 special functions can be used that query lists in an asynchronous mode.
Code
GetListItemValue(URL, LIST_NAME, LIST_ITEM_ID, FIELD_VALUE_TO_RETURN)
Returns the field value
Code
GetListItemValueD(URL, LIST_NAME, LIST_ITEM_ID, FIELD_VALUE_TO_RETURN)
Returns the display part for a field value (for lookup fields)
Code
GetListItemValueV(URL, LIST_NAME, LIST_ITEM_ID, FIELD_VALUE_TO_RETURN)
Returns the value part for a field value (for lookup fields)
NOTE : The URL parameter should be the url of the site (or subsite) that the list belongs to.
NOTE : Field names included in the above functions should be the Internal names and not the display names of the fields.
These 3 functions can be mixed with the calculation functions above, but are evaluated first of all and the formula returns the final result as soon as all lookup queries have completed.
Example :
You have a combobox in your form for Customers named "c_Customer". The combobox is a lookup for a list called CUSTOMERS.
You want to display in a readonly control on the form, the address of the selected customer.
You create a new readonly control, set the Is Calculated property and enter the following formula inside the Calculation formula field:
Code
"Address : GetListItemValue("http://servername", "CUSTOMERS", "{value:c_Customer}", "Address")"
Note that {c_Customer} should return something like "ID;#NAME"
{value:c_Customer} should return the ID of the customer and {name:c_Customer} should return the Title of the customer record.
Dont forget to enclose the whole formula in double quotes.
TIP: Replace spaces in column names with "_x0020_".
Since the lookup queries run asynchronously, the new address field will be updated as soon as the evaluation completes.
The form automatically identifies depedencies between controls and recalculates the formulas whenever control values change.
Visibility Formula Example
If a control should be visibile under certain conditions depending on values of other controls, formulas like the following can be applied to achieve the required result :
Code
FieldValue("c_Priority")="High" and (FieldValue_Value("c_Status")="2" or FieldValue_Value("c_Owner")="1")
NOTE : In order to apply more compex rules for your forms, you can write simple python script in the Value Change events of controls.
See : Iron Python Scripting in PowerForms