INDEXOF() With Expression

There was a recent question post related to finding the position of an element in a list and also a useful feature request for the INDEXOF() function that I upvoted. Till such a function is available, have tried to come up with the following expression.

I have tested the expression on an enumlist by entering element to search for in a text as well as text type enum column in the same record. I believe it can be further tweaked / optimized as necessary based on individual use case. Hope it helps.

IFS(
OR(FIND([SearchText], [Enumlist])=0, ISBLANK([SearchText])), 0,
FIND([SearchText], [Enumlist])=1, 1,
FIND([SearchText], [Enumlist])>1,
COUNT(SPLIT(INDEX(SPLIT(TEXT([Enumlist]), [SearchText]),1), " , ")))

In the expression above, [SearchText] is text type column and [Enumlist] is an enumlist type column. If the element to search is missing in the list, it returns 0 .

In the picture below [INDEXOF (Enum)] column is computed with input from [Enum] column by searching the position in the [Enumlist] column.

[INDEXOF (Fruit)] column is computed with input from [Fruit] column by searching the position in the [Enumlist] column.

8 Likes

Very cool, @Suvrutt_Gurjar! This workaround should work for most cases until a true INDEXOF() expression is available.

A few things to be cautious of though:

  • What happens when the value appears in the list more than once or contains one of the other words? (Apple, Applesauce, Banana, Banana Pudding, etc.)
  • What if one of the Enum values contains a comma?
1 Like

Hi @GreenFlux,

Thank you. I agree with your mentioned use cases in real life. I had also seen your useful suggestion in the feature request post that the function should also have a start element option.

Thought of creating a simple one for now. I believe we could include more options and validations in the expression but it could become bulky. At the moment, it obviously does not handle those specific use cases but I believe it could be still useful in may be 50 % simple use cases at least.

I will try to post revisions if I come up with fairly compact expressions to handle those variations as well.

1 Like
3 Likes

You have a challenges in case of we have part-match in terms of strings? rather than full match?

I do have an idea how to get the index number basis full-match if this is what you are looking for.

See the gif shot.

1F9C5025-7B32-42AE-9187-CC1F0F29AB7A

In this sample, all the enumlist (or list) items have string of “Apple”.
Once we select the index position of “Apple” it could always return 1, but with my workaround, it will return the 4 as it will look up the first index number where the selected strings fully match with list item.

2 Likes

Excellent, useful improvement @tsuji_koichi.

In general, I am sure there will be many improvements possible , as this is expression based and with experts like you , they could take the concept to a new high level. Even the expression I posted could possibly be further optimized.

My main idea was to just kick in possibility that this could be done.

As @GreenFlux had mentioned, one more improvement could be allowing the user to mention the start element.

1 Like

Yeah, possibility would expand. I simply need INDEXOF() expression which makes our life much more easier, but until then we can twist the existing set of expression to achieve the same.
Indexof type of tricks could be used for another purpose, like RANK() expression as well.
Thank you for your contribution.

1 Like

Yes , I agree with you. I think the very meaning of “Tips and Tricks” section is to employ some innovative ideas as workarounds and usefully add to the capabilities of any platform with those ideas.

Community members like you, @Steve, @GreenFlux , @Fabian , @Jonathon, @Kirk_Masden, @Marc_Dillon , many other community members and AppSheet team experts @Peter, @TyAlevizos, @Rich and others have enriched this category with amazing tips and tricks.

I like to daily visit this section first when I log in to the community portal. :slight_smile:

8 Likes

This is amazing. Thank you :slight_smile:

@tsuji_koichi Would you please share your solution as it might help other readers.

Sure @Aleksi you may find alternative or better way, but this is what I do.

Not horiffically complicated, but it is a bit complex actually.

Preparation and Assumption

[EnumList] — User pick up multiple or sole item out of the list. Usual Enum list type field.

[Enum] — Enum type. This fields will generate the dropdown list of of [EnumList], and user select single value. We will find the index number of the selected item out of the [Enumlist] as above.

Sample for instance

EnumList hold {Apple Juice, Apple Cake, Apple, Apple Computer}
Enum ; I select “Apple”

(1) Add virtual column with name something like [add @ to enumlist]

Push and expression like this

“@”&Substitute(text([EnumList])," , “,”@,@")&"@"

This expression will convert the Enumlist filed onto text type first, then add @ to both beginning and end of “EACH” enum items. This expression should yeild something like

@Apple Juice@, @Apple Cake@, @Apple@, @Apple Computer@

  • It is not bold, but this post convert to bold…

As you can see, now each enum list item is “Wrapped” by @ mark

(2) Add another virtual column [Add @ to enum]

Expression is

Similarly, we wrap enum value (selected value) by @

@&[Enum]&@

(3) Add another VC with Index Number

Expression required here is

COUNT(
SPLIT(
LEFT([add @ to Enumlist],
FIND( [add @ to Enum] , [add @ Enumlist])+LEN([add @ to Enum])-1
),
“,”
)
)

This expresion will give us the index number of selected item basis “full match” the value.
If enumlist item contain the string as apart, it will not take account, but just see and find the index number where the selected enum value full match with the item value.

In case we have duplicate in the enum item, then it should return the first and earliest index number counting from 1.

Trick behind here is to convert the enumlist item as well as selected enum item by adding arbitrary value. In this demo case, I used the string / text of @, but we are able to involve any arbtrary text.

I select “Apple” on enum selection which will be returned as

@Apple@

then we will find the index number which perfectly match from

@Apple Juice@, @Apple Cake@, @Apple@, @Apple Computer@

Then return the index number.

First valid test ; @Apple Juice@,

Does not match with @Apple@

Go next and next.

This case, index number expression will return 3, as it is fully match.

5 Likes

Thanks for sharing it for other readers.

1 Like

I checked what I have used in a production app and it was made in one column directly… but idea seems to be quite the same than with @tsuji_koichi

IFS(ISNOTBLANK([ENUM]),
COUNT(
SPLIT(
LEFT(“X , “&[EnumList]&” , X”,
FIND(", “&[Enum]&” ,",“X , “&[EnumList]&” , X”)-2),
" , ")
)
)

4 Likes