Superlookup Frustration

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.