Expression or trick to get the index number

[Koichi_Tsuji]

Appsheet expression of INDEX(list, number) will return the ‘value’ of the designated index number out of the arrray/list.

I’m now exploring a way to get the index number out of the expression.
For instance, we construct the expression to generate list by using select function and so on. In a sense, the list of those rows are kind of the array. I wish to have the expression to get index number for each rows generated dynamically by list expression.

Anyone does know if this is possible or we need to place a feature request?

Thank you for your attention.

[Kirk_Masden]

I’m not sure if I understand the question correctly.

Let’s imaging we have the following list in the “My list” column:

apples, pears, plums

The following expression would give us “pears”

INDEX([My list],2)

Now, are you asking how to find out what number (1st, 2nd, or 3rd) “pears” is in “My list”? If so, I think that should be possible. You could divide the list into two lists – the list that comes before “pears” and the list that includes “pears” and all that follows. Then the count of the first list +1 would be the location of “pears.” Of course, you’d also have deal with situations where “pears” cannot be found.

[Koichi_Tsuji]

Hello pal, Konbanwa,

Thank you for asking. Can we go with Japanese ? Or you are not on the feel to do so now, and just watching Rugby games? haha

Okey, let me explain to bit deeper extent what I m trying to achieve.

Let s assume we have simple table called ‘User’ like this.

---

ID Name Gender

ABC YAMADA F
BCD TANAKA M
CDE SATO M
DEF SUZUKI F
EFG ADACHI M

Then I use expression to generate list using select expression like this.

Select(User[ID], [Gender]=“M”)

This will generate the list, actually sliced table like this.

---

ID Name Gender

BCD TANAKA M
CDE SATO M
EFG ADACHI M

On this case, I m not “Ordering” the sequence of the row, which comes first / top, by using order expression against any of expression. (Should be ordering the rows by [_RowNumber] and asc.

Above sliced table is just based on the appsheet default how to order the set of rows.

Anyway, index of TANAKA = 1, SATO = 2, ADACHI = 3 in this case.

If I use Order expression along with select expression, for instance, order by Name by asc, then sliced table output looks like this.

ID Name Gender

EFG ADACHI M
CDE SATO M
BCD TANAKA M

Index number for each person now turns to be
ADACHI = 1
SATO = 2
TANAKA = 3

The index number of each rows should alter dynamically based on the expression all the time.

I want to detect such ‘index number’ by using some expression if available.

for instance, i add virtual column and put this sliced table then return : -

---

ID Name Gender VC

---

EFG ADACHI M 1
CDE SATO M 2
BCD TANAKA M 3

This is little complicated story, as the sliced table is dynamic all the time, and value of VC is always changed based on the expression.

When we make a ref type column, it generate the VC list type of column to the parent table. This is kind of similar to sliced table. Let s say we have inline table view to a particular row on the parent table. And assume it have 10 rows. This set of child rows are ordered by number of the row unless we specifically instruct where and which column to be ordered.
But with or without order expression, the set of rows always have sequence starting from 1 all the time.

All in all, i wish to capture those number at the end.

Then we can use such number to slice the table further, for instance, just retrieve the rows where the index_number = odd or index_number = even .

Make sense and clear?

Might be better to throw away my poor English and then explain again in Japanese ? haha.

Once again thanks for asking.

[Kirk_Masden]

Thanks for your replies. What you are trying to do reminds me of something I was trying to do earlier. Here’s a link to a long thread that may be relevant:

An expression for "next record" in a shuffled slice Questions

I’ve made a flashcard app where learners work through a set of flashcards that have been shuffled randomly. When the learner taps an action indicating that he or she has successfully answered the question, the action removes that card from the slice and then automatically takes the reader to the next card (according to the order of randomized numbers assigned to the cards that remain). My app is working because I’ve figured out how to find the “key” of the card that should be next through the …

If what you are trying to do is quite different from this I might not be able to help.

By the way, I think your English is fine. Whether one is a native speaker or not, I think that writing about the kinds of topics we talk about on this forum and also understanding what other people have written can be quite difficult. I’m often amazed at the ability of users to read a description of a problem and then come up with a solution while I’m still scratching my head.

[Bellave_Jayaram]

Not a clever way but if your list is not very long, how about this:

Your list, Names is (ADACHI, SATO, TANAKA), you can create two lists EvenList and OddList like so:
LIST(INDEX([Names], 2), INDEX([Names], 4), INDEX([Names], 6))
LIST(INDEX([Names], 1), INDEX([Names], 3), INDEX([Names], 5))

I believe this would work if Names was a list of lists as well.

[Koichi_Tsuji]

Yes, this is nice solution when we can expect the number of the list. Thank you.

Otherwise, I will keep exploring solutions to find way.

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

I believe what you are looking at is possible, if I have understood your requirement well.

I tested it with a small test appthat I built. Here are some screenshots

Image 1: The original list of employees.Please note that this one is ordered by employee id. The names of the employees are in random order as far as first letter of their name is concerned.

Image 2: The list in descending order by names. Please take a look at the column called Serial Number. This column has serial numbers matching the descending sort order of names.

Image 3: The list in ascending order by names. Please take a look at the column called Serial Number. This column has serial numbers matching the ascending sort order of names. Thus the serial numbers have changed to match the new ascending sort order.

The test app is available at

Orderby Serial Numbers

I will be glad to update/ discuss if you have any queries while browsing/copying the app.

[Koichi_Tsuji]

Hi @Suvrutt_Gurjar,

Thank you for your valuable advice indeed!
I really wish to see what sort of tricks behind. The point is how you did create the SerialNumber column, while I m assuming this could virtual column with App formula.
Or is this physical column?

Koichi

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

Yes, the serial number is VC. I would urge you to take a look at the app. Iwouldhave liked to describe the implementation here itself. It is a bit tricky.

1. ORDERBY column [SortEmployees] in the parent table MasterTable is carried back to the child table EmployeeList , through dereference.

2)Using this carried back dereferenced column [SortEmployees], the Serial Number VC in the EmployeeList table with an expression COUNT(SPLIT(LEFT([SortEmployees],FIND([Name],[SortEmployees])),",")) is created.

