Lists produced by LIST() seem to be inferior to list columns

[Kirk_Masden]

I use the following expression to convert text with spaces and/or commas into lists where the spaces and commas both function as separators (that is, are all converted to " , "):

list(substitute(substitute([Tags],","," , “),” “,” , "))

This expression converts the following kind of user generated text

donyuen money extra,spaces more money

into the following kind of list

donyuen , money , , , extra , , , spaces , more , money

So far, so good. Now for the problem: Expressions that modify lists do not work directly on this expression. So, for example,

unique(list(substitute(substitute([Tags],","," , “),” “,” , ")))

does not eliminate the second use of “money” and

sort(list(substitute(substitute([Tags],","," , “),” “,” , ")))

doesn’t sort.

However, if the same expression, that is

list(substitute(substitute([Tags],","," , “),” “,” , "))

is used by itself in a “list” column called “Tags to list” then

unique([Tags to list])

properly produces

donyuen , money , , extra , , , spaces , more

and

sort(unique([Tags to list]))

produces

, , , , donyuen , extra , money , money , more , spaces

Could LIST() be made to be truly equivalent to a list column?

P.S. I would like users to be able to tag items so that they can then choose from a list of tags to display all items with that tag in a slice. So, users need to be able to type in tags but I don’t expect them to always obey formatting rules. I want all spaces and commas to be treated as separators for this reason.

[Suvrutt_Gurjar]

Hi @Kirk_Masden,

As per my understanding using SPLIT() (Definition- List from text) for converting text string into list is better approach than using SUBSTITUTE() ( Definition: Text with replacements)

By the function definition, SUBSTITUTE() still returns a text value out of a text string, even if we have separated it by a comma. So even though the return value looks like a list, it is still a text.

You can check that by removing list from the expression and using

substitute(substitute([Tags],","," , “),” “,” , ")

The expression result will show up as text.

Now applying further LIST() to this value will not create a true multi element list out of an intrinsic text value. When you apply LIST(), it does create a list but a single element list with the comma separated string.

You can check that with expression count(list(substitute(substitute([Tags],","," , “),” “,” , ")))
It will return count as just 1 , meaning the list has just 1 element.

help.appsheet.com

SUBSTITUTE()

Text with replacements

On the other hand, SPLIT() returns a true list value by definition of the function.

help.appsheet.com

SPLIT()

List from text

So ,I believe,we need to use SPLIT() to split a text string into list rather than a SUBSTITUTE()

I tested the above string with SPLIT() and it works with UNIQUE() function.

Hope this helps.

[Kirk_Masden]

Thanks a lot! I work with what you have told me and report back!!

[Kirk_Masden]

I knew about SPLIT() but thought that LIST() would look for commas and interpret them appropriately. That confusion of mine was reinforced by the fact that, even without a SPLIT() expression, text with commas that is contained in a LIST() expression that is in a “list” column type is properly interpreted as being demarcated by commas.

If I combine the SPLIT () and LIST() expressions as you indicated, @Suvrutt_Gurjar, I can make fairly large, nested expressions in which letters are substituted and the list is simplified and reordered. Here’s expression I came up with to solve me original problem. To convert a string like

donyuen money extra,spaces,more money

(which has extra spaces that you can’t see and commas without spaces) to a comma-separated list I made the following expression:

unique(sort(split(substitute([Tags]," “,”,"),",")))

which results in

, donyuen , extra , money , more , spaces

If possible, I’d like to get rid of the comma at the beginning. I could probably convert the list back into text, use an IF() expression to look for a comma in the beginning and remove it, and then convert the rest back to a list – but I guess that won’t be necessary.

Thanks again for your help. I removed the “bug” tag on this post as it wasn’t really a bug after all.

[Suvrutt_Gurjar]

Hi @Kirk_Masden,

You are welcome.

My testing shows that your latest expression is excellent to convert the text string

Kirk_Masden:

donyuen money extra,spaces,more money

into a list. My testing ( My column name is [SizeCount]) did not show any leading unintended comma. Please refer the test results below. It also shows the list element count as 5 in another test column I tested.

As per me it is a a great expression you have built to achieve what you wish to do.

[Steve]

Kirk_Masden:

list(substitute(substitute([Tags],","," , “),” “,” , "))

Produces a List with one item: the content of the Tags column with some substitutions. A one-item list.

Kirk_Masden:

I would like users to be able to tag items so that they can then choose from a list of tags to display all items with that tag in a slice. So, users need to be able to type in tags but I don’t expect them to always obey formatting rules.

Use an EnumList.

Suvrutt_Gurjar:

As per my understanding using SPLIT() (Definition- List from text) for converting text string into list is better approach than using SUBSTITUTE() ( Definition: Text with replacements)

Correct.

Suvrutt_Gurjar:

Now applying further LIST() to this value will not create a true multi element list out of an intrinsic text value. When you apply LIST(), it does create a list but a single element list with the comma separated string.

Correct.

Kirk_Masden:

I knew about SPLIT() but thought that LIST() would look for commas and interpret them appropriately.

Nope. LIST() creates a List from its arguments by placing them into a list; it doesn’t examine the arguments at all.

Kirk_Masden:

, donyuen , extra , money , more , spaces

If possible, I’d like to get rid of the comma at the beginning.

The reason for the leading comma is that the original text, donyuen money extra,spaces,more money, “has extra spaces that you can’t see and commas without spaces”. Two consecutive commas, when SPLIT(), will produce a blank item. Blank values sort before non-blank values. So the leading comma is actually separating the first item, a blank value, from the other items. If you don’t want blank items, you can subtract them:

(sort(split(substitute([Tags]," “,”,"),",")) - list(""))

Note that subtracting one list from another has the side-effect of also removing duplicates, which renders UNIQUE() unnecessary in this case.

[Kirk_Masden]

Thanks so much to @Steve and @Suvrutt_Gurjar for the support and advice.

I had a lot of trouble with this today. I was particularly glad to learn from Steve how to remove a blank value. I had been trying to do that but instead of list("") (focusing on removing the blank) I tried list(",") (and other versions), erroneously focusing on the comma, which seems to be the sign of the unnecessary blank, rather than the problem itself.

Actually, the unnecessary comma was causing me a good deal of trouble because it was appearing in a part of the app that users will see:

If I had understood 24 hours ago about the proper use of SPLIT() and how to use -list("") to get rid of a blank, I could have saved myself a lot of time. I wound up trying a lot of other attempts a work arounds that didn’t work and wound up breaking my app. Fortunately, I’ve been able to revert to a stable version from earlier today but if wound up being a hard day for me.

Still, I’ve learned a few things, so that’s good.

Thanks for your help!!!

[Bahbus]

I’ve similarly learned many hard lessons about how AppSheet treats lists. At one point, I was poor-man debugging by printing their length at every step. If I had any wish, it would be that regular columns would be allowed to be regular lists instead of VC only.

[Steve]

Bahbus:

If I had any wish, it would be that regular columns would be allowed to be regular lists instead of VC only.

What’s wrong with EnumList?

[Bahbus]

Sometimes I wish for a real column that doesn’t allow multiple selections by default.

[Kirk_Masden]

Thanks for your interest, @Bahbus. I have a better understanding of how to handle lists than I did a couple of days ago but, I agree, they can be quite difficult.

In regard to your last point, if you don’t want to allow multiple selections, wouldn’t ENUM work?

[Bahbus]

That’s how I do it now via a Valid If that is a list. I guess I misspoke about the single selection point in this case. That isn’t an issue for this. When I started designing my first app, the data had a few columns that were already lists inside of the cell ready to go. Nothing new to be added or deleted. Just displayed linked together. But AppSheet treats that as a single string just like your initial problem. Hell, I’m pretty sure even with EnumLists you can’t just prefill a cell with data and have AppSheet read it as a list of any kind straight from the sheet. So I guess, my main point is that I occasionally wish that you could set a regular column as a list in such a way that the backend wraps the cell’s value in a SPLIT() automatically. And maybe even somehow does it better than I could myself.