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