Emptycell( not working correctly inside an arraybuild( query

Panorama X
1

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.

2

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()})
3

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.

4

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.

5

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?

6

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(.

7

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

8

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.

9

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.

10

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

11

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

12

FWIW, this is still broken in b32 … and I wasted a lot of time re- re- re- re-figuring out my logic, until I realized EmptyCell() does not work within ArrayBuild … but it does (nearly) everywhere else. grr. :slight_smile: