From The Field

I recently received a requirement from a customer who wanted to build one click approval into the email notifications being received around a Purchase Order process.

As Purchase Orders are being created in a forms library (although this could work against any list item use case), a SharePoint Designer workflow was created to determine approval routing and an email notification was sent seeking their approval.  When the approver received the email, two links are presented to allow the approver to Approve or Reject the purchase order.

These built in links call a Toolset provider to update the status accordingly and then the connection redirects the user to their dashboard in SharePoint.

Here are the easy to follow implementation steps:

1) You will need to create an External Data Provider (for more information on creating an EDP consult other articles on the Community site).  Create your connection needed for the provider that will be associated to each link in the email.  In our example below, we are leveraging a SharePoint web service for updating a list item.  Our links in the email will call this Toolset connection passing in the List ID and the appropriate status.  Some of the key information needed in this connection is the <listName>.  This will be the list your updating through this connection.  You will need to change your field names to correspond to your actual field names.  And the <Values> section you will need to include the passthrough parameters for this connection.  One last key component to this connection is the <RedirectTo> node.  This will be the URL that the user will be redirected to after the status is updated.  You will want to add this to provide a more seamless user experience.  If you don’t include this node, the user will see the native SOAP response coming back from the SharePoint web service.  I directed the user to the dashboard in our customer solution.

<Data>
    <Name>POApprovalDecision</Name>
    <Default>false</Default>
    <ConnectionType>Web Service</ConnectionType>
    <Request>http://devrapps.corasworks.net/rfdev/hapts/_vti_bin/Lists.asmx</Request>
    <RedirectTo>http://devrapps.corasworks.net/rfdev/hapts/ADefault.aspx</RedirectTo>
    <SOAP>
        <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
            <soap:Body>
                <UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
                    <listName>Purchase Orders</listName>
                    <updates>
                        <Batch OnError="Return">
                            <Method ID="1" Cmd="Update">
                                <Field Name="ID">%ListID%</Field>
                                <Field Name="PO_x0020_Status">%Status%</Field>
                            </Method>
                        </Batch>
                    </updates>
                </UpdateListItems>
            </soap:Body>
        </soap:Envelope>
    </SOAP>
    <SOAPAction>http://schemas.microsoft.com/sharepoint/soap/UpdateListItems</SOAPAction>
    <UseCurrentUserCredentials>2</UseCurrentUserCredentials>
    <UserName>%CWUserID%</UserName>
    <Password>%CWPassword%</Password>
    <OutputType>text/xml</OutputType>
    <Values>
        <ListID />
        <Status />
    </Values>
</Data>

2) Build your workflow email using SharePoint Designer.  Of course you will want to include as much detail that the approver will need to make a decision on the Purchase Order approval.  But the main ingredient here are the Approve and Reject links.  You will notice that we are building links to the Toolset provider created in step 1.  We append the ID for the list item and the appropriate status for the link.  These are used by the connection to identify the right purchase order and to update the approval decision.

3) The user will receive an email that looks something like this.  The Approve and Reject links execute the provider that updates the status.  And then the user is redirected to the dashboard.

Imagine all of the one click scenarios you could implement around this technique.  As you implement, please share them with the community so we can all benefit from your creative genius. 

There has been some recent interest in whether the Toolset could be used to create MS Office documents.  So being the adventurous guy that I am, I set off to see whether it could be done.  And I’m pleased to say it’s possible and really straight forward.

Imagine you were building out an employee on-boarding process using the Toolset.  At some point in the process, a user needs to generate an On-Boarding Form that will be used by IT to do things like order a laptop, setup the user in AD and grant access to one or more systems.

The user could simply go to a grid display for all new employees.  They would click on an icon that will then invoke MS Word with the employee details already merged into the document.  This could be printed or emailed to the appropriate people. 

This article is focused on setting up the provider needed when the user clicks on the icon.  There are other articles in the community on how to setup a grid display with icons.  In our use case above, the icon would simply leverage the provider that you will create using the steps below.

Here are the steps for creating the provider needed to create the Word ML:

1) Create your Toolset provider using a SharePoint Data Provider if your employee content exists in a SharePoint list.  Or use the External Data Provider if your employee information resides in an external data source like SQL Server.  If you need help setting up your provider, there are a number of community articles and related videos on how to setup a Toolset provider.

2) Create your document in MS Word formatting the way you want the document to look when the Toolset provider creates it.  The placeholders in bracket are where you want dynamic content to come through the Toolset provider.  More on that in a moment.

3) Now save your document as a Word XML Document.  In our example below we are saving it using the Word 2003 XML Document file type.

3)  It’s time to move on the XSLT needed to insert our dynamic content into the Word XML that you just saved in the previous step.  You will need to have an XSLT file that contains the following XSL.  Be sure to change the <xsl:for-each select=”/NewDataSet/Table1”> to the node structure of your provider.  NewDataSet/Table1 is the default node structure for a SharePoint Data Provider.  NewDataSet/Data is the default node structure for an External Data Provider.  Be sure to reference your actual provider for its node structure.

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
    <xsl:output method="xml" indent="yes" standalone="yes" />
    <xsl:template match="/">
        <xsl:for-each select="/NewDataSet/Table1">

        [Insert Word XML Here]

        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

4) Now you cut and paste the Word XML into this XSL file.  You don’t need the xml declarations at the top.  So just highlight everything except the first two lines and insert into the XSL file a the appropriate location where you see [Insert Word XML Here] in the code snippet in the previous step.  Save your XSLT file.

Word XML file:

XSLT file after copy and paste:

5) Now replace all your placeholders in the XSLT that came over from your Word XML with the appropriate xsl statement for selecting values in your provider.  Once you have replaced all your placeholders and saved the file, you’re ready to move on to the next step.

6) If you are using a SharePoint Data Provider, you will need to create a Mashup Adapter that uses your provider and applies the XSLT file you created in the previous steps.  If you don’t know how to create a Mashup Adapter, there are articles in the community on how to do this.  Note: this step can be eliminated in the 1.5 release of the Toolset.  The SharePoint Data Provider prior to 1.5 is not outputting the encoding XML declaration that is required by Word ML to process correctly.  If you are reading and using Toolset 1.5, you can skip this step.

7) If you are using an External Data Provider or the SharePoint Data Provider starting with version 1.5, you will need to complete this step. 

If you’re using a SharePoint Data Provider, create an external data provider HTTP Post connection that references your provider.

<Data>
    <Name>EmployeeOnBoarding</Name>
    <Default>false</Default>
    <ConnectionType>HTTP Post</ConnectionType>
    <HttpPostURI>Insert URL to your SharePoint Data Provider</HttpPostURI>
    <UseCurrentUserCredentials>2</UseCurrentUserCredentials>
    <UserName>%CWUserID%</UserName>
    <Password>%CWPassword%</Password>
    <XslStylesheetLocation>Insert URL to your XSLT file created in step 4</XslStylesheetLocation>
    <OutputType>application/msword</OutputType>
</Data>

Reference the URL to your SharePoint Data Provider in the HttpPostURI node.

In the XslStylesheetLocation Node, you will reference the URL for the XSLT file you create in step 4.

The OutputType node tells SharePoint to treat the output of this provider connection as a MS Word document.  So rather than opening the aspx page for the provider as XML, it will invoke MS Word.  There are a number of content types that can be used here.  There will be a separate list of these content types posted to the community at a later time.

If you are using an External Data Provider to grab your employee details, you would just need to add the XslStylesheetLocation node and OutputType node as they are above into that connection.

Provider output without the OutputType node applied.

When you add the OutputType node, the provider will invoke MS Word.

You have successfully setup a Toolset provider to create MS Word documents.  Imagine the possibilities.  Of course, you need to implement this provider in a user display like a grid.  So when they click on an employee, it leverages your provider to create the MS Word document.  There are some helpful articles in the community on how to do this.

During a recent customer engagement, I was asked to help customize the Rich Text Field (RTF) available in the Toolset.  The following customizations are available whether you are using the RTF in a Business Data Form Adapter (BDF) or in a list or library.

The customer wanted to customize the tool options and configure a default font type and font size for RTF being configured in several of their BDF forms.

Below is a screenshot of how they wanted the RTF control customized.

You will notice that there are less tool options than what the out of the box RTF control comes configured with.  The customer wanted to also set the font type and font size while removing the ability to change it within the UI.  In this example, it’s been configured to use Times New Romans and 24pt font.

