cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cwebb365
Level 10

Adding a rowid to Collection

Is there an easy way to take a table of say 200 records and add a column with a rowid that increments up? Preferably I'd like to take 5 rows as groupid 1 and next 5 be groupid 2 and so on, but if I can at least figure out how to have each row in a collection increment I could figure a way to do the groupid. 

 

Thanks for the help, I thought this should be easier to do than my brain can wrap around right now lol.

1 ACCEPTED SOLUTION

Accepted Solutions
shailendra74
Level 8

Re: Adding a rowid to Collection

There would be performance impact when trying to generate rowid in powerapps. Instead have

 

  • ID column (as rowid) if data is from sharepoint 
  • generate row_num (as rowid) if data is from SQL Server

 

if you still want to generate rowid in powerapps:

 

Clear(collRowidItems); //new collection which would have rowid
ForAll(collItems,  // existing collection with 200 records or so
       Collect(collRowidItems,
               Last(FirstN(AddColumns(collItems,
                                "RowID",
                                CountRows(collRowidItems)+1
                           ), 
                           CountRows(collRowidItems)+1
                    )
               )    
       )
) 

 

Thanks.

5 REPLIES 5
shailendra74
Level 8

Re: Adding a rowid to Collection

There would be performance impact when trying to generate rowid in powerapps. Instead have

 

  • ID column (as rowid) if data is from sharepoint 
  • generate row_num (as rowid) if data is from SQL Server

 

if you still want to generate rowid in powerapps:

 

Clear(collRowidItems); //new collection which would have rowid
ForAll(collItems,  // existing collection with 200 records or so
       Collect(collRowidItems,
               Last(FirstN(AddColumns(collItems,
                                "RowID",
                                CountRows(collRowidItems)+1
                           ), 
                           CountRows(collRowidItems)+1
                    )
               )    
       )
) 

 

Thanks.

cwebb365
Level 10

Re: Adding a rowid to Collection

Yeah, this is pulling from Office365Users not sql or SP. But this works beautifully, exactly what I needed. I was close, just didn't have the collection lined up right. Now I just need to figure out how to split them into groups of 5 Smiley Happy. Mod function or something like that. Anyway, thanks again!

shailendra74
Level 8

Re: Adding a rowid to Collection

Thats simple.

 

 

 

Clear(collRowidItems);
//new collection which would have rowid
ForAll(
    collItems,// existing collection with 200 records or so
    Collect(collRowidItems,
        Last(
            FirstN(
                AddColumns(
                    collItems,
                    "RowID",
                    If(
                        Mod(
                            CountRows(collRowidItems) + 1,
                            5
                        ) = 0,
                        5,
                        Mod(
                            CountRows(collRowidItems) + 1,
                            5
                        )
                    )
                ),
                CountRows(collRowidItems) + 1
            )
        )
    )
)

 

since you need Mod 5 - all marked in red need to be same number

 

 

Thanks.

cwebb365
Level 10

Re: Adding a rowid to Collection

well that works for how I said it, but I said it wrong. Basically what I'm trying to do is make a gallery that's 5 items horizontal wide nested in a vertical gallery. So trying to somehow get groups of 5's. So basically in the horizontal gallery I'm going to reference the verticals id so it shows 5 items, then next 5 and so on. 

 

So Vertical gallery ID=1 which the horizontal references where it's Thisitem.ID = RowGroupID, so you get 5 results. 

 

Then next row on vertical gallery is ID=2, then horizontal is ThisItem.ID = RowGroupID again which match 5 with the Rowgroupid of 2 and so on.

 

I'm trying to get my collection to be 

RowGroupID

1

1

1

1

1

2

2

2

2

2

3

3

3

3

3

 

and so on, so every item in the collection counts 5 rows, then increments.  

 

Hopefully I said that right, but I appriciate if you have any idea's. If I nail this, it's going to be some nifty stuff I plan to blog about the final product if I get it working. Thanks for all you've done so far!

cwebb365
Level 10

Re: Adding a rowid to Collection

Wooooo!!!

 

My math logic skills are not as bad as I thought lol...Figured it out Smiley Happy

 

Used RoundDown around the rowid and divided by 5. This basically always rounds down so you will get the same number for all 5 id's then go up one. This is perfect. Thanks for getting me to this point. 

 

ForAll(
ColEmployees,
Collect(
Employees,
Last(
FirstN(
AddColumns(
ColEmployees,
"RowID",
RoundDown(CountRows(Employees)/5,0)
),CountRows(Employees)+1
)
)
)
)

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 242 members 5,262 guests
Please welcome our newest community members: