Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Define Calculated Fields

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 […]

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Dynamically set the data source of a choice control

There are cases when the data source of a choice control depends on the value of another control on the form. For example: You have a multi-choice field with the associated company departments for a support request, called c_Department. You want the user to select the primary department and store it in another field. You […]

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Define Calculated Fields

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Dynamically set the data source of a choice control

August 15, 2017/by Konstantinos

Full Sized Blog Element (Big Preview Pic)

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Define Calculated Fields

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()

Code

adddays(now(), -10)

today()

day(DateTime date)

month(DateTime date)

year(DateTime date)

adddays(DateTime date, int days)

addmonths(DateTime date, int months)

Code

addmonths(today(), 1)

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”)

Code

“{c_c1} {c_c2}”

Another example to multiply a field value by 4 :

Code

nn(FieldValue(“c_c1”)) * 4

Code

nn(“{c_c1}”) * 4

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

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Dynamically set the data source of a choice control

There are cases when the data source of a choice control depends on the value of another control on the form.

For example:
You have a multi-choice field with the associated company departments for a support request, called c_Department.
You want the user to select the primary department and store it in another field.

You create a new ComboBox control (named for example c_MainDepartment) and in the Lookup Details tab, you must set the Lookup Static Values property to :

Code

=”{c_Department}”.

As soon as the user sets the values inside the c_Department control, the data source of the c_MainDepartment control is updated to reflect the new data.

August 15, 2017/by Konstantinos