Using a variable in lookup(

This worked in 6, but is having issues in X. I have a procedure that is called with a parameter, which is the name of a field in another database that is the target of a lookup( function. I assign a local variable to parameter(1) and use that variable name as the “datafield” argument of the function. In 6 this worked, in X it reports an error that a field by that name (my variable name, not the field name that is assigned to it) does not exist. How do I get lookup( to interpret this correctly?

Use ""+variablename instead of just variablename.

i’ve seen that mentioned in other threads. Is this now the standard way to handle this situation in all such cases?

Probably. It depends on your definition of “such cases.”

I guess I would call it “when using a variable name where a field name is expected.” Does that pretty much cover it, or is there more to it.

It worked, by the way.

This is a feature that worked in a few places in Panorama 6 but now works universally. In places where a field name is expected, old versions of Panorama used to require that you quote the field name. Now this is no longer necessary – you can quote or not quote the field name. Either way, if you provide a single identifier, like a or name or price, Panorama will assume it is a field name, and it will not try to get the value contained in the identifier, in other words, it doesn’t evaluate the formula, it just uses the name itself as the value. This was a very popular change when started in Panorama 3, since you didn’t have to remember to quote some of the field names when using the lookup( function.

The only downside is the unusual case where you actually want to use a variable that contains the field name. This is rare but sometimes very useful. To allow that, if the parameter contains any operators or functions (+, -, /, etc., anything more than a bare identifier), it will be evaluated as a formula. So there is nothing magic about ""+variable, it is simply a formula that contains an operator. You could also use variable+"", or any other formula that contains an operator and evaluates to the correct value.

These rules apply anywhere that Panorama expects a field name rather than a value, which includes: datavalue(, fieldalignment(, fieldformula(, fieldnumber(, fieldpattern(, fieldwidth(, grabfieldtype( and various lookup( functions, and the getfieldproperties statement. This list is courtesy of Michael Kellock, he and I had been discussing this very topic yesterday in regard to updating the documentation to make it more clear about this.

Jim’s answer just appeared, but I spent so much time typing this, that I will post anyway.

I would say it was “when using a variable to name a field.” There are various commands and functions that need to have the name of a field passed to them as a parameter. They do not want to have the value of that field passed to them. This may be because they need to return some of the meta data associated with that field. Or perhaps, they need to scan the field throughout the database, or any other reason where knowing its value in a single record would not be any help.

The name of a field is a string of text, so you would normally just put the name between quotes, but you can also use a formula that produces that text. Properly, a field or variable name without quotes is a formula that produces the content of that field or variable, but so many people were leaving the quotes off of the field name, that Jim just finally made that legal. When the purpose of a parameter is to supply the name of a field, and there is a field name without quotes as that parameter, Panorama just assumes the quotes were left off, and treats it as if they were there.

Fields and variables use the same syntax. A variable name without quotes looks just like a field name without quotes, so Panorama thinks your variable is just the name of the field without the quotes.

If you are using a formula to specify the name of a field, the formula needs to be more than just a field or variable in order for Panorama to recognize it as a formula in “such cases.”

1 Like

Very interesting, thank you both for the details. I may actually remember this in the future.

Although I sent the list to Jim as part of a draft amendment to the documentation, it was Dave Thompson who compiled it.