Extracting list of values from lotus notes fields

Question

I am migrating a Lotus Notes database to SQL Server using the LN java API. While traversing through each LN field in the documents I find all tabular info have field names like fld, fld_1, fld_2 etc where fld represents the name of a column and the numbering scheme is to take care of each individual row. Is there an simple way of extracting these info as arrays using the LN java API?

Answer 1

There's no simple way, or more specifically, no way that Lotus Notes will help you with the task. Lotus Notes is a 'flat-file' database and each document is allowed to have any number of items (columns) within it.

Although a Lotus Notes form might display data in a tabular format using a table or layout region, the document (record) that holds that data is simply like a single row of a SQL table, and there are no ways to logically group items of that document, for example, to relate all the fld_ items together.

My suggestions is to use whatever data structure you find convenient to get the data from the items into SQL. That might be an array or a custom object that can then be saved to the database.

Answer 2

Well "simple" can be an ambiguous term.

If you haven't solved this problem yet. You can write a method that loops around and grabs each field like this:

import lotus.domino.*;
....
public static void main(String[] arg) {

    for(int i=1 ;i<MAX_FIELD;i++) {
        Item itm = doc.getFirstItem("field_"+String.valueOf(i));
        if (itm != null) {
            // if it's a multi-value field.
            Vector v = item.getValues();
            // do other stuff here with the values.
         };
    }
}

You'll need to make sure you import the appropriate NotesJava API's in your project.

Remember that each field on a document is effectively and array with 1 or more values. Those fields ("field_1", "field_2", etc) were defined arbitrarily by a developer. This is a common practice to emulate table-like data structures. Usually the fields represent columns not rows. But accessing the field in this way using the "item" object should give you access to the data.