Converting text to unique lists for use as tags

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:

Screen Shot 2019-12-29 at 12.28.24

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:

Thanks to them, my app is now working well.

3 Likes
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.

3 Likes

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!

1 Like

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.

1 Like

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

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

I tried to test it but got a negative result:

Screen Shot 2019-12-30 at 11.29.23

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!

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.

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.

1 Like

I see. Thanks @Steve!

1 Like

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.

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

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.

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

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.

1 Like

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.

1 Like