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:
Thanks to them, my app is now working well.
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.
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.
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:
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.
I see. Thanks @Steve!
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.
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.
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:
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:
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โ.
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.
Thanks! I was afraid I had some redundancy in there. Iโll fix it.
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.