New Function to play around with "List"

Appsheet introduce new features, OCR and other useful functions, they returns the values to the data fields as LIST types like {50, 80, 150, 300}
However, the important feature we are missing which have been discussed with the community.
In short, we are missing feature to “reconstruct the list with given conditions”
This is something like controlling “array” with forEach syntax in programming languages with “where” conditions.

Just borrowing name of JS function of forEach to make it clear, so the new expression is something like

ForEach([ListTypeDataField], “EachItem” >= 100) to return new list.

For the condition part, we are able to use existing appsheet expression off course, like

ForEach([ListTypeDataField], Contains([ListTypeDataField],“ABC”))

Somethin like intuitive expression desired.

Thank you for consideration.

@Fabian

Status Open
7 9 849
9 Comments
MultiTech
Participant V

You’re right, there is no way to accomplish what you’re wanting. For example:

If I have the following list: list(1.5, 0.2, 5.3, 1.9) - and I only want values greater than 1… there’s no way to do that.

I could use list math (the ability to +/- one list from another [ LIST(1,2,3,4) - LIST(4) = LIST(1,2,3) ]) - but you have to construct the list of options to remove BEFORE you can remove them.

I’ve considered all manner of formulas to try: index, substitute, find, etc.

Nothing would work for that scenario, I’d have to build some other way of working with the data to try and make it work; but if this was a list from OCR results…

Koichi_Tsuji
Participant V

Yes, Matt, I tested all possible workaround, but all failed… I spent hours.
As you rightly said, when we scan (ocr) text and Appsheet nicely recognize all, then afterwords the question is how to cook them?
As @Fabian pointed out, it may contain rubbish, then how to remove those?

After deep consideration, I concluded the new feature like ForEach to reconstruct the array, sorry LIST need to be in place.

Will see !

Grant_Stead
Participant V

agreed…
Maybe a crazy TEXT work around…
SORT
FIND
LEN
TRIM
type vibe?

MultiTech
Participant V


Like what???

Fabian_Weller
Participant V

I found a workaround for my case:
I only want numbers greater than 100000.
First I have to SORT() my list:
SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE)

Now I can search for the first time, the number is <100000 and use this as the number for a TOP() expression:

TOP(SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE),
IFS(
INDEX(SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE),1)<100000,1,
INDEX(SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE),2)<100000,2,
INDEX(SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE),3)<100000,3,
INDEX(SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE),4)<100000,4,
INDEX(SORT(LIST(12 , 217435 , 217434 , 2019 , 1000),TRUE),5)<100000,5
)-1
)

The result is: 217435 , 217434

MultiTech
Participant V

_redline
Participant IV

+1 on this. Of course handling list computation can be offloaded to just creating a new table, but this can be frustrating when you want to operate on a list of data without adding more and more tables to manage.

I’m trying to simply build a list of “suggested values” on one enum type column that just pulls from two columns that have dependent relationships. I could of course accomplish this with adding a lookup table…on top of all the other tables though.

Whereas having a collection of lower-level list based functions could be used to operate directly on a list (e.g. if FILTER() could take any old list and not just a table). Endless possibilities could be made available by providing lower-level tools in general that the community could then use to build their own tools: list offsetting, list filtering, a more concise list indexing workflow, etc.

Koichi_Tsuji
Participant V

In programming tems, “manipulating array” is not being provided by appsheet in native way, which is awaited to come.

I just wait and see AppSheet team to introduce those type of capability by introducing new type of Appsheet Expression so that we App Creator can easily manipulate date to control what we want.

AppSheet is quite good at dealing with “list” type of data, based on SQL type of syntax, but not good at dealing with same list type of data (array) using simple expression to manipulate.

Lets wait and see.

Status changed to: Open
Pratyusha
Community Manager
Community Manager