The Help wizard describes the wrap option for the superlookup( function as follows:
“If the wrap option is true, the search starts where the last search left off. If the search reaches the end of the database without finding a match, it wraps back around to the top of the database, and resumes searching from there.”
This is fine if every keyfield (using lookup( terminology) data item in the primary database has a match in the target database. If, however, there is a poor match of data items, the wrap option offers no benefit. If both databases are sorted on the fields to be matched, then continuing a failed search from the top again is guaranteed to be a pointless exercise.
Well, it may be useless for whatever application you have. If that is so, don’t use it. It will be useful if most of the searches do result in a match.
This feature was included at the request of a particular user, who has been asking for this repeatedly (and politely) for close to 20 years. Last I checked, he was very happy that this was finally available, and he has an application where it is very useful indeed. However, it is certainly is not an option that should be blindly applied without evaluating your data sets. That’s why it’s an option and not permanently turned on.
It doesn’t matter what the application is. If both data sets are sorted, and you can’t find “harry” in the items that follow “harrow”, you are certainly never going to find it in the items that precede “harrow” so the search from the beginning can never produce a result - it’s just a waste of time that negates the potential benefit of the wrap option.
If used properly, the wrap option has the potential to make successful searches much faster for sorted databases. It makes no difference to failed searches. Panorama will always search every record before deciding that there is no match, regardless of whether the wrap option is used or not.
Now you are correct, there could possibly be an even faster lookup that started from the last search and didn’t wrap. In fact, it could stop searching as soon as a value was found that was less than the searched for item, so it could be much much faster for failed searches. Perhaps I will consider that.
However, the current wrap option is guaranteed to find a match if it is there, even if the databases aren’t sorted correctly. If the databases aren’t sorted correctly, performance will suffer, but it will always return a match if it is there. But if there was an option that worked the way you are proposing, the lookup would not work reliably if the databases weren’t actually sorted in the correct order.
As someone who has to provide tech support, I would not relish the support requests coming in saying that “lookup doesn’t work – there’s a match and it didn’t find it”. People will insist that the files are sorted correctly when they aren’t, or they won’t even know that is a requirement. The scheme you are suggesting will invite this sort of very difficult to track down problem. Beyond tech support, there will be customers with silently failing lookups that won’t even realize that there is a problem – they’ll just wind up with incorrect data. They won’t be a tech support problem, but maybe their Mars lander will crash, or a patient will get incorrect treatment. In spite of the potential benefit for some smart users, I’m not sure that is worth it. Perhaps it could be done with huge caveats in big red letters, I’ll think about it, but no matter the warning, it will get misapplied at some point.
Point taken. But I would really love to see an option that was more efficient. I’ve actually written one of my own which I’m currently finalising. You will not be surprised to learn that its genesis lies in a clever solution provided by Dave T. to another problem.