This formula correctly returns the value I want from another database:
superlookup("DatabaseB",{Customer contains "Teapot"},{CustomerNumber},"default","<< Not found >>")
It correctly returns 9999, the Customer number. But if I put a fileglobal variable, fgCustomer, in the query formula, it fails and returns “Teapot”, the value of the variable.
superlookup("DatabaseB",{Customer contains fgCustomer},{CustomerNumber},"default","<< Not found >>")
What the heck is happening?
A fileglobal variables is only available in the database they are defined in. This is called the “scope” of the variable.
When using a superlookup( function, both the query formula and the data formula are evaluated in the scope of the database being searched – NOT the current database. So fgCustomer variable can’t be used in the query formula. (I’m not sure why it returns Teapot, I would think it would return an error. That may be a bug.)
There are a couple of possible solutions. One would be to use the fileglobalvalue( function. Another would be to temporarily create a local variable with this value.
let xCustomer = fgCustomer
superlookup("DatabaseB",{Customer contains xCustomer},...
Or you could use the cache function within the formula, like this:
ignore("",cache(fgCustomer,"xCustomer"))+superlookup("DatabaseB",{Customer contains xCustomer},...I thought about scope but didn’t know what to do with that issue.
Thank you for the solution.