Lookup problems


I think maybe I posted this to Slack or Bitbucket, and I think there have been some sort of revisions to the lookup statement, but I’m still having a problem with this code:

local bucket
global postit
bucket=«First Name»+«Last Name»
City=lookup("New Panx Alex Mail","FullName",bucket,"City","",0)
if error
field Address
Address=lookup("New Panx Alex Mail","FullName",bucket,"Address","",0);Doesn't work!
State=lookup("New Panx Alex Mail","FullName",bucket,"State","",0)
Zip=lookup("New Panx Alex Mail","FullName",bucket,"Zip","",0)
Country=lookup("New Panx Alex Mail","FullName",bucket,"Country","",0)
«HomePhone»=lookup("New Panx Alex Mail","FullName",bucket,"HomePhone","",0)
email=lookup("New Panx Alex Mail","FullName",bucket,"email","",0)
Field «Date Received»

It all works fine except for the line starting with Address. That line alone returns a blank, though the field in the source DB is not blank. It doesn’t matter if I change the order of the lines. I did check to make sure that I don’t have a typo or a subtly different field name. But evidently since all the other lookup lines work as expected, I’m making a programming error. I’m quite sure I don’t have a variable named Address. (Is there a way to list your variables?)

I’m really stumped, hope someone else can see what dumb mistake I’m making…


Should bucket have a space?


No, and I can’t see one anywhere in the code.


Info(“filevariables”) will give you a list of fileglobals, and info(“globalvariables”) will give you a list of globals. Another way to test to see if there is a variable involved would be to put

message Address

right after

Address=lookup("New Panx Alex Mail","FullName",bucket,"Address","",0)

If the address shows up in the message, when it wasn’t assigned to the field, it must be that the message is showing you the value of the variable it was assigned to.


I find I don’t have any variables in either DB or globally called Address.
I checked the name Address in both files to make sure there were no hidden characters anywhere; there weren’t.
I tried Dave’s suggestion about message Address several different ways and it always came up blank. I did it using a different field and it always returned the contents of the field in question.
I deleted the offending line, replaced it with a working line and replaced the fields with Address but that didn’t help.
I made a new local variable and assigned the offending lookup to it and messaged the new local variable and it, too, came up blank.
I messaged the field directly in the source DB and it came up with the contents of the field.
I renamed the field Address in the source DB to Address1 and made the corresponding change in the lookup line. That too was no use.
I mentioned before that moving the offending line later in the series of lookups had no effect.
Working backwards (that is, posting from the target DB to the source DB) functions perfectly.
Other suggestions? This is driving me nuts.


You didn’t say if you’ve tried it on looking up different records. Is it possible that the one (if it’s one) you’re testing on has more than one entry and one of them has a blank address?


Thanks, Jim,
Good thought, but no cigar. The problem is consistent with any record. This is a procedure I used in P6 (and before) for years with no problems. Ever since I migrated these DBs to Px it’s been this way for every case and only with this field.


Solution (kludgey): in the offending line, use superlookup( instead of lookup(. Works like a charm.


You might try a formulafill that removes all non-printing characters from that field.


Thanks, Michael, but since the superlookup( solved the problem, that doesn’t seem like it would be an issue; and since I retyped the field name myself both in the code and in the DB, I don’t see how there could be any non-printing characters around there. Anyhow superlookup( solved it.

Now that that one is solved, here’s a new one:

Reviewing the original code, you’ll see that before my problem line came this snippet:

local bucket
global postit
bucket=«First Name»+«Last Name»
City=lookup("New Panx Alex Mail","FullName",bucket,"City","",0)
if error
field Address

Suddenly this code stopped working; the City lookup in line 4 didn’t function and the code jumped the if error lines and failed at the Address superlookup( point (since in that case there was no existing record to look up).

Taking a leaf from this morning’s book, I substituted superlookup( for lookup( in line 4 and all was hunkey-dorey again.

I think this needs some attention from Jim, because that line had been functioning fine up to the point that I fixed the Address line; and the subsequent plain lookups after the Address line still function fine.