Wrong count in list relation

I have a list relation that shows the count as 4, but inside it shows just one item. I check the spreadsheet itself and actually it is just one item, but on Glide the item count shows as 4.

I’m not sure if this is a bug or if it’s something I’m doing wrong and it’s driving me a bit mad. I have a date based filter on the list relation. Screenshots attached. Would appreciate if anyone could point out what I am doing wrong.

This is the list relation filter

This is what shows inside

I think it’s a bug. The empty date field is also being counted for “after today”.

It’s a date time field, if that’s relevant.

Yes, I would say this is a bug.

Empty dates will not show in a calendar view, which is working correctly, but the filter should not be picking up the empty dates.

If you change the style to list instead, of calendar, do the other 3 records show with the filter still applied?

Yes, it shows. It’s definitely related to how Glide is counting the date field and displaying in calendar format.

I have another list relation showing with a string-based filter applied. I’m checking for the string " + (HV" in the title. It shows a count of 21 in the list relation, but inside (displayed as calendar) there are only 17 items. When I change the format to something to non-calendar it shows 21 items.

For this string based filter that I have mentioned here, Glide cannot really do anything. The records won’t show in the calendar format because the date is empty, because the users have neglected to input the date.

However, the count when filtered with the date itself is also picking up empty values. So that one definitely needs a fix.

Should I set the filter inside the spreadsheet itself using formulas and then set the Glide filter to just checking whether that spreadsheet column is TRUE?

Yeah, the new column and true filter would have been my next suggestion for the time being. That’s what I would do.

I’ve hit a snag. I have to check whether the status column is not empty AND the date column is empty, so I can alert the users to fill the date column. Apparently ARRAYFORMULA does not work with AND. It won’t propagate to the cells below. Any suggestions?

https://support.google.com/docs/thread/5127906?hl=en

This is what I was trying to use: ARRAYFORMULA(AND(H2:H<>"", O2:O=""))

I tried to get around that by using COUNTA(L2:L, O2:O) in the arrayformula, but that doesn’t work either. Same problem as above. :expressionless:

I have a mildly lunatic solution
ArrayFormula(IF(ISBLANK(L2:L) + ISBLANK(O2:O)<>2, ISBLANK(L2:L) + ISBLANK(O2:O), “”))

If one of the columns is empty, this will return 1
If both are empty, this will return 2
If both are filled, it returns 0

So it basically acts like a boolean. However I’m not sure if this is the way to go. Any better suggestions would be greatly appreciated.

Here, try this:

=ARRAYFORMULA(IFERROR(IFS(L2:L="", FALSE, O2:O<>"", TRUE), “Needs Date”))

IFS can check multiple conditions and will return the first value that equals true. If L2:L is empty (true statement), the I return FALSE as the value. You can make it blank if you want. If L2:L is not empty, then it checks O2:O to see if it’s filled. If it is, then I return TRUE. Again, you can make this blank if you want. If none of the conditions are met, then it returns ‘#NA’, which is an error, and is caught by IFERROR. This then returns the “Needs Date” value.

1 Like

That’s a beautiful formula. This is what I did.
=ArrayFormula(IF(L2:L="", “”, IFERROR(IFS(L2:L="", FALSE, O2:O="", TRUE))))

I have a column called Empty Date, so I put TRUE there if the date is empty.

I also see how my first solution was checking for an OR, which is not really what I want. This one exactly solves the problem.

1 Like

Just a couple observations with your formula…Your outside IF is checking L2:L="". Since you have that, the inner IFS will never become True for the L2:L check in there. If what you have is working, then I would strip it down to the following instead:

=ArrayFormula(IF(L2:L="", “”, IF(O2:O="", TRUE, "")))

Maybe I overcomplicated my solution since I had multiple conditions in my head. I think you are on the right path though.

Oh right. That’s true. Yes, this simplified formula works for my specific situation. I am looking for the second column which is left empty when the first column is necessarily filled in.

Thank you :relaxed:

1 Like