Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Knowledge Base, PowerForms Siverlight, Printing

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

August 15, 2017/by Konstantinos

External Lists, Knowledge Base, PowerForms Siverlight

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

August 15, 2017/by Konstantinos

Knowledge Base, PowerForms Siverlight, Printing

Printing with custom XSLT file

August 15, 2017/by Konstantinos

External Lists, Knowledge Base, PowerForms Siverlight

SQL Queries in PowerForms

August 15, 2017/by Konstantinos

Full Sized Blog Element (Big Preview Pic)

Knowledge Base, PowerForms Siverlight, Printing

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:

August 15, 2017/by Konstantinos

External Lists, Knowledge Base, PowerForms Siverlight

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.

Code

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

In our example, we want to set the parameter with the value ({value:c_Customer}) of a form control.

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 :

Code

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 :

Code

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

August 15, 2017/by Konstantinos