@Midas Lookups 4.40 (free license to the community)
One of the strengths of IBM Lotus Notes/IBM Lotus Domino is that data may be stored in both structured and ad hoc ways. One of the weaknesses is that while structured data may be accessed easily, ad hoc data is more difficult to access.
The @Midas Lookups provide one remedy for this weakness. Using the familiar and well known syntax of the @DbColumn and @DbLookup calls, the @Midas Lookups allow you to retrieve ad hoc data from Notes rich text fields as if it were structured data. Best of all, use of the software is provided for free as a gift to the Lotus Notes community from Genii Software. Please note, the file should be installed in the program directory of the Notes client or Domino directory. No changes need to be made to NOTES.INI or elsewhere. If the file is found, it can be used. The file should have read/execute access set depending on the operating system used.
Because the software license is free, there is no support provided. A forum will be set up shortly for community use. Please do not contact our support lines for help with the product, although questions may be sent to , who may or may not be able to provide help as time allows.
| @Midas Lookups 4.40 (no license required) |
• Download for 32-bit Notes/Domino 6.x - 8.5.x on Windows (V4.40 Released June 2, 2010)
• Download for 64-bit Domino 64-bit 8.x/8.5.x on Windows (V4.40 Released June 2, 2010)
Versions for AIX and Linux are due out within about a week. |
| @Midas Lookups samples |
| • @Midas Lookups - Rich text tables (Updated June 2, 2010) |
License agreement - License is same as for all Midas products, but the license is free for @Midas Lookups, and no license file is required..
Syntax
@DbColumn("Midas":"NoCache" ; server : database ; doc-selection ; field [ : chunkdef ] ; columnNumber [; properties] )
@DbColumn("Midas":"NoCache" ; server : database ; doc-selection ; field [ : chunkdef ] ; key; columnNumber [; properties] )
@DbColumn("Midas":"NoCache" ; server : database ; doc-selection ; field [ : chunkdef ] ; row; columnNumber [; properties] )
Note: The caching logic is identical to that used by the Notes call, so "" or "ReCache" may also be used, with similar results.
Notes Lookups vs. @Midas Lookups
The @DbColumn and @DbLookup you already know (Notes) are used to retrieve columns of data, or filtered subsets of columns of data, from Notes views. The @DbColumn and @DbLookup provided by @Midas Lookups are used to retrieve columns of data or subsets of columns of data from Notes rich text tables, or from other non-tabular data. The result of the lookup in both cases is a list, whether a text or numeric list. The resulting lists can be used in the same way for Notes development.
The similarities are striking, but hardly surprising. A Notes view, after all, is basically a table of data dynamically put together from a set of documents, but it is still a table.
Getting Started with @DbColumn
If you know how to use an @DbColumn now in Notes, you basically know how to use it in @Midas Lookups. Drill down to the table of data, return a column and work with it. You already know how to use @DbColumn, right? The Notes Help shows this syntax:
@DbColumn("Notes":"NoCache" ; server : database ; view ; columnNumber )
and a simple example would be :
@DbColumn("Notes":"NoCache";"";"Inventory On Hand";2)
Looking from left to right, you start with the server and database (which may be "" for the current database), drill down to the view which acts as a table, then drill down to the specific column of data.
The @DbColumn you use with @Midas Lookups is very similar. The syntax is:
@DbColumn("Midas":"NoCache" ; server : database ; doc-selection ; field [ : chunkdef ] ; columnNumber [; properties] )
and a simple example would be :
@DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body"; 2)
Again, looking from left to right, you start with the server and database, drill down to the document itself, usually identified by note id or unid, then drill down to the exact rich text field, then drill down to the specific column of data. Instead of the
view, the @Midas format uses two parameters, the
doc-selection and the
field to determine the table of data, and then is back to the column, just like the Notes format. The optional
chunkdef, which forms a pair with the
field, defaults to "Table 1", but could be "Table 2" or a more complex chunk definition to signify a different set of tabular data.
Getting Started with @DbLookup
Similarly, @DbLookup works much the way it does in standard Notes, although the key matching is far more elaborate. Drill down to the table of data, determine which column(s) to use, return them as a list, and work with it. You already know how to use @DbLookup, right? The Notes Help shows this syntax:
@DbLookup("Notes":"NoCache" ; server : database ; view ; key ; columnNumber )
@DbLookup("Notes":"NoCache" ; server : database ; view ; key ; fieldname )
and simple examples would be :
@DbLookup("Notes":"NoCache";"";"Inventory On Hand";"Hardware";2)
@DbLookup("Notes":"NoCache";"";"Inventory On Hand";"Hardware";"ComponentPrice")
Looking from left to right, you start with the server and database (which may be "" for the current database), drill down to the view which acts as a table, select the rows which match the key, and return the column of data for those rows.
The @DbColumn you use with @Midas Lookups is very similar. The syntax is:
@DbColumn("Midas":"NoCache" ; server : database ; doc-selection ; field [ : chunkdef ] ; key; columnNumber [; properties] )
@DbColumn("Midas":"NoCache" ; server : database ; doc-selection ; field [ : chunkdef ] ; row-list; columnNumber [; properties] )
and simple examples would be :
@DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body"; "Hardware"; 2)
@DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body"; 2:6:8; 2)
Once you are down to the fieldname and specific table, the key is a matching value or the row-list is a set of row values. The key usually matches the first column, but may be used in variouis different ways by adding a properties string (see below).
Properties string for @DbColumn/@DbLookup
There is an optional properties string. The reason for this parameter is due to the difference between the tables of data which make up Notes views and the tables of data which make up rich text tables. View data is made up of fairly simple columns of data, even if the view is displayed in fancy ways. You could say that the content and presentation are separate. In a rich text table, the content and presentation are mingled, so you need properties such as SkipTitle=Yes, which says that the first row of data is a title. (In a Notes view, the title row is separate from the data.) While there are several properties which can be used, you will mostly only need to worry about three:
Frequently used properties:
- Format="Currency", "Number" or "Text" (to allow you to determine the type of the result. Defaults to "Text". "Currency" returns a result which is a numeric list, but skips preceeding characters before the number starts.)
- SidewaysTable="Yes" or "No" (defaults to "No")
- SkipTitle="Yes", "No" or number (to allow you to skip more than one row. Defaults to "No")
Less frequently used properties:
- ColumnChunk=def (specifies a special chunkdef to use inside column, such as "Text 2", which would return the second text chunk in the column rather than the first. Usually not used)
- CompleteText="Yes" or "No" (return all text in table cell even if format changes, defaults to "No" meaning first text only)
- KeyChunk=def (for @DbLookup determines the chunkdef to match with key. Usually not used, since KeyColumn is default)
- KeyColumn=col (for @DbLookup determines the column to match with key. Defaults to 1)
- KeyMatch="Exact", "Starts", "Ends", "Substr" (for @DbLookup determines the match rule for key. Defaults to "Exact")
- KeyProperty=prop (for @DbLookup determines the property to match with key. Usually not used, since KeyColumn is default, but most common use is KeyProperty=TabLabel which uses the value of a tab in a tabbed table)
- Property=prop (specifies a property of the column to return instead of the value, such as "LinkURL", which would return the url inside the first URL hotspot in the column. Usually not used)
- RaiseError="Yes" or "No" (returns an error rather than a "" value when the table does not exist. Defaults to "No")
- Separator=str (added between each chunk of text if CompleteText=Yes. Defaults to "")