cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zuurg
Resolver I
Resolver I

Gallery Label showing sum of records from Sharepoint List

One of the screens I'm building in my app is supposed to show a list of feature requests for the app and give users the ability to Like or Dislike a request.  I have two Sharepoint lists to do this:

Feature Request (Title, Details)

Feature Request Voting (Feature ID, Vote)

 

When a user clicks on the Like button, a record gets added to the Feature Request Voting table with a Vote value of 1

When they click on the Dislike button, a record gets added with a value of -1

 

On the screen is a Gallery which shows the list of Feature Requests.  I had originally bound the gallery to the Feature Request list, but then when I decided I wanted to show the total votes for each item in the gallery, I got pulled into this clunky world of Collections and bound the Gallery to one of the many collections I had to make to be able to make it happen.

 

Here's my code for the Like button:

 

Patch('Feature Request Voting',Defaults('Feature Request Voting'),{Feature_x0020_ID:galRequestList.Selected.ID,Vote:1});
ClearCollect(colFeatureRequests,'Feature Request');

ClearCollect(colFeatureVoting,'Feature Request Voting');
//Group by and sum column code
ClearCollect(
    colTotals,
    DropColumns(
        AddColumns(
            GroupBy(
                colFeatureVoting,
                "Feature_x0020_ID",
                "GroupedItems"
            ),
            "Sum of Votes",
            Sum(
                GroupedItems,
                Vote
            )
        ),
        "GroupedItems"
    )
);
ClearCollect(
colFeatureGallery,
AddColumns(colFeatureRequests,"Votes",LookUp(colTotals,Feature_x0020_ID = colFeatureRequests[@ID])))

 

As you can see, I'm having to use 4 collections to do this: 1 to load the feature requests, another to load the voting, another one to store the totals per feature request, and a last one to add that total column to the 1st collection to bind the gallery to.  I'm guessing there's an easier way to do this.

 

Is there a way that I can gracefully (and quickly) show the total # of votes / feature in the gallery with less steps?  I'm assuming that a single collection of the Feature Requests list with an added column for the sum of the votes in the voting table has to be doable?

 

Bonus question: What would be the best way to prevent someone from voting twice?  I have a global variable varUser which is the user's email address (stored in the voting list as 'Created By'.email

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @zuurg ,

The below is free-typed, so check commas/brackets etc, but this should save a few collections hanging around

Patch(
   'Feature Request Voting',
   Defaults('Feature Request Voting'),
   {
      Feature_x0020_ID:galRequestList.Selected.ID,
      Vote:1
   }
);
With(
   {
      wTotals:  
      AddColumns(
         GroupBy(
            'Feature Request Voting',
            "Feature_x0020_ID",
            "GroupedItems"
         ),
         "VoteSum",
         Sum(
            GroupedItems,
            Vote
         )
      )
   },
   ClearCollect(
      colFeatureGallery,
      AddColumns(
         'Feature Request',
         "Votes",
         LookUp(
            wTotals,
            Feature_x0020_ID = ID
         ).VoteSum
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

12 REPLIES 12
WarrenBelz
Super User
Super User

Hi @zuurg ,

The below is free-typed, so check commas/brackets etc, but this should save a few collections hanging around

Patch(
   'Feature Request Voting',
   Defaults('Feature Request Voting'),
   {
      Feature_x0020_ID:galRequestList.Selected.ID,
      Vote:1
   }
);
With(
   {
      wTotals:  
      AddColumns(
         GroupBy(
            'Feature Request Voting',
            "Feature_x0020_ID",
            "GroupedItems"
         ),
         "VoteSum",
         Sum(
            GroupedItems,
            Vote
         )
      )
   },
   ClearCollect(
      colFeatureGallery,
      AddColumns(
         'Feature Request',
         "Votes",
         LookUp(
            wTotals,
            Feature_x0020_ID = ID
         ).VoteSum
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Hi @WarrenBelz ,

 

Here's a screenshot of the error that code gave.  

@zuurg ,

I was simply using your values - your question was how to condense the code (I cannot see your data structure). Go back to your lookup if it was working before.

Thanks Warren, I got it to work - issue was another column named Votes.  Removing it fixed it.

@WarrenBelz ,

I've adapted your method to another area of my app and it's working for 1 column that I'm trying to sum, but I'm also trying to add a 2nd calculated column which is the count of records matching a criteria.   The 2nd column is working properly, but it's giving me a delegation warning on the CountIf portion.

 

I'm trying to figure out how to handle that 2nd one without the warning.  Here's my code:

With(
    {
        wTotals: AddColumns(
            GroupBy(
                'Resources',
                "Project_x0020_ID",
                "GroupedItems"
            ),
            "HoursSum",
            Sum(
                GroupedItems,
                Hours
            )
        )
    },
    ClearCollect(
        colTestProjects,
        AddColumns(
            'Projects',
            "Hours",
            LookUp(
                wTotals,
                Project_x0020_ID = NewProjectID
            ).HoursSum,
            "Resource Count",
            CountIf(
                'Resources',
                'Project ID' = NewProjectID
            )
        )
    )
);

 

Hi @zuurg ,

You still have an underlying Delegation issue as both GroupBy  and AddColumns are "hidden" limits - they will only action the first xxx (limit) items, however if (like me) you do not like warnings, you can do this

With(
   {
      wTotals: 
      AddColumns(
         GroupBy(
            'Resources',
            "Project_x0020_ID",
            "GroupedItems"
         ),
         "HoursSum",
         Sum(
            GroupedItems,
            Hours
         )
      ),
      wRows:
      Filter(
         'Resources',
         'Project ID' = NewProjectID
      )
   },
   ClearCollect(
      colTestProjects,
      AddColumns(
         'Projects',
         "Hours",
         LookUp(
             wTotals,
             Project_x0020_ID = NewProjectID
         ).HoursSum,
         "Resource Count",
         CountRows(wRows)
      )
   )
);

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Thank you!  I'm getting an error though in the Filter portion of the With statement.  It's saying that the NewProjectID Name isn't valid.  'NewProjectID' isn't recognized.  (it's the ID field of the record in the Projects list so it's definitely there in both the list and the colTestProjects collection.)  If I try changing it to read 'Project ID' = Projects.NewProjectID, I get an error on the = saying Incompatible types for comparison.  These types can't be compared: number, table. 

Thank you!  I'm getting an error though in the Filter portion of the With statement.  It's saying that the NewProjectID Name isn't valid.  'NewProjectID' isn't recognized.  (it's the ID field of the record in the Projects list so it's definitely there in both the list and the colTestProjects collection.)  If I try changing it to read 'Project ID' = Projects.NewProjectID, I get an error on the = saying Incompatible types for comparison.  These types can't be compared: number, table. 

Hi @zuurg ,

I am simply Grouping by the field name you provided. Go to your list setting and choose Project ID and you will see the actual "original" field name at the end of the top URL. Also what is NewProjectID ?

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (4,601)