Get top 3 records from SharePoint list

[rwittels]

I have a quiz app which saves a name (varNickname) to a Nickname column and a score (userScore) to a Score column in a SharePoint List. My scoreboard is a gallery which orders names by score:

SortByColumns('Quiz Scoreboard', "Score", Descending)

At the end of the quiz, I want to navigate to a screen which has 3 labels for Gold, Silver and Bronze. I can't use a gallery as each label has a different colour background. I would like to display the corresponding Nickname in each label for the top 3 scores e.g. Highest score Nickname in Gold, second highest in Silver and third highest in Bronze.

[timl]

Hi @rwittels 

Here are the formulas to return these 3 top users:

Gold:

First(SortByColumns('Quiz Scoreboard', "Score", Descending)).Nickname 

Silver:

Last(FirstN(SortByColumns('Quiz Scoreboard', "Score", Descending),2)).Nickname 

Bronze:

Last(FirstN(SortByColumns('Quiz Scoreboard', "Score", Descending),3)).Nickname 

[RandyHayes]

@rwittels 

I would still recommend a gallery for this to reduce your overhead.

You can set the colors and backgrounds based on the data in the Gallery.

So, your items property on the Gallery would be:

With({_top3: First(SortByColumns('Quiz Scoreboard', "Score", Descending), 3)},
    ForAll(Sequence(CountRows(_top3)),
        With(_record: Last(FirstN(_top3, Value))},
            {Nickname: _record.Nickname,
             Score: _record.Score,
             Place: Value
            }
        )
    )
)

The Label for the Nickname in your Gallery would be:

    Text property :  ThisItem.Nickname

    Fill: Switch(ThisItem.Place, 1, Gold, 2, Silver, 3, GoldenRod)   - GoldenRod is as close as a named color to bronze.

The significance of this is that your items in the Gallery will have the nickname, the score and the Place (1st, 2nd, 3rd - 1,2,3) that you can then use on properties in the gallery to color as you need.

I hope this is helpful for you.

[rwittels · Accepted solution]

Thank you both for the responses. I actually figured out a simple way to do it with a gallery:

FirstN(SortByColumns('Quiz Scoreboard', "Score", Descending),3)

The gallery background is transparent so I overlayed it on my colours and just set responsive positioning properly.

I then just set the Score Visible property to false and positioned the Nickname field nicely. Works great.

[RandyHayes]

@rwittels 

Good deal!  I'd still consider providing the placement in the Items formula to the gallery as I demonstrated and then just setting the colors based on that.  It will be far simpler than trying to do responsive placement...especially if you then decide to make changes to the layout.