Printing with custom XSLT file
Users can create their own custom print layout using XSLT file.
The xml file that we use to extract the data from the form is the one below:
The control c_Feedback is a DataEntryGrid control and the extra elements ‘Values’ and ‘Record’ are used to describe its fields and values.
An xslt file should be created that handles the xml data above and will give us the desired result as a printing template. One simple structure of the xslt can be shown below:
As you can see the controls are listed under ListItem/Control element. This simple xslt file will display a header with the Title field and 2 more fields ID and Customer.
When the xslt file is ready, it should be uploaded in a Sharepoint Document Library.
Then we navigate in the specific list and we associate this file with our form. We open the designer of the PowerForms and in Selection drop-down we choose Options.
At the bottom of this page we fill the Print Template textbox with the full path of the xslt file.
The final printing page with this simple xslt will be as below:
SQL Queries in PowerForms
Applies to PowerForms Advanced
Since version 2.6, PowerForms supports direct SQL Queries that can be used to provide lookup data for lookup controls (such ComboBoxes).
For security reasons, the actual sql query is not defined inside PowerForms designer but another setup model has been adopted :
- A custom list should be created server-side.
- The definition should be stored there (including ConnectionString, SQL Command and parameters) in XML format
- Inside the PowerForms designer, the user references the sql-query entry of that list and binds parameter values using static data or dynamic control values
- Create a new custom list in your site collection (for example name : PowerForms SQL Queries)
- Add a new column called “QueryXML: (multiple lines of text, PlainText)
- Setup security for your list as you may want to restrict users from viewing query details (query is executed using elevated permissions)
Add a new item inside the list. Set the title of the item.
Inside the QueryXML field, you should use the following xml schema to define the query.
<Query>
<ConnectionString>Provider=SQLOLEDB;Data Source=.;Initial Catalog=WSS_Content;Integrated Security=SSPI;</ConnectionString>
<SQL>select ID, Title, Priority, Type, StartDate from Customers where ID=?</SQL>
<Parameters>
<Parameter Name=”ID” Type=”Number” />
</Parameters>
</Query>
Note that the schema contains 3 main properties :
ConnectionString
This contains the OLEDB connectionstring to the database
SQL
Contains the sql command of your query. If a stored procedure is to be called, you should add the attribute Type=”SP” to the Query element. For example : <Query Type=”SP”>
Parameters
You can define unlimited number of parameters in your query. Values for these parameters are provided by the form during query execution.
Parameter types supported : “Text”, “Number”, “DateTime”.
A new section inside the designer has been added to configure SQL Queries.
There you must reference the Query List URL and Query List Name, set the Query Name (which corresponds to the Title column) and setup parameters (that should be already defined in the query xml)
Add a combobox control on your form.
Inside the Looup Details section, you should select that the lookup values come from a SQL query, select the query from the provided list and set the Display and Value fields of the control.
If a single field is required (i.e. Title) use that field for both properties (Display and Value members)/
NOTE : PowerForms provide automatic refresh to the lookup control every time a control value that affects the query parameter, is changed by the users.
We assume we have a table (named Products) containing our product list.
Columns : ID (int), NAME (nvarchar), DESCRIPTION (nvarchar)
We create a stored procedure to retrieve products :
CREATE PROCEDURE P_GetProducts
@ID int = null
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, NAME, DESCRIPTION FROM Products
WHERE (@ID IS NULL OR @ID=ID)
END
As you may notice, we have provided an optional parameter in order to retrieve a single record if necessary.
Now to define the query xml we would have to provide something like the following :
<Query Type=”SP”>
<ConnectionString>Provider=SQLOLEDB;Data Source=.;Initial Catalog=TestDB;Integrated Security=SSPI;</ConnectionString>
<SQL>P_GetProducts</SQL>
<Parameters>
<Parameter Name=”ID” Type=”Number” />
</Parameters>
</Query>
Notice the Type=”SP” attribute of the query xml element. It is important to use this attribute in order to call a stored procedure.
Now we setup the query inside the PowerForms designer and test the results:
Test the optional parameter by setting “ID;1” inside the Parameters propery.
Now we bind the query to a ComboBox control to examine the results :
The combobox in action :
Instead of a ComboBox control, RadioButtons can be used to display SQL Query results :
External Lists using Power Forms
Since version 2.4, support for External Lists/External Content Types is available using PowerForms (SharePoint 2010 only).
Assuming you have already created your external list, to setup that list to use PowerForms, you should follow the steps below :
REGISTER PowerForms
The PowerForms registration buttons should be available in the list ribbon, so register PowerForms for the New and Edit forms.
DESIGN
As you would normally do with any other form, open the designer and examine/change the default configuration created.
NOTE 1 : In some cases, the default control type may differ from the one you would expect. For example a database field of type “Tinyint” will create a NumberTextBox. If the purpose of the database field is to keep boolean values (1 or 0), just change the control type to CheckBox
NOTE 2 : The identifier of the record is no longer numeric, and is stored in a system field called BdcIdentity (a textbox control will be created for that column in case you want to use it)
Whenever you declare a lookup control based on an external list, SharePoint instead of binding the list to the lookup control, binds the external Data Source.
This has the result that the default form produced by PowerForms will not recognize the binding details and will leave the lookup control as a simple textbox.
What you have to do, is to change the control type to a ComboBox or a LookupPicker and set the properties manually through the control properties in the “Lookup Details” tab.
Type the list url, select the list name and in the value field, select the “BdcIdentity” field which is the actual record identifier.
Visit our blog for a more detailed example on External Lists and lookups on foreign keys.
Script Examples
The above system columns do not create bound controls during form initialization.
You can get the values though through script and set these values inside your custom controls through script :
You should add the following script in the “Load Completed” script of the form : Designer > Options > Scripting > Load Completed
created = dataitem.GetValue(“Created”)
template.GetControl(“c_Created”) .SetValue(created)
modified = dataitem.GetValue(“Modified”)
template.GetControl(“c_Modified”) .SetValue(modified)
# THE RETRIEVED VALUE IS IN THE ID;#TITLE FORMAT
author = dataitem.GetValue(“Author”)
# GET THE ID PART
authorId = dataitem.GetValue_Value(“Author”)
# GET THE TITLE PART
authorName = dataitem.GetValue_Name(“Author”)
template.GetControl(“c_Author”) .SetValue(authorName)
editor = dataitem.GetValue(“Editor”)
template.GetControl(“c_Editor”) .SetValue(editor)
You can enable, disable, show or hide tabs through script.
So in the Load Completed script or in the Value Change event of a control, you can write something like the following :
If template.Functions.UserInGroup(“Admins”) :
template.ShowTab(1)
template.EnableTab(2)
template.HideSection(3)
else :
template.HideTab(1)
template.DisableTab(2)
template.ShowSection(3)
Note : the template.Functions property includes the functions used from formulas too :
string ColumnDescription(string colInternalName)
string ColumnTitle(string colInternalName)
string ColumnType(string colInternalName)
bool ColumnIsRequired(string colInternalName)
string FieldValue(string controlName)
string FieldDisplayValue(string fieldname)
string FieldValue_Value(string controlName)
string FieldValue_Name(string controlName)
string LoginName()
string UserID()
string UserFullName()
bool UserInGroup(string groupName)
bool UserInGroupById(string groupId)
string ListName()
string ListId()
string ListUrl()
There are cases that you need to set the value to 2 controls (from script) with the second being affected by the first one.
For example you have 2 dropdown controls (ComboBoxes, LookupPickers, etc) one for Countries and one for Cities.
The Cities combo is affected by the Countries combo, meaning that when the user selects a Country, the Cities combo is forced to reload its data.
If you try to set the values to both controls using the following script, it should fail for the Cities combo since the time we are setting the value, the combo values are not loaded yet (loading is asynchronous).
# THIS CODE WILL NOT WORK
template.GetControl(“c_Countries”).SetValue(“1;#Country1”)
template.GetControl(“c_Cities”).SetValue(“34;#City34”)
For that reason a new method has been introduced which set the value to a lookup control and the control will set the value as soon as loading is complete.
The method is called : SetValueOnLookupLoaded
The folloing script will work as expected.
template.GetControl(“c_Countries”).SetValue(“1;#Country1”)
template.GetControl(“c_Cities”).SetValueOnLookupLoaded (“34;#City34”)
The ListDataGrid control includes some useful methods which can be used through script:
# PROPERTY THAT RETURNS ALL THE DATA RECORDS OF THE GRID
items = c.InputControl.DataItems
count = items.Count
id = items[0].GetValue(“ID”)
# RELOAD GRID
c.InputControl.SetValue(“”)
# OR
c.InputControl.RefreshData()
# GET A VALUE FROM THE SELECTED RECORD
c.InputControl.GetGridItemValue(“ID”)
# SELECT A ROW BY INDEX
c.InputControl. SelectGridRow(3)
# SELECT A RECORD BY A VALUE EXAMPLE
id = template.GetControl(“c_ID”).GetValue()
grid = template.GetControl(“c_DetailData”)
count = grid.InputControl.DataItems.Count
grid.InputControl.SelectGridItem(None)
for i in range(0, count) :
item = grid.InputControl.DataItems[i]
if item.GetValue(“ID”) == id :
grid.InputControl.SelectGridItem(item)
In order to set the value of a control during form initialization (usually for new records) you can use 3 different methods.
- Set a default value for that control
- Pass the value in the querystring
- Use script
To set the value you must use the LoadCompleted script and add something like the following :
# The “dataitem” variable references the record being displayed
# For new records, it will only contain the default values in ColumnName/Value pairs
# Only for New records set the priority to HIGH
if dataitem.GetValue(“ID”) == “” :
dataitem.SetValue(“Priority”, “High”)
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 :
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.
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.