How to get the underlying view of a form using lotusscript

Question

Hi, I am a newbie in lotusscript. We have a legacy lotus notes/domino system. We need to migrate the data from lotus notes to oracle. Lotus domino's data model is entirely different than that of oracle's data model. So, we are creating a relational table per Lotus notes form. I can access the views programmaticaly using lotusscript. But I couldn't get the views associated with a Notes form using lotusscript. Could anyone please give a code snippet or explain the relation between from and view? I need to migrate the data associated with a single form even if it is connected with multiple notes views.

Will the following code snippet work?

Dim session As New NotesSession
Dim db As NotesDatabase
Dim notesDocumentCollection As NotesDocumentCollection

Set db = session.CurrentDatabase
Set notesDocumentCollection = db.FTSearch( """Form=Help Ticket""", 0)

For i = 1 To notesDocumentCollection.Count    
  Set doc = notesDocumentCollection.GetNthDocument( i )  
  ' process each document 

End Forall

Answer 1

In Lotus Notes, views operate using selection criteria. These criteria can reference forms (but don't have to). For example, say you create a form called "HelpTicket". Any documents created using this form can then be selected for display in a view using a "view selection formula" like so:

SELECT Form="HelpTicket"

… and columns in the view can then be added to pull out data from the selected documents, by referencing the fields laid-out on the HelpTicket form.

Where I suspect you're getting confused with regards the form / view relationship is around this idea of migrating the data: forms are both a schema definition for their associated data, and a way of presenting that data, on a document-by-document basis. Theoretically, data can be presented via any number of form "definitions" in Notes, i.e. data and presentation are quite separate, but in practical terms there is a relationship between a form and the documents that reference it.

As an aside, it's probably worth mentioning that Lotus Notes is not a relational database system, it can be more readily described as a document-based database, or a form of "NoSQL".

Now, back to views: these then present data via columns, categories, and simple calculations based on the underlying data (a view column may either simply reference a field value, or can perform basic operations on that value via the proprietary Lotus Notes formula language).

So, in short, the data you need to migrate are your Lotus Notes documents. The views are kind of irrelevant, although you may wish to replicate what they offer in terms of reporting / visualising said data.

Answer 2

I suggest you avoid using Lotusscript altogether. It doesn't sound like you are using this Notes database after you migrate the data, so the only thing you need to do is get the data out. You can do that a lot easier if you use the NotesSQL driver (http://www.ibm.com/developerworks/lotus/products/notesdomino/notessql/)

Once you've configured the driver for your database, just use Excel or Access to connect to the database and pull the data in. You can then easily reformat the data properly so you can import it into Oracle.

The SQL driver can pull data based on Form, so you can get all HelpTicket documents (and their items) into a table in Access or a sheet in Excel.