PowerApps: Highlight Selected Item In Gallery

Hello Friends,

Welcome back with another post on PowerApps. Sometimes, the client asks that when a user clicks on any row in gallery, it should be highlighted. As of know, there is no direct feature. However, we can achieve using a very small piece of code. The expected outcome is-

1. 
2. 

Let's start-

1. First create a list "EmployeeInfo" in SharePoint and have some records.
1. 
2. Now, open the PowerApps maker portal and create a canvas app.
1. 
3. There will a screen by default "Screen1". Rename it to "ScreenViewByExperience".
4. Add the list "EmployeeInfo" as DataSource.
1. 
2. 
5. Now, add a "Blank flexible height gallery" and rename it to ParentGallery.
1. 
2. 
6. Update below properties of "ParentGallery"-
1. X: 0
2. Y: 100
3. Width: ScreenViewByExperience.Width
4. Height: ScreenViewByExperience.Height-ParentGallery.Y
5. Items: Distinct(EmployeeInfo,Experience.Value)
7. Now, click on template edit icon and add a "Label" and another "Blank flexible height gallery".
1. 
8. For Label, update below properties:
1. Name: Label_Experience
2. Text: ThisItem.Result
3. X: 0
4. Y: 0
5. Width: Parent.Width
6. Height: 40
7. Fill: RGBA(0, 13, 75, 1)
8. Color: RGBA(255, 255, 255, 1)
9. The outcome will be-
1. 
10. Similarly, update the properties of child gallery as below
1. Name: ChildGallery
2. X: 0
3. Y: Label_Experience.Y+Label_Experience.Height
4. Width: Parent.Width
5. Height: Parent.TemplateHeight-Label_Experience.Y-Label_Experience.Height
6. Items: Filter(EmployeeInfo,ThisItem.Result in Experience.Value)
7. TemplateSize: 40
8. TemplatePadding: 0
11. The outcome will be-
1. 
12. Now, edit the ChildGallery again and add 2 labels to show "First Name" and "Last Name". Link them with ThisItem.'First Name' and ThisItem.'Last Name'.
1. 
13. We have the basic structure ready. Now, comes to the actual part.
14. Click on App >> OnStart property and add below code.

1. Set(vrSelectedItemID,0);
   Set(vrClickCounter,0);
   

15. We have initialized 2 variables-
1. vrSelectedItemID- It will capture the ID of the item, user has clicked and will be used to validate the upon next click whether the same item is re-clicked or other item is clicked.
2. vrClickCounter- It will capture the count of click for a particular item
3. 
16. Now, click on ChildGallery >> OnSelect property and write below code

1. If(
       vrClickCounter = 0,
       Set(
           vrClickCounter,
           1
       );
       Set(
           vrSelectedItemID,
           ChildGallery.Selected.ID
       );
       ,
       If(
           vrClickCounter = 1 && vrSelectedItemID = ChildGallery.Selected.ID,
           Notify(
               Concatenate(
                   ChildGallery.Selected.'First Name',
                   " ",
                   ChildGallery.Selected.'Last Name'
               ),
               Success
           );
           ,
           Set(
               vrSelectedItemID,
               ChildGallery.Selected.ID
           );
       )
   )

17. The outcome is-
1. 
18. Next, click on ChildGallery >> TemplateFill property and write below code.

1. If(vrSelectedItemID = ThisItem.ID,RGBA(244, 196, 48, 1),RGBA(0, 0, 0, 0))
   

19. The outcome is-
1. 
20. Save the application, publish it and run/play.
21. On Load-
1. 
22. Clicked "Deepika Gupta"
1. 
23. Clicked "Manoj Sharma"
1. 
24. Re-Clicked "Manoj Sharma"
1. 
25. This is how, you can achieve the functionality. If you want to navigate to another screen upon double click, then instead of using Notify, use Navigate function.
26. In case, if you wish to give user the "Hand" experience upon hover, then replace the labels used in child gallery with buttons and update the properties as below-

