All data is tabular
Depending on how you think about it, it could be argued that any data that can be iterated through is essentially tabular data. Whether this is a list of bullet points, such as:
- My first point
- My second point
- My third point
or a series of URL hotspots, such as:
or even a series of images,such as:
these are all tabular information, that could be represented as:
My first point |
My second point |
My third point |
and
and
All tabular data is multi-dimensional with virtual columns
It could also be argued that each of these tables has more than one column, with the properties of the elements forming virtual columns. To look at just one example, the URL hotspots could be seen as:
| Text | LinkURL | Target |
| IBM | | _new |
| Microsoft | | _new |
| Apple | | _new |
with these virtual columns and many more representing different properties of the elements, in no particular order. The images might have different virtual columns than the text, but they all have properties or attributes, each of which could be thought of as a column. Of course, you can't really talk about column 1 or column 3, so instead, you would need to think of these as "named columns", such as column LinkURL or column Target.
Accessing tabular data programmatically
So, if you accept my premise that all data is tabular, and you additionally accept that all tabular data is virtually multi-dimensional, how can we use that fact? How can we treat these multi-dimensional virtual tables that may span a great deal of extraneous contents, such as the additional content between the URL hotspots? I would suggest that Notes developers would like to do it with @DbColumn and @DbLookup, since those are the "tabular access" methods for Notes. Non-Notes developers might well want to use SQL statements, but I will leave that aside for not (not forever, but for now).
Well, as some of my Penumbra ISV friends would say, "I have a tool for that". Of course, the tool I am talking about is the @Midas Formulas. As I have written before, especially in @Midas for Domino Developers, Part 1, @Midas will let you use @DbColumn to read a column of data from a rich text table. (Really! Go ahead and read the other post and come back, if you like. I'll wait.)
Accessing non-table tabular data programmatically
But if all data is tabular, why should we be constrained to rich text tables? A perfect example was raised in a wish list item in the Business Partner forum. Jorick Astrego wroteWhen I receive an email with a hyperlink in it that I want to send to someone else or paste somewhere else; I have to open the source and then copy paste it. And to make things worse, the link is extra difficult to copy because of the automatic selection. I just hate it when software automatically selects the whole string when I only need a part of it. I can think for myself thank you.
Now, you might be able to write a script that would handle this by iterating through the rich text and finding the URL hotspots and URLs, and ask the user which one he or she wanted and give back the URL for it. I don't know that the native Notes LotusScript classes give you enough information, but our Midas Rich Text LSX could do it. You might also be able to write some JavaScript and see if you can access the URL hotspots, but I think you would also run into limitations there. Even if one of those approaches would work, you would face a few problems:
- The LotusScript or JavaScript would have to be part of the design of the database, which prevents a general solution.
- This is an iterative approach to a tabular problem. In much the way you can always use addition to solve multiplication problems by iterating, the solution is functional but quite tedious. Better to use the multiplication.
- The native classes don't give you much access to the various "virtual columns", such as the URL part of the URL hotspot, or the target of the hotspot, or the width of the graphic, for example.
@DbColumn and @DbLookup are the "multiplication" version in this case, so we extended the syntax just a bit, so you can have:
hotspots := @DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *");
Since I picked a target type that wasn't a table,the @DbColumn simply acts as if there is a virtual column of URL hotspots (which we call URLLinks in Midas-speak). This returns a list of the text values by default. To return a different virtual column, you could change the column identifier to the property desired:
urls := @DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *"; "LinkURL");
which would return the URLs associated in the virtual column. The properties available are the same as are available for any chunk, although some may not make much sense for this type of chunk.
This technique is almost universally applicable, if you know the language of Chunk Definitions. For example:
colors := @Unique(@DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"Text *"; "FontColor"));
attachments := @DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"File *"; "FileName");
filesize := @Sum(@DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"File *"; "FileSize"; "Format=Number"));
Extending the tabular data access
So, we have extended the @Midas Formulas scope just a bit, and opened a whole world of possibilities, but there's still more to accessing tabular data. In SQL parlace, it's as if we showed the SELECT statement without the WHERE statement. In Notes, this would be done with @DbLookup.
@DbLookup is very similar in syntax to @DbColumn, but takes an additional key value, which can be either a number or a key string. Taking an example from above,
third_attachment := @DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"File *"; 3; "FileName");
would return the filename of the third attachment. Interesting, but not compelling, as you still have only a one column table so this could have been accessed in @Midas anyway with:
third_attachment := @DbCommand("Midas":"NoCache"; "FileName"; ""; @DocumentUniqueID; "Body":"File 3");
Still, it offers a simple access method for those less familar with the @DbCommand syntax. But what about the key string? Now we get interesting:
OK, let's assume I attached three different license files, all called the same thing "licmidas.lic", and Notes stores them with different names internally. Now, I want to get the internal file names of the files that were originally called "licmidas.lic", but not the internal names of any of the other twenty files attached to this document. The @DbLookup would need to treat the key field as being applied to the virtual, named column OriginalFileName, and restrict it to "licmidas.lic". Here is what it would look like:
licenses := @DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"File *"; "licmidas.lic"; "FileName"; "Property=OriginalFileName");
Still, that may seem a forced example, so lets go back to the example in the BP forum. If you have any confidence that the hotspots will have different text values, you could use:
hotspots := @DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *");
textval := @Prompt([OkCancelList]:[NoSort]; "Get URL"; "Select URL hotspot whose URL you want:"; ""; hotspots);
url := @DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *"; textval; "LinkURL");
Another example might be finding all the doclinks in the current rich text which point to this database, and then returning their unid's, thus providing a good table of links.
unids := @DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"Doclink *"; @ReplicaID; "LinkNoteUniversalID"; "Property=LinkReplicaID");
There are lots of additional examples using both @DbColumn and @DbLookup. See if you can figure out the purpose for these, and how you might use the information in Notes coding for either the Notes client or the web:
max_width := @Max(@DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *"; "GraphicWidth"; "Format=Number"));
anchors := @DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"Anchor *"; "AnchorText");
urls := @DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *"; "LinkURL");
blank_targets := @DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"URLLink *"; ""; "LinkURL"; "Property=Target");
nonblank_target_urls := @Trim(@Replace(urls; blank_targets; ""));
Copyright © 2004 Genii Software Ltd.