PowerApps, Power Automate, SharePoint, Dynamics 365, Azure, C#: December 2021

Friday, December 31, 2021

PowerApps: Pagination In Gallery

Hello Friends,

Welcome back with another post on PowerApps. Have you ever encountered a scenario where you have hundreds or thousands of records in PowerApps gallery. The most tedious job at that time is to view all records by scrolling. It would be great if we have a pagination kind of functionality similar to other technologies. Unfortunately, PowerApps doesn't provide any pagination concept in Gallery as well as in Data table. Let's try to do with Gallery.

Here, we will first fetch all records from SharePoint List. For this will take help of my earlier article PowerApps: Getting More Than 2000 Items (Using Sequence).
Then we will try to create pagination upon gallery with search feature as well.I had created a list having 4 column with around 25000 items.
Now open the PowerApps maker portal by clicking here.
You may also go to PowerApps maker portal by clicking on Integrate >> PowerApps >> See all apps.
Create a new PowerApps app by clicking "+ Create" >> "Canvas app from blank".
Give in the name. Choose format as Tablet. Click on Create. It will take couple of minutes to create a blank canvas app. The moment, it creates and shows you the blank canvas screen to design, the first most important task you need to do is to Save the app. Click on File >> Save to save the app. The benefit of doing so is that until you save it, your app will remain in browser memory and if anyhow, browser gets closed, your hard work will get vanished. But, if you have saved the app at very beginning, then it gets saved on cloud and PowerApps maker portal start saving the changes every 1-2 minutes automatically.
Let's move on. You will get Screen1 by default. Let's rename it ScreenGalleryPagination by clicking on 3 dots just next to the Screen1 name.
Now we will add a SharePoint connection. For this click on cylinder icon (baiscally it is a database icon). Click on "+ Add data". Search for SharePoint and then click on SharePoint connection.
It will show you the connection with your login. Click on same. Then it will show you the possible sites at right side. Select your SharePoint site. Then it will show you the SharePoint lists. Choose the list and click on Connect.
Now we will fetch the items from this SharePoint list to PowerApps collection. For this click on Apps and then choose "OnStart" property.
Now click on function box and input below code-

ClearCollect(
    Coll_AllItems,
    []
);
ClearCollect(
    DataSourceFiltered,
    []
);
Set(
    FirstRecord,
    First(TestListSJ)
);
Set(
    LastRecord,
    First(
        Sort(
            TestListSJ,
            ID,
            Descending
        )
    )
);
Set(
    BatchSize,
    500
);
Set(
    MaxIteration,
    RoundUp(
        (LastRecord.ID - FirstRecord.ID) / BatchSize,
        0
    )
);
ClearCollect(
    TotalIterations,
    AddColumns(
        AddColumns(
            ForAll(
                Sequence(MaxIteration),
                Value
            ),
            "min",
            (FirstRecord.ID + (Value - 1) * BatchSize)
        ),
        "max",
        (FirstRecord.ID + Value * BatchSize)
    )
);
ForAll(
    TotalIterations,
    Collect(
        Coll_AllItems,
        Filter(
            TestListSJ,
            ItemID >= min && ItemID < max
        )
    )
);
ClearCollect(DataSourceForGalleryItems,Coll_AllItems);

Let's explain one by one. Please match with color-

Coll_AllItems: It is a collection to hold all items fetched from SharePoint list.
DataSourceFiltered: It is a collection to hold the result obtained when we click on "Search Record" button.
FirstRecord / LastRecord: These are variables to hold the First item and the Last item of SharePoint list (Sort By ID).
BatchSize: It is the number of records we will fetch in each call to SharePoint.
MaxIteration: It is the number of total calls we need to made to SharePoint in order to fetch all records.
TotalIterations: It is a collection which is holding the starting ID and the ending ID which will be passed in Filter call to SharePoint. (i.e. 0-500, 501-1000, 1001-1500, ...)
ForAll: It is a function call using which we will fetch all records from SharePoint
ClearCollect: This command we are using to make clone of Coll_AllItems collection. This clone will be used to assign back if we had made a call of Search Record and then we are making call of Search Page / Reload.

With this, we have fetched all records from data source.
Now, click on screen "ScreenGalleryPagination" and add a Gallery (Vertical). Name this gallery as "GalleryListData". Set-

Layout: Title, Subtitle, and body
TemplateSize: 50
Height: Parent.Height-GalleryListData.Y-100
Width: Parent.Width

Title1 label-

Rename it to lblID
X: 0
Y: 0
Width: 100
Height: GalleryListData.TemplateHeight

Subtitle1 label-

