Genii Weblog

OpenSesame: @DbLookup on spreadsheets

Fri 23 Mar 2007, 12:46 PM



by Ben Langhinrichs
Yesterday, I wrote about using the OpenSesame @DbColumn call.  Today (now that it is working), I though I'd share the @DbLookup logic, which is a bit more complex, but very powerful.  Below are a couple of simple examples.  The first returns the values in cells from F4 to F50 for which the corresponding value in column E is equal to 4.  Taking a look at the spreadsheet, you will see that this matches F4 ("Red"), F7 ("Silver"), etc.

Colors := @DbLookup("OpenS":"NoCache"; "c:\\temp\\trial.ods"; "Sheet1"; "F4-F50"; "E=4");

The second call is similar, in that it returns the colors in F4 to F11 for which the corresponding value in G does not equal DHL.

Colors := @DbLookup("OpenS":"NoCache"; "c:\\temp\\trial.ods"; "Sheet1"; "F4-F11"; "G<>DHL");

All in all, the choices for comparison include starts with, ends with, contains substring, equals, does not equal, as well as less than, greater than, less than or equal to and greater than or equal to.  For example, you could use:

Units := @DbLookup("OpenS":"NoCache"; "c:\\temp\\trial.ods"; "Sheet1"; "D"; "C^=Edwards,");

which would return all the Units (product names) sold by anybody whole last name was Edwards (since the last name comes first and is separated from the first by a comma).

Spreadsheet view of data

Copyright © 2007 Genii Software Ltd.

What has been said:


574.1. Carl Tyler (03/26/2007 03:44 PM)

This brings back memories, way back when I came over to the US to work for Lotus as SmartSuite Marketing Manager, I wrote a 1-2-3 addin for sales reps to demonstrate the powerful integration between 1-2-3 and Notes, it included the Notes LSX to do exactly this. Took about 3 hours of coding, but it worked a treat. I might even have a screencam of it somewhere, I'll have to see if I can find it.


574.2. Carl Tyler (03/26/2007 04:26 PM)

Doh, I am mistaken, I misread what you were doing. My addin worked the other way, you would put the dblookup directly in the spreadsheet as a 1-2-3 formula and it would query notes, you are doing it the other way and querying the spreadsheet.


574.3. Bill McNaughton (04/02/2007 07:06 PM)

Extremely cool. The idea of integrating spreadsheet data into Notes applications in this way will make it very easy to, for example, build picklist values directly from a spreadsheet, which is the format they are often stored in.