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).
Copyright © 2007 Genii Software Ltd.