Converting text to unique lists for use as tags

[Kirk_Masden]

If you put a “list” into the “Valid if” part of an Enum or Enumlist column, the content of that list can then be selected by the user. An advantage of using the “Valid if” part of the in this way is that the contents of the enum (what the user sees as choices) can as data is added to or erased from the app.

In the app I’m working on right now, I wanted such lists to be made from a record called “Tags” into which the users can type whatever they wish and then the contents would be parsed automatically by the app. I wanted spaces and commas to function as separators (delimiters). Also, I wanted to make sure the text typed by unpredictable humans could be parsed properly even if the human used commas and spaces in odd ways. I have the humans type in their tag information as text and then parse it in a virtual column with the following expression.

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

As you can see, I first convert all of the spaces into commas. Then, it is made into a “list” with the SPLIT. The main problem with the list at this point is that all of those commas have produced a lot of empty list items. UNIQUE() gets rid of the repeats but I can still be left with the following kind of situation:

I learned from @Steve that empty list items can be removed with -LIST(""). Then, through trial and error, I learned that combinations of list altering virtual columns in my app were also producing “,” and " " as list items. Thus, the following three subtractions at the end of my expression are designed to eliminate all three unwanted list items:

-list("")-list(" “)-list(”,")

By the way, I found that the “Valid if” part of an enum column can be sensitive to the history of the list (where it came from and what virtual columns had changed it in the past). My lists appeared as follows if I used too many virtual columns:

I found that it was better to avoid a long chain of virtual columns and do as much as I could directly in the “Valid if” space, even if that meant creating a rather complicated expression.

Finally, I learned from @Suvrutt_Gurjar and @Steve to be careful about expecting too much from the LIST() expression when dealing with expressions.

list(“1 , 2 , 3”)

merely interprets “1 , 2 , 3” as the sole component of a list of one – not as a list column with {1 , 2 , 3} as the expression.

Thanks to @Suvrutt_Gurjar and @Steve for their help on the following thread:

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

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 …

Thanks to them, my app is now working well.

[Bahbus (Dave Papajcik)]

UNIQUE(
  SORT(
    SPLIT(
      TRIM(
        SUBSTITUTE([Tags],","," ")
      )
    ," ")
  )
)

I think this will do the same thing. First convert all commas to spaces. Trim will then eliminate any extra spaces that are getting converted to multiple commas, without the need for the list subtraction. Theoretically, if this does work without any unforeseen complications, it should be faster if the tags list ever gets huge.

EDIT: rearranged. I think I just extra big brained myself coming up with that… and now I need to go back into my apps and “fix” all my extraneous list subtraction… grumble I’m tired… I should not be awake at 4am.

[Kirk_Masden]

Thanks @Bahbus!! I remember seeing TRIM() earlier but hesitating to use it because I couldn’t find any AppSheet documentation. I don’t know if the documentation doesn’t exist or if I just couldn’t find it.

Thanks again, @Bahbus!

[Bahbus (Dave Papajcik)]

I’m awake again. No problem! Yeah, no one has ever bothered to create documentation for that and few other expressions. It was definitely one of those expressions that Praveen was like “We don’t have that and we totally should have that. It’ll be ready in a week.” type moments.

[Kirk_Masden]

I suspect that AppSheet doesn’t have TRIM() yet, after all.

First, I found the thread where @praveen promised TRIM():

TRIM Spaces from Text as a Command or Express...

We should have a Trim function! Will get it in next week.

I tried to test it but got a negative result:

With this expression, I’m trying to trim a text string that has a lot of extra spaces between words. Here’s the result, showing that the spaces haven’t been trimmed:

By the way, I had to substitute the spaces with “*” to make them visible. Ironically, the test function on the editor trims all extra spaces (or, to be more precise, makes it look as though the spaces have been trimmed) so you need to use a substitute() expression to make your test results visible.

@praveen, I wonder if you could let us know what the current status of TRIM() is? Thanks!

[Bahbus (Dave Papajcik)]

Hmm… I wonder how it actually functions, because I’m positive I’m using it successfully somewhere… I’ll play with this after some sleep.

[Steve (Steve Coile)]

TRIM() removes spaces at the beginning and end of a textual value, that’s all. It does nothing with interior space, nor with any non-space characters.

[Kirk_Masden]

I see. Thanks @Steve!

[Kirk_Masden]

By the way, @Steve, I wonder if you might have an idea about what might have caused the ellipses (that is, the truncated words with dots indicating that they had been cut off) that I described toward the end of my post.

[Steve (Steve Coile)]

what might have caused the ellipses (that is, the truncated words with dots indicating that they had been cut off) that

I’m not clear what you’re referring to.

[Kirk_Masden]

The Enum list yielded the following when I had a chain of virtual columns that I assume was too long or complicated. My app no longer has this problem so it’s not an important issue but I was curious if it was a known issue or if it has a known cause.

[Steve (Steve Coile)]

I can’t say for sure, but I’d assume the names were simply too long.

[Bahbus (Dave Papajcik)]

Well… crap. Since there is no documentation on it, and based off the thread, I assumed they were mirroring the Excel version of it which does remove spaces in between. Hopefully we can get an updated version of it. I’ll create a new feature request.

[Kirk_Masden]

Thanks @Steve. I thinks it reasonable to assume that it had something to do with the length but I’m sure that’s not the case here. The length, with the same words, is not a problem now. And, as you can see in the image, all of the words, long and short, are cut off. But, as I wrote before, it’s no big deal. My app is working now. I was just curious.

[Kirk_Masden]

I’m coming back to this after a few month because the strategy I had used to resolve my problem proved to be flawed and I’d like to report on what I think is a better strategy. First, I have two points I’d like to make:

1. The trim() function now works the way it does in Excel:

Update TRIM and FIND

As of today, TRIM() does what Excel does.

2. I’ve learned through trial and error that when one is preparing a list of data for the “Valid if” part of an Enum or Enumlist column, it’s generally better NOT to try to use a series of virtual columns. I tried that and kept getting inexplicable errors. It seems that it’s better to come up with a single expression that one can put into the “Valid if” slot directly, even it the expression winds up being pretty complicated.

To review what I was trying to do in the first place, I have a “Tags” text column and I wanted to make either commas or spaces work as separators to turn anything in the column into a tag in a list. I wanted it to work even if the user did something weird like putting a couple spaces or commas in a row. Here’s the expression that I ultimately wound up putting directly in the “Valid if” spot. It might be possible to simplify it but this seems to work quite well (no errors that cause me to stare blankly at my computer wondering what went wrong ):

sort(
	split(
		trim(
			substitute(
				concatenate(
					unique(
						SELECT(Kankaku[Tags],ISNOTBLANK([Tags]),true)
					)
				),
			","," ")
		)
	," ")
)

Here’s what I did:

• Use SELECT() to make a list of all of the cells that have data
• Use UNIQUE() to get rid of any duplicates (just to make it a shorter list)
• Use CONCATENATE() to convert it to a single chunk of text
• Use SUBSTITUTE() and TRIM() to get rid of the commas and insure that each tag is separated by a single space
• Use SPLIT() to convert it back to a list
• Use SORT() to put it in alphabetical order

By the way, I didn’t worry about using UNIQUE() a second time to clear any duplicates that might be created as a result of the SPLIT() because duplicates are ignored in “Valid if”.

[Steve (Steve Coile)]

FYI, the UNIQUE() around the SELECT() is redundant because you have TRUE as the third argument to SELECT(), which specifically directs SELECT() to return only the distinct values.

[Kirk_Masden]

Thanks! I was afraid I had some redundancy in there. I’ll fix it.