Here are the steps for configuring the RTF to look as it does in the above image.

1) Create an XML file for the tool options being configured.

<?xml version="1.0" encoding="utf-8" ?>
<root>
    <tools>
        <tool name="AjaxSpellCheck"/>
        <tool separator="true"/>
        <tool name="Bold"/>
        <tool name="Italic"/>
        <tool name="Underline"/>
        <tool separator="true"/>
        <tool name="JustifyLeft"/>
        <tool name="JustifyCenter"/>
        <tool name="JustifyRight"/>
        <tool separator="true"/>
        <tool name="InsertOrderedList"/>
        <tool name="InsertUnorderedList"/>
        <tool separator="true"/>
        <tool name="Outdent"/>
        <tool name="Indent"/>
        <tool separator="true"/>
    </tools>
</root>

If you look at the image above, you will notice the correlation between the options being configured in the XML file and what appears in the UI.

2) This file must be placed on the server in the following path. And needs to be placed on each SharePoint WFE.

3) Create a CSS file for the font type and font size desired.

body
{
    font-family: Times New Roman;
    font-size: 8pt;
}

The External Data Provider is THE Swiss Army knife of the Toolset. Sure it connects to your external data, but did you realize all the connection types it supports and the possible properties you can specify with each? During this blog series we’ll cover all the connection types, the properties associated with them and also provide examples for you to reference going forward. This article assumes you have seen a connection file before and understand its format of an opening & closing <CorasWorks> parent element with each connection being stored as a <Data> element within.

This final article in the series will cover two topics actually – the File connection type and the aggregate properties that can be used when “mashing up” data via the External Data Adapter.

A connection type of File, as the name implies, is for when you simply want to connect to a file containing XML data. If the file in question is in a web accessible location, this connection will actually work the same way as if you created an HTTP Post connection to the URL of the file. However, imagine a situation where you have an XML file on a network share. Perhaps you have some legacy or archived data; or it’s a secured application in where you only need periodic data dumps from a secured source you cannot or do not want to tap directly into.

With the File connection type, you can place that XML file on any network resource accessible (from the perspective of the SharePoint server) and pull it in as if it were any other connection type. Update the file in its location; see the results in your SharePoint applications.

The File connection type rivals the HTTP Post in its simplicity. A connection could be as basic as:

Your path is then simply a URL, like in an HTTP Post connection, or it can be a UNC network path such as “\\ServerName\SharedFolder\MyXMLFile.xml”. Once you’ve connected to that XML file on your network or out on the web, you have the familiar properties from the other connection types available as well.

They are:

Finally, I’ll share some properties that can reside outside your individual connections (i.e. outside your <Data> elements) which we call the aggregate properties. A handy capability of the External Data Provider is that you can actually do mashups with it. It doesn’t provide all the feature/functions of the dedicated Mashup Adapter, but for quick & simple vertical mashups, it can often fit the need in short order.

To perform a “mashup” with the EDP, simply create a connection file that (a) has one or more connections set as the default and (b) has one or more connections with the same name. If both these conditions are met, the EDP will make all the connections listed and output their aggregate result as a vertical mashup.

In doing so, we’ve then provided some aggregate properties that you can set for the behavior/handling of the aggregate data returned. Many work just like the similarly named properties within a given connection type, they’re simply applied at the macro level.

These aggregate properties are:

Wrapping up the series, you can download a sample connection file for a File connection, as well as an example of a connection file employing aggregate properties, from the Building Block Exchange section of the CorasWorks Community forum here.

We hope this series has proven useful in opening your eyes to all the possibilities in the EDP. Check back soon for a related post on the EDP’s newest (Toolset v1.4 & later) capability; two-way support for Binary data in your ADO connections.

The External Data Provider is THE Swiss Army knife of the Toolset. Sure it connects to your external data, but did you realize all the connection types it supports and the possible properties you can specify with each? During this blog series we’ll cover all the connection types, the properties associated with them and also provide examples for you to reference going forward. This article assumes you have seen a connection file before and understand its format of an opening & closing <CorasWorks> parent element with each connection being stored as a <Data> element within.

This article will focus on the dark horse of the EDP connections, that oldie but a goodie – the HTTP Post.

