LinkedIn
Twitter
At this point, I’ve solved the problem using Steve’s approach. I’m still studying the other approaches and they may be equally good, but I’ve confirmed that the direction @Steve pointed me in worked out.
Here’s my understanding of Steve’s approach:
- Use list functions to reorder all of the values in a proper list.
- Use CONCATENATE() to make the list into a single text string so that you can chop it into two pieces at the appropriate point using SPLIT().
- Then, save the half of the list you want (with the appropriate commas so that it can be split into a list again next time) to the sheet as text.
That’s a bit of an oversimplification but that’s pretty much what I have done following Steve’s lead.
I can report, however, that the following was rejected by AppSheet:
+ {""}
This produced the following error:
{""} could not be parsed due to exception: Sequence contains no elements.
Also, since I have many values over 1000 I found it necessary to use concatenate() several times to get rid of commas between the hundreds and thousands places. I found that if I had some values with the commas and some without that SORT() didn’t work properly. Also, the commas interfered with proper functioning of SPLIT(), as I recall.
I’m fascinated and amazed by the many different approaches that have been so kindly suggested. I would like to continue to study all of them but for now, @Steve’s approach has done the trick for me. 
Thanks everyone!
Thanks so much everyone!! I need to study these solutions. I deeply appreciate the extremely helpful feedback and would like to report back after I learn to use them.
@tsuji_koichi I think the total number of list items may be six or so. So, I think that using index() I could go through each one up to that number. I need to study how @Steve has used INDEX() but it looks like his approach is different from what I had imagined. I don’t understand @Aleksi’s use of INTERSECT() yet but I think that’s probably just my own failure to wrap my brain around it. I’ll work on it. 
As far as you can place the max number of count for the enumlist items, then it could work.
Not sure your exact use case in terms of how to generate the enumlist, which comprise of the numeric number.
See screenshot.
Is this kinda of what you like to achieve?
To get started with, I just unhided the hidden fileds for the explanation.
Base Number is number type where the app user enter the arbtray number for the subscruction calc, i.e. N - each enum list item.
Count is just count the number of items for the list.
Base list. This is a bit of trick.
I made independent table looks like this.
Base list filed is ref type field, which refer to this table based on “Count” Fields. By doing so, we are able to generate the list such as
{1}
{1 , 1}
{1 , 1, 1} … based on the count of list items dynamically.
Then Base number dynamic list.
For this quick sample, I retrcted the max number of count = 3. The user can not select 4 and more enum list items.
App formula for this field
IFS(
[Count]=1,
list(number(index([Base List],1))*[Base Number]),
[Count]=2,
list(number(index([Base List],1))*number([Base Number]),number(index([Base List],2))*number([Base Number])),
[Count]=3,
list(number(index([Base List],1))*number([Base Number]),number(index([Base List],2))*number([Base Number]),,number(index([Base List],3))*number([Base Number]))
)
Looks complex, but not so. I think this expression self explanatory. Based on count of list and the arbitray number for the substruct calc, it will generate the dymamic list.
Lastly the result.
IFS(
[Count]=1,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
)
)-list(" ")
,
[Count]=2,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
),
IF(
number(index([Base Number Dymamic List],2))-number(index([Enum List],2))>0,
number(index([Base Number Dymamic List],2))-number(index([Enum List],2)),
" "
)
)-list(" "),
[Count]=3,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
),
IF(
number(index([Base Number Dymamic List],2))-number(index([Enum List],2))>0,
number(index([Base Number Dymamic List],2))-number(index([Enum List],2)),
" "
),
IF(
number(index([Base Number Dymamic List],3))-number(index([Enum List],3))>0,
number(index([Base Number Dymamic List],3))-number(index([Enum List],3)),
" "
)
)-list(" ")
)
If the permissible “MAX” count of the list is 6, then amend the calc accordingly.
In case we are not able to put a bar for max permissible count for the list, I suppose we need to employ the Steve’s “Looping Actions” technique. But by the number of the count of list foreseeable always, we can push all the possible different case within the calculation, which make it possible to run the calc dynamically.
Hope a bit of help for you.
I have tested with a few numbers as below picture, based on my understanding on this. As comparison I have also indicates of using google sheet formula:
- Expression:
TOP(
SORT([EnumList],TRUE),
IFS(
INDEX(SORT([EnumList],TRUE),6)>=[MinValue],6,
INDEX(SORT([EnumList],TRUE),5)>=[MinValue],5,
INDEX(SORT([EnumList],TRUE),4)>=[MinValue],4,
INDEX(SORT([EnumList],TRUE),3)>=[MinValue],3,
INDEX(SORT([EnumList],TRUE),2)>=[MinValue],2,
INDEX(SORT([EnumList],TRUE),1)>=[MinValue],1,
)1
)
- Google Sheet Formula
TEXTJOIN(", ", 1, FILTER((TRANSPOSE(SPLIT(A1,","))), (TRANSPOSE(SPLIT(A2, ",")))>=B2))
Thank you so much, @Heru! I’ve been a bit busy and have not yet had enough time to study your answer and the other answers carefully enough. I’ll study and experiment some more and get back to this thread.
