Blog Single Author Fullwidth

There are cases when the complexity of your business cases require that you need to take over and save list items manually.

Besides controlling how data are saved through a normal PowerForms designed form, or by using the ListAndForm control to save “related” items to connected lists, saving through script is now available (v.2.4)

The “template” object provided to most of scripts in PowerForms supports a SaveRecord method.

The SaveRecord method accepts the following parameters:

URL (string) : represents the url of the target list

LIST (string) : the title of the target list

DATA (list of objects) : represents an array of the data to be saved. the array should follow the FIELD1, VALUE1, FIELD2, VALUE2, … pattern.

CALLBACK : the callback method that will be invoked as soon as saving is complete (saving list items is asynchronous)

The callback method should accept 2 parameters (the template and the result of the save-action).

We want use a button to manually save a list item in a list and inform the user of the result.

We should add an ActionButton (which supports script) and add the following script :

In order to handle the asynchronous call we must define a callback method that will be invoked as soon as the action completes. The is acchieved by the def Callback(e) part of the script. If no handling is required, None should be passed for that parameter.

The “e” result contains an “Error” variable that contains the Error if any otherwise it is empty (None for python)

The SaveRecord method performs an INSERT action if no “ID” field is provided otherwise it performs an UPDATE action.

The “e” parameter in INSERT actions contains a property named “NewId” which contains the ID of the newly inserted record.

The T = template command, enables the callback function to have access to the template variable.

To actually insert a new list item we should use the template.SaveRecord method passing the URL, the ListName and an array of FieldName/FieldValue pairs.

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

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

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

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 :

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 :

 or

Another example to multiply a field value by 4 :

or

Inside the calculation formula 3 special functions can be used that query lists in an asynchronous mode.

Returns the field value

Returns the display part for a field value (for lookup fields)

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:

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 :

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

Python scripting is a powerful tool for your customizations.

You can find here some common scripts you can use in your forms.

IMPORTANT: Python uses “Significant Whitespace”, meaning that whitespaces in front of commands are important for the interpreter to identify code blocks.

You can apply scripting to either Control Value Change events, or to handle the Form Validation and the completion of Form loading.

For Value Change events, you can apply the script in the control properties window, in the Scripts tab.

Variables :

control : this variable points to the control that raised the event.

template : this variable references the current form

IMPORTANT :

Value Change events are raised when controls value change. This happens both by user interaction or during form loading.

When the form initializes to display an existing record, it sets the appropriate record values to controls, one by one.

This also raises the ValueChange event so users should be carefull when writing scripts because some actions should only happen on user action.

For example you may want when the STATUS control of your form is set to “CLOSED”, to set a value to the “ClosedBy” control. Since the ValueChange event would also be raised when editing an existing record, this script should only be executed when a user makes that change.

For that reason, a property exists in the “template” object called “Loaded” which indicates if the form has finished loading or not.

So for our example you should write something like the following :

The form Validation event is raised just before submitting the record for save.

Variables :

template : this variable references the current form

result : boolean variable that should be returned according to the validation result. Return False when validation fails

error : string variable that should contain the error messagee that should be displayed to the user.

Example :

IMPORTANT :

You should always define the “result” and “error” variables for validation to work as expected.

You should initialize the “result” to True and the “error” variable to a blank string, and then perform validations. In case validation fails, you should set False to the “result” variable and fill the “error” variable with the error message you want to display to the end user.

This event is raised after the controls have been placed on the form and before the data item values are applied.

Variables :

template : this variable references the current form

dataitem : this variable contains the fieldName/fieldValue pairs of the actual data from the record.

Available methods for the dataitem :

dataitem.GetValue(“ColumnName”)

returns the value from the record

dataitem.GetValue_Value(“ColumnName”)

returns the ID part of a lookup column value

dataitem.GetValue_Name(“ColumnName”)

returns the TITLE part of a lookup column value

Example :

control.SetValue(string) 

Sets the value to a control

control.GetValue()

Retrieves the current value of the control (string).

GetValue_Value()

Retrieves the “value” portion of the control value in case of lookup columns where the value is in the ID;#NAME format

