I want to use the aggregate function to get a sum of values in a second database. But the query function needs to refer to a fileglobal variable value in the first database. I tried using this syntax:
aggregate({Payment},"+",{Field = val(constantvalue(fgVariable[2,-2])},"OtherDB")
fgVariable is a variable in the first database, and I get an error message that says datatype( function error: no field or variable named “fgVariable”. The field FieldName is a numeric field, which is why I used the val( function.
I have a found a workaround. I used an arraybuild( function with an arraynumerictotal( function. The arraybuild( function handles the reference to fileglobal variables in the first database.
arraynumerictotal(arraybuild(cr(),"OtherDB",{Payment},{Field=fgVariable}),cr())
Is there a way to get the aggregate function to use a fileglobal variable in the Query formula when doing the aggregate in another database?