cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajeshcs
New Member

Counting the number of occurrences of an item from a SharePoint list and sorting another list based on the highest number of occurrences

Hello Everybody

 

I am new to PowerApps and learning from the videos many of you have posted in Youtube. This community page is also helping in my learning journey. I am developing a canvas application and hit with an issue.

I have two SharePoint lists. AssetList which stores the details of various assets. Second list (LikesList) which stores the number of Likes for the asset items. Whenever somebody clicks the like button the AssetID and the email of the user is is stored in the LikesList. The LikesList will look like this

 

AssetID

eMail

1

user1@abc.com

1

user2@abc.com

3

user1@abc.com

2

user2@abc.com

1

user5@abc.com

3

user4@abc.com

3

user2@abc.com

 

I want to count the number of likes for each asset and display the assets which is having highest number of likes  on top in a gallery.

I have created a collection with Distinct function to collect the AssetsIDs which have been liked by the users from the LikesList. ClearCollect(likes, Distinct(LikesList,AssetID) which is bringing back the individual AssetIDs which people have liked. I have then created a gallery with the collection likes and added a label to count the number of likes against each asset ID.

AssetID1

LikesCount

1

3

2

1

3

3

 

Now I want to filter  the AssetList gallery with the AssetID1 column and sort it with the LikesCount from Gallery2. I tried AddColumn to create a collection combining the AssetList and Gallery2 with no success.

 

 Is there a way I can achieve this? Is there a better solution for this scenario? Any help on this will be greatly appreciated.

Thanks in Advance

Rajesh

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@rajeshcs 

Yes, everything I provided was based off of your original post where you had an AssetID.

Your AssetGallery should have the formula that I provided in it with the change that, if you want the asset information, the look that up:

SortByColumns(
    AddColumns(
        GroupBy(yourDataSource, 
            "AssetID", 
            "_recs"
        ), 
        "Likes", CountRows(_recs),
        "Asset", LookUp(AssetList, ID=AssetID)
    ), 
    "Likes", Descending
)

You will now have the full Asset record in your table records that you can use.

ex. ThisItem.Asset.'Asset Name'

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

8 REPLIES 8
RandyHayes
Super User III
Super User III

@rajeshcs 

You will need to utilize the Group by function for this.

Set your Gallery Items Property to:

SortByColumns(
    AddColumns(
        GroupBy(yourDataSource, 
            "AssetID", 
            "_recs"
        ), 
        "Likes", CountRows(_recs)
    ), 
    "Likes", Descending
)

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Thanks @RandyHayes! The solution worked perfectly fine for counting the Likes and sorting in descending order. Now I want to filter my actual asset gallery when ID matches the AssetID from the previous formula. How do i write the filter function for the same? Apologies for my poor knowledge in the subject.

RandyHayes
Super User III
Super User III

@rajeshcs 

No problem, but I am not understanding what you mean.  The formula provided gives you the Likes in top to bottom order. So, it is a table.  I am lost when you say filter the gallery (what gallery?) when ID matches (where is the ID?) the AssetID from the previous formula (the formula provides a table...there are many AssetID's...which do you want, the top one?)

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@RandyHayes 

Thanks for helping me.

Sorry for the confusion. From my original post, I have two SharePoint Lists
AssetList
ID Asset Name Asset Owner Created Asset Type
1
2
3
4
13
This AssetList is connected to a Galley which displays the assets. I have a like button in the assets galley associated with each asset and when people clicks on the like button it writes data to the LikesList. The LikesList is as follows.
AssetID   eMail
1      user1@abc.com
2      user3@abc.com
13    user1@abc.com
13    user4@abc.com
3     user2@abc.com
2     user3@abc.com
13     user2@abc.com
Here the AssetID corresponds to the ID in my first list (AssetList)
I want to actually display the assets in my asset galley where the assets with maximum number of likes appears first. Hope this clarifies.
Thanks
Rajesh

RandyHayes
Super User III
Super User III

@rajeshcs 

Sorry, I am not understanding...that was the formula that I provided for you.  It displays the assets in the gallery with the maximum number of likes appearing first.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@RandyHayes  Apologies for my poor understanding and I am sure, I may be using the formula wrongly 😥

Here is my issue. Apologies if I am asking a silly question again and again. 

 

rajeshcs_2-1615469298696.png

 

Thanks

Rajesh

 

RandyHayes
Super User III
Super User III

@rajeshcs 

Yes, everything I provided was based off of your original post where you had an AssetID.

Your AssetGallery should have the formula that I provided in it with the change that, if you want the asset information, the look that up:

SortByColumns(
    AddColumns(
        GroupBy(yourDataSource, 
            "AssetID", 
            "_recs"
        ), 
        "Likes", CountRows(_recs),
        "Asset", LookUp(AssetList, ID=AssetID)
    ), 
    "Likes", Descending
)

You will now have the full Asset record in your table records that you can use.

ex. ThisItem.Asset.'Asset Name'

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

Thanks a ton @RandyHayes 

 

It worked exactly like I wanted it. Thank you so much for helping me. It was very good learning experience for me.

 

Thanks

Rajesh

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (3,420)