Rank Order based on number of entries in a column with that value

[abrae005]

Working on a "Voting" app for my team. The voting part is working well, just running into a bit of a challenge on the "tabulate the results" part.

I've got a data table that looks similar to this:

Voter	Best_Costume	Scariest	Pumpkin
Calvin	5	12	22
Hobbes	5	13	22
Susie Derkins	7	12	22
Calvin's Dad	5	13	22
Calvin's Mom	3	14	21
Spaceman Spiff			22

(The actual data is in a SharePoint List, but also a collection within my app). I'd like to put the results in either 3 galleries or 3 lists, showing the item number, and the number of votes it received, sorted by the # of votes received.

In the example above, 1st place in Best Costume would go to #5, with 3 votes. 2nd place would be a tie between #7 and #3 with 1 vote each. 1st place in Scariest would be a tie between #12 and #13 with 2 votes each, etc.. How would I go about setting up these galleries? (Looking primarily for the "Items" property that I'd need to put in)

Other possibly useful information:

• Each voting category could have different numbers of options - So, there could be 30 different choices for Best Costume, 15 choices for Scariest, and only 3 choices for Pumpkin. But that doesn't really matter since each category (column) is independent.
• Not every voter needs to vote for every category
• We don't care who voted for what, just how many. (The Voter column is just so people only vote once)

[JR-BejeweledOne]

I love that you used Calvin and Hobbes as your examples!    How does the data table you show in your example correlate to the item being voted on?

[abrae005]

There's a second sharepoint list, with items to vote on.

ID Number	Submitted By	Category	(Photo is attachment on SP List item)
5	Garfield	Best_Costume
6	Odie	Scariest
7	Jon	Best_Costume

Those items are in galleries by category. When a voter picks a photo from that gallery and presses the vote button, the ID # of the photo they voted for is recorded (along with their name) in the appropriate column of the "Votes" list (which is the table example shown in the original post)

[JR-BejeweledOne]

I am curious right away why you are tabulating votes accorded to the user rather than the item.

[abrae005]

I thought recording the votes with users as rows, and category as column would be an easier way to make sure each voter only votes once in each category, and it also allows voters to come back and change their vote later if they change their mind about which picture they like the most.

[JR-BejeweledOne]

For starters I would also record the vote against the item that is being voted on.  You can take votes away if people change their votes later.    This will then be used in your other galleries for sorting by vote for each item.

[abrae005]

So, you're saying that I should add another column to my "Photos" list, something like NumVotes, and then if someone votes for that item, increment NumVotes by 1? (and then find a way to decrement it if they change their vote?)

Here's the current code for the "Vote" button on the "Best Costume" page. Halloween_Votes_2021 is the SP list where I'm storing the votes (by user). I think I'd want to keep this code, and then add something to update the NumVotes column in the other list (Halloween_Photos_2021). Any suggestions on how to implement that? (Adding 1 would be easy enough, removing one if someone changes their vote would probably be a little more difficult)

If(IsBlank(LookUp(col_Votes, Title=_CurrentUser.FullName)),
    Patch(
        Halloween_Votes_2021, Defaults(Halloween_Votes_2021), {Title: _CurrentUser.FullName, Best_Costume: gal_BestCostume.Selected.ID}),
    Patch(
        Halloween_Votes_2021, LookUp(Halloween_Votes_2021, Title = _CurrentUser.FullName), {Title: _CurrentUser.FullName, Best_Costume: gal_BestCostume.Selected.ID}));
ClearCollect(col_Votes, Halloween_Votes_2021);

[JR-BejeweledOne]

Yes, that's what I am saying, otherwise you don't have a way to tie the number of votes to the item that was voted on.  

How are you displaying the pictures?  Are they in a gallery and each one has a button to press or something else?

[abrae005]

Yes, the pictures are in a gallery. There is one vote button on the screen (not one per gallery item), and it uses the ID of the item that is selected in the gallery. The grey item in the gallery is the one that is selected.

[JR-BejeweledOne · Accepted solution]

Ok, so on click of the button you would do something like this:

Patch(DataSource, ID = gal_BestCostume.Selected.ID, {NumberColumn: NumberColumn + 1})

If that is problematic, create a variable to use for this on App start or on visible of the screen.

Set(varVotes, varVotes + 1);

Patch(DataSource, ID = gal_bestcostume.selected.ID {numbercolumn: varVotes})

I am not sure how you have it setup for someone to change their vote, but the premise is the same.  When they deselect an item, you would subtract 1.   If you were setting a variable to do that it would be like this:  Set(varVotes, varVotes - 1).