Genii Weblog

Totally tabular (the 'Good Parts' version)

Thu 18 Nov 2004, 11:10 AM



by Ben Langhinrichs
With special thanks to the Princess Bride for the name, this is the abridged version of my first post, Totally Tubular (the 'Technology Wonk' version).

Getting non-table data through @Midas Formulas
In our latest version of @Midas Formulas, we have added the ability to get non-table data as if it were in virtual tables.  Rather than using numbered columns, we use named column based on the properties available for the non-tabled element, such as the LinkURL for a URL hotspot.  There are multiple virtual columns for each data element.  

An example we could use was raised in a wish list item in the Business Partner forum.  Jorick Astrego wrote
When 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 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.

What has been said:


234.1. Stan Rogers (11/19/2004 02:07 PM)

I guess I must be a wonk -- the other version was the one with the goods parts. This one's just got code in it ;o)