27. Color	RGBA(0, 13, 75, 1)
    Fill	Transparent
    BorderColor	Transparent
    DisabledColor	Self.Color
    DisabledFill	Self.Fill
    DisabledBorderColor	Self.BorderColor
    PressedColor	Self.Color
    PressedFill	Self.Fill
    PressedBorderColor	Self.BorderColor
    HoverColor	Self.Color
    HoverFill	Self.Fill
    HoverBorderColor	Self.BorderColor
    BorderStyle	None
    BorderThickness	0
    OnSelect	Select(Parent)
    BorderRadius	0

28. After replacing labels with buttons, below is the outcome-
1. 
2. 
29. As we can see, there is no difference in the outcome. However, when you hover the mouse upon the item, you will find the cursor converted into Hand.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

PowerApps: Convert Delimiter String To Collection Using Ungroup And ForAll

 Hello Friends,

Welcome back with another post on PowerApps. Recently, we came up with a scenario where user has selected choice of cities for each state and the same got displayed as comma separated value in gallery.

Now, while saving, we have to split this string and create a collection like below screenshot so that we can save the information in data source.

Initially, things were looking extreme complex and not getting the way to resolve. But Where, there is a lock, there is a key. If there is a question, then there will be an answer.

So, we also figured out the resolution. Let's try-

1. First of all create a blank canvas app by logging in PowerApps maker platform.
2. 
   
   
3. Now, add a button (Name: btnLoadCollection; Display Text: Load Collection) to create a collection and assign the below function to OnSelect property.
4. 
   
   
5. The complete code is-

6. ClearCollect(
       collMyData,
       {
           State: "Uttar Pradesh",
           Cities: "Lucknow, Kanpur, Meerut"
       },
       {
           State: "Rajasthan",
           Cities: "Jodhpur, Jaisalmer, Alwar,Ganganagar"
       },
       {
           State: "Gujarat",
           Cities: "Ahmedabad,Gandhi Nagar"
       }
   );
   

7. 
8. Now, we will add a gallery (Name: ParentGallery) and link this gallery with the collection created above.
9. 
   
   
10. Now we will add another button (Name: btnSplitData), which will split the string, convert into collection then we will use Ungroup function to get the desired result.
11. 
    
    
12. We will add the functions one by one to achieve the functionality. First add below function to OnSelect property.

13. ClearCollect(
        TempSplittedCollection,
        []
    );
    ForAll(
        ParentGallery.AllItems,
        Collect(
            TempSplittedCollection,
            {
                State: State,
                City: Split(
                    Cities,
                    ","
                )
            }
        ) 

    ); 
14. It will create a collection "TempSplittedCollection" which will be having 2 columns-
1. State
2. City (it will hold another collection having cities separated based on delimiter)
15. 
    
    
16. Here you will see that the "TempSplittedCollection" is somewhat similar to the result we get after using GroupBy function. It means, if we apply Ungroup, we should get desired result. Therefore, add below code to OnSelect property of btnSplitData just after the previous code.

17. ClearCollect(
        FinalSplittedData,
        Ungroup(
            TempSplittedCollection,
            "City"
        )
    );
    

18. 
    
    
19. The output, we receive upon clicking of this button comes out to be-
20. 
21. This is the result we were looking for.
22. Now, we can apply Patch command with ForAll to save this data to our data source.
23. Lastly, we can add a cleaning code for the collections we have created during Split functionality (if required).
24. The complete code to be written on OnSelect property of btnSplitData will be-

25. ClearCollect(
        TempSplittedCollection,
        []
    );
    ForAll(
        ParentGallery.AllItems,
        Collect(
            TempSplittedCollection,
            {
                State: State,
                City: Split(
                    Cities,
                    ","
                )
            }
        )
    );
    ClearCollect(
        FinalSplittedData,
        Ungroup(
            TempSplittedCollection,
            "City"
        )
    );
    

26. If you wish to get this final data to be displayed on screen, add another gallery and bind the Items with "FinalSplittedData" collection.
27. 
    
    
28. This way we can achieve the desired functionality.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

PowerApps: Nested Gallery - Alternate Row Color

Hello Friends,

Welcome back with another post on PowerApps. In our last post, we have studied on how to create nested Gallery and then we have applied the Expand / Collapse feature on that gallery. Then we applied Expand All / Collapse All.

