@tagustin
Thanks for sharing so much of your app to the community. Always plenty for folks to learn from a working app!
So...sorting!! That is going to actually change the dynamics of things - fairly drastically.
The reason is, in order to sort, you need the calculation in the items property of the gallery. Not a big deal, just that it changes things a bit.
Nice document! Except...I was looking for your Items property for the gallery. But I think I can guestimate from the doc.
So, I will not address the top gallery. I believe it is really only one record anyway!?
The bottom gallery is the place.
So, if the Items property of the Bottom gallery is essentially the list sorted: Sort('Olympic Contest Responses', 'Full Name')
The it would need some changes, and this is what would be needed (NOTE: I am guessing at the names of the labels in the top gallery...so correct as needed):
Sort(
With(galFinalResults.Selected,
AddColumns(
AddColumns('Olympic Contest Responses',
"_matches",
Table(
{Item: "Gold", Value: Abs(Value(lblGoldResult.Text) - Value('Gold Medals'))},
{Item: "Silver", Value: Abs(Value(lblSilverResult.Text) - Value('Silver Medals'))},
{Item: "Bronze", Value: Abs(Value(lblBronzeResult.Text) - Value('Bronze Medals'))},
{Item: "Top Sport", Value: If(lblTopSport.Text in 'Top Sport', 0, 100)},
{Item: "Women", Value: Abs(Value(lblWomen.Text) - Value('US Women Medals'))},
{Item: "Men", Value: Abs(Value(lblMen.Text) - Value('US Men Medals'))},
{Item: "Top Female", Value: If(lblTopFemale.Text in 'Top Female Athlete', 0, 100)},
{Item: "Top Male", Value: If(lblTopMale.Text in 'Top Male Athlete', 0, 100)},
{Item: "Top Country", Value: If(lblTopCountry.Text in 'Top Country', 0, 100)}
)
),
"_score", CountRows(Filter(_matches, Value=0))}
)
),
_score,
Descending
)
As always...beware of typos - typed the above freehand!
So, what is going on above??
First, we start with the list and then add columns to it. In the first AddColumns, we are adding a column called _matches. That will consist of a table of records. Each record in that table will have an Item and a Value column.
The Item column should have in this scenario, the same name as your header labels.
The Value property is a calculation. Now...I added something to this which is really only for a demonstration. I considered that perhaps there might be a thought of awarding people that were "close" or "almost" there. (NOTE: I did not do anything else with that concept in the solution above...it was just there for the concept and future need perhaps, or just future reference.)
So, the way the calculation works is - for the number values - it subtracts that result value and the guessed value (takes the Abs value of the subtraction so it is always positive). For Text entries, they either got it or they didn't, so either 0 (dead on) or 100 (way off). Also note that I used the In operator for the text. I believe this is always best in comparing text where there could be a letter-case difference. I don't believe that you will have one as you have these values hard-coded in the MS Form where you collect the data, but I added just to emphasize the text matching ignoring case.
Okay...the above is the first column added. We now have all the columns of the list, plus a new column with a table of the scores.
The next thing is we add another column where we get the score sum. That is pretty straightforward. We just filter the _matches table for any Value that is 0 (dead on) and count those rows.
So now we have a table with the columns of the list, the _matches column and a _score column. So we sort it now by the _score column descending.
We have the table now just as we want it for the Gallery. Next we move on to the colors.
Now, technically you can just leave them as you have them. All of the formulas you have for them will work.
But, the purpose of the Item column in the _matches was to do some color formatting.
Again, you need not do this as you have already put the effort into the formulas that you have, but, if you were to do it, it would go like this:
For the Gold Label (bottom gallery template) (and all other labels in the Gallery)
(also - responding to your point about the text in the SharePoint...it is not relevant with the formulas you have as you are taking the values from the text properties of the Labels...they are ALWAYS text! Even if it is a number, it is in a text format and using Value is needed. It also would not have impacted the Sum because the sum is based on colors, not values. And now, we really don't need it anyway - but you can keep it.)
For this, we are going to rely on the header label of your gallery to find the result in the items data.
If(LookUp(_matches, Item = goldHeaderLabelName.Text, Value)=0, FireBrick, Black)
This will take the text in the header label ("Gold") and will do a lookup into the _matches table to get the value, then based on that...set the color.
IF you were to colorize the "close" and "almost" type results, then the formula would look more like this:
Switch(LookUp(_matches, Item = goldHeaderLabelName.Text, Value),
0, FireBrick,
1, Green,
2, Yellow
Black
)
The Color formula for the text results would be the exact formula as above. In this case it is already done in the Items property, so we just need to match score.
For the FontWeight and Underline, you can keep the FontWeight formula, but change the Underline formula to:
Self.Color = FireBrick
The If and true false statements are all redundant - and just extra typing.
NOW...to the Score label.
This would change and the Text property would become: ThisItem._score
That is pretty much it. This will give you the sorting by score.
As you can see, it was a shift in the data. We really needed to have the score in the Items already instead of calculating it in the Gallery Template.
Some will say to just have another Gallery and sort the AllItems of the first Gallery and make it hidden...but this is really double work for your app and not necessary. And the method outlined above is VERY common as you build your app. There are always situations where you do some calcs in the gallery, only to find out that you want to do something with that above the gallery row level. So, not a big deal...just push it up a level - which is what we did above.
Just remember there are some things in the above that you don't necessarily need that were presented for some learning, and also that typos happen for me when freehanding formulas - may be a paren missing or a comma missing too.
I hope with all this that I won as well here as I have won in the contest!! 😁
