Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Filter "Is Empty" Not Working on Rollup Column

2660 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Andrew_Schmitt
5 - Automation Enthusiast
5 - Automation Enthusiast

I’ve used a Rollup column to aggregate data from another table. There are duplicates in the data being aggregated, as well as blanks. I use the ARRAYUNIQUE(values) to operate on the roll up data. I even created an adjacent cell with an IF statement to test for ISBLANK and it is being returned as being blank.

Once I set a filter the Rollup column to not show empty cells, empty cells are still shown. The issue is in the only DB in my account, in the Organization tab.

8 Replies 8
Andrew_Schmitt
5 - Automation Enthusiast
5 - Automation Enthusiast

I am guessing there is something different about the results of a rollup column. I also just noticed if I try to make a calendar view I cannot use the dates in a rollup column to act as the dates for the calendar. Only native dates on the table are accepted.

I’m bumping this.

I’m rolling up results of a column where EMPTY() is used to define the cell. The result is a cell that appears to be empty. Yet if I filter records with “Is Empty” these records are still shown. Let me know if anyone would like me to walk them through the issue.

Can you share a version of the base? Or a copy with some data? It is hard to say without being able to see the data types and formatting. But…

Have you tried using BLANK() instead of EMPTY()? I am not familiar with EMPTY(), to be honest, but all my efforts with BLANK() work quite well with Rollups.

Also, what Rollup function are you using for the dates? I have a calendar that works with a Rollup of MAX(values) to get the max date for a given record. If your date field is computed you may need to check that the resulting format is actually being read as a date (I know this sounds dumb but sometimes it really is an issue like this!).

Hope some of this was helpful.

Sorry - I am using BLANK().

I have a column in a table (SUBSCRIPTIONS) that returns a another column or BLANK() depending on the date.

IF(IS_AFTER({Expires}, TODAY()),{Report Code},BLANK())

When I roll up that column on another Table Tab (COMPANIES, Active Services column) the results are as I expect. I use the ARRAYUNIQUE(values) function to create an array. Some of those arrays are multiple entries that are rolling up BLANK() results.

But when I then filter the records in COMPANIES for records where the Active Services “is not empty” these records which are an ARRAYUNIQUE of EMPTY() values are still visible.

I would rather not share the database with a non-Airtable employee as we use it for production functions. But I could create a sample of the failure.

Here’s a sample DB that I constructed that has the problem.

Also to be clear, I am not rolling up a date field. I’m rolling up text fields.

Are these dates just text?

To clarify, these will need to be formatted as dates somewhere in the process if you want the Calendar view to recognize them.

Nicole_Burgess
5 - Automation Enthusiast
5 - Automation Enthusiast

My workaround was to accentuate the logic in the rollup field with text:

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