1. PowerApps: Nested Gallery
   
2. PowerApps: Nested Gallery - Expand / Collapse
   
3. PowerApps: Nested Gallery - Expand All / Collapse All
   

Now, we will try to add Alternate Row Color in nested gallery (Items gallery). For this, we will add a dynamic column RowNumber and assign a sequential value to it. Then we will use this RowNumber to define the color of rows. But Before start, I assume that you had visited all the three posts above and created the PowerApps as described. Let's start for further implementation.

1. Click on GalleryItemDetails gallery and select the Items property. Right now it is mentioned as ThisItem.ListItemDetails.
2. Now, change the function with below-

3. With(
       {RecordsListItemDetails: ThisItem.ListItemDetails},
       ForAll(
           Sequence(CountRows(ThisItem.ListItemDetails)),
           Patch(
               Last(
                   FirstN(
                       RecordsListItemDetails,
                       Value
                   )
               ),
               {RowNumber: Value}
           )
       )
   )
   

4. 
5. Further, select the TemplateFill property of the same gallery (GalleryItemDetails) and add below function. It will check if Mod value is ZERO (means even number row) then it will apply one color otherwise (odd number row) other color.

6. If(Mod(ThisItem.RowNumber,2)=0,RGBA(241, 244, 209, 1),RGBA(200, 200, 200, 1))
   

7. 
8. Save the PowerApps and execute/play. The magic works. 🙂
9. 
   
   
10. This way, we can add Alternate Row Color in PowerApps Gallery.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

PowerApps: Nested Gallery - Expand All / Collapse All

Hello Friends,

Welcome back with another post on PowerApps. In our last post, we have studied on how to create nested Gallery and then we have applied the Expand / Collapse feature on that gallery.

1. PowerApps: Nested Gallery
   
2. PowerApps: Nested Gallery - Expand / Collapse
   

If you have not read these posts, please go through it.

Now, we will try to implement Expand All / Collapse All feature. Let's start-

1. I am assuming that you have read my last 2 posts mentioned above and created the PowerApps as described. We will continue from there.
2. Let's add 2 buttons-
3. Button1:
1. Name: btnCollapseAll
2. Text: Collapse All
3. OnSelect: ClearCollect(SelectedOrderIDCollection,{SelectedOrderID:0})
4. 
4. Button2:
1. Name: btnExpandAll
2. Text: Expand All
3. OnSelect: 

4. ClearCollect(
       SelectedOrderIDCollection,
       {SelectedOrderID: 0}
   );
   ForAll(
       OrdersList,
       If(
           CountRows(
               Filter(
                   SelectedOrderIDCollection,
                   SelectedOrderID = OrdersList[@OrderID]
               )
           ) = 0,
           Collect(
               SelectedOrderIDCollection,
               {SelectedOrderID: OrdersList[@OrderID]}
           )
       )
   );
   

5. 
5. For Collapse All, we have cleared the collection which was storing the OrderID. As the collection becomes clear, no Order is there therefore, all galleries height will become ZERO (0).
6. 
7. For Expand All, first we have cleared the collection, then, we have added all OrderIDs in that collection. Therefore, all galleries will be now having height based on their items count.
8. 
9. That's all. It was so simple to implement.
10. This way you can apply Expand All & Collapse All feature in nested gallery.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

PowerApps: Nested Gallery - Expand / Collapse

 Hello Friends,

Welcome back with another post on PowerApps. In our last post, we have studied on how to create nested Gallery. We have successfully achieved the functionality. Now, by default, all nested galleries were appearing in expanded mode. We want to show only the selected order item gallery in expanded mode and other in collapse mode. Let's see-

If you have not read my previous post on Nested Gallery, please visit below link to go through and create the Nested Gallery PowerApps.

1. PowerApps: Nested Gallery
   

Now, as we to make changes so that only the selected Order's detail would get expanded and remaining gets collapsed. For this, 

1. Option 1: Only One Expanded At A Time
2. We will make a little change in the height property of nested gallery. Set the Height property of GalleryItemDetails as

