Ben Langhinrichs

March, 2007
SMTWTFS
    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 31

Search the weblog





























Genii Weblog


Civility in critiquing the ideas of others is no vice. Rudeness in defending your own ideas is no virtue.


Fri 23 Mar 2007, 12:46 PM
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.