Whether it’s a classic web-based application or a web 2.0 mash-up integrating external services like RSS Feeds, Twitter, Facebook & more, the HTTP Post connection allows you to interact with data that’s local or out on the web that may not be in a traditional database or addressable via web service. Especially with RSS feeds and, more recently, social networking sites, the HTTP Post connection type makes it easy to grab data that is already being served out as XML. Add in the ability to post parameters in as well, and you have two-way interaction.

Let’s look at the basic requirements for a successful HTTP Post connection:

As you can see, it’s certainly the easiest of the connection types. As long as your “URLtoData” is a web page outputting XML, such as an RSS feed or even a Toolset Provider sitting on another SharePoint farm, you can now bring that data into the local SharePoint environment and use it within your applications.

I also alluded to interacting with external data sources through HTTP Post connections. One recent example of this is a “Twitter Dashboard” we built; within it, we’re both aggregating the latest Tweet from a series of users we wanted to track as well as enabling users to make changes and post new Tweets to their Twitter account, all without ever leaving their SharePoint site.

Listed here are all the optional properties for use with an HTTP Post connection; just like a web service connection, <ProtocolVersion> is available here as well, in addition to the HTTP Post-specific properties <ContentType> and <Parameters>:

No to be left out, you can download a sample connection file for an HTTP Post connection from the Building Block Exchange section of the CorasWorks Community forum here.

Check back next week for the final post, Part 4 in the series, where we wrap up the secondary connection types and the aggregate properties.

The External Data Provider is THE Swiss Army knife of the Toolset. Sure it connects to your external data, but did you realize all the connection types it supports and the possible properties you can specify with each? During this blog series we’ll cover all the connection types, the properties associated with them and also provide examples for you to reference going forward. This article assumes you have seen a connection file before and understand its format of an opening & closing <CorasWorks> parent element with each connection being stored as a <Data> element within.

This article will focus on my favorite connection type for building web 2.0 and enterprise mashup applications – Web Services.

When creating a web service connection type, effectively what we’re doing is telling our External Data Provider which web service to call through its documented endpoint and passing in the SOAP Action and Envelope for the web service method (also called operation) you want to execute.

Let’s look at a sample web service method provided out-of-the-box in SharePoint. This screenshot shows the details for the “GetUserInfo” operation of the “Users and Groups” web service in SharePoint. You can access this same page on any SharePoint environment (WSS v3 or MOSS) by appending the following to any Site’s URL: /_vti_bin/usergroup.asmx?op=GetUserInfo. You can also leave off the “?op=…” portion to see all the operations the User and Groups web service supports.

First, let’s briefly decode what we’re looking at – and need – from this web service. The key here is that just about any data source that supports SOAP web services, be it SharePoint, Dynamics CRM or even commercial web services like those offered by companies such as CDYNE, will have a page and/or documentation like this. For CorasWorks Data Integration Toolset customers, this means calling a web service is as easy as copy-pasting the three highlighted sections you see above.

In terms of your connection setup, the yellow text represents the Request URI, the orange your SOAP Action and the green section your SOAP Envelope. Let’s now see how this looks inside a Toolset web service connection; the sample below is for the web service method in the screenshot above.

This is all you need in a connection to begin working with web services. You may notice that I left the “?op=” part off of my request URI; this is fine because, by virtue of my SOAP Action and envelope, the web service knows which method to execute. And don’t forget; a web service connection supports all the EDP variables and parameter replacement options an ADO connection does. In our example here, I’m using the EDP variable %CWUserName% to dynamically pass in the username of the currently logged in user when this connection is run.

And just like an ADO connection, here are all the optional properties you can set with a Web Service connection; the only ones not available on an ADO connection, are <ProtocolVersion> and <DecodeXML>:

Just like yesterday’s post on making an ADO connection, you can download a sample connection file for a web service connection from the Building Block Exchange section of the CorasWorks Community forum here.

Check back tomorrow for Part 3 in the series – HTTP Post connections.

The External Data Provider is THE Swiss Army knife of the Toolset. Sure it connects to your external data, but did you realize all the connection types it supports and the possible properties you can specify with each? During this blog series we’ll cover all the connection types, the properties associated with them and also provide examples for you to reference going forward. This article assumes you have seen a connection file before and understand its format of an opening & closing <CorasWorks> parent element with each connection being stored as a <Data> element within.