3. If(ThisItem.OrderID = GalleryOrderMaster.Selected.OrderID,(CountA(ThisItem.ListItemDetails.ItemName)+1)*50,0)
   

4. This way, when we click on any Order ID, it's nested gallery will get a height of 50 times the number of rows +1. For rest of the order ids, their nested gallery will have the height as 0.
5. 
   
   
6. Now save the PowerApps and execute (play). The output will be
7. 
8. This way, only one order will be displayed in expanded mode at a time.
9. Option 2: Multiple Expanded At A Time
10. Sometimes there could be scenario that user need to expand more than one order at a time. Click on order id should be in a toggle mode. If Collapsed then it should expand otherwise collapse.
11. For this, we will use another local collection, where we will store the OrderID, upon which the user has clicked. Upon first click, the ID will get saved in this collection.
12. Upon second click, it will be removed from the collection.
13. This would be a repetitive process.
14. For this, we will add a collection creation code on btnLoadData. You may write the same on App >> OnStart.

15. ClearCollect(SelectedOrderIDCollection,{SelectedOrderID:0});
    

16. It will create a local collection with name as "SelectedOrderIDCollection" and having one record with column name as SelectedOrderID and value as 0. Being 0 will not be ID for any order hence, it will not impact our logic.
17. Now add below logic on GalleryOrderMaster >> OnSelect property-

18. If(
        CountRows(
            Filter(
                SelectedOrderIDCollection,
                SelectedOrderID = GalleryOrderMaster.Selected.OrderID
            )
        ) = 0,
        Collect(
            SelectedOrderIDCollection,
            {SelectedOrderID: GalleryOrderMaster.Selected.OrderID}
        ),
        Remove(
            SelectedOrderIDCollection,
            Filter(
                SelectedOrderIDCollection,
                SelectedOrderID = GalleryOrderMaster.Selected.OrderID
            )
        )
    )
    

19. This function will check if the selected OrderID is present in local selected collection (SelectedOrderIDCollection). If not, then this function will add it, otherwise it will remove. Means a toggle functionality will work.
20. 
21. Now, we redefine the Height property of GalleryItemsDetails. Change the existing function to-

22. (CountA(ThisItem.ListItemDetails.ItemName) + 1) * 50 * CountA(
        Filter(
            SelectedOrderIDCollection,
            SelectedOrderID = ThisItem.OrderID
        )
    )
    

23. 
    
    
24. Now save the PowerApps and execute/play the same.
25. 
    
    
26. 
    
    
27. This way, we can achieve the multiple expand feature in PowerApps nested gallery.
28. In our next post, we will learn about Expand All / Collapse All.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

PowerApps: Nested Gallery

Hello Friends,

Welcome back with another post on PowerApps. We are starting a series of posts in which we will learn about-

1. Nested Gallery
2. Expand / Collapse (individual master child record set)
3. Expand All / Collapse All
4. Alternate row color

Below is the overview of functionality, we are going to discuss-

We will learn all these features one by one. So let's start-

1. Before starting, we need a SharePoint List having below columns-
1. OrderID
2. ItemName
3. ItemQuantity
2. We have kept the list simple to just show the functionality.
3. 
4. Now add 5-6 orders with each having multiple items.
5. 
   
   
6. Now click on App Launcher (grid of 9 dots at top left corner) and click on PowerApps to open the PowerApps maker platform.
7. I am creating PowerApps in Tablet mode so that the display of nested gallery should be clear.
8. First of all, add connection with SharePoint list. For this, click on Data icon from left navigation menu, click on Add Data. Now select the SharePoint connector. Select the Site and then list "OrdersList".
9.  
10. Now, add a button at bottom right corner which will load the data from SharePoint to local collection.
11. Click on Insert >> Button. Rename it btnLoadData. Set Text as "Load Data".
12. Add below function on "OnSelect" property-

13. ClearCollect(OrdersListCollection,GroupBy(OrdersList,"OrderID","ListItemDetails"));
    

