Writing Code to Deal With Multivalued Data 

Introduction

Beginning developers often have problems figuring out how to program with multivalued fields.

There are three main reasons for this:

• Misunderstanding the distinction between a true multivalue and delimited strings
• Misunderstanding the relationship between forms and the data stored in items
• Not knowing the methods and operators to use

Multivalue versus Delimited String

The first thing you must do, is forget about forms. We'll deal with them in the next section. Think of a Notes document as a container for items. Each item has a name, a datatype, and a value (as well as some other flags and header values). You can see this information about an item using the Document Properties infobox from a view. For instance:

Figure 1: Multivalued Text Item
This is an example of a true multivalued item. The document contains an item named Category, and its datatype is Text List. The value of the item is shown at the bottom right; it has two values ("A Category" and "Another Category") listed on separate lines with quotes around each value. The values are listed one per line, regardless what the corresponding field on a form may have selected for input or output list delimiters. Again, this has nothing to do with forms as yet.

To be multivalued, an item must have a data type that ends with List, like the Text List above. Here's an example of a Number List item:

Figure 2: Multivalued Number Item
Lists of numbers and date-times don't use quotes around the values, but you would still see the multiple values on multiple lines. Again, it doesn't matter whether there's a form out there with a QuarterTotals field that lists semicolon as the multivalue delimiter; the values are still listed one per line in this dialog.

Here's an example of an item which appears to be multivalued, but is not:

Figure 3: "Delimited String" Single-valued Item
Notice the differences; the data type is Text, not Text List, and the value is shown in a single set of quotes. The value contains a delimiter character (comma) and you might look at this and think, "two values," but that's only happening in your mind. Notes considers this a scalar value -- a single string value that in this case just happens to contain a comma. To make it clearer consider a different field, Subject:

Figure 4: Another Single-valuedText Item
This value also contains a comma, but we can see that the value is not intended as a list. But you only know that because of your language understanding. Notes considers these two cases the same.

Different documents in the same database can have different datatypes for the same item name. For instance, Figures 1 and 3 above come from the same database. Once again, at this point the form is irrelevant; any document can contain any item with any name and datatype; it doesn't have to match other documents, even if those other documents were created with the same form.

In most applications, inconsistent fields like this suggest a programming error. It's a suspicious sign if the same item name has different datatypes in different documents, or is sometimes a delimited string and sometimes a multivalue.

NOTE: Sometimes the Document Properties window will show multiple items with the same name (the same item name appears more than once on the list on the left). This is normal, especially for rich text fields, but it is not how multivalues are represented. Duplicate item names are used when the contents of an item exceed the item size limit; it's split into multiple items which are each under the size limit. Regular multivalues are generally represented in one item with multiple values, as shown in Figures 1 and 2. Duplicate item names with non rich-text items, generally means someone has misused the NotesDocument.AppendItemValue method. For regular programming, you don't need this method at all.

Form Fields versus Document Items

Now let's consider forms. A form does not store data. The data are stored in the items of a document. The fields on a form describe what sort of items you want to create in the document. But these field descriptions are only relevant when you edit and save the document using the form. If you write code to manipulate the document directly, you can assign its items however you like. Many times, when you have problems with multivalues, it's because the form's description of the field is inconsistent with the way you're manipulating the item in your code.

For instance, let's look at that Category field, which is defined on a form as:

Figure 5: Field options for a multivalued field
When you edit the document using this form, it would produce an item value such as is shown in Figure 1. But if you run an agent that contains the following INCORRECT code:

then you create a scalar value -- a single string containing a comma, like that shown in Figure 3. The agent doesn't look at the form at all, so it has no way to know that comma is intended to be used as a delimiter in that field. The correct way to do this assignment is:

The colon character is not a list delimiter here; it's an operator (like + or *), whose function is to take two values and concatenate them together into a list. A colon outside of quotes is the list concatenation operator. A colon inside of quotes is just the character ':'.

In Figure 5, the Type of the field is "Dialog List" -- this describes the UI of the field. But the type of the item created by the field is "Text List", as shown in Figure 1. There are only a few item datatypes, but many field UI types. All of these different keyword and Names and so forth, end up being stored as Text (or Text List if multivalued). So a Dialog List is stored as Text, a Names field is Text, a Checkbox is Text, etcetera. Just as the document properties don't know anything about the delimiter character of your field, they don't know anything about the other UI-based attributes of the field.

You can use an agent to assign any item name with any value; you don't have to use an item name that corresponds to a form field. When you edit a document using a form, any items that don't correspond to form fields will not be touched. However, if there is a matching field name, the formulas, formatting and data types of that field will be applied to the document item when you save. So you can "fix" some erroneous values by editing and resaving the document. But that works only for fields that are on the form.

Back End and Front End

"Back end" as applied to documents, refers to functions used to manipulate the contents of the document without the intervention of a form, as for instance in any agent run from a view, or a server scheduled agent. "Front end" refers to functions that operate on the contents of fields in an open document window.

In formula language, the "back end" functions are things such as the FIELD statement, or referring to fields by just using their names, whereas the "front end" functions are the @Commands that let you mess with what's visible on screen, such as @Command([EditInsertText]) to add text to the field where the cursor is sitting.

In LotusScript, the front end classes are easy to identify because they contain "UI" in their names, e.g. NotesUIDocument as opposed to NotesDocument. Like the @Commands, these functions let you move the cursor around on screen and insert or replace text into fields.