So, without delay, let’s look at the first (and most common) connection type – ADO.

The ADO connection type is the method you’ll use when connecting to most traditional databases and legacy systems. Generally speaking, any data source that supports an OLEDB or ODBC connection has a connection string that can be used for accessing that data source. The most comprehensive listing of these connection strings can be found online at www.ConnectionStrings.com; when on the site, look for the “ADO” or “ADO.Net” version of a connection string for your data source. You can also find connection string details within a source’s documentation or SDK.

When creating an ADO connection, there are two properties specific to an ADO connection type: <ConnectionString> and <Query>. As with all connections, you will also need a <Name>, <Default> and <ConnectionType> property.

Thus, an ADO connection can be as basic as:

Now, let’s look at some of the additional properties that are optional for an ADO connection:

And just to make it all easier to hit the ground running, you can download a sample connection file with an ADO connection from the Building Block Exchange section of the CorasWorks Community forum here.

Check back tomorrow for Part 2 in the series – Web Service connections.

This series of screenshots and comments will help you implement a master-detail grid solution using the Toolset. The instructions assume you know how to create providers for the grid displays that will accept passed in parameters for filtering the provider. (If you need more information on this, you can reference this document.)

Note: screenshots have been redacted to protect sensitive data.

Top grid displays technical specifications. When you click on the Documents icon, the bottom grid will display related documents for that specific technical specification.

This screenshot shows the loading icon when documents are loading in the grid after clicking on the Documents icon in the top grid.

This screenshot shows the related documents after the grid loads.

To implement this place a grid on your page that loads master items. We will later return to what you need to add to this grid display to create the Documents icon which provides the interaction between it and the documents grid.

Now create a web part page that has nothing but web part zones. Place a grid display on this page for the related documents. Configure it for passthrough. In the example below, it’s looking for a single passthrough parameter called TSN.

<PT><P><PTType>post</PTType><PTRequest>TSN</PTRequest><PTName>TSN</PTName></P></PT>

Now place a Page Utility web part on the same page as your master (parent) grid. Configure it to load the page with your child grid. In our case, this was the web part page with the Related Documents grid. Note the name in our example - RelatedDocs.

<PageLoad>
<Setup>
<Name>RelatedDocs</Name>
<Id>RelatedDocs</Id>
<Width>100%</Width>
<Height>390</Height>
<Src>http://devrapps.corasworks.net/rfdev/hapts/Toolset/TSDocs.aspx</Src>
<FrameBorder>0</FrameBorder>
</Setup>
</PageLoad>

At this point, there is a main grid for displaying Technical Specifications. And a Page Utility with a second grid display for displaying Related Documents that has been configured to accept a passthrough parameter.

The only remaining step is to add a new column to our Technical Specifications grid for the Documents icon. When the user clicks on the icon, it will load the Page Utility with a reference to the web part page with the Related Documents grid display. And it will pass in the TSN.

The Add Column XML for the Documents icon is provided below. The main take away from this step is the javascript function for reloading the Page Utility. By appending the name of your Page Utility (RelatedDocs) to the function WTFrameSetter, you can invoke the reloading function. WTFrameSetter is a built-in function in the Page Utility that allows you to reload the source URL.

Notice in the Add Column XML that we are concatenating the Technical Specification Number to the end of the page URL.

CorasWorks has been using a new verb lately, Jing. This new concept has allowed us to quickly create a how-to, walk through, and demo. Since we have captured so much, we figured perhaps you would be interested in getting access to a continuous feed of them, so here are some RSS links to help.

Toolset How-To RSS Feed

http://www.screencast.com/users/CorasWorks/folders/Toolset%20Captures/rss

Suite How-To RSS Feed

http://www.screencast.com/users/CorasWorks/folders/Suite%20Captures/rss

Community Walk Through RSS Feed

http://www.screencast.com/users/CorasWorks/folders/Community%20Resources/rss

Demo RSS Feed

http://www.screencast.com/users/CorasWorks/folders/Demo%20Scripts/rss

Jing is a free software that adds visuals to your online conversations. Learn more at http://www.jingproject.com/.