A) This expression extracts the list till the employee name in current column using LEFT([SortEmployees],FIND([Name],[SortEmployees])) and

B) then reconverts the extracted names back to list type column using SPLIT() command,
with SPLIT(LEFT([SortEmployees],FIND([Name],[SortEmployees])),",")

C) Finally this list is counted by COUNT() till current name with COUNT(SPLIT(LEFT([SortEmployees],FIND([Name],[SortEmployees])),","))

3. Now this VC [SerialNumber] in child table gets carried back to parent table master table with reverse reference columns [Related EmployeeLists] and [SortEmployees] and now the [SerialNumber] column ia available in parent table for display.

Hope this helps.

[Koichi_Tsuji]

You are a star and how smart you are!
I will implement this to my apps. Apart from the use cases I described in the previous post, there will be number of the useful use case!
I will keep working on and will get back once I encounter any problem to call for your further help.
Thank you once again. Arigato!

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

You are welcome.Please feelfree to reach out anytime. I will be glad to be of any assistance. In this community , we all keep getting help from each other.

[Koichi_Tsuji]

I just copied your app and looking into the details. Tricky and hacky, but smart!

Every single day, Appsheet and this community give a new lesson and learn. We never get bored!
Thank you again.

[Koichi_Tsuji]

Nothing to do serialnumber virtual column, but i noticed one odd.

This could be a bug potentially? Have a look. Inline view, counter for the row is indicating nil.

Just give you head up.

[Suvrutt_Gurjar]

Yes, I realized that. I suspect it is because of that bit of extra rotation of columns between two tables.

However on tapping on View() does take to extended list.

[Koichi_Tsuji]

Gotcha.

I played around your app, and tested if SerialNumber VC would generate the sequential number for sliced table, but actually it was not.
For instance, I made a slice based on the strings for EmployeeLocation column to generate table dynamically. When I opened this sliced table, the sequetial number is inherited from master table of Employees_List.

So I worked around a bit and find a solution to get the serial number for sliced table, using a bit of trick.

See the updated sample app here.

AppSheet

AppSheet

Create mobile apps for your business in just minutes. No code, no delay, no cost.

For user setting, please select the location. Then have a look at the new view called SerialNumberforSlicedTable

I added another two virtual columns to get the serial number for sliced table and it worked perfect!

[Suvrutt_Gurjar]

Hi @tsuji_koichi,

Thank you. I am able to browse the app shared by you. Somehow, I am unable to open that app in the editor. Just in case you have not, could you please share the app for copying?

[Koichi_Tsuji]

May I have your email or send blank email to koichi.tsuji@vendolasolutions.com

[Koichi_Tsuji]

Hi there,

My intention is not making you more complicated nor puzzled.

Actually the topic we are talking here is related to my other post on the community I posted days ago.

Excel Worksheet Templates for Email Attachments and SaveFile Announcements

Hey Jeremy, thank your feedback and sharing your experience, knowledge as well. Yes, I also prefer Googlesheet rather than Excel, as it is more flexible in terms of available services and extensions. In the meantime, can you elaborate a bit more how you construct the syntax for == I use multiple versions of the same template to make it fit the exported data. just use the IF THIS IS TRUE… condition in the workflow to choose the right one by counting image number. == I wish to fully unders…

Now we have capability (Thanks Phil!) to generate the report out of Excel template. Where we are able to generate the multiple column type of table as I mentioned in other post.

Let’s say we have one table which contain image file.

When I make a report out of this image table, currently what we can do is to make a single column table where each independent row contains single image (image(s) per row) based on the table column structure.

The report table looks like this

---

Image Comment
[Image] xyzxyzxyz
[Image] xyzxyzxyz
[Image] xyzxyzxyz
[Image] xyzxyzxyz
[Image] xyzxyzxyz
.
.
.

But thanks to the introduction of excel based template, it might be possible to generate the two column type of table, which looks like this.

---

Image Comment Image Comment
[Image] xyzxyzxyz [Image] xyzxyzxyz
[Image] xyzxyzxyz [Image] xyzxyzxyz
[Image] xyzxyzxyz [Image] xyzxyzxyz
[Image] xyzxyzxyz [Image] xyzxyzxyz
[Image] xyzxyzxyz [Image] xyzxyzxyz
[Image] xyzxyzxyz [Image] xyzxyzxyz

again each table is using the same table (or slice)

To generate first column, we potentially use expression to slice the data out where [Index_number] = ODD

In the same way, to generate the second column, you now know the answer, to slice the table by
[index_number] = EVEN.

Simple math.

Hope you are now with me.

Take care.