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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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

7 REPLIES 7
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
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!

KJHawk
New Member

I think there is a cleaner way to create a primary key in a collection that doesn't involve creating a new collection and doesn't rely on a SharePoint list.  Say there is a collection named colTable and it already exists, and there is a column in that collection called RowNumber that you want to use as a primary key.  You can use ForAll, Sequence, and Patch to place a primary key into the RowNumber (or fix the numbering in RowNumber to get a contiguous list of integer values if you have removed some of the rows in the collection and your RowNumber values now have gaps in them) by using the following formula:

 

ForAll(Sequence(CountRows(colTable)),Patch(colTable,Last(FirstN(colTable,Value)),{RowNumber:Value}))

 

Value is the output of the Sequence function and provides a set of number from 1 to number of rows in the collection.

Great solution! I can expand it a bit. If the collection doesn't exist (say you're pulling data from SharePoint and want the server to do the sorting) you can use the WITH function to hold your items temporarily for you.

With(
    {
        items: SortByColumns(
            Filter(
                lstMySharepointList,
                Status.Value = "Created"
            ),
            "OriginalShipDate",
            Ascending
        )
    },
    ForAll(
        Sequence(CountRows(items)),
        Patch(
            Last(
                FirstN(
                    items,
                    Value
                )
            ),
            {RowNumber: Value}
        )
    )
)
Woong
Regular Visitor

Now we have index function so Last(FirstN()) can be simplified.

 

ForAll(
    Sequence(CountRows(collection)),
    Patch(
        collection,
        Index(collection, Value),
        {Index: Value}
    )
)

 

But as mentioned here, the column needs be declaired previously

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,321)