Calling external web services and displaying data
Users can create external web service calls and display the data using a datagrid on the form.
NOTE: Other controls can also be connected with a web service call. In that case only the first attribute for the first record will be used and if the value is valid for that control it will be displayed properly.
To define a new web service call, all of the followng properties must be defined:
– A custom Name for referencing the web service call.
– The Stored Credentials used to call the web service.
– The external Url of the web service.
– The specific Method to call from the above web service.
– The Xml Namespace used by the Web Service. This is required for a well formed request to the service.
– The Handle Namespace option to allow for simpler xpath queries (see below).
– The Parameters with their respective values. Each parameter is defined as pair of strings (ParameterName];[ParameterValue) and each pair is seperated by a pipeline (|) from the next one. For example, if two parameters exist, FirstName and LastName and their respective values are John Smith then one should input the following: FirstName;John|LastName;Smith. One can also enter the same values or edit them, using the provided pop-up form.
NOTE: As with everything that has to do with XML, the above values are case sensitive.
When all the above fields are filled properly pressing the Test button shows the result that the web service provides, to confirm their validity. If any error occurs while calling the web service the resulting error will be displayed instead.
The second part of a web service call, consists of defing an XPath Query to be used for extracting the needed information. For each element returned a row will be displayed in the DataGrid with all of its attributes (Auto Populate)
If Handle Namespaces is selected simple xpath queries can be used, e.g.: //z:row[@ows__Level=2].
If Handle Namespaces is not selected the xpath cannot contain prefixed elements and one should reside to the use of local-name(), e.g. //*[local-name() = ‘row’][@ows__Level=’1′].
Any element of the form can be referenced inside the XPath Query, by enclosing its name inside brackets. For example: //sales[@customerId={c_customer_id}.
The {c_customer_id} will be replaced with its specific value before Xpath Querying the Web Service Call result.
You can also select specific Attributes to display using their names, seperated by a semicolon (;), e.g. ows_UniqueId;ows_FileLeafRef.
A different name can be used for displaying purposes, other than the name of the Attribute, if a similar list of headers is filled in the Headers textbox. The Headers should match the respective Attributes one-to-one. If fewer Headers are provided the remainng attributes will use the default (the Attribute’s name). If more Headers are provided they will be ignored.
Example :
Once the user has defined a Web Service call, he can then connect it to a WebServiceDataGrid control. In the Advanced tab one has to select a Value Type of Web Service Call, and then pick a specific web service call from the list box Web Service Post Url. The control will display the matching data using the xpath query and the (optionally) defined attributes in a read-only grid.
The same advanced parametrization can be used in all controls but the resulting value might not be valid. For example a checkbox will parse 0 as not checked and 1 as checked but any other value will be ignored and the checkbox will remain not checked.
You can find a video describing how to integrate Web Service calls with your form here.
SQL Queries in PowerForms
PowerForms HTML 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 :
- 1. A custom list should be created server-side.
- 2. The definition should be stored there (including ConnectionString, SQL Command and parameters) in XML format
- 3. 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
- 1. Create a new custom list in your site collection (for example name : PowerForms SQL Queries)
- 2. Add a new column called “QueryXML: (multiple lines of text, PlainText)
- 3. 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 Elevated=”true”>
<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>
The Elevated=”true” parameter forces the execution of the query to be done under the application pool account. This is needed in case you need to access your database using windows authentication. If you are using SQL/native database authentication then this is not needed.
Note that the schema contains 3 main properties :
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 :
SetSectionCellSpacing, SetSectionCellPadding methods for Sections
SetSectionCellSpacing, SetSectionCellPadding are two new methods which when used, will control the internal cell spacing and padding of a section on a form.
The generic way of calling both methods is:
form.SetSectionCellSpacing(“Section Key”, “Size in Integer”);
form.SetSectionCellPadding(“Section Key”, “Size in Integer”);
A prerequisite to use the above two methods is to add a key to the section you wish to change the spacing/padding, like in the picture below:
We’ll use the section below to show the changes performed by the two methods individually. The section without any changes:
Changing our example’s section called testSection spacing size to 20:
form.SetSectionCellSpacing(“testSection”, “20”);
Changing our example’s section called testSection padding size to 20:
form.SetSectionCellPadding(“testSection”, “20”);
SetDataSource method for DataGrid
SetDataSource is a new method used to bind a DataGrid to an array of objects during runtime.
c_Control5 is our example datagrid.
var c = form.GetControl(“c_Control5″).InputControl;
var data = [];
var item = new Object();
item.ID=”1”; item.Name = “John”; data.push(item);
item = new Object();
item.ID=”2″; item.Name = “George”; data.push(item);
c.SetDataSource(data);
Let’s analyze the above code in more detail:
var c = form.GetControl(“c_Control5”).InputControl;
The above segment finds our example datagrid and stores it in a variable. Normally we’d only have to use
var c = form.GetControl(“c_Control5”); but in our case the SetDataSource command is under the InputControl property.
var data = [];
Declares an array called data which we’ll use to store objects and finally binds it to the datagrid.
var item = new Object();
item.ID=”1″; item.Name = “John”; data.push(item);
The above code segment creates a new object called item, declares two parameters called ID and Name and initializes them with values . data.push(item); stores the newly created object in the array.
c.SetDataSource(data);
Finally, we use SetDataSource to bind the array to our example’s datagrid. The result of our actions is shown below:
Using the SetCellColor method
SetCellColor is used to dynamically change the cell color of a control using scripting. To call the method:
form.GetControl(“ControlName”).SetCellColor(“ColorCode“);
where ControlName is the name of the control whose cell color you want to change and ColorCode is the HTML color’s code representation.
For example, let’s assume we have a TextBox control called c_Country and a Button control, c_Control4 with an action script used to dynamically change the textbox’s cell color to purple.
The Button’s custom script:
The actual code:
form.GetControl(“c_Country”).SetCellColor(“#8467D7”);
The Textbox before pressing the Button:
The Textbox after pressing the Button:
Using the ParentListName method
A new method called ParentListName was added in the latest version of PowerForms HTML. This method can be called from a form used inside a ListAndForm control and will return the list name of the form containing the actual ListAndForm control.
In order to call the new method, use:
form.ParentListName();
The method is best used when a list is required to show information to one or more lists which contain ListAndForm controls and maybe needs to show that information in a different manner, based on the parent list.
For example let’s assume we have two lists, Test1 and Test2. Test2 contains a ListAndForm control which uses a ListQuery to return information from the Test1 list. Test1 contains a Button control which executes a custom script when pressed. The script uses the ParentListName method, stores it in a variable and then presents the information to the user using an Alert pop-up. The code is listed below:
var theListName = form.ParentListName();
alert(theListName);
Following below is an image showing an attempt to add a new item to the Test2 list. We’ve already selected an item in the ListAndForm control which shows information from the Test1 list.
Pressing the Button control Button 1 will cause a pop-up message to appear which will show us the name of Test1’s parent list.
