Address=superlookup("New Panx Alex Mail",{«First Name»=««First Name»» and «Last Name»=««Last Name»»},{Address})
If error
Address=""
Message "No such person in DB"
stop
endif
City=lookupmoredata("City")
State=lookupmoredata("State")
Zip=lookupmoredata("Zip")
Country=lookupmoredata("Country")
HomePhone=lookupmoredata("HomePhone")
email=lookupmoredata("email")````
and finds the information from a record in the NewPanxAlexMail DB.
Sometimes it doesn't work and returns the "No such person" message even though the person does in fact exist in the other DB. I can't predict when it will work and when it won't. I just had a success, two fails, and then a success. I'm sure there aren't any hidden characters or spaces in the names, and I have often had success with this procedure on the very records that just failed.
I wish I could give more clues, but if I could, I'd probably have solved it myself....:smirk:
Are all of the records in “New Panx Alex Mail” selected? It shouldn’t matter, and I just checked the code to verify that it shouldn’t matter.
You say you had a success, two fails, and then a success. Were you doing anything else in between? Do you do anything that uses regular lookup( functions? Again, it shouldn’t matter, I’m grasping at straws.
For whatever it’s worth, the code I use to render the Panorama Help system uses superlookup( heavily. Every link in the help is generated with the assistance of a superlookup( function. So that’s literally tens of thousands of superlookups( when re-rendering the entire documentation set (actually > 100,000). It has never failed once (if it did, that would immediately stop the render, so it’s definitely something I would have noticed).
Nevertheless, I have opened a BitBucket issue. But without more information or some further clue, I won’t be able to do anything on this issue. One thing you could try is to use match instead of =, then the lookup would work even if the names didn’t match exactly in upper/lower case.
One other thing you could try is to rewrite the formula to not use the double chevron trick. I haven’t tested this, but I believe in this case you could rewrite the formula like this:
Address=superlookup("New Panx Alex Mail",{«First Name»=}+quoted(«First Name»)+{ and «Last Name»=}+quoted(«First Name»),{Address})
I’m sure about there not being hidden characters and spaces because the records I’m working with (e.g. my own name) have worked with this procedure correctly for years, including in PanX ever since I started using it, until quite recently. Where would the problem characters have come from?
I tried using match instead of = and your non-double-chevron code, but neither worked. I did 3 different records that I know are in the source DB and they all failed. I haven’t yet thought about the what was I doing in between question; will get to it later when I’m not supposed to be doing something else… : )
One aspect of all this that I can fool with in about a month: these are DBs converted from Pan6. I might start from scratch, importing the data after I rewrite them. That would give me a chance to try things out without your having to waste time pursuing anomalies from the conversion process. I suggest that we do it that way; you have more important things to do.
Meanwhile I can go ahead with current business more or less ok.
Well if you can get it to fail reliably then it should be possible to figure out what is going on.
That’s not what I would consider “sure”. To me, sure means you have checked the actual data involved and verified that it matches exactly. The symptoms you are describing could easily happen if there was an extra space or carriage return on the end of a data cell, that is very hard to notice. The extra space or carriage return could be in either database, either way it will mess up the lookup. That could happen simply by pressing a space or return key and not noticing it. That would be consistent with the problem just appearing recently. If that is the problem, you could fix it for the entire database with this procedure:
field “First Name”
formulafill strip(«»)
field “Last Name”
formulafill strip(«»)
Maybe you should run that procedure on both of the databases in question.
FYI – I think it is very unlikely that this has anything to do with the conversion from Panorama 6. I’ve never heard of a single problem ever in the conversion of data from 6 to X. That code is very straightforward and at this point undoubtably tens of millions of data cells have been converted without incident.
I was pretty sure you must be right. I ran your formula on both DB’s.
No luck, known record not found.
I manually did a formula fill on both DB’s.
Still no luck.
I restarted PX, in case there was something lingering.
Still no luck.
On a wild hunch, I replaced the superlookup( function with a plain lookup( function. (I used an existing extra field in both DB’s that is a concatenation of the first and last names.) Bingo. It worked perfectly.
I totally believe what you have said about the reliability of the superlookup( function. But nevertheless…