Rename it to lblTitle
X: lblID.X+lblID.Width
Y: 0
Width: 400
Height: GalleryListData.TemplateHeight

Body1 label-

Rename it to lblGUID
X: lblTitle.X+lblTitle.Width
Y: 0
Width: 600
Height: GalleryListData.TemplateHeight

I have renamed according to my list columns. You may rename according to your requirements. Other cosmetic surgeries is up to you. 😊
Set GalleryListData >> DataSource to DataSourceForGalleryItems
Now click on Edit link just ahead of Fields property and set the which field is to be displayed in which label.
Now, click on ScreenGalleryPagination and set it's "OnVisible" property. Here we will set 2 variables-

PageSize: It the number of items to be displayed in gallery at a time.
intFilterRecordsFromGallerySource: It is a number which will be used to filter the number of such items from entire collection using FirstN so that we can then use LastN with PageSize to get the actual items to be displayed on screen when user will click on Next/Previous icon.

The code is-

UpdateContext({PageSize: 0});
UpdateContext({intFilterRecordsFromGallerySource: 0});
UpdateContext(
    {
        PageSize: RoundDown(
            GalleryListData.Height / GalleryListData.TemplateHeight,
            0
        )
    }
);
UpdateContext({intFilterRecordsFromGallerySource: PageSize});

Now, click on GalleryListData gallery and choose Items property and assign the below code.

LastN(
    FirstN(
        DataSourceForGalleryItems,
        intFilterRecordsFromGallerySource
    ),
    If(
        Mod(
            intFilterRecordsFromGallerySource,
            PageSize
        ) = 0,
        PageSize,
        Mod(
            intFilterRecordsFromGallerySource,
            PageSize
        )
    )
)

This code is what we were talking about in above step. The moment you add this code, you may find that the gallery becomes blank. This is due to non initialization of variable. So, save the app and play it once.
With this, we have completed the basic display part. Now the actual work starts where we will implement the pagination and will see, how it works.
Let's add a reload button at top. For this, add a Reload icon at top and update it's "OnSelect" property as-

ClearCollect(DataSourceForGalleryItems,Coll_AllItems);

The other changes are-

Rename it to IconReload
Height: 40
Width: 40
Padding Left/Right/Top/Bottom: 5
Color: RGBA(255, 255, 255, 1)
Fill: RGBA(50, 86, 160, 1)
Y: 0
X: 1326 (or set it accordingly to make it extreme right)

Now, we will add Previous / Next button. For this, add Left icon and update it's "OnSelect" property as-

If(
    intFilterRecordsFromGallerySource < 2 * PageSize,
    UpdateContext({intFilterRecordsFromGallerySource: PageSize}),
    UpdateContext(
        {
            intFilterRecordsFromGallerySource: intFilterRecordsFromGallerySource - If(
                intFilterRecordsFromGallerySource = CountRows(Coll_AllItems),
                If(
                    Mod(
                        CountRows(Coll_AllItems),
                        PageSize
                    ) = 0,
                    PageSize,
                    Mod(
                        CountRows(Coll_AllItems),
                        PageSize
                    )
                ),
                PageSize
            )
        }
    )

);

The other changes are-

Rename it to IconPrevious
Height: 40
Width: 80
Padding Left/Right/Top/Bottom: 5
Color: RGBA(255, 255, 255, 1)
Fill: RGBA(50, 86, 160, 1)
Y: GalleryListData.Y+GalleryListData.Height
X: 0

Now, add right icon and update it's "OnSelect" property as-

