This is great stuff, Randy! The best tips are the ones you don't come looking for, but can't do without once you've seen them. This is exactly that. Thanks!
This so cool Randy ...thanks for sharing 1. Gallery1, set the Item properties of data source 1:44 2. We need sequential number 2:48 3. In Item properties use With statement 3:04 4. Use ForAll Sequence with countrows function 3:57 5. Use Last function with FirstN records 4:29 6. Use Patch with Last FirstN function 7:07 7. Use a new label that we created rowNumber 7:32 8. Use SortByColumns by Title column within Items property 7:56 9. Use of TemplateFill 8:21 10. Copy the label, use Mod(ThisItem.rowNumber ,2) 8:55 11. Use TemplateFull with If Mod(ThisItem.rowNumber ,2) 9:31 12. Gallery’s Item properties with Filter function 10:13 13. Additional conditional with TemplateFill If Statement 11:10 14. To use Edit Form use Item’s property to Lookup 12:52
Great Randy, I have been doing it the long way. You should get some links to this from the forum from me. Finally conquered how to use Sequence in this context - this has uses beyond colouring alternate rows.
Thanks for the great feedback Warren. Yes, Sequence and little tricks like this are all through my apps...saves a tremendous amount of time over collections and variables too.
Really helpable to me With({records:Filter('datasource',DateDiff(Date,Today()-28))},ForAll(Sequence(CountRows(records)),Patch(Last(FirstN(records,Value)),{rownumber:Value})))
Hi Randy, I've been struggeling all afternoon trying to renumber a collection if I remove random items from it. Your video isn't exactly about this but you led me in the perfect direction. SUbscribed to your channel! Thanks!
Thank you so much for this tutorial, been looking for this for ages. I do find however this makes my gallery very slow when working with a large data set from SharePoint.
Hi sir Thank you for this idea. you saved my day. very useful content. I just have a doubt in while removing the particular item in the gallery. I have used Remove(Datasourse name,thisitem) and throws a delegation warning. how to resolve that sir?
Great tip! It is working perfectly in my gallery which has both Sort and Filtering functions, but I now have a small problem in my Form which I use to enter, edit or delete an item. When I select an item to edit or delete, the edit form is not populating itself anymore with the data since I am now getting an error related to Item = "GalleryName".Selected. Up till now I haven't found a way how to resolve it. Would appreciate any ideas on how to resolve it, if possible! Thanks
So the thing is, this process will add a column called rowNumber to the gallery. This is needed for the template fill to work. If you reference the Gallery.Selected item, it will have that rowNumber column in it. If you try to use that for a Form, it will not match the schema of the record it expects. The way around it is to drop the column. There are a number of ways to go about it, but one quick way you can do is to change your Item property on your form to this: First(DropColumns(Table(yourGallery.Selected), "rowNumber")) This will drop the column. DropColumns only works on Tables, so we have to convert the record to a Table, then drop the column, then take the first record of the table (there will only be one) and convert from a table to a record.
@@randyhayes891 Just tried it and works like a charm! Don't think I would have managed to solve it myself. Thanks so much for the solution and the quick reply.
Thanks for the information in the video. Really appreciate your efforts here. This worked out really well when applied to a SharePoint List but I cannot get it to work properly on a PowerApps collection. Any ideas?
Hi Randy! Great video, very helpful. I'm trying to patch a table on a button click and of course there's a problem because of the rowNumber. Can you provide the syntax for this? I'm not sure where to place the First function. My code has a ForAll(...,Patch(...)); Everywhere I place is an error. Thanks -
Great tut! Can you please help with incorporate multiple column filter (First name, last name ...etc) with a searchbox (TextInput)? Thank you With( { GalleryRecords: Filter(My List, SearchBox.Text,'First Name') }, ForAll( Sequence(CountRows(GalleryRecords)), Patch( Last( FirstN( GalleryRecords, Value ) ), {RowNumbers: Value} ) ) )
Sure thing, you just need to look at your Filter function. It needs a DataSource and criteria. So, in your case, you can utilize the startswith or search functions. For startswith, your Filter would be : Filter('My List', StartsWith('First Name', SearchBox.Text)) And to change to Search (non-delegable) : Search('My List', SearchBox.Text, 'First Name')
Hi, Randy. Nice video and it helped me sort the problem with our leaderboard. Now the only thing I really want to do is how to access that rownumber outside of the leaderboard gallery? For example, I have a label outside of the leaderboard gallery saying, "You are currently, 10th out of 50 with the most points!" I don't know how to access this and if I filter, my rownumber as expected counts to one so i can't even do another gallery to try and just patch it up. I hope you can help me, thank you!
Randy, would you mind adding how to add the Last number (I'm guessing LastN somewhere) so the gallery will display 1 of 10, 2 of 10, etc. That would be really helpful. Thanks so much!
This is a very good tutorial Randy. Good explanation of what is happening. However, I am unable to get the Fill to work. I am able to add a label with rowNumber just fine, but when i type ThisItem.rowNumber, intellisense isn't picking it up (not sure if this is the problem of the formula though, but the if statement is erroring with some invalid arguments If(Mod(thisitem.rownumber,2)=0, Color.LightGreen, Color.LightGreen) is my formula
Ignore me. I was putting the formula into Fill property not TemplateFill. D'oh. I'm not sure what each one of those does but never mind, its working now
Hi Randy, This was really useful. Thank you very much. I have one issue though. The Patch function isn't working, once I have changed the details in Items. ForAll(Filter(GalleryThreholdPeri.AllItems, ToggleThreshold_Peri.Value = true), Patch('THRESHOLD for PERIPHERAL', ThisRecord , { LOCATION:TextInput_Location_Peri.Text , 'THRESHOLD LOW':TextInput_Low_Peri.Text , 'THRESHOLD CRITICAL':TextInput_Critical_Peri.Text, MODEL:TextInput_ProductThreshold.Text})) Can advise how can I change the Patch command to make this work?
This is great stuff, Randy! The best tips are the ones you don't come looking for, but can't do without once you've seen them. This is exactly that. Thanks!
Awesome video!! 👏👏
Quick and straight forward!
Thanks for sharing!
Wonderful article, Randy. It is very useful thanks for posting this nice video. Keep it up.
Great video Randy! I’ve been wondering how to do this. I look forward to future videos. Happy New Year!
Good stuff! Thanks for this
Randy! Thanks so much. This even, totally unexpectedly, answered my question on the schema issue I keep bumping into elsewhere!
This so cool Randy ...thanks for sharing
1. Gallery1, set the Item properties of data source 1:44
2. We need sequential number 2:48
3. In Item properties use With statement 3:04
4. Use ForAll Sequence with countrows function 3:57
5. Use Last function with FirstN records 4:29
6. Use Patch with Last FirstN function 7:07
7. Use a new label that we created rowNumber 7:32
8. Use SortByColumns by Title column within Items property 7:56
9. Use of TemplateFill 8:21
10. Copy the label, use Mod(ThisItem.rowNumber ,2) 8:55
11. Use TemplateFull with If Mod(ThisItem.rowNumber ,2) 9:31
12. Gallery’s Item properties with Filter function 10:13
13. Additional conditional with TemplateFill If Statement 11:10
14. To use Edit Form use Item’s property to Lookup 12:52
And again...a big Awesome to you for providing such great pointers from your notes!
The best solution for this problem by far!!
Great video and great explanation, couldn't find it better explained and executed elsewhere!
Great Randy,
I have been doing it the long way. You should get some links to this from the forum from me. Finally conquered how to use Sequence in this context - this has uses beyond colouring alternate rows.
Thanks for the great feedback Warren. Yes, Sequence and little tricks like this are all through my apps...saves a tremendous amount of time over collections and variables too.
Very neat thanks for the tip. I’m sure I’ll need this in future 👍🏻
Literally used this the very next day for one of my projects at work 😄😄 thanks
Too cool 😎. I never thought of this approach
Hey, Great Video! Thank's a bunch!
Extremely helpful! Thank you.
Really helpable to me With({records:Filter('datasource',DateDiff(Date,Today()-28))},ForAll(Sequence(CountRows(records)),Patch(Last(FirstN(records,Value)),{rownumber:Value})))
Thank you sooo mcuh, it works perfectlly
This is fantastic!
Hi Randy, I've been struggeling all afternoon trying to renumber a collection if I remove random items from it. Your video isn't exactly about this but you led me in the perfect direction. SUbscribed to your channel! Thanks!
Thank you very much! Very helpful
Very good video
If I wanted to have the other column a certain like DarkBlue how would we do that. How do you combine 2 IF statements for the rowNumber colors
Thank you so much for this tutorial, been looking for this for ages.
I do find however this makes my gallery very slow when working with a large data set from SharePoint.
I can't get this to work without delegation warning (not showing me a warning, but I don't get all the rows from sharepoint) Any Idea? thanks
great video
Does this work with SharePoint delegation with a large dataset?
Hi sir Thank you for this idea. you saved my day. very useful content. I just have a doubt in while removing the particular item in the gallery. I have used Remove(Datasourse name,thisitem) and throws a delegation warning. how to resolve that sir?
Hi Randy, why remove ítem ?
Thanks! You saved me :)
What if we have 5000 items in the data source?
Great tip! It is working perfectly in my gallery which has both Sort and Filtering functions, but I now have a small problem in my Form which I use to enter, edit or delete an item. When I select an item to edit or delete, the edit form is not populating itself anymore with the data since I am now getting an error related to Item = "GalleryName".Selected. Up till now I haven't found a way how to resolve it. Would appreciate any ideas on how to resolve it, if possible! Thanks
So the thing is, this process will add a column called rowNumber to the gallery. This is needed for the template fill to work. If you reference the Gallery.Selected item, it will have that rowNumber column in it. If you try to use that for a Form, it will not match the schema of the record it expects.
The way around it is to drop the column. There are a number of ways to go about it, but one quick way you can do is to change your Item property on your form to this:
First(DropColumns(Table(yourGallery.Selected), "rowNumber"))
This will drop the column. DropColumns only works on Tables, so we have to convert the record to a Table, then drop the column, then take the first record of the table (there will only be one) and convert from a table to a record.
@@randyhayes891 Just tried it and works like a charm! Don't think I would have managed to solve it myself. Thanks so much for the solution and the quick reply.
Thanks for the information in the video. Really appreciate your efforts here. This worked out really well when applied to a SharePoint List but I cannot get it to work properly on a PowerApps collection. Any ideas?
Hi Randy! Great video, very helpful. I'm trying to patch a table on a button click and of course there's a problem because of the rowNumber. Can you provide the syntax for this? I'm not sure where to place the First function. My code has a ForAll(...,Patch(...)); Everywhere I place is an error. Thanks -
Hmmmm; question answered... I needed to watch the entire video. ;)
Great.
Great tut! Can you please help with incorporate multiple column filter (First name, last name ...etc) with a searchbox (TextInput)? Thank you
With(
{
GalleryRecords: Filter(My List, SearchBox.Text,'First Name')
},
ForAll(
Sequence(CountRows(GalleryRecords)),
Patch(
Last(
FirstN(
GalleryRecords,
Value
)
),
{RowNumbers: Value}
)
)
)
Sure thing, you just need to look at your Filter function. It needs a DataSource and criteria. So, in your case, you can utilize the startswith or search functions.
For startswith, your Filter would be : Filter('My List', StartsWith('First Name', SearchBox.Text))
And to change to Search (non-delegable) : Search('My List', SearchBox.Text, 'First Name')
Hi, Randy. Nice video and it helped me sort the problem with our leaderboard. Now the only thing I really want to do is how to access that rownumber outside of the leaderboard gallery? For example, I have a label outside of the leaderboard gallery saying, "You are currently, 10th out of 50 with the most points!" I don't know how to access this and if I filter, my rownumber as expected counts to one so i can't even do another gallery to try and just patch it up. I hope you can help me, thank you!
I've solved it :) This is the code I used: LookUp(gal_leaderBoard.AllItems, NickName = lbl_nickName_1.Text).rowNumber
Randy, would you mind adding how to add the Last number (I'm guessing LastN somewhere) so the gallery will display 1 of 10, 2 of 10, etc. That would be really helpful. Thanks so much!
XLENT!
This is a very good tutorial Randy. Good explanation of what is happening. However, I am unable to get the Fill to work. I am able to add a label with rowNumber just fine, but when i type ThisItem.rowNumber, intellisense isn't picking it up (not sure if this is the problem of the formula though, but the if statement is erroring with some invalid arguments
If(Mod(thisitem.rownumber,2)=0, Color.LightGreen, Color.LightGreen) is my formula
Ignore me. I was putting the formula into Fill property not TemplateFill. D'oh. I'm not sure what each one of those does but never mind, its working now
Hi Randy, This was really useful. Thank you very much. I have one issue though.
The Patch function isn't working, once I have changed the details in Items.
ForAll(Filter(GalleryThreholdPeri.AllItems, ToggleThreshold_Peri.Value = true), Patch('THRESHOLD for PERIPHERAL', ThisRecord , { LOCATION:TextInput_Location_Peri.Text , 'THRESHOLD LOW':TextInput_Low_Peri.Text , 'THRESHOLD CRITICAL':TextInput_Critical_Peri.Text, MODEL:TextInput_ProductThreshold.Text}))
Can advise how can I change the Patch command to make this work?