Select Superlookup comparisons don't work


#1

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.


#2

I’m guessing that you are not using superlookup properly, maybe the formula isn’t quoted? Since you haven’t submitted any concrete examples, I can’t say for sure.

Not to mention the fact that you can use any formula as the key, rather than just a single field in each database.

However, since superlookup( allows arbitrary formulas, it is slower than a regular lookup. The regular lookup has optimizations that are not possible when an arbitrary formula is used. If a regular lookup will work for you, that is what you should use.

[/quote]

This is exactly correct. This is the one situation where superlookup( can be faster than lookup(, but only if all the criteria are met. But, it’s nice to have options, right? Superlookup( gives you more options, but also more potential tradeoffs.


#3

More options are very nice! Even though they make the learning curve longer and make the challenge of remembering everything possible that much bigger.

I gather you think selections via superlookup comparisons should work. The power of Superlookup’s many options comes with a complex syntex, including proper quoting. I doubt I understand it completely, but I think in these cases I did get it correct. My recollection was that I had pretty good evidence for the problem when I first figured it out and figured out the two step work around. Then I got busy and didn’t report it. I found another example of this in my code recently, prompting my report. I’ve since fixed that example. I believe I can create a simpler, specific example of the alleged problem and will email you that once I have it. It may take me some time to get to it.


#4

It may also take me some time to get to it once I receive it :slight_smile: