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:
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.