In Panorama6 I often made selections of the kind:
Select lookup(database, keyfield, keydata, datafield, "") operator value
When looking up a queried datafield, known to always contained non-empty text, and using ≠""
for the “operator value,” this would select records in the current database that matched some record’s criteria in the queried database. Using =""
would instead select records that didn’t match criteria in the other database. Using operators other than ≠
or =
, comparing to values other than ""
, and potentially using lookupselected(
instead of lookup(
, gave yet other options for selecting records in one database based on the contents of another. Some of these selections could equivalently be made with Select arraycontains(
or Select arraynotcontains(
on an array built from values from one database and a field from the other. In both cases databases are scanned to find desired records.
In PanoramaX the above still works but the added overhead of Unicode can slow large scanning cross-database lookups and large scanning arraycontains operations significantly. PanoramaX’s potentially speedier solution is the Superlookup(
function. However,
Select Superlookup() ≠ ""
, with the lookup details configured to be identical to the previous example, simply doesn’t work. Actually, Select Superlookup() ≠ ""
produces the identical selection to Select Superlookup() = ""
, rather than the reverse selection it should. But if you first Formulafill Superlookup()
into a Field and then Select Field ≠ ""
or Select Field = ""
you get the identical selections after two steps that the older Select Lookup()
comparisons had produced in one step. The “Select Superlookup() operator value
” combination code compiles and runs fine, it just doesn’t select what you’d expect. I discovered this when trying to speed up some converted Panorama6 code. I was getting results quicker, just wrong results. Once I switched to the two step method: Superlookup()
to a field then Select
that field in comparison to the value I got my potential speed AND accurate results.
Increased speed via Superlookup()
depends on many factors. Its many Options
sometimes make it superior for reasons other than speed. Enhanced speed potential comes via its Wrap
option which can be tricky to optimize. For best results both keyfield
and keydata
need to be sorted the same way and every record scanned needs to produce a match. Match failures either produce the Default
value or stop the procedure if no default option was set. The latter stops have proved helpful in correcting my code when I hadn’t expected them! Too many “default” values and your code will both run and produce accurate results, but may take significantly longer than the older Lookup()
equivalent. Of which the documentation warns! I think “default” free, always matching, wrapped superlookups are possible. However, they can be quite tricky to accomplish and their speed isn’t always worth the effort.
I don’t understand why Select Superlookup() operator value
doesn’t work in one step, but does work if split into two by initially doing the lookup to a field; whereas Select Lookup() operator value
works in one step. It might not be a bug; it might be something about Superlookup()
I don’t understand.