List filtering method

AppSheet AppSheet Q&A
1

Simple question, but I posted the question here because I couldn’t figure out how to do it after researching.
Please tell me a function to get a list of numbers greater than 3 in the list below in the appsheet’s Expression .
{1,2,3,4,5,6,7}

Return value I want to get: {4,5,6,7}

I have tried the formulas below, but they all result in an error.
{1,2,3,4,5,6,7} > 3
List(1,2,3,4,5,6,7) > 3
SELECT({1,2,3,4,5,6,7}, _ > 3)
SELECT({1,2,3,4,5,6,7}, [] > 3)
SELECT({1,2,3,4,5,6,7}, {1,2,3,4,5,6,7} > 3)
FILTER({1,2,3,4,5,6,7}, _ > 3)
FILTER({1,2,3,4,5,6,7}, [] > 3)
FILTER({1,2,3,4,5,6,7}, {1,2,3,4,5,6,7} > 3)

Thank you. Regards

1 Like
2

For such a list that is NOT based of a column value in a backend table or number value in a VC, I believe you may not be able to use SELECT(), FILTER() functions. Instead, you could use an expression something like

[NumericList]-

TOP([NumericList],

COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & [NumericList] & " , “),
(” , " & [SplitNumber] & " , ")
)
),
" , "
)
)
)

Where [NuemricList] is the list column with base type as number such as {1,2,3,4,5,6,7} that you have shared.

[SplitNumber] is the number type column containing the numeric value from where you wish to remove the numeric list ( 3 in your case)

The expression assumes the list is sorted. Or else you will need to first sort the list in ascending order by using SORT([NuemricList], FALSE) everywhere in the expression.

**This suggested expression is based on the concept of finding the index of an element in a list.**The beautiful tip of this INDEXOF() expression was shared by our senior community colleague @Steve as referenced below

INDEXOF() - Google Cloud Community

By the way, may we know where and how you are creating such a list in your app.

3 Likes
3

Thanks for the great answer.
I understand that select and filter are not available for lists that are not based on VC.

In an application I’m writing, I’m trying to build a mutable workflow.

Table A: Main_table
Columns: Main_ID, current_flow_ID, current_flow_Num, Next_flow_ID, Next_flow_Num

Table B: Workflow_table
Columns: Workflow_ID, Ref_Main_ID, flow_Number,

Example column values (flow_Number): 10, 20, 21, 30, 40,…

Image of workflow:
10 → 20 → 21 → 30 → 40 …

When routing to a workflow, we need to get the next workflow Num and ID from the current workflow ID.

I was thinking about how to get the next workflow ID as follows.

Next_flow_Num = MIN( SELECT(tableB[flow_Number],[Workflow_ID]= [Ref_Main_ID].[current_flow_ID]) > [Current_flow_Num] )

I would like to consider the advice you gave me this time.

4

Thank you for the details. Are you trying to get an element value from the enumlist column [flow_Number] for a single row? If, so, Then I believe , yes, you may need to use the suggested approach based on INDEXOF() expression.

5

Thank you. By applying the method you taught me, I was able to obtain the desired information with the following formula.
The original question was about returning a list of numbers that exceed the specified number, but in the end I was able to get the next number I needed.

INDEX( SORT(List(10,70,30,50,40,90,70)),
COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & SORT(List(10,70,30,50,40,90,70)) & " , “),
(” , " & 30 & " , ")
) )
,
" , "
)
) + 1
)

retune value: 40

1 Like
6

Great to know you created an expression matching your need. Thank you for the update.

1 Like