Filtering empty look-up of empty roll-up not working

[Alyssa_Buchthal]

Hey all! I have what I suspect is an array issue with Airtable's back-end, but it's preventing me from filtering on the front end, so I would love some help.

I have 3 tables: Books, Products, and Authors. A book might have multiple authors, and each author may or may not have a bio. I have made a roll-up in the books table for author bios with this formula: 

IF(values, ARRAYJOIN(values, "\n\n"), BLANK())

I have also tried it with just the ARRAYJOIN, and no if statement.

I then have a 1-to-1 relationship between Books and Products. In the Products table, I have a look-up of the roll-up of the bios in the Books table. 

Here's the issue: when a title has multiple authors, but none of them has a bio, the roll-up should obviously return blank. The look-up in the products table should return blank. But when I filter products by having the look-up be blank, the record in question won't show up. I tried filtering by newlines, whitespace, anything I could think of, but I can't find how AT is parsing the empty array returned from the look-up of the roll-up.

Has anybody run into this before and found a workaround?

[pressGO_design]

Try removing the BLANK()

IF(values, ARRAYJOIN(values, "\n\n"))

 and see if that works....

[pressGO_design]

Although, now that I reread your comment, I'm wondering if you could run an IF(Bio = BLANK(), "Blank", "notBlank")  - or IF(Bio, "notBlank", "Blank") - to see if any of your Bio fields actually do have stuff in them. So, rather than filtering for blank spaces and whitespace in the Products table, do that check in the Bio field of the Authors table.

Also adding that I hate using BLANK() to output empty values because something always seems to go amiss.

[Alyssa_Buchthal]

Good idea to test with the if statement! Here's what I got:

The red covers up cell contents (so obviously not blank), the middle 3 cells marked "not blank" are the record in question with 2 authors but no bio, the bottom 3 cells are a single author with no bio. So it looks like even when there's nothing there, AT is parsing the array as non-empty.

I tried adding a filter condition to the roll-up to only show records where the final bio is not empty, and even then the field is still showing as empty but indicated as not empty by Airtable.

My takeaway from this is that whatever they're using as a separator for the array on the back end is populating the field, but is non-parseable by end users, which is... pretty not cool. There's no way to access the contents at all to even cast it as a different value I could filter by (if I could access them, I could filter them). Can you think of any other explanation?

[pressGO_design]

Hmmmm. My next troubleshooting step would be to use TRIM() on the bios and then change the IF statement to check whether the trimmed value is Blank/notBlank. If that is correct, then you just need to change your rollup target from the Bio field to the Trimmed Bio field and that should solve your problem.

I’m not altogether convinced by your separator hypothesis, but I do think that if the TRIM() doesn’t solve your problem that you might want to contact support and, if your data is coming into Airtable via an external source (or even an Airtable form), take a little data trip in that direction to see if any unexpected values are being created and passed along. 

[Alyssa_Buchthal · Accepted solution]

The magic solution was thusly, combined with a filter on the roll-up to only include fields where values exist. Seems majorly redundant to me, but such is life I suppose:

IF(values, ARRAYJOIN(ARRAYUNIQUE(values), "\n\n"))

[Nicole_Burgess]

I used this workaround for a basic rollup field so that I could filter out what was not "blank" in the text of the field.

IF(ARRAYUNIQUE(values)=BLANK(),"blank", ARRAYUNIQUE(values))