PowerApps: Pagination Component

Hello Friends,

Welcome back with another post on PowerApps. We had discussed about pagination in some of our earlier posts. Today, we will try to create a component, which will provide the pagination feature and can be used with any gallery / data table. This component will provide you the features-

1. To show current 10 pages (1,2...10 or 11,12...20).
2. To show Prev / Next button with show / hide feature based upon the page you are currently visiting.
3. To show the total number of items.
4. To select the Page Size (number of items you want to show on one page). (Homework For You) 😊

Let's start-

1. Login to PowerApps Maker Portal and create a new blank canvas app and give a suitable name.
1. 
2. Click on Components and then click on "+ New component" to add a component. Set properties of component screen as-
1. Name: SJPagination
2. Width: 1000
3. Height: 50
4. Fill: RGBA(0, 0, 0, 1)
5. 
3. Add below custom properties-
1. Total Items
1. Display Name: Total Items
2. Name: TotalItems
3. Property Type: Input
4. Data Type: Number
2. Page Size
1. Display Name: Page Size
2. Name: PageSize
3. Property Type: Input
4. Data Type: Number
3. Page Number
1. Display Name: Page Number
2. Name: PageNumber
3. Property Type: Output
4. Data Type: Number
4. 
4. Click on Component name (SJPagination from left tree view and set the default values for above input properties-
1. TotalItems: 100
2. PageSize: 10
5. Add a Text label to show number of items.
1. Name: lblTotalItems
2. Text: Concatenate(Text(SJPagination.TotalItems)," items")
3. Align: Align.Center
4. LineHeight: 1
5. X: SJPagination.Width-Self.Width
6. Y: 0
7. Width: 120
8. Height: SJPagination.Height
9. PaddingTop: 0
10. PaddingBottom: Self.PaddingTop
11. PaddingLeft: Self.PaddingTop
12. PaddingRight: Self.PaddingTop
13. Color: RGBA(0, 0, 0, 1)
14. Fill: RGBA(255, 255, 0, 1)
15. 
6. Add Blank horizontal gallery and set properties as below-
1. Name: gal_BHG_Pagination
2. X: (SJPagination.Width-Self.Width)/2
3. Y: 0
4. Width: lblTotalItems.X/2
5. Height: SJPagination.Height
6. BorderColor: SJPagination.Fill
7. TemplateSize: Self.Width/10
8. TemplatePadding: 0
9. Items: Sequence(10,1,1) //Will Update Later
10. 
11. 
7. Add a Button and set it's properties as below-
1. Name: btnPrevious
2. Text: "< prev"
3. X: gal_BHG_Pagination.X-Self.Width
4. Y: 0
5. Width: 80
6. Height: SJPagination.Height
7. Fill: RGBA(0, 0, 0, 0)
8. BorderThickness: 0
9. RadiusTopLeft: 0
10. RadiusTopRight: Self.RadiusTopLeft
11. RadiusBottomLeft: Self.RadiusTopLeft
12. RadiusBottomRight: Self.RadiusTopLeft
13. DisabledColor: Self.Color
14. DisabledFill: Self.Fill
15. PressedColor: Self.Color
16. PressedFill: Self.Fill
17. HoverColor: lblTotalItems.Fill
18. HoverFill: Self.Fill
8. Add another Button and set properties as below-
1. Name: btnNext
2. Text: "next >"
3. X: gal_BHG_Pagination.X+gal_BHG_Pagination.Width
4. Y: 0
5. Width: 80
6. Height: SJPagination.Height
7. Fill: RGBA(0, 0, 0, 0)
8. BorderThickness: 0
9. RadiusTopLeft: 0
10. RadiusTopRight: Self.RadiusTopLeft
11. RadiusBottomLeft: Self.RadiusTopLeft
12. RadiusBottomRight: Self.RadiusTopLeft
13. DisabledColor: Self.Color
14. DisabledFill: Self.Fill
15. PressedColor: Self.Color
16. PressedFill: Self.Fill
17. HoverColor: lblTotalItems.Fill
18. HoverFill: Self.Fill
9. Now, we will add controls in gallery (gal_BHG_Pagination). Click on Edit Template icon and add a button. Set the properties of button as below-
1. Name: btnPageNumber
2. Text: ThisItem.Value
3. X: 0
4. Y: 0
5. Width: Parent.Width/10
6. Height: Parent.TemplateHeight-5
7. Color: If(ThisItem.Value=SJPagination.PageNumber,lblTotalItems.Fill,RGBA(255, 255, 255, 1))
8. Fill: RGBA(0, 0, 0, 0)
9. BorderThickness: 0
10. RadiusTopLeft: 0
11. RadiusTopRight: Self.RadiusTopLeft
12. RadiusBottomLeft: Self.RadiusTopLeft
13. RadiusBottomRight: Self.RadiusTopLeft
14. DisabledColor: Self.Color
15. DisabledFill: Self.Fill
16. PressedColor: Self.Color
17. PressedFill: Self.Fill
18. HoverColor: Self.Color
19. HoverFill: lblTotalItems.Fill
20. OnSelect: Set(vrSelectedPageNumber,If(ThisItem.Value<=0,1,ThisItem.Value))
21. 
10. Now add a Rectangle and set below properties-
1. Name: rctPageNumberSelected
2. X: 0
3. Y: btnPageNumber.Y+btnPageNumber.Height
4. Width: btnPageNumber.Width
5. Height: Parent.TemplateHeight-btnPageNumber.Height
6. Fill: If(ThisItem.Value=SJPagination.PageNumber,lblTotalItems.Fill,RGBA(0, 0, 0, 1))
7. 
11. Our basic preparation has been done. Now, we will focus on actual functionality.
12. First, we will set the value of PageNumber property of component. For this, click on component SJPagination and then select the PageNumber property (Output Property). Update the function as-

1. If(
       IsBlank(vrSelectedPageNumber) || IsBlank(SJPagination.PageSize) || SJPagination.PageSize = 0,
       1,
       If(
           vrSelectedPageNumber > RoundUp(
               SJPagination.TotalItems / SJPagination.PageSize,
               0
           ),
           1,
           vrSelectedPageNumber
       )
   )

2. 
3. Basically, it is first validating if vrSelectedPageNumber (currently selected page number) is blank or the PageSize property (Input property) is blank or the PageSize property is 0. If any of the condition is true, then it is setting the PageNumber property as 1 otherwise it is validating if the vrSelectedPageNumber variable value is greater than the total number of pages itself (It needs to be validated in case of filter criteria applied. For example, initially the total number of pages were 25 and the user was currently on page number 24. After applying filter criteria, the number of pages reduced to 13. In such case, there will be no page number 24. Therefore, user must be shown the records of first page). If Yes, then it is again setting the PageSize property as 1 otherwise the vrselectedPageNumber.
13. Now, we will update the Items property of gal_BHG_Pagination gallery. Ideally, it's items must change as per the initial, previous, next condition. Means, initially, it must show 1...10. If moving to 11th page, it must show 11...20. Then, in case, moving back to 10th page, it must show 1...10 page. So, click on gal_BHG_Pagination and select the Items property from the dropdown and update the logic as below-

1. If(
       !IsBlank(SJPagination.TotalItems) && !IsBlank(SJPagination.PageSize) && SJPagination.PageSize > 0,
       If(
           RoundUp(SJPagination.TotalItems / SJPagination.PageSize, 0) > 0,
           If(
               SJPagination.PageNumber > RoundUp(SJPagination.TotalItems / SJPagination.PageSize, 0),
               Sequence(10,1),
               Sequence(
                   1 + 
                   If(
                       SJPagination.PageNumber = RoundUp(SJPagination.TotalItems / SJPagination.PageSize, 0
                       ),
                       SJPagination.PageNumber,
                       If(
                           If(
                               Mod(SJPagination.PageNumber, 10) = 0,
                               RoundDown(SJPagination.PageNumber / 10, 0) * 10 - 9,
                               RoundDown(SJPagination.PageNumber / 10, 0) * 10 + 1
                           ) + 9 <= RoundUp(SJPagination.TotalItems / SJPagination.PageSize, 0),
                           If(
                               Mod(SJPagination.PageNumber, 10) = 0,
                               RoundDown(SJPagination.PageNumber / 10, 0) * 10 - 9,
                               RoundDown(SJPagination.PageNumber / 10, 0) * 10 + 1
                           ) + 9,
                           RoundUp(SJPagination.TotalItems / SJPagination.PageSize, 0)
                       )
                   ) - If(
                       Mod(SJPagination.PageNumber, 10) = 0,
                       RoundDown(SJPagination.PageNumber / 10, 0) * 10 - 9,
                       RoundDown(SJPagination.PageNumber / 10, 0) * 10 + 1
                   ),
                   If(
                       Mod(SJPagination.PageNumber, 10) = 0,
                       RoundDown(SJPagination.PageNumber / 10, 0) * 10 - 9,
                       RoundDown(SJPagination.PageNumber / 10, 0) * 10 + 1
                   )
               )
           )
       )
   )

14. Let's understand this logic. Dividing the logic into colors to explain in better way.
1. Color: First check if the current selected page number is greater than the total number of pages. If yes, then create a sequence of 1...10. (However, it will never show the 10 number because the Current Page Number will reset to 1, the moment total number of pages goes beyond the current page number (generally the filter case)).
2. Color: We are adding 1 so that when the sequence function will create the page number, it starts with 1, 21, 31 and so on because 30-21 will give 9 while we need 10 number for pagination.
3. Color: If the Current page number is equal to the Total number of pages, then use the same otherwise apply the color block.
4. Color: This block of code is validating the end number of sequence whether it is less than or equal to the total number of pages. If yes, the use the same otherwise use the total number of page. The reason is that you cannot go beyond the total number of pages. For example if Total Items are 250 and Page Size is 10 the the Total Number of Paes are 25. So the visible pages will be 1-10, 11-20, 21-25. It cannot be 21-30.
5. Color: It will give you the start page number of the current sequence (explanation given below for color)
6. Color: This piece of code is defining the start number of sequence. It is saying that- If the Current Page number is 10, 20, 30... then the true part will return 1, 11, 21... otherwise false part will return 1, 11, 21... Because if the Current Page number is lying between 1 - 10, then the sequence must start with 1. Similarly, if the Current Page number lying between 11 - 20, then the sequence must start with 11.
7. Overall Explanation: The overall objective is to create a sequence of page numbers like 1-10, 11-20, 11-30 and so on until the last page number reaches. If total pages are 40 then the last series will be 31-40. If the total pages are 46 then the last series will be 41-46.
8. 
15. Now, we will update the OnSelect property of btnPrevious.

1. If(
       IsBlank(vrSelectedPageNumber),
       Set(vrSelectedPageNumber, 1),
       If(
           vrSelectedPageNumber > 1,
           Set(vrSelectedPageNumber, Value(vrSelectedPageNumber) - 1)
       )
   )

2. 
16. Similarly, update the OnSelect property of btnNext.

1. If(
       !IsBlank(SJPagination.PageSize) && SJPagination.PageSize > 0,
       If(
           SJPagination.PageNumber < RoundUp(
               SJPagination.TotalItems / SJPagination.PageSize,
               0
           ),
           Set(
               vrSelectedPageNumber,
               SJPagination.PageNumber
           );
           Set(
               vrSelectedPageNumber,
               vrSelectedPageNumber + 1
           );
       )
   )

2. 
17. Now, the Previous button must be visible only if PageNumber is greater than 1. Similarly, the Next button must be visible if the PageNumber is less than the Total Number of Pages. 
18. Update the Visible property of btnPrevious-
1. SJPagination.PageNumber > 1
2. 
19. Update the Visible property of btnNext-

1. If(
       !IsBlank(SJPagination.PageSize) && SJPagination.PageSize > 0,
       SJPagination.PageNumber < RoundUp(
           SJPagination.TotalItems / SJPagination.PageSize,
           0
       ),
       false
   )

2. 
20. That's all. Now, go to screen tab, add a gallery add few buttons and create collection of different row counts on these buttons. Assign this collection to Items property of gallery. Add the pagination component. Set TotalItems property to rowcount of collection. Set PageSize as 10. Now play the app and test multiple scenarios.
21. (Added Apr 01, 2023) The Items property of the gallery should be set as-

1. Filter(FirstN(collItems,SJPagination_3.PageNumber*10),!(ID in ShowColumns(FirstN(collItems,(SJPagination_3.PageNumber-1)*10),"ID")))
   

2. 
22. (Added May 22, 2023) Another fast response way to implement the Items property is -

1. If(
       SJPagination_3.PageSize * SJPagination_3.PageNumber <= CountRows(collItems)
       ,LastN(FirstN(collItems, SJPagination_3.PageSize * SJPagination_3.PageNumber), SJPagination_3.PageSize * 1)
       ,LastN(FirstN(collItems, SJPagination_3.PageSize * SJPagination_3.PageNumber), (CountRows(collItems) - SJPagination_3.PageSize * (SJPagination_3.PageNumber - 1)))
   )

2. 
23. Any of the Items property can be chosen. However, I would prefer the later one as it was found to be more effective, and fast as compared to former one in case of bulk data.
24. Clicked on 80 Items Coll button.
25. 
26. Clicked on Page No 2.
27. 
28. Clicked on last page number (8).
29. 
30. Clicked on 160 Items Coll button.
31. 
32. Moved to last page of the new collection.
33. 
34. Clicked the 80 Items Coll button again. As the current page number (16) was greater than the total number of pages (8) of new collection. The selected page number resets to 1.
35. 
36. This is how, you can create pagination component. Now, using the output property of component "PageNumber", you can reload the collection by applying filter on datasource. Ideally, your master collection will be the separate one. From that master collection, you have to filter the desired number of items into a child collection and that child collection will be passed to the gallery items property. The pagination component's TotalItems property will be set basis upon master collection.
37. If you wish to show the TotalItems like "1 - 10 of 80 items", then you need to make couple of changes-
1. Add 2 buttons-
1. Name: btnFirst (Copy of btnPrevious)
1. Text: << first
2. X: 0
3. Width: 85
4. OnSelect: Set(vrSelectedPageNumber, 1)
2. Name: btnLast (Copy of btnNext)
1. Text: last >>
2. X: btnNext.X+btnNext.Width
3. Width: 85
4. OnSelect: Set(vrSelectedPageNumber,RoundUp(SJPagination.TotalItems / SJPagination.PageSize,0))
38. Also, you need to update few properties of existing controls-
1. btnPrevious.X: btnFirst.X + btnFirst.Width
2. gal_BHG_Pagination: btnPrevious.X + btnPrevious.Width
39. Also, you need to add 2 input properties in component-
1. Name: FromItemNumber / DataType: Number / Default Value: 0
2. Name: ToItemNumber / DataType: Number / Default Value: 0
40.  Then you need to update the lblTotalItems controls as-
1. Text: If(

       SJPagination.TotalItems > 0
       ,SJPagination.FromItemNumber & " - " & SJPagination.ToItemNumber & " of " & SJPagination.TotalItems & " item" & If(SJPagination.TotalItems > 1, "s")
       ,"0 items"
   )
   

2. Width: 220
3. Size: 12 (Font size)
4. 
41. Coming back to Screen, where the component is used, we have to give input values to the 2 input parameters we had created.
42. FromItemNumber : If(CountRows(collItems) > 0, ((SJPagination_3.PageNumber - 1)* SJPagination_3.PageSize + 1), 0)
1. 
43. ToItemNumber : (((SJPagination_3.PageNumber - 1) * SJPagination_3.PageSize) + Gallery1_1.AllItemsCount)
1. 
44. Save the app and play-
45. Default-
1. 
46. Next-
1. 
47. Last-
1. 
48. Previous-
1. 
49. First-
1. 
50. In case, if you wish to provide Page Size option as well, then you need to add a dropdown and wherever we have used 10 as static value for PageSize, use the selected value of dropdown. Also change the type of PageSize property from Input to Output.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Power Automate: Use Of SELECT Function

Hello Friends,

Welcome back with another post on Power Automate. Today, we will discuss about another amazing function provided by Microsoft. This function is "SELECT". Let's start.

1. The scenario is, you have to get the items from a SharePoint list based upon the IDs provided. These IDs are sent to the flow as comma separated. Usually, what we do is we convert these comma separate IDs into an array, create a filter query using "Apply to each" loop and then use Get Items action. Now, the same we will do using Select action.
2. We had created a list named TestListSJ.
3. 
4. This list has 25700 items starting from ID 401 upto 26100. We will use IDs from 401 upto 500 means 100 items.
5. Let's create a flow in Power Automate.
6. We are creating a "Manually trigger a flow" with name as "POC-SelectFunction".
7. 
8. Click on Create.
9. Declare a string variable "strItemIDs" to hold the comma separated Item IDs. Assign the comma separated IDs from 401 to 500.
10. 
11. Now, we will initialize another variable "arrItemIDs" of type array which will hold these IDs in array form.

1. split(variables('strItemIDs'),',')
   

12. 
13. We will define another variable "intTotalItemIDsCount" of type Integer to capture the total count of Item IDs received.

1. length(variables('arrItemIDs'))
   

14. 
15. From here, the actual logic starts. Now, we will use "Select action and convert each item ID into individual filter query like from 401 to (ID eq 401).
16. 
17. Click on the arrow marked icon. It will switch the Mapping window to basic mode.
18. 
19. Now add the "Concat" function as below.

1. concat('(ID eq ',item(),')')
   

20. 
21. This concat function will convert each item id into individual filter query.
22. Keep saving the flow frequently.
23. Now we need to combine all these filter queries into one single string, separated with " or ". For this we will use "Compose" action and in compose action, we will use the "join" function. 

1. join(body('Select_-_Individual_Filter_Query'),' or ')
   

24. 
25. This is what we were looking for.
26. Now, add "Get Items" action.
27. Select the Site Address, List Name, click on "Show advanced options". In "Filter Query", select the output of Final Query Compose action.
28. 
29. That's all we done.
30. Now save the flow and Test it.
31. 
32. If you see the execution time of each action. It all 0. It means they all individually too less than a second to execute.
33. This is the power of "Select" action. It reduce the execution time. Now, to prove that, we will use tradition method in parallel as well. We will also capture Start/End time for each method and then we will compare the time consumption.
34. For this, we will add a parallel branch just after the "Initialize variable - intTotalItemIDsCount" action. This variable will be named as "strCombinedFilterQuery".
35. 
36. Add "Apply to each" action on the arrItemIDs. Inside the Apply to each action add another action called "Append to string variable". Here we will append the individual filter query to the variable "strCombinedFilterQuery" 

1. concat('(ID eq ',item(),') or ')
   

37. 
38. This step will give us a string like "(ID eq 401) or (ID eq 402) or ... (ID eq 500) or ". As we see, there is an extra " or " at the end, we need to scrap it out.
39. We will add a Compose action and use "substring" function to scrap the last " or " piece.

1. substring(variables('strCombinedFilterQuery'),0,add(lastIndexOf(variables('strCombinedFilterQuery'),')'),1))
   

40. 
41. Now, use the Get Items action to get the filtered records.
42. 
43. Save the flow and test it again. 
44. 
45. If you see, the traditional approach took 26 seconds to prepare the filter query itself.
46. Now, if we see the Run history.
47. 
48. Here, we will find that the "Select" approach took only 31 milliseconds to complete the process, while the tradition approach took 27 seconds. It means the Select approach is 81 times faster than the traditional approach.
49. This select approach is surprisingly useful, when you have to process data. The traditional approach will again add a "Apply to each" loop, while the same work will be done by Select action in fraction of time.
50. For example, here after getting the data, if I have to add a column where, I need to fill in-
1. "Apple" if GUID starts with "a"
2. "Banana" if GUID starts with "b"
3. "Citrus" if GUID starts with "c"
4. "Others" for all other GUIDs
51. Then I need to create HTML of this output. So, what I will do in traditional approach is, I will initialize a string variable to hold the HTML data.
52. 
53. Then, I will use "Apply to each". In "Apply to each" either I will use the Condition (If-Else) or Switch-Case action. In that action, I will check the first character of GUID and based upon that character, I will append HTML code in the declared string.
54. 
55. At the end, I will add another Append to String variable to complete the HTML.
56. 
57. So, the final sequence in Traditional way is
58. 
59. For new approach, I will use the Select action and add column "Flavour". In mapping, I will write the logic.

1. if(
   	equals(toUpper(first(item()?['GUID0'])),'A'),
   	'Apple',
   	if(
   		equals(toUpper(first(item()?['GUID0'])),'B'),
   		'Banana',
   		if(
   			equals(toUpper(first(item()?['GUID0'])),'C'),
   			'Citrus',
   			'Others'
   		)
   	)
   )
   

60. 
61. Lastly, I will add the Create HTML Table action and provide the output of previously added Select action as the input here.
62. 
63. Now, Save the flow and execute it.
64. 
65. Again, if you see, the new approach completed it process in less than a minute. While the traditional approach took around a minute to get the same completed.
66. If we verify the same, this comes out to be "0" seconds (means less than a second) for New approach and 55 seconds for Traditional approach.
67. 
68. Thus, you can see, how powerful is the "Select" action. So, friends, wherever possible, please use Select action and try to avoid "Apply to each" or "Switch" actions.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !