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.

12 14 2,137
14 REPLIES 14

GreenFlux
Participant V

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?

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.

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.

3X_4_4_44f826d28291d3b061674791573ae5967408da7e.gif

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.

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.

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.

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.

Jonathan_S
Participant V

This is amazing. Thank you

@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.

Thanks for sharing it for other readers.

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),
" , ")
)
)

MultiTech
Participant V

Sorry @Suvrutt_Gurjar, looks like I just made a duplicate post about how to solve this:

Oh well , maybe with two posts about it people will be able to find the answer and stop asking the question!

Hi Matt,

No problem. You have excellently described the solution in your customary style. I am sure the community has become richer by one more option.

It is always interesting, how one problem can be solved in many ways. That is the beauty of logic and versatile platforms like AppSheet.

Top Labels in this Space