UpdateContext(
    {
        intFilterRecordsFromGallerySource: intFilterRecordsFromGallerySource + If(
            intFilterRecordsFromGallerySource + PageSize <= CountRows(Coll_AllItems),
            PageSize,
            If(
                intFilterRecordsFromGallerySource = CountRows(Coll_AllItems),
                0,
                Mod(
                    CountRows(Coll_AllItems),
                    PageSize
                )
            )
        )
    }

The other changes are-

Rename it to IconRight
Height: 40
Width: 80
Padding Left/Right/Top/Bottom: 5
Color: RGBA(255, 255, 255, 1)
Fill: RGBA(50, 86, 160, 1)
Y: GalleryListData.Y+GalleryListData.Height
X: Parent.Width-IconRight.Width

Another section has been completed. Now save the app and play it. Click on Next / Previous buttons. The gallery is getting loaded accordingly.
Now, we will try to show the

Page Number / Total Pages
Total Records
First Page
Last Page
Search By Page Number
Search By Record Title

Let's add Page Number / Total Pages. For this, add a label.

Rename it to lblCurrentPage
Width: 75
Height: 40
X: 0
Y: Parent.Height-lblCurrentPage.Height
BorderThickness: 1
Text: RoundUp(intFilterRecordsFromGallerySource/PageSize,0)
Align: Center

Add another label.

Rename it to lblSeparatorSignLabel
Width: 30
Height: 40
X: lblCurrentPage.X+lblCurrentPage.Width
Y: Parent.Height-lblSeparatorSignLabel.Height
BorderThickness: 1
Text: /
Align: Center

Add another label.

Rename it to lblTotalPages
Width: 80
Height: 40
X: lblSeparatorSignLabel.X+lblSeparatorSignLabel.Width
Y: Parent.Height-lblTotalPages.Height
BorderThickness: 1
Text: RoundUp(CountRows(DataSourceForGalleryItems)/PageSize,0)
Align: Center

This way Page Number / Total Pages is done.
Now we will Total Number of Records.
Add a label.

Rename it to lblTotalRecordsText
Width: 130
Height: 40
X: lblTotalPages.X+lblTotalPages.Width+20
Y: Parent.Height-lblTotalRecordsText.Height
Italic: true
Text: Total Records:

Add another label.

Rename it to lblTotalRecordsValue
Width: 75
Height: 40
X: lblTotalRecordsText.X+lblTotalRecordsText.Width
Y: Parent.Height-lblTotalRecordsValue.Height
Text: CountRows(DataSourceForGalleryItems)

This way, Total Records display gets done.
Now we will work upon First Page & Last Page. Let's Add Last Page button.
Add a button-

Rename it to btnLastPage
Width: 125
Height: 40
X: Parent.Width-btnLastPage.Width
Y: Parent.Height-btnLastPage.Height
Text: Last Page
OnSelect: UpdateContext({intFilterRecordsFromGallerySource:CountRows(DataSourceForGalleryItems)});

Add another button-

Rename it to btnFirstPage
Width: 125
Height: 40
X: Parent.Width-btnLastPage.Width-btnFirstPage.Width-20
Y: Parent.Height-btnLFirstPage.Height
Text: First Page
OnSelect: UpdateContext({intFilterRecordsFromGallerySource:PageSize});

This way First Page & Last Page also done.
Now, we will implement Search Page / Search Record functionality. For this-
Add a button-

Rename it to btnSearchPage
Width: 150
Height: 40
X: lblTotalRecordsValue.X+lblTotalRecordsValue.Width+20
Y: Parent.Height-btnSearchPage.Height
Text: Search Page
OnSelect:

If(
    Value(txtSearchBox.Text) <= RoundUp(
        CountRows(DataSourceForGalleryItems) / PageSize,
        0
    ),
    UpdateContext({intFilterRecordsFromGallerySource: Value(txtSearchBox.Text) * PageSize})
)

The moment, you add the OnSelect code, it will error because, till now, we haven't added textbox, from which it will pick the data. So don't worry,, we are going to add it next.
Add a textbox (Text input).

Rename it to txtSearchBox
Default: "" (Blank)
Width: 250
Height: 40
X: btnSearchPage.X+btnSearchPage.Width+20
Y: Parent.Height-txtSearchBox.Height

Add another button

Rename it to btnSearchRecord
Width: 150
Height: 40
X: txtSearchBox.X+txtSearchBox.Width+20
Y: Parent.Height-btnSearchRecord.Height
Text: Search Record
OnSelect:

ClearCollect(
    DataSourceFiltered,
    Search(
        Coll_AllItems,
        txtSearchBox.Text,
        "Title"
    )
);
ClearCollect(
    DataSourceForGalleryItems,
    DataSourceFiltered
);
UpdateContext({intFilterRecordsFromGallerySource: PageSize});

This way, we have completed our functionality. Below are the screenshots.
Home Screen-
Next Button-
Previous Button-
Search Page-
Search Record-
Reload-
Last Page-
First Page-
This way you can implement pagination.
You may also implement numeric pagination. For this, you need to visit my next post. PowerApps: Pagination In Gallery (Implement Numeric Pagination).

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Bye... Bye... 2021

Wishing All Readers & Coders . . . !!! A Very Happy New Year !!!

Wednesday, December 29, 2021

Power Automate: Get All Records (Do-Until)

Hello Friends,

Welcome back with another post on Power Automate. In this post, we will discuss about getting all records from SharePoint list irrespective of items count. As we know, that the "Get Items" action results only 100 records and so is the "Send an HTTP request to SharePoint". In "Get Items" action, we can stretch this limit by using Top Count which itself has max limit of 5000 items irrespective of Power Automate license. If any list have more than 10000 items then how do we get all items.

The answer is using Do-Until. It is a great feature using which we can loop on SharePoint list and get all Items. All these items can be stored in an array and then can be processed. So, what are we waiting for? Let's start-

First of all we will create a list and add thousands of records ( 😉 ). I had created a list and added around 25000 records (using a manual flow).
Now, open the Power Automate maker portal and create an "Instant cloud flow" of type "Manually trigger a flow". I am using this flow for demo purpose. You may use any flow.
Add an action "Initialize variable", declare a variable "intItemID" of type Integer and set it to ZERO (0).
Click on Save to save the flow.
Add an action "Initialize variable", declare a variable "arrAllItems" of type Array and set it to [].
Click on Save to save the flow.
Add an action "Initialize variable", declare a variable "blnIsEmpty" of type Boolean and set it to false.
Now, the process starts. Add an action "Do until".
Add the validity condition as "blnIsEmpty" is equal to "true".
Now you will see a link "Change limits". Click on it. It will show 2 type of limits-

Count: Maximum number of recursive calls, this Do-until action will perform. Max limit is 5000
Timeout: It is a value which we can use to stop loop if the Loop condition is not met to avoid indefinitely running. More details can be found here.

Change Count to 3000.
Now click on "Add an action" inside this Do-until action and choose "Get Items" action.
Choose Site Address, List Name, Filter Query (as ID gt intItemID), Order By (ID).
Now add a "Select" action. It will take input from Get Items output and in Map, we will map fields according to our requirement.
Map ID & Title fields.
Next, we will make a union (join) of this output with arrAllItems using Compose action. Here we will use "union" function.
Now, we will assign the output of this compose action to the variable arrAllItems so that in next loop call, the updated array will be available to Compose action for union. For this, use "Set variable" action.
Now, we will reset the intItemId with the last record, we received from "Get Items" action. Again use "Set variable" action. To get the last item id, we will use the "last' function.
The last action we need to add is to set blnIsEmpty variable. For this we will check if the output of "Get Items" action is blank. If blank, then make blnIsEmpty as true. The "empty" function server this condition perfectly. Let add another action "Set variable".
Now, the last thing, we need to add is "Configure run after". In case, if the output of "Get Items" action is blank, the "Set intItemID" action will get failed and throw error. It will result in failure of execution of our flow. So, to avoid that scenario and terminating the flow of Do-until properly, we will update the "Configure run after" property of "Set blnIsEmpty" as "is successful" and "has failed".
After selecting both check boxes, click on Done.
That's all. Now Save the flow and execute it.
As we were having 25700 items and each iteration (Get Items) returns 100 records, it took 258 iterations to get the data. The 258th iteration is what where it found blnIsEmpty as true and terminated the Do-until process.
To verify the output, I had fetched the length of the array "arrAllItems". It comes out to be 25700 which is exactly same as in the list.
This way, we can get all items in Power Automate.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Tuesday, December 28, 2021

Get User Permissions From SharePoint Group In PowerApps: Part 5

Hello Friends.

Welcome back in the User Permissions series. In previous post, we had fetched SharePoint Group name, Permissions and Users. Links of past 4 posts are-

This is the last post of this series. In this post, we will process the output of Users and apply for each loop to check if the requested user is present in the group. If present, save the group permission details.

Let's start-

Add a Condition action after the Parse Users action and name it Check User Match. Now, as the data may be case sensitive hence we will use toLower function.
Now choose the UserPrincipalName in left input box. The moment, you choose it, an additional Apply to each action is applied to this condition action. This is because there could be multiple users. Rename this action as For Each User.
Now we will re apply the condition (Check User Match) input values by applying toLower function.
Now come to If Yes condition. Here we will add "Append to string variable" action and save the name of the permission name (Full control / Contribute ...) and other stuff from RoleDefinitionBindings to strUserPermissions variable.
Now the moment we will add the "Name" field from "Parse_RoleDefinitionBindings", it will add a "Apply to each" action wrapper upon this Append action.
Rename this "Apply to each" to "For Each RoleDefinitionBindings".
Add a delimiter at the end of value.
You may add multiple information values separated by a Data Delimiter (semicolon here) and a Row Delimiter (Hash here)
Now the last step is to send this data as output response back to PowerApps. For this, collapse all the actions and it will looks like-
Add new action "Respond to a PowerApp or flow'-
Then click on Text output type-
Give the output title as "Response" or whatever you wish. It will provide you the output in PowerApps and value as "strUserPermissions"
This way, it completes the flow and this series as well.
In PowerApps, you need to split data based on delimiters and use accordingly.
You may visit below URL to do the same-
PowerApps: Convert Delimiter String To Collection Using Ungroup And ForAll