cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Adding an index column to a collection

I have a simple collection, Col_Table1, as follows:

 

Building Element Name:              Project Name

Superstructure                              Building Refurbishment in Michigan

Superstructure                              New Build in Kansas

Superstructure                              Extension in Arizona

 

I want to add an 'index' column. For now, I am creating a new collection to do this. The new collection is Col_Table1_Indexed. The desired table would therefore look like this:

 

Building Element Name:              Project ID                                                     Index

Superstructure                              Building Refurbishment in Michigan           1

Superstructure                              New Build in Kansas                                    2

Superstructure                              Extension in Arizona                                    3

 

 

I tried using the following formula, based on the 'last(firstN) principle. 

Clear(Col_Table1_Indexed);

ForAll(Col_Table1,
Collect(Col_Table1_Indexed, 
Last(FirstN(AddColumns(Col_Table1,"Index",CountRows(Col_Table1_Indexed)+1))))) 

 

Unfortunately this just duplicates the first collection row 3 times, so the 2nd and 3rd rows are ignored:

Building Element Name:              Project ID                                                     Index

Superstructure                              Building Refurbishment in Michigan           1

Superstructure                              Building Refurbishment in Michigan           2

Superstructure                              Building Refurbishment in Michigan           3

 

 

I am therefore trying to accomplish 2 things:

 

1) Add the subsequent rows (rows 2 and 3 in this case) without problems. 

2) Ideally, I would like to add the index column to the existing collection, Col_Table1, via an addColumns() approach. I attempted this earlier but with no success. 

 

Any ideas? Thanks. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Anonymous 

There is not a real clean way to add a simple index to a collection, but it can be done with a secondary collection fairly easily.

Consider using this formula in your app:

Clear(Col_Table1_Indexed);

ForAll(Col_Table1,
   Collect(Col_Table1_Indexed,
      Patch(Last(FirstN(Col_Table1, CountRows(Col_Table1_Indexed)+1)), 
            {Index: CountRows(Col_Table1_Indexed) + 1})
          )
   )

In this case we need to add the column with a patch statement as the AddColumns statement would be for an entire table/collection not a single item.

So, in this formula, we're adding a patched record from the original collection based on its location (determined by the size of the new collection) and then using that count as another column in the record called "Index".

That should give you what you are looking for.

 

I hope it is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
RandyHayes
Super User
Super User

@Anonymous 

There is not a real clean way to add a simple index to a collection, but it can be done with a secondary collection fairly easily.

Consider using this formula in your app:

Clear(Col_Table1_Indexed);

ForAll(Col_Table1,
   Collect(Col_Table1_Indexed,
      Patch(Last(FirstN(Col_Table1, CountRows(Col_Table1_Indexed)+1)), 
            {Index: CountRows(Col_Table1_Indexed) + 1})
          )
   )

In this case we need to add the column with a patch statement as the AddColumns statement would be for an entire table/collection not a single item.

So, in this formula, we're adding a patched record from the original collection based on its location (determined by the size of the new collection) and then using that count as another column in the record called "Index".

That should give you what you are looking for.

 

I hope it is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Anonymous
Not applicable

This is exactly what I was looking for. Many thanks. Apologies for the delay in replying due to an early weekend! 🙂

 

Yet again you solve my Collection woes.. cheers!

OscarSotoCL
Frequent Visitor

Excelent Solution, Thank You  so much!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,173)