How to get the underlying view of a form using lotusscript

Question

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.

Answer 3

A late answer, but it may help someone: When trying to allocate similar 'terms' between Notes and SQL, it may be helpful to think of Notes Views as SQL Tables, Notes Documents as SQL Records and Notes Agents/Events as SQL triggers. Notes Views with sorted columns are like tables with SQL indexes. The sorted Notes columns define the index. To 'query' a Notes View, you either scan the view (equiv to an unindexed SQL table scan), or do a key-based lookup (select where y=x). There is no query optimisation as such, you have to choose how to query the view. These terms are an approximation. Notes Views are more like materialised views in SQL and documents as mentioned elsewhere are schemaless records that can contain dissimilar fields and data (but generally they don't).

As the other answers have mentioned. accessing Notes data via Notes-SQL is fairly straightforward and is more 'grokable' to SQL savvy users. You will get a table on answers based on SQL select queries. For Notes/LotusScript savvy users, scanning the views and writing agents to export data as CSV values for import into SQL later is straightforward. You can even just use the built view-exports to get a CSV table of data you can then import into SQL.

One major 'gothcha' with Notes is that the data in a single field or view column may have 'type issues' depending on how good the developer(s) is/was. For instance, in one row a data may be a true-date, and in another row it may be represented as a 'text-date'- This can choke an import routine unless you handle this possibility. Other issues are delimiters and field lengths; Notes doesn't use explicit field sizes and so some fields can be larger than expected and contain delimiter characters than can choke your SQL import. There are other issues to address as well. To address a lot of these issues, if possible, create a view for each form you want to migrate, put every field in the view, add formula to clean the data if necessary and export each view as a CSV and treat it as a SQL table.