Genii Weblog

When Notes table data doesn't play nicely with others

Fri 3 Sep 2021, 08:51 AM



by Ben Langhinrichs
I'm working on a series of posts for next week on the four most common ways to represent a table of values in Notes that do not play nicely with relational databases. There are others, but these four are ones I see routinely when exporting data with Midas Exports or retrieving data with Exciton Boost. (There are also ways that do play nicely, but why talk about them?) My challenge for myself is whether I can use both tools to export or retrieve the data from each in a way that forces them to play nicely with relational databases.
 
In order from most to least common of the four top ways I see to make table data that don't play easily with relational databases:
 
1) Hard-coded table cell fields, every one separate
2) Parallel multi-value fields, one per column
3) Response documents, one per row
4) Tables embedded in rich text
 
Watch this blog for my posts. Also, feel free to leave a comment if you know of other ways to represent table data that don't play nicely with relational databases/techniques.
 
Inline JPEG image
 

Copyright © 2021 Genii Software Ltd.

What has been said:


1127.1. Wayne (09/03/2021 06:40 AM)

A multi column list box would have sorted that issue out a long time ago.


1127.2. Ben Langhinrichs (09/03/2021 09:13 AM)

Well, yes and no. It is worth noting that the field type on the form doesn't necessarily translate well to the data type on the document. I could have a list box or radio button or text field or combo box or whatever on the form, but the data on the document is still a text list (multi-value text field), even if it only has a single value. When accessing via REST API or migrating via JSON, CSV, or whatever, the only thing that matters is what is stored on the document. So, a multi-column list box would probably be stored as a multi-value text list with delimited values. Still a mess to access or export, though better at keeping the rows together.


1127.3. Richard Pajerski (09/03/2021 01:32 PM)

Ben --

I can say I've implemented the first three methods you list in a number of production applications. :-) They each work with varying degrees of benefits/drawbacks. I tend to use option 3 the most since it seems to offer the highest degree of flexibility (formatting, interacting, auditing, searching, etc.).

Two other (more exotic) ways of using tables in Notes:

1) Embeddable Java applets (Swing JTables in the applet container, not the Notes embeddable Java view applets). They're impractical though for a number of reasons including the difficulty of developing and integrating a good Java UI and issues around storing data (for editable tables). They'll also probably be outright impossible to use after the next JVM upgrade, since applets have been deprecated in recent JDKs.

2) Eclipse plugin, using SWT table widgets. Similar usability issues as applets but with the additional burden of the Notes plugin architecture.

Looking forward to your upcoming post.


1127.4. Wayne (09/03/2021 11:26 PM)

@Ben, I agree on the issue of field types. However, the developer could convert items to text format before display. And, yes, it would be limited in flexibility, but it would still be miles ahead of what has to be done now.

I don't have actual figures but I would guess that thousands of man hours have been lost trying to figure out how to get something to work - and look reasonably good.

Regards