In both cases, there's a certain degree of blurring of these when you're editing a document, because when you use the back-end functions to assign a field in the document you're editing, the change is usually instantly reflected in the front end also (except for rich text, or unless you deliberately deactivate this functionality for better performance).

The distinction is important when working with multivalues because, when you read or assign a multivalued field in the front end, you're working with the text string that the user sees on their screen, which is a single delimited string, and the UI converts it to a list when you save or refresh. When you work in the back end, you use a list value, and the UI converts it the other way for the user to see, inserting the output delimiter between the values.

We generally recommend using the back-end methods for working with field values, with the exception of rich text. That's because back-end code doesn't have to worry about how the data are formatted; it doesn't have to know whether the field is delimited with commas or semicolons, or what the user's currency symbol is, or whether they use commas or periods as their decimal point, or anything else that can trip you up, making the application work on one user's computer but not another. Sticking to back-end methods where possible makes your application easier to maintain, since you can then change how the field is formatted in the UI without worrying about also having to update the code that works with that field.

Also, using back-end methods gives the potential for sharing business logic between code that works on an open document, and code in an agent. You can put back-end code in a script library and call it from both places, rather than writing a back-end version for agents and a front-end version of the same code for when you're editing a document.

Programming Techniques

Formula Language

As shown above, the concatenation operator (:) is used in formula language to create lists. Do not use + (plus) for this purpose. Do not expect the formula language to convert a delimited string into a list, even if the delimiter you use matches that specified for the field on a form.

If your application requires you to convert a delimited string into a list, or vice versa, use the functions @Explode and @Implode (if there might be extra whitespace in addition to the delimiter, @Trim may be used on the output of @Explode).

To work with a value that's already a list, it's useful to know that nearly all the operators and @Functions do something reasonable when passed multivalued data (even if the Designer help doesn't specifically say so). For instance, if you have a multivalue whose items are each of the form "code:description" and you want a list of just the codes, you can use:

Please note: The character ":" is being used here, not the list concatenation operator :.

A couple of functions that do not work with multivalues are @Prompt and @Statusbar. If you want to display the values of variables for debugging purposes, use @Implode to convert them to a scalar string, or these functions will only show you the first value.

If you have documents containing single-value delimited strings, and you need to convert them to multivalues using the delimiter defined on the form, you can explicitly do so using @Command([ViewRefreshFields]). However, this may have side effects you don't like, so it's probably safer to write an agent to do this update for you. For instance:

Or, of course, you can manually resave the documents.

When working with formulas, it's especially important to be aware that input translation formulas, and value formulas of computed fields, are evaluated after the client has already applied the delimiter rule to the text in the field. So if your input translation formula returns the scalar string value "hello, goodbye", the field will be assigned that string value, not the two values "hello" and "goodbye", even if comma is given as a delimiter in the field definition.

LotusScript

In LotusScript, multivalues are represented by arrays. In fact, all regular field values are represented by arrays; if the field is single-valued, the array contains only one element. That's why, when you refer to a single-valued field, you use the array notation to extract the first element of the array:

Beginning developers have often gotten so used to putting (0) after a fieldname, that they forget that it is actually an array index. If you want to get all the values from a multivalue field, you must not use (0), thus:

To assign a multivalued field, you must assign it from an array. For instance:

Note: ReplaceItemValue is generally preferred to the "extended" syntax (doc.fieldname) for assigning item values, because it preserves the case of the item name, and because it's less likely to break in future versions if a new method or property with the same name is added to NotesDocument.

If you have a delimited string and you want to assign that to a multivalued item, you can use the Split method to do so. Please remember that the values will be exactly what you assign, so pay attention to extra whitespace and null elements.

DO NOT USE the NotesItem.AppendItemValue method. This is not useful for working with multi-values; it's about multiple items with the same name, as described above. LotusScript handles the multiple-items thing automatically.

The NotesDocument.AppendToTextList method is occasionally useful, but usually, it's more convenient to assign the field value from an array containing all the values, rather than appending to it. The trouble with appending is, if the item already exists and is blank, you end up creating a two-value list with "" as the first value. If the item has the same name as a form field and you edit the document with that form, then the blank value will go away, but when writing back-end code, it's generally just a bother to have to worry about what's already in the item. Also,

If you have documents that already contain incorrect values (scalar strings with delimiters in them, or the wrong datatype); you can use NotesDocument.ComputeWithForm to apply the form to the document. However, this is (a) slow, and (b) not exactly the same as editing the document with the form. It's generally more efficient and a little safer to just use the right form of assignment in the first place, and if you need to fix wrong data, write an agent to reassign just those items.

NOTE: Although the word "list" is often used to refer to multivalues, the List datatype in LotusScript has nothing to do with multivalues. Multivalues=arrays in LotusScript.

Java

When you read an item value in Java, you can choose the return type based on what method you call (Document.getItemValue vs. getItemValueString, for instance). If you know the item is (or might be) multivalued, use getItemValue, which returns a java.util.Vector with an element for each value, whereas the methods to return specific types only return the first of multiple values.

Likewise, if using the Item class, the Values property is more useful for dealing with multivalues than ValueDouble or ValueString. The Text property will return all the values as a semicolon-delimited string, but that doesn't let you tell the difference between multiple values vs. single values that contain semicolon. If the multivalue delimiter defined for the field is not semicolon, then there can legitimately be semicolons in the data even if the item was assigned by editing a document.