Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Script Example : Setting the datasource of controls through script

In order to have filtered dropdowns, the most common approach is to use the Constraint feature of PowerForms which enables automatic filtering of a dropdown control (see this link for details) There are cases though when you want to manually apply the datasource of a control using business rules. For example a set of COUNTRY and STATE combos. The State combo needs to […]

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Script Example : Calculate average value for a query resultset

For this example, we want to dynamically load data from a SharePoint list, loop through the resultset and calculate the average value of a specific column in the resultset: To build our script, we assume the following : – We have a list of “Cases” that have a Rate field (number) and a Customer lookup – We have […]

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Script Example : Setting the datasource of controls through script

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Scripting And Formulas

Script Example : Calculate average value for a query resultset

August 15, 2017/by Konstantinos

Script Example : Setting the datasource of controls through script

In order to have filtered dropdowns, the most common approach is to use the Constraint feature of PowerForms which enables automatic filtering of a dropdown control (see this link for details)

There are cases though when you want to manually apply the datasource of a control using business rules.

For example a set of COUNTRY and STATE combos.

The State combo needs to show available states when USA is selected in the COUNTRY control and the “Not Applicable” value otherwise.

This can be easily done through script. The COUNTRY control should be bound to the Country Lookup of the list whereas the STATE control should be a simple text field (NOT bound to a lookup/list since we want to set the datasource ourselves)

In the ValueChange event of the COUNTRY control add the following script :

Code

from System import Array
target = template.GetControl(“c_State”)
country= control.GetValue()
a1 = Array[str]([“AL”,”AK”,”AZ”,”AR”,”CA”,”CO”,”CT”,”DE”,”FL”,”GA”,”HI”,”ID”,”IL”])
a2 = Array[str]([“Not Applicable”])
if country == “USA” :
target.InputControl.SetDataSource(a1)
else :
target.InputControl.SetDataSource(a2)
target.OnLookupLoaded()

Script explained :

In order to change the datasource of a combobox control, the target.InputControl.SetDataSourcefunction should be used. It accepts an array of texts.

First we import the Array class from the the framework (from System import Array)

Then we get a reference for the STATE control (target = template.GetControl(“c_State”))

We get the value of the country control (country= control.GetValue())

We create the data source lists we want to use (a1, a2)

We check the value of the country control and apply the datasource accordingly

As a last step, we call the OnLookupLoaded() method of the STATE control to allow the control to set the undelying value. If we ommit the last call, when editing existing records, the value change script will clear the value of the State control.

August 15, 2017/by Konstantinos

Script Example : Calculate average value for a query resultset

For this example, we want to dynamically load data from a SharePoint list, loop through the resultset and calculate the average value of a specific column in the resultset:

To build our script, we assume the following :

– We have a list of “Cases” that have a Rate field (number) and a Customer lookup
– We have a “Customers” list
– In the Customers form, we added an ActionButton and a readonly textbox called “c_Avg”
– When pressing the button, we want to load all cases for the specific customer, and calculate the Average Rate
– Then we want to display the average value in the c_Avg control

We add a script action in the button and write the following code:

Code

T = template

def callback(e) :
if e.DataItems.Count>0 :
total = 0
avg = 0
for record in e.DataItems :
rate = e.DataItems[0].GetValue(“Rate”)
if rate != “” :
total = total + float(rate)
avg = total / e.DataItems.Count
T.GetControl(“c_Avg”).SetValue(avg)

T.GetControl(“c_Avg”).SetValue(“”)
id = T.GetControl(“c_ID”).GetValue()
template.LoadRecords(http://server1/siteA, “Cases”, “Customer”, “Equals”, id, “Lookup”, [“Rate”], callback)

In the above example we follow the steps below to load data and calculate the required value :

– We define the “callback” method that will be executed as soon as loading completes
– We clear the value for the c_Avg control
– We load the “Rate” for all cases for that customer
– Inside the callback method, we calculate the total rate and divide by the count to get the average value
– We set the calculated average value in the c_Avg control.

August 15, 2017/by Konstantinos

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Full Sized Blog Element (Big Preview Pic)