Displaying, sorting, grouping, and filtering live Notes/Domino data in a SharePoint "Data View" Web part is useful. However, the real value is adding calculated columns, conditional formatting, and linking the data to other SharePoint lists. Thrill your end users by combining existing Notes/Domino applications with the dynamic capabilities of SharePoint. Become a hero by extending the value of existing Notes/Domino applications. This is very easy and only takes a few minutes.
In this third article of the series, we will add value to the Notes/Domino data we surfaced in SharePoint in the previous articles (for the second article in this series, please click here). After all, why surface the data if it provides no additional value? At this point in our demo, we have live Notes/Domino data in a SharePoint "Data View" Web part using a Domino XML Web Agent as its data source.
Click to Enlarge
As you can see, end users would have no hint that this data is live Notes/Domino data. In fact, now that we have abstracted Notes/Domino data, this article's material is completely independent from the type of data source that was used (i.e., SQL Server, DB2, Lists, Web services, etc.).
We can create a rich user experience, without code, just by using the features of Data View Web parts. To access the Data View setting, we need to open our page with SharePoint Designer.
Click to Enlarge
Click the arrow at the top right of our data view Web part to display the "Common Data View Tasks" menu.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig3.jpg?openImageResource)
To start, we will re-order and add to the columns. Select the "Edit Columns..." menu item. This brings up the "Edit Columns" dialog box.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig4.jpg?openImageResource)
Highlight a column name in the right list and use the "Move Up" and "Move Down" buttons to order the displayed columns as follows: productID, description, location, cost, quantity, reorderlevel.
Next, we add a calculated column by selecting "Add Formula Column..." in the left-hand list and clicking the "Add" button. An "XPath Expression Builder" wizard launches to help you build the calculation.
Click to Enlarge
By clicking, or just typing, we enter "quantity*cost" in the XPath expression text box. Note the Preview box at the bottom showing the result of the calculation. For now, disregard the format and click OK. A new column, "quantity*cost", now shows in our data view.
Click to Enlarge
To set the format for this column data, we click the arrow that displays when you hover over one of the new column's values. On the menu that appears, click the "Format as" pull down.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig7.jpg?openImageResource)
Then select "Number" from the choices.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig8.jpg?openImageResource)
The "Format Number" dialog lets us specify "Currency" and 2 decimal places.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig9.jpg?openImageResource)
Click OK to see the formatted column data. You can change the column widths by dragging, and you can change the column label by just directly editing the text in SharePoint Designer.
Click to Enlarge
Conditional Formatting is a great feature for stressing important data conditions. In our scenario we want to highlight product quantities that are less than the stock re-order level. This requires a calculation and an action. To achieve this, we again click the arrow at the top right of our data view Web part to display the "Common Data View Tasks" menu. This time select "Conditional Formatting".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig10a.jpg?openImageResource)
Notice on the right hand side of the screen a "Conditional Formatting" panel appears.
Click to Enlarge
In this panel, click the "Create" button and select "Apply formatting".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig12.jpg?openImageResource)
This opens the "Condition Criteria" dialog. Click on the "Click here to add a new clause" link.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig13.jpg?openImageResource)
Use the row column pull downs to build the condition to trigger the formatting.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig14.jpg?openImageResource)
In the first column select "quantity (Number)". Set the second column to "Less Than". For the third column, select "More Fields" which opens the "More Fields" dialog.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig15.jpg?openImageResource)
Here we select the "reorderlevel" field and hit OK. Back in the "Condition Criteria" dialog click OK.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig16.jpg?openImageResource)
Now that the condition is defined, we can specify what format to apply when the condition is "True" with the "Modify Style" dialog.
Click to Enlarge
I've chosen to make the "quantity" field, (specified in the condition clause), font bold and red to make it noticeable. The result is this:
Click to Enlarge
There is plenty more we could do with the data in our data view. However, our next step is to link this data to a SharePoint List in another Web part. In our scenario, each product has a location and each location can have one or more sales representatives. Our application will show the list of representatives for the product location you are interested in. So, Location is our key for linking the Notes/Domino data with the representatives SharePoint list.
First, we need to add another data view Web part to our page within SharePoint Designer. Put the focus just above the existing data view Web part by selecting it then hitting the up arrow on your keyboard. Now, on the menu, select "Data View" then "Insert Data View".
Click to Enlarge
A new data view Web part appears, prompts for a "Data Source", and displays the Data Source Library in the right hand panel.
Click to Enlarge
In the Data Source Library, locate the SharePoint Lists section, expand it, click on the "Representatives" list pull down button and select "Show Data".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig21.jpg?openImageResource)
We can now see the fields found in the "Representatives" List.
Click to Enlarge
Select the "Region", "Rep_Name", and "Phone_Number" fields and drag them to the new data view part. This finishes the new data view part, and we need to focus back on our original data view part to set up the linking.
Click to Enlarge
Click the arrow at the top right of our original data view Web part to display the "Common Data View Tasks" menu and this time select "Web Part Connections".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig24.jpg?openImageResource)
When the "Web Part Connections Wizard" appears, select the action "Send Row of Data To" and click next.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig25.jpg?openImageResource)
On the following page of the wizard, select the radio button next to "Connect to a Web Part on this page" and click the "Next" button.
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig26.jpg?openImageResource)
With the third page of the wizard, we set the "Target Web Part" to "Representatives", the "Target action" to "Get Filter Values From", and click "Next".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig27.jpg?openImageResource)
The fourth page of the wizard links the key fields in our Web parts. Here, next to the "Region" field in Representatives, we select "location" then "Next".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig28.jpg?openImageResource)
We are almost done. The fifth page of the wizard lets us specify a column whose values will be displayed as hyperlinks that, when clicked, cause communication between the Web parts to happen. Here, select "location" and click "Next".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig29.jpg?openImageResource)
The last page of the wizard just shows us some of the settings and we click "Finish".
![](http://www.lotususergroup.org/glug.nsf/201004DevendorfFig30.jpg?openImageResource)
(A video showing the building of the Web part connection and how it works can be seen at here.)
Our page in SharePoint Designer doesn't appear to have any changes. To see the result, we must "Save" and "Preview" our page.
Click to Enlarge
Finally we have the finished page.
Click to Enlarge
You can see our "Notes bike parts" page with two Web parts. The top one shows Representatives information while the bottom one shows part inventory information. Look at the third row of the part inventory Web part and note there are 150 "BB Ball Bearings" in stock at a cost of $8.99 and a reorder level of 600. The calculated column we added shows the Total Value of $1348.50 ($8.99 x 150). Furthermore, the conditional formatting we added shows the 150 Quantity as red and bold because 150 is less than the 600 unit reorder level.
Because, Notes/Domino is the data source for the Data View Web part, as the data changes back in the Notes database, the data here changes as well as the calculations and formatting.
Observe the "Location" values are now links that drive the filtering of the Representatives Web part data. If I click on the "Boston" link, I will only see the "Boston" Representatives in the top Web part.
This simple SharePoint 2007 exercise can quickly provide end-user value. However, we only looked at a small set of the SharePoint features and options. Explore other menu options and consider getting SharePoint, SharePoint Designer, and InfoPath training. The Microsoft 2010 products, to be released soon, open up a whole new set of possibilities. In particular, check out Access Services 2010, SharePoint Workspaces 2010, InfoPath 2010, and SharePoint Designer 2010.
Even if this demo is very useful and compelling, there are limitations. Consider that the Notes data here is read-only and the UI is not very "Notes like", possibly making it difficult for existing Notes users. There are ways to add document-level Create/Read/Update/Delete functionality with Domino Web services, and maybe I'll create an article on it soon. Also, you can work on the way the UI looks to make it more "Notes like". Or, you could just use third-party tools like the Quest Web Parts for SharePoint to do all this for you. Like all things software, you can do it yourself or buy a tool that does it for you.
I will be presenting at THE VIEW's Lotus Developer2010 in Boston May 12-14. Hope to see you there!
![](http://www.lotususergroup.org/glug.nsf/DevendorfGaryBioPic.jpg?openImageResource) |
Gary Devendorf is currently a Subject Matter Expert (SME) on Lotus Notes applications at Microsoft, spending his time creating demos of integration and migration between Lotus Notes and Microsoft products. Many of his demos can be found at Interoptips.com. In prior positions Gary was a Product Manager at IBM Lotus, a Product Manager at Lotus/Iris/IBM covering the application development features of Lotus Notes/Domino (LotusScript, Domino Designer, Web service, toolkits and more). He is the author of the GaryDev blog, a frequent speaker at technical events, and a long-time Lotus technical expert with a thorough technical knowledge of SharePoint. |
|