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:
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.
Maybe Iโm missing something, but why not just use an Enumlist column type to hold the list? COUNT() works fine on that.
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().
EnumList would work for you here.
I see. Thanks. Iโll look into changing the column type later.
User | Count |
---|---|
43 | |
30 | |
26 | |
14 | |
14 |