Use Aggregate( to test for existence of a value in field

Panorama X
1

In order to find out if the value of a variable already exists in a particular field, I tried this:

?(aggregate({},"count",{Fieldname=varname})>0,"Already Exists!","")

but the aggregate function always returns 0 even if the value does exist. It seems that the value of the variable is not available to the formula. Is there some way to use aggregate to test for uniqueness of a variable value in a particular field? If not, any other approaches that would work?

2

The only way that I could duplicate your problem was to use a calculated value in a floating point field. It could fail in that instance, because slight differences in the calculated values would make them unequal.

Local x
x = π
A = sqr(x)^2
message "The value " + ?(aggregate({},"count",{A=x})>0,"Already Exists!","")

If I simply set the A field to x, rather than sqr(x)^2, it worked just fine.

3

When I modified my formula using an ignore function, everything worked as hoped for:

?(ignore(aggregate({},"count",{fginv=Invoice}),fginv)>0,"Already Exists!","")

If I understand what is happening, the aggregate function was not being evaluated when it stood alone just because fginv was changed. Adding the ignore function, set to ignore fginv, then prompts aggregate to be recalculated. I had forgotten about that fact.

4

Reading between the lines, I think you are saying that this formula is in a text display object, and a ShowVariables command wasn’t updating it.

The reason for that is that the query formula in the aggregate( function is quoted. Only the aggregate( function itself knows that it isn’t just a string of text. fginv is simply a part of that string. It isn’t recognized as a variable. The ignore( function put fginv in its second parameter, without quotes, where it would be recognized as a variable.

5

Thanks for the help Dave. You are correct that the function was inside a text display object.