Ben Langhinrichs

Photograph of Ben Langhinrichs

E-mail address - Ben Langhinrichs

Recent posts

Thu 7 May 2020

Can we get a huzzah for updated Domino Limits?

Wed 29 Apr 2020

A bigger boat: when in Rome

Mon 27 Apr 2020

HCL Notes 11 - Quick Intro to features and enhancements

May, 2020
     01 02
03 04 05 06 07 08 09
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Search the weblog

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:

566.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.

566.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.

566.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.