Get the index number out of list

Have been thinking around if any of workaround is available to get this done, but need a help from the community.

First of all, I have list type data field, does not matter it is physical or virtual column, like

{val1, val2, val3, val4, val5… } —> length of list is unknown and variable.

Then there is another text files which contain one of the value out of this list.

We have “In expression” to test if this text value is IN the list or not. What I want to achieve is to get the INDEX number if IN expression returns true to know where the text value reside within the list.

Solved Solved
0 20 2,163
1 ACCEPTED SOLUTION

You gave me a great guide.
I placed your expression as it it, to my sample app, but unfortunately it does not work. After playing around a bit, I found why it does not work, and twisted a expression a bit.
Great new is revised expression finally did a job!

This is expression.

count(
SPLIT(
Left(TEXT([text val]),
FIND( [text val] , TEXT([list]))+len([list])
),
" , "
)
)

View solution in original post

20 REPLIES 20

Hi @tsuji_koichi,

Not very extensively tested , but please try if following helps

COUNT(SPLIT(LEFT(TEXT([List Column]), FIND( " Text Value to Search in list" , TEXT([List Column]))-4), " , "))+1

Edit:
A minor typo in expression above. The value to search is “Text Value to Search in list” and not " Text Value to Search in list" , that is there is no space in the beginning.

Correct expression is below

COUNT(SPLIT(LEFT(TEXT([List Column]), FIND( “Text Value to Search in list” , TEXT([List Column]))-4), " , "))+1

Edit ends

Assumptions

  1. First check with IN() expression if the value exists in the list. The above expression assumes that the value is present in the list.

  2. I tested it on a text values list. Did not test it on numeric value list such as price or date etc.

  3. The list format is as created by AppSheet in say a reverse reference list column such as
    { Value1 , Value2 , Value3 , Value4} This means there is one space after each value followed by a comma and then again one space before next value.

Request you to test. I believe we could fine tune it further if necessary.

Thank you!, let me test it with my sample app and revert.

Now we are able to dynamically extract the index number of given list (array) regardess of the length of the list.
I do have a bunch of use cases with this expression to be honest.

Thanks again.

First use case of this syntax could be get the “serial/sequential number” dynamically. I will be working on the sample for this.

You gave me a great guide.
I placed your expression as it it, to my sample app, but unfortunately it does not work. After playing around a bit, I found why it does not work, and twisted a expression a bit.
Great new is revised expression finally did a job!

This is expression.

count(
SPLIT(
Left(TEXT([text val]),
FIND( [text val] , TEXT([list]))+len([list])
),
" , "
)
)

Brilliant @tsuji_koichi!

Actually this was years-running mystery for me to find out a way to get this jobs done with existing expression. My friend @Suvrutt_Gurjar gave a great hint once again.

I will recap converation on this thread and place a new post for “tips and tricks” .

@tsuji_koichi
@Aleksi and I was getting similar results with this expression for a long time

(
	FIND(
		"TextVal",CONCATENATE([ListColumn])
	) - 
	FIND(
		"TextVal",SUBSTITUTE(CONCATENATE([ListColumn])," , ","")
	)
)/
LEN([ListColumn])

Thank you I will examine your expression as well.

Good thing with this new expression is now we are able to deal with array - type of functions in a dynamic way , actually without doing any sort of 'for loop" .

Hi @LeventK,

That is very compact/creative expression. Thank you very much.

Hi @tsuji_koichi,

You are welcome and thank you for your good words. When AppSheet masters like you have some good words for people like us, it is always encouraging.

We keep getting so many insights/ tips from you all experts and from entire community as I believe everyone has something unique to contribute. Your Tips and Tricks posts are so out of box as another community member very rightly manetioned yesterday. Glad to be of small assistance.

Thank you .

I m not testing yet, but as I said, there shoul be bunch of use case.
For instance, top () expressoin will return the set of row as list, but we did not have a way to search the number of top “N” the exact row are ranked. We are also missing rank expression, but wihtout it, we probably can do similar.
For instance, user select number of N, dynamically, then expressoin will pick up row which are ranked top N etc.

This is just one of examples.

I remember @Kirk_Masden looking for a solutions for rank()

I think now we are able to achieve rank() type of expression.

Yes, your are correct @tsuji_koichi on RANK() sort of functionality.

I quickly tested, yes indeed, finding top “N” value out of the list is working with this expression.

I expanded usage a bit further. Changing row order by action could work as well. For instance, by the given conditions, the table view is showing the set of rows on the certain order (Order by number, category, etc whatsoever) then swap the order, for instance, a row was on the 3rd row, then push row to 2nd or 3rd etc. Quick test showed positive, so should work.

@tsuji_koichi
私は何かを習得します、そして創造性が来ます

I can summarize how Appsheet works and bring a fun to us.

const something_we_believe_impossible

appsheet(something_we_believe_impossible) {
return “Actually_Possible!”
}

thats why we wont get bored with appsheet.

This is an adorable brief @tsuji_koichi

Hi @tsuji_koichi,

Thank you and great to know that it works with some twist for your case. Also good to know you have many use cases for it.

Thank you for sharing revised expression.

Another solution…

COUNT(SPLIT(INDEX(Split(“123 , 234 , 345 , 456”," 345 “),1),” , "))+1

And the Alien Master @Aleksi has just arrived from his backyard

True , this is @Aleksi special. It is always fascinating how compact he makes every solution.

Top Labels in this Space