GetValue_Name()

Retrieves the “name” portion of the control value

GetDisplayValue()

Some controls return a user-friendly representation of their value.

SetFontBold(bool)    (For boolean operations you should use “True” or “False”)

SetForeColor(string)

Sets the foreground color of a control. You can use named colors (“White”, “Red”, etc) or hexadedimal colors (i.e. “#AB557D”)

SetBackColor(string)

Sets the background of the control

SetBorderThickness(int)

Set a control to look required under conditions using script

SetBorderColor(string) 

SetEnable(bool)

SetVisible(bool)

GetVisible()

Returns if the control is visible or not

SetReadOnly(bool)

 

 

GetControl(name)

Retrieves a control from the form

Loaded

The “Loaded” property is readonly and returns True if the form initialization has completed. Otherwise it returns False.

SaveRecord(), SaveAndExit()

Saves the current record. Can be used inside Action Buttons to apply Save operations to the form without using the default toolbar buttons.

DeleteRecord()

Deletes the current record

CancelRecord()

Cancels the record editing

GetListId(), GetListName(), GetListUrl()

Retrieves list metadata

ReloadValue(controlName)

Reloads the value of a control

EnableTab(int index)

Enables a form tab. The index is zero based. (0 for first tab, 1 for the second, etc)

DisableTab(int index) 

Disables a form tab

ShowTab(int index) 

Shows a (hidden) form tab. Index is zero based.

HideTab(int index) 

Hides a form tab

ExecuteJavascript(string script)

Executes javascript command(s).

SaveRecord(url, listName, data) 

Saves a record (Insert or Update depends on the ID value provided)

SaveRecord(url, listName, data, callback) 

Saves a record and calls a callback method when complete

DeleteRecord(url, listName, id, callback) 

Deletes a record

LoadRecord(url, listName, id, callback) 

Loads an existing record.

Example : LoadRecord(“http://servername/sitename, “TestList”, “1”, callback)

where “callback” is a method defined inside script

SetDataItemValue(fieldName, value)

You can use this method to set values to the current record. If you set values to fields bound to controls, the values will be overriden before record update. So it can normally be used for columns that do not have a bound control on the form.

GetDataItemValue(fieldName)

Retrieves a value from the underlying record (this value may be different from the current value inside the bound control)

SetSectionBackColor(index, color)

Changes the background color of a section on the form.

SetSectionBorderColor(index, color)

Changes the border color of a section on the form.

SetSectionBorderThickness(index, thickness)

Chages the border thickness of a section on the form

SetSectionHeader(index, headerText)

Changes the header/caption of a section

 

ReloadRecord()

Reloads the current record from the SharePoint list

 

ExpandSection(index)

Expands the form section

 

CollapseSection(index)

Collapses the form section

 

HideButton(string)

Hides a toolbar button. Available values are : “New”, “Save”, “SaveAndExit”, “Delete”, “Cancel”, “Edit”, “Help”, “Design”, “Customizations”

 

ShowButton(string)

Shows a (hidden) toolbar button. Available values are : “New”, “Save”, “SaveAndExit”, “Delete”, “Cancel”, “Edit”, “Help”, “Design”, “Customizations”

 

SetGlobalVariable(string, object)

Sets the value for a global variable. Global variables are created inside the “LoadCompleted” script.

ExecuteSQLQuery(string queryListUrl, string queryListName, string queryName, FieldValue[] parameters, callback)

Executes a SQL Query defined in the specified list. Read more about how to define a sql query here.

ExecuteSQLQuery(string queryName, callback)

Executes a predefined sql qeury (from the Sql Queries section of the designer)

CallWebService(string wsName, callback)

Executes a predefined web service (from the web services section of the designer)

 

Change Control Appearance depending on control value 

Import a .Net namespace

Show a confirmation dialog

Exception Handling

Asynchronous Methods / Save a new list item

Load Records using criteria from the form / Perform Calculations

 

Instead of using scripting in various form events, scripting may be applied with the ActionButton control.

For extensive language reference use the following links :

http://ironpython.net

http://en.wikipedia.org/wiki/IronPython