Counting lists in cells when some cells are empty

[Kirk_Masden]

Postscript: I originally posted this in the “Tips & Tricks” category but I learned that I was using a text column when a EnumList would have made more sense. So, I think my post is too problematic to leave up as a “tip.” At this writing the one question that remains is why I experienced a minor problem when trying to count text that had been converted into a list via split().

==============================================

Here’s a little tip regarding how to get an instantaneous, correct result when counting a list that is contained in a single text cell.

Let’s say that a column, which is a “Text” column in AppSheet, is made up of a cell that contains the following text:

Item 1, Item 7, Item 24

This is text but, within AppSheet, it can be parse as a list, as follows:

count(split([List column],", "))

This expression uses ", " (a comma and a space) to “split” the text into the following list:

Item 1
Item 7
Item 24

Then, the Count() expression counts the number of items in the list, resulting in “3”.

This works fine most of the time but I came across one problem: When 1) the cell to be counted was empty and 2) the app was still syncing recently changed data to the cloud, I would temporarily see a “1” for the empty cell on my app. After the other data had been synced the number would change to “0” but, since this is a number that is part of the user experience, unexplained number flipping was something I couldn’t tolerate. Here’s my solution:

if(len([List column])=0,0,count(split([List column],", ")))

The Len() expression works immediately to produce a “0” for empty cells so this killed the number flipping phenomenon.

If you’re wondering why the expression is

if(len([List column])=0,0,count(split([List column],", ")))

and not

if([List column]="",0,count(split([List column],", ")))

as it could be in Google Sheets, see the following:

Problem with condition based on whether or not column is empty Questions

I have a real column (one with data in the spreadsheet), called [DW Set aside], that may or may not have text in it. I want to have a virtual column display “None” if the cell is empty but to display the text if it has any. This formula seems to work: if( len([DW Set aside])=0, “None”, [DW Set aside] ) However, the following did not work: if([DW Set aside]="",“None”,[DW Set aside]) In regard to this second expression, it always shows “None”, even when the column has text. I wonder why…

As @Steve kindly pointed out there, ISBLANK() would work too. (I probably should have used that . . . just thought of len() first.)

Hope this helps. Happy computing!

P.S. This is a tip, or work around, for what seems to be to be a bug. It would be nice if Count() could work consistently, even with empty sets.

[MultiTech]

Maybe I’m missing something, but why not just use an Enumlist column type to hold the list? COUNT() works fine on that.

[Kirk_Masden]

Thanks for responding. Actually, I’ve used Enum but I don’t think I’ve used Enumlist. Perhaps I should have done so. Here’s what the official explanation says:

What it does: An EnumList field contains one or more values from a specified list of values. You specify the list of values from which the user can pick as described for Enum above.
https://help.appsheet.com/en/articles/961388-effective-use-of-column-headers

In my case, I’m not specifying a list that users pick from. Rather, the “list” is being accumulated by the user in a way that I can’t predict in advance (starting from blank); it’s frequently rewritten and gets longer and longer. Is that something I could have don’t with EnumList? My current approach seems to be working well for what I’m trying to do – except for the problem with counting the results of split().

[Steve]

EnumList would work for you here.

[Kirk_Masden]

I see. Thanks. I’ll look into changing the column type later.