Superlookup( problem


#1

i think i may have a fundamental misunderstanding of superlookup(.

in this example there are two databases, bm (brand & model) and bmold, with identical structure. each have a field id which is a unique identifier for a unique pairing of a brand value and model value.
i need to fill the id field in bmold with the corresponding values in bm. these are both large files. an ordinary lookup( takes forwever, so i need to use superlookup(:

field id
formulafill
superlookup(‘bm’,
    {brand = ««brand»» and model = ««model»»},
    {id},
    ‘wrap’, true(),
    ‘selected’, true())

looks right to me, but the formula fills the id field in bmold with the id value from the first record in the bm database (and takes several seconds to do it.)

what’s wrong?


Superlookup - unknown statement?
#2

Just looking at it, your code looks correct. It’s hard to spot errors in a “loose” piece of code like this, though, so I may be missing something.

You haven’t mentioned anything about sorting. When you are using the wrap option, you want to make sure that BOTH databases are sorted in the same order, in this case by brand and model. If they are not in the same order, the lookup should still work, but instead of being faster, it will actually be much slower. You might try taking the wrap option out. Then once you get everything working, you can try putting it back in.

By the way, the superlookup( function is not automatically faster than lookup(, in fact, all things being equal it will be slower. If you get everything sorted correctly and use the wrap option, then it definitely can be much faster, but you’ve got to get the sort right.


#3

I think there is a fundamental bug in superlookup(. I think the problem is here.

Advanced Note: Internally, Panorama actually converts double chevrons into the grabdata( function. So assuming that the current database is named Invoice, the example above is internally converted into this:

superlookup(“Mailing List”,
{First=grabdata(“Invoice”,FirstName) and Last=grabdata(“Invoice”,LastName)},
{Address+", “+City+”, “+State+” "+Zip})

When grabdata( looks back to the current database, it is grabbing its data from the record that was active before the FormulaFill, rather than the record the FormulaFill is currently filling. I was able to duplicate your results by making the first record active. If I made another record active, the field was filled with the appropriate id for that record.


#4

Thanks Dave, excellent catch. I believe the bug is actually in the grabdata( function, not specifically superlookup(. I was able to demonstrate a problem just with

formulafill grabdata(

as documented in this bug report I just created.

Unfortunately @samrutherford, I don’t think there is currently any way to do what you are trying to do.


#5

I guess until the grabdata( problem is fixed you could try a loop to do the work of the formulafill.

field id
noshow
firstrecord
loop
	id=superlookup('bm', 
	{brand = ««brand»» and model = ««model»»}, 
	{id}, 
  	  'wrap', true(), 
	'selected', true())
        downrecord
until info("stopped")
showpage
endnoshow

Don’t know if this would operate fast enough for your purposes or if the wrap option is usable with the sorting you have set up. Just a thought.


#6

When I saw Gary’s code, I wasn’t sure if the wrap option was designed to work in a loop like that, as opposed to in a formulafill. I checked the source code, and it should work (assuming, of course, that both databases are sorted in the same order).

Hmm, I just noticed a bug in Gary’s code – superlookup( is not a statement. The 5th line should be:

id = superlookup(`bm',

#7

Oops, missed that so I edited the above to include that code.


#8

i understand about the need to sort both tables, and both were correctly sorted.

glad i turned up a bug in an otherwise very useful function, and thanks for all the suggestions.

the loop gary suggested would be very slow. what i’ve done in similar situations in the past is to build a loop making reasonably sized selections and use lookupselected(.
this is clumsier and probably slower that the superlookup( would be if it worked.


#9

Assuming Jim is correct and the wrap option will work when used inside a loop and the loop is bracketed between noshow and showpage - endnoshow statements, things might be much faster than you would expect. Only testing on a very large data set would tell.


#10

point taken.
i’m actually running my loop right now. wish i had noted the start time.


#11

Sam - I’m glad you spoke up too. I had this same experience earlier this week with superlookup(, returning only the value of the first record, even if it was not a match. I assumed I was doing something wrong. Thank you!


#12

kind words. thanks.

i feel like most people on this forum are way above my level of expertise, yet i have turned up two important bugs.

i’d encourage you to jump in whenever you think you’ve found something.


#13

so superlookup( is still not working, at least not in a formula fill, in v 0.9.001 (2484) because grabdata( is still not working?
am i right about this or am i doing something wrong?

i hope this is something of a priority.
superlookup( would speed things up a lot.