Thursday, March 15, 2012

Showing horizontal notes data as vertical in view

Today at work, I needed to transfer some data from a Notes application to SQL. The documents in question were horizontal.

An example of what I mean:
An order form in Notes with five order lines. For each line, there may be five fields that contains information about the order. Making it a total of 25 fields for five order lines.

Traditionally, if you want to show this data in a regular notes view, you have to have a column per field.

Thanks to the way the index is organized in a Notes view, you can show this data vertically using Show multiple values as separate entities.

A summary of the technique:
  • In each column, create a list of the field values that you want to show
  • Each column list has to have equal amount of values
  • Each column has to have Show multiple.. property enabled
  • Only the first column can be sorted (it can be categorized), or you end up with a lot of rows.
    This is probably due to how the index is organized/matching of multiple values

Here's the demoapp I took the screenshots from:
>> Download

Take a look at the Vertical view to see the technique I used.

Thanks to this technique, I can simply pull the data from a view using view entries/column values/send row by row to a stored procedure in SQL. The alternative would be to write code for each field.

Share and enjoy! :)


Roy Rumaner said...

This would probably be easier in a repeat using XPiNC

Tommy Valand said...

It would, but my use case was not presentation. It was for consumption by export routine.