IBM®
Skip to main content
    Country/region select      Terms of use
 
 
   
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

If you have multivalued fields, and you want to display a document multiple times in a view, once for each value, you have a couple of choices. One, you can categorize the column. Or, use the column option "Show multiple values as separate entries." Both ways let you sort the multi-valued column.

One thing to watch out for are the combinatoric effects if you have multiple such columns. For instance, if you categorize by the multivalue field Categories, and then add another categorized column for the multivalue field Authors, then if a document has 3 categories and 4 authors, it appears in the view 12 times. Which is fine if that's what you wanted (though you should be aware of the performance consequences -- number of rows in a view is a big factor).

The complications happen when you're storing tables of information in your document, with each column stored in a multivalued field. E.g. ItemNos = "3109B":"4428C":"5660D" and Qtys=14:1:3 meaning you want 14 of item 3109B, 1 of 4428C and 3 of 5660D. How do you get the corresponding rows to match up in the view? The document should appear three times, not nine.

There are two techniques for this, one better known and one lesser known.

First, if both columns are categorized, you can create multiple level categorization in a single categorized column by using the character "\" in your category value. In this example you could use the column formula ItemNos + "\\" + @Text(Qtys).

This isn't a particularly good choice for this example, because you didn't really want a quantity text category -- you want the quantity in its own column, as a number, so you can put totals on the category. To do this, put Qtys in a separate column (to the right of ItemNos), enable "Show multiple values as separate entries," and do not sort the column. This tells Notes to match up the multiple values with the multiple values in a previous sorted column.

To make it real, here's how the different configurations of the views look. In each case the view contains only one document with the above example data.

CatCat.gif
Two categorized columns. Result, no good. Looks like someone ordered 1 of 3109B, which is wrong. The second column is total-able but the total is meaningless.
CatSort.gif
ItemNos categorized, Qtys sorted and set to show multiple values as separate entries. No good, same problem.
CatSlashCat.gif
One categorized column with formula
 ItemNos + "\\" + @Text(Qtys). OK for some applications, but (1) we can't get totals out of it because the numbers are text, (2) what if we didn't want it categorized? and (3) converting things to text in the view is a bad idea; it uses the server's formatting settings instead of the end-user's.
CatNoSort.gif
ItemNos categorized, Qtys not sorted, and set to show multiple values as separate entries. Each quantity only displays once, under its matching category. Now we can get meaningful totals.
MultSortMultSort.gif
Both ItemNos and Qtys sorted and set to show multiple values as separate entries. Bad news.
MultSortMultNoSort.gif
Both ItemNos and Qtys set to show multiple values as separate entries, but only ItemNos is sorted. This is more like it.

Andre Guirard | 12 February 2008 04:00:00 AM ET | Home, Plymouth, MN, USA | Comments (8)


 Comments

1) Haven’t tried it in over a year, but...
Nathan T. Freeman | 2/12/2008 7:06:35 AM

In 6.5.4 and in 7.0.0, there were conditions where that second column HAD to be text.

In my specific experience, I had something almost identical to your last view graphic, except that it was ALSO categorized, and the view was embedded and set to show a single category. The matched multi-value displays only worked if all values were text in that situation.

2) Cool
Theo Heselmans | 2/12/2008 7:34:01 AM

I would have guessed that you were lucky in your last example, as your initial itemNo were already sorted correctly. But I just did a test with 'unsorted' itemNo, and the Qty column is still aligned with the correct itemNo.

Strange, but welcome behaviour !

Tx for the tip.

3) Adding to the lexicon
Ben Poole | 2/12/2008 7:35:30 AM

Multi-value fields, columns, and wot-not are all very well, but I'd just like to say that "combinatoric" is my word of the day :)

4) Thanks Andre
Mirek Navratil | 2/12/2008 9:36:59 AM

Excellent post. It is going to save a lot of my time.

5) Why doesn’t GetAllDocumentsByKey work properly with this?
Vitor Pereira | 9/1/2011 10:37:31 AM

Hi Andre, I've been using this solution for presenting data in a view for quite a while. Just run into a new requirement where I need to use notesView.GetAllEntriesByKey in the view, the problem is that although I get the 3 entries notesViewEntry.ColumnValues for the second column always returns the first value in the field instead of what's actually displayed. Any thoughts?

6) Thank you
Paul Aram | 1/25/2012 6:42:46 AM

Took a little bit of playing around - but works really well - just what I needed - thank you. Putting columns in front breaks it - as I found out

7) Thank you
Corinne Clinton | 3/20/2012 3:35:39 PM

I've been trying to find a way to do this for a long time. You make it so simple.

8) ColumnValues with Multivalue Fields
Neven Susa | 4/27/2016 1:37:04 PM

@Vitor, a late response, but in case somebody stumbles upon this, here's a solution:

1) { Link }

In case when column's property "Show multiple values as separate entries" is selected, the ColumnValues property of NotesViewEntry class will return an Array.

2) { Link }

Instead of iterating through NotesView.AllEntries, you should create ViewNavigator from NotesView.

 Add a Comment
Subject:
   
Name:
Comment:  (No HTML - Links will be converted if prefixed http://)
 
Remember Me?     Cancel

Search this blog 

Disclaimer 

    About IBM Privacy Contact