|
This is the first of a series of articles that show you how to get the most out of your Lotus Notes applications by extending their reach and functionality using standards. This first article demonstrates, step by step, the automated creation of a SharePoint List based on the scheme and data of a Domino web View. Thanks to the built-in URL commands found in Domino, this is extremely easy. So spend a few minutes learning these methods and you will greatly increase your ability to use Notes/Domino data in a heterogeneous environment. It's true that this article shows Notes data copied to SharePoint and migrated back into Notes. In the subsequent articles we will surface, not copy, Domino data in a SharePoint site and link it to non-Notes data.
Many Lotus Notes/Domino users suffer from the misperception that it is closed and proprietary just because much of its functionality pre-dated standards. In truth, Lotus Notes' embrace of standards like XML and web services makes it an integrator's dream and the following demonstration proves it. The URLs shown are to my public Domino server so you can try it yourself. However, I encourage you to try it on your Notes databases. At the end of this article I will discuss the advantages and limitations of this method.
First let's look at our Notes database in a web browser. Here is a "Web View" of a basic Notes database (Orders.nsf)
This URL is actually a "URL command" for the Domino server. Notice the Server name, Database name and View name in the URL. After the "?" the command "openview" is used. (Note: this is a built-in function of a Domino Server with the HTTP task running.)
Now we change the "openview" command to the "ReadViewEntries" URL Command to retrieve an XML version of the View.
Note |
The "&count=-1" parameter requests all documents in the View. However, this is still limited to 1000 by a default server setting. For ways around this limit, please click here. |
This gives us an XML version the data found in the View. For more information on Domino URL commands, look in Lotus Notes help documentation. The default format of ReadViewEntries XML is not easy to work with. We will address this soon.
The ReadViewEntries URL gives us a window to our database data as defined by a View. Next we will use this window to pull a snapshot of the data using Access 2007.
We start Access 2007 and create an empty database.
Next, in your new blank Access database, on the ribbon under the "External Data" tab, in the "Import" section, select "XML File".
A dialog appears asking for the source of the XML. Here we enter the URL for our URL command "ReadViewEntries" and click OK.
The wizard imports the data and shows a tree view of the schema which does not contain the data we want. We will need to apply a Transform. Click "Transform..."
The wizard will ask you for the location of an XSLT file. You can download mine here.
Put it on your file system then click the "Add..." button and navigate to the XSLT file. Once the file is added, you can select it and click "OK".
This is what my XSLT looks like (I took a transform that created HTML and changed it to create nice XML)
Now we have our XML data in a format Access 2007 likes. Click OK. (note: I have used this transform with many technologies not just Access)
You are then prompted to save these import steps
Here, just click Close.
Now we have an Access 2007 relational database table called "viewentry" with our Notes Data in it.
Here you have the opportunity to go into the Database Design and set the Field data types. (i.e. Number, Date, Time, String). This way you can easily cast the data into the desired format. This is not required but is a good way to find bad data.
Finally, with our "viewentry" table selected, we pick "SharePoint List" from the "Export" section of the "External Data" ribbon tab. You are asked for the address of a SharePoint Site to create the new list in. (I like to use my "MySite" URL.) Enter the URL of a SharePoint site and click OK.
By default, the wizard opens the new SharePoint list in a Datasheet View. In the Datasheet View, we can edit, sort, and filter data.
Using the "Actions" menu, pick "show in standard view" to see a standard list view.
Standard View:
Here is the list seen in a standard view after configuring the view to show the "Modified By" and "Created By" fields. They are SharePoint built-in fields and the values are set to whoever ran the process.
Also a default web form has been automatically created for the orders.
To bring this data back to a Notes database, we can save the view as an Excel spreadsheet. Use the "Actions" menu to "Export to Spreadsheet".
The result is an Excel spreadsheet:
In Excel you can "Save a copy of the document" in "Other Formats".
"Text (Tab delimited)(*.txt)" and "CSV (Comma delimited)(*.csv)" formats both should work according to the Lotus Notes help documentation on importing.
Let's talk about the value and limitations of this process. It's a great demo to show the openness of Lotus Notes/Domino without doing anything special to the server especially when admins won't let you put any code on their servers. It's a jumpstart in using SharePoint by creating a populated list you can use to try SharePoint reporting, workflow, web UI, linking data, and other services provided by SharePoint. The moved data can be exposed to enterprise wide search, life cycle management, check-in/check-out, sorting/filtering/grouping and more. In any case, it good to know you can do this. However, we are limited by what you can put in a Notes View. Data validation, code logic, audit trail information and form UI are not moved. If you need these things, there are tools available to help you move or recreate them in a SharePoint environment. One I've been involved with is the Quest Notes Migrator for SharePoint.
In the rest of the articles in this series we will show live Domino data in SharePoint, LotusScript, linked data and cool UI tricks. These methods are easy and increase the value of your Notes applications and your skills.
|
|
About the Author
 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.
|