Lookup field impossible to be blank?

Well, isn’t that how it goes. I found a workaround right after posting (I promise, I did a good deal of trying and researching before posting :slightly_smiling_face: Still got that inspiration right after I wrote it all up).
And I’m still curious about the non-blank blank!
If you have any ideas…

Anyhow, here’s the workaround.

Instead of {Chosen Status} being a lookup field, I made it an aggregated rollup with COUNTA(values) as the aggregate function.

This gives the desired targeting, as I can now filter out those > 0.

I’m stoked to have a solution ,though the mystery remains… :female_detective:

Something about your post vaguely rang a bell (or should that be, “rang a vague bell?”), so I took a quick skim through my latest bases to see if I was correctly remembering a similar problem, one that was resolved in a somewhat incongruous manner. I was, and it was — but now I look more closely at the situation, I don’t understand how this base can possibly work…

The base in question is my gold price example base, originally written to demonstrate how a value in one row can be calculated based upon a value in the previous row (you know, the whole "how can I do what Excel does with just $B2 - $B1?" question). If you look at the last field defined in the Daily Closing Price (DCP) table,PreviousPrice`, you’ll see it is a formula field with a formula along these lines:

IF({EvaluatedCondition) IS TRUE,
. ReturnStringValue,
. BLANK())

Now take a look at the third field in the base, the {Change in Price) formula field. Originally, my formula for that field read

IF(PreviousPrice!=BLANK(), Price-PreviousPrice, BLANK())

Makes sense, right? Under certain conditions, the PreviousPrice formula sets that field to a value of BLANK(); clearly a test for empty PreviousPrice values should test for BLANK()…

…except, if it does, no records test positively for BLANK(). Instead, the calculation is performed on any record with an empty PreviousPrice using an assumed PreviousPrice of zero dollars ($0.00), resulting in a bogus {Change in Price}.

When I encountered this problem a few nights ago, after kicking it around a bit, I tried the non-intuitive band-aid of replacing BLANK() in the formula with the empty string ('""'). Much to my surprise, it worked. I told myself is must be, since all non-empty values of PreviousPrice are strings, Airtable must have cast BLANK() into '""' for consistency’s sake.

Therefore, as an experiment, I suggest you replace BLANK() in your misbehaving formula with the empty string, and see if it works. If so, we still won’t be any closer to understanding why, but we’ll be a tad better-informed.

In fact, it’s likely after finishing this reply we’ll both end up knowing less about Airtable than we thought. Non-blank values for PreviousPrice are strings, right? They’re extracted from a longer string by a call to MID(), no subsequent call to VALUE() is made, and you’ll notice they all display left-aligned in the PreviousPrice column, indicating they are strings.

Now take another look at that Change in Price formula.

Weird, innit?

edit:

To be a little less cryptic, the formula subtracts a string from a number — but instead of returning #ERROR, as one would expect, for some reason it returns the difference between the number and the string’s numeric value. That is to say, the formula actually executed is

IF(PreviousPrice!=BLANK(), Price-VALUE(PreviousPrice), BLANK())

Now that is curious.

It seems like your formulas are indeed working as intended. Are they currently using the empty string? (The field descriptions show the formulas using BLANK() )

For the experiment, I replaced BLANK() in my misbehaving formula (great word choice, btw) with the empty string and the result was the same as before. It is still seeing all as “not blank” despite their apparent empty and non-empty states.

As a side note, why did you use '""' as the empty string? I have been using simply '' or "" as I’ve done in the past with Google Sheets or js
…
…
…
At this point I could’ve gone back and deleted much of my reply and replaced it with the new finding, but I’ll leave it here to preserve the order it happened.

As I wrote the question above about your empty string styling, I realized the quotes were to single out the empty string, at that you likely were saying that an empty string should be entered merely as a pair of quotes. I went back to my formula field, changed !=BLANK() to !='' (I prefer single quotes) and—lo and behold—it worked!

I’m curious, though, because I’m almost sure I tried that already. I didn’t even know about the BLANK() function before I started troubleshooting this yesterday. My default formula choice for this task would’ve been to use an empty string. Perhaps it’s simply a case where I started out with some formula using an empty string, changed it to BLANK(), changed something else about the formula, then changed it back to empty string, and the resulting combination is what worked.
I wonder what I had written before…

In fact, it’s likely after finishing this reply we’ll both end up knowing less about Airtable than we thought.

Actually, this troubleshooting round has resulted in me knowing more about Airtable (though less about the inner workings of the system, it’s true, like what it’s seeing in that field other than an empty void; there should be an AMA with an Airtable engineering team :slightly_smiling_face: That would be quite fun.)

I had exactly the same problem with rollup columns. Thread here. https://community.airtable.com/t/filter-is-empty-not-working-on-rollup-column/4290

Yeah, there are a few infelicities in how and when rollup and lookup fields handle the conversion of arrays to strings. So far, though, I’ve not managed to wrap my brain around it enough to know whether I’m seeing inconsistencies that need to be addressed by product management, product failures that need to be addressed by development, or user incomprehension that needs to be addressed by me. (In the following examples, I use the term ‘array-string’ to mean an array returned as a string consisting of array elements separated by a certain character, usually a comma. It’s the equivalent of the result of ARRAYJOIN({Array},','), and it is the format to which Airtable automatically converts many arrays prior to formula execution.)

Case in point was a problem I encountered with a recent example base. I was using @Simon_Brown’s old trick¹ to get the number of elements in an array, with the array in question the result of a rollup field. My initial attempt used ARRAYJOIN() as the aggregation function, but it returned separator characters delimiting all elements, empty or filled. (That is, the result was something like ,,[value],[value],,,[value].)

I next tried ARRAYCOMPACT(), which returned only non-blank elements, comma-separated — exactly what I expected to see: [value], [value], [value]. However, when I tried to calculate the number of non-blank fields, the result was always ‘1’ — indicating the formula found the length of the array-string without separator characters was the same as the length of the original array-string. Ordinarily, when I have an array I need to convert to a string, I append ‘&""’ to the field name, and it does. This time, though, appending ‘&""’ to the rollup field returned [value][value][value] — all run together, and essentially the same thing as I was comparing it with, the array-string minus separators. In the end, I had to wrap the ARRAYCOMPACT() with an ARRAYJOIN() to get what I wanted.

As best I recall, I’ve been converting arrays to strings by appending ‘&""’ for a while, now; I even think there are a number of examples of this in my shared bases so far. To date, I don’t recall any responding the way this one did.

I’m going to keep looking into this — and if you’ve been able to tweaze out additional information on when Airtable does and does not automatically convert a field into am array-string, I’d love to see it!

1. The trick involves subtracting the length of an array-string minus any separator characters — arrived at with SUBSTITUTE({Array-String},",",""), assuming the separator is a comma – from the length of the unmodified array-string. The resulting value + 1 gives the number of elements in the array. (This only works for array-strings with at least one element, so it should be wrapped with an if statement: IF({Array-String}!="",[Get # elements],0).)

Blank is shaky.