14. This function will load the data from SharePoint list into a local collection "OrdersListCollection" with grouping on OrderID. The grouped data will reflect under column "ListItemDetails". It means, the local collection will have two column-
1. OrderID
2. ListItemDetails
15. 
    
    
16. 
    
    
17. Now we will add gallery control on screen. For this, choose the gallery of type "Blank flexible height". The reason behind this is that we will expand the item based on the number of child items.
18. 
    
    
19. Set the-
1. Height property of gallery as "Parent.Height"
2. Width property as "1000"
3. Items property as "OrdersListCollection"
20. Now click on the Edit icon of item template as shown in below screenshot-
21. 
22. Add a label and set it's-
1. Width as 400
2. Position X as 0
3. Position Y as 0
4. FontWeight as Bold
5. Height as 50
6. Fill as RGBA(241, 244, 249, 1)
7. Rename it as lblOrderID
8. Text as Concatenate("Order ID: ",Text(ThisItem.OrderID))
23. Add one more label and set it's-
1. Width as 585
2. Position X as 400
3. Position Y as 0
4. FontWeight as Bold
5. Height as 50
6. Fill as RGBA(241, 244, 249, 1)
7. Rename it as lblCustomer
8. Text as Concatenate("Customer: ",LookUp(OrdersList,OrderID = ThisItem.OrderID,'Created By'.DisplayName))
24. 
    
    
25. This will show the Order ID and the Customer name. Rename this gallery as "GalleryOrderMaster".
26.  Now we will add one more gallery, which will show the Items associated with that particular Order ID. Once again, click on edit icon as shown in Step 20 and add another gallery of type "Blank flexible height". Rename it as "GalleryItemsDetails".
27. Set-
1. Position X as 50
2. Position Y as 50 (you may set it dynamic by specifying lblOrderID.Height + lblOrderID.Y)
3. Width as 885
4. Height as 100 (we will make it dynamic later in code)
5. Items as ThisItem.ListItemDetails
28. Now we will add two labels to show the ItemName and ItemQuantity.
29. 
30. Add first label and set-
1. Name as lblItemName
2. Text as ThisItem.ItemName
3. Position X as 0
4. Position Y as 0
5. Width as 300
6. Height as 50
31. 
    
    a
32. Similarly, add another label and set-
1. Name as lblItemQuantity
2. Text as ThisItem.ItemQuantity
3. Position X as 300
4. Position Y as 0
5. Width as 300
6. Height as 50
33. 
    
    
34. This way you can show nested items. The basic part is complete. Now, if you execute the PowerApps, you will find that the height of nested gallery is fixed. So, we will make it dynamic. For this, set the Height of GalleryItemDetails as "(CountA(ThisItem.ListItemDetails.ItemName)+1)*50". We have added +1 to make sure there should be no scrollbar.
35. 
    
    
36. This way we can achieve nested gallery functionality. In next post, we will discuss about Expand / collapse feature in nested gallery.
37. UPDATE:
1. Some times, you may find that if the data in nested gallery is too large, then the behavior of parent-nested gallery becomes abnormal.
2. 
   
   
3. As you can see, Parent Items (Kamal, Manoj...) are inline with Child Items (Item-491, Item-492...). (Below is the dynamic height code which you will read in my next articles of this series).
4. The Height code is-

5. If(
       ThisItem.OrderID = ParentGallery.Selected.OrderID,
       (CountA(
           Filter(
               CollChild,
               OrderID = ThisItem.OrderID
           ).ItemName
       ) + 1) * 50,
       0
   )
   

6. To avoid this situation, we need to fix the height of child gallery upto a max limit. But the problem is that, there is Max Height property available for Gallery control.
7. So, we have prepared a workaround to overcome this problem. We have Min function. We will make use of it.
8.  Let's modify the code for Height (I am taking maximum height for child gallery as 400)-

9. If(
       ThisItem.OrderID = ParentGallery.Selected.OrderID,
       Min(
           (CountA(
               Filter(
                   CollChild,
                   OrderID = ThisItem.OrderID
               ).ItemName
           ) + 1) * 50,
           400
       ),
       0
   )
   

10. After implementing this code, the height of child gallery is set to maximum 400 as the calculated height was much greater than 400. Therefore, Min function gave 400 as output.
11.   
12. In case if the calculated height is less than 400, in that case, the Min function will gave the calculated value as output. 

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !