The documentation for the two lookupmore functions doesn’t make it clear whether another complete lookup is conducted or whether, by some alchemy, the work done in the previous lookup is utilised again.
I ask because I have to conduct two lookups on two very large (millions of records) databases and I’m looking for every possible way to reduce the time taken.
When Panorama does a second consecutive lookup, using the same database, key field, and key value, it remembers where it found the match the first time and goes directly to it, without searching for it. Whether you do another lookup(, or use lookupmoredata(, you should get the same alchemy.
That’s very clever. Thanks Dave.
\Michael didn’t say whether he was doing single record or scanning lookups. I agree with Dave’s answer for the former and believe it also applied in Pan6. I don’t think it applies to the latter, eg. consecutive FormulaFill Lookups, each is a separate search in both Pan6 and PanX. If I’m wrong I’d be delighted to be corrected.
They could be reduced to one cross database scanning search by using Superlookup and having its dataformula collect a text array for each record: Field1+Sep+Field2+Sep+Field3 etc. with an appropriate separator. The found text array entries could then distributed to other fields via a series of FormulaFill Array( statements. This can be faster than multiple scanning FormulaFill Lookup searches between databases. I haven’t tried it, but presume the same could be done using data arrays.
A functionally equivalent, albeit more complicated, approach would be to first collect the Field1+Sep+Field2+Sep+Field3 etc. array, for all records to be searched, in the database to be searched, either into a dedicated field or within one of the fields previously searched separately. FormulaFill Lookup (superlookup or otherwise) that collective field once, distribute its found array entries as above, then convert back the searched database to its pre-collected state. This involves more steps and temporarily (you hope) changes the searched database, but in my experience it sometimes is faster than the simpler, safer first method. I don’t understand why it can be faster, but if speed is important it may be worth trying and doing some comparative speed testing.
That’s a valid point. Michael often says “lookup” when he really means a formulafill with a lookup( function in the formula. Panorama will remember the previous lookup, but it won’t remember the previous million lookups, or even the previous two.
I have no secrets from you Dave - that’s exactly what I meant. My apologies for my imprecision.
And John’s suggestion of concatenating the target fields is an excellent one.
In that case, lookupmore( is not an option for your application.
Suppose you have a database with fields A, B, C, D, E and F and you want to fill B, C and D with a lookup using the same key but different data fields in the second database. In that case, I think you could use the fillallfields statement, something like this:
formulafillallfields A+tab()+
lookup("database","keyfield","datafield","default")+tab()+
lookupmoredata("anotherdatafield","default")+tab()+
lookupmoredata("yetanotherdatafield","default")+tab()+
E+tab()+
F
This could get pretty unwieldly if you had a lot of fields, and you would really mess up your database if you got it wrong, but it should work. If I was ever going to try this, I would probably build a program to automatically write this program, rather than trying to code it manually.
Good idea - thanks.