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.
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.
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.
Then select "Number" from the choices.
The "Format Number" dialog lets us specify "Currency" and 2 decimal places.
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".
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".
This opens the "Condition Criteria" dialog. Click on the "Click here to add a
new clause" link.
Use the row column pull downs to build the condition to trigger the
formatting.
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.
Here we select the "reorderlevel" field and hit OK. Back in the "Condition
Criteria" dialog click OK.
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".
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".
When the "Web Part Connections Wizard" appears, select the action "Send Row
of Data To" and click next.
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.
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".
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".
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".
The last page of the wizard just shows us some of the settings and we click
"Finish".
(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!
 |
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. |
| 
