Emptycell( not working correctly inside an arraybuild( query

This is a problem that leads me to think I must be making a mistake, but I can’t find one. These two versions of arraybuild( result in substantially different results, but they should be identical, I think:

arraybuild(cr(),"",{ARRAY FORMULA},{arraysize(FIELDNAME)=0}) 

and

arraybuild(cr(),"",{ARRAY FORMULA},{emptycell(FIELDNAME)}) 

Many more rows are included in the second array. (121 rows vs 89 rows in my database).

But if I simply do a selection with those two formulas, the both return 89 rows. So it seems like emptycell( is not working correctly when used inside the query of an array build function.

emptycell(FIELDNAME) gives you a TRUE or a FALSE.

If you want to select empty cells you have to do a query for the value TRUE.

arraybuild(cr(),"",{ARRAY FORMULA},{emptycell(FIELDNAME)=true()})

I see the Help page shows making a selection using the criteria emptycell(FIELDNAME)=true(), but I found it works whether or not you include the =true(). In my test, emptycell(FIELDNAME)=true() does not work in the query either.

I am also getting strange results, but they aren’t consistent with yours. I created a database with one binary field, and one integer field. The database has 4 records. The first record contains a data array with 2 elements in the A field. The next two records have empty cells in the A field, and the last record contains a data array with one element of empty text. A formulafill with the formula arraysize(A) returns values of 2, 0, 0, and 1 respectively.

If I run the statement
message "Y"+arraybuild(cr(),"",{"x"},{arraysize(A)=0})+"Z"
or
message "Y"+arraybuild(cr(),"",{"x"},{sizeof(A)=0})+"Z"
I get the result

Yx
xZ

which is what I would expect. If I run the statement

message "Y"+arraybuild(cr(),"",{"x"},{emptycell(A)})+"Z"

I get

YZ

so it would appear that the query is coming up false every time. It’s interesting that queries of
{emptcyell(A)} and ,{sizeof(A)=0} would get different results since CustomFunctions.def defines the emptycell( function like this.

EMPTYCELL+1(sizeof(•1)=0

I should also mention that I tried using the emptycell( function as the formula, instead of the query, and the results I got were correct.

I’m questioning the use of arraysize( without the separator parameter which should only be omitted when dealing with data arrays. My test show that using it in an arraybuild( without a separator will always return 0. What am I missing?

The arraybuild( function is building an array with a separator, but the query is testing for the size of a data array in a binary field. The query that uses arraysize( is getting the correct results for both Tom and me. The strange results are coming from the query that uses emptycell(.

Ok, I didn’t realize he was working with a binary field.

I am sorry to say that I made a mistake in my original post, although the point I am making is unchanged. The first formula should have been

arraybuild(cr(),"",{ARRAY FORMULA},{sizeof(FIELDNAME)=0}) 

It appears that the emptycell( function returns a false for every row when used in the query even though for 32 rows the field is not empty.

Now I see why Dave was using an array in one of the fields.

Yep, you’ve found a bug. For now, you better use the sizeof( function. As Dave pointed out, this is really weird since emptycell( is just a custom function defined based on the sizeof( function, so I would have expected them to work exactly the same (not just because they are documented to work the same, but are actually using the same code). There may be a problem with how custom functions work in this situation.

Your Bitbucket title refers to arrayfilter, although I commented on arraybuild.

Ooops, sometimes the fingers don’t type what the brain is thinking! I knew what I meant :slight_smile: I have corrected this.