Count IsMatch Fields in a Record

[tagustin]

@RandyHayes 

Hello Randy,

I'm working on an Olympic app the purpose of which is to figure out which employees were able to make the most accurate predictions (I don't have time to learn Power BI or Power Query from the ground up right now). Employees submit their guesses via an MS Form which gets recorded into a SharePoint list and surfaced in the app as seen below.

The idea is to use IsMatch to see which results employees predicted correctly and tally a sum of the correct answers to the right so I can then use the sort icon to sort from highest to lowest scores to figure out who should win the Gold, Silver & Bronze medals. 

Originally, I was hoping to be able to write a formula that would compare what they entered (bottom gallery) to what is contained in the top gallery, but I'm having a hard time making that happen. I settled for writing If(IsMatch) formulas for the Fill and Color for the sake of testing to make sure the IsMatch function is working. It is, but I'm having trouble figuring out how to tally the correct answers in the label to the far right. Can you help me out?

Here are the Fill and Color formulas for the Green labels. The label in the top gallery is named "lblGoldResult".

Fill: If(IsMatch(lblGoldCSO.Text,"66"), Green, Transparent) Color: If(IsMatch(lblGoldCSO.Text,"66"), White, Black)

For the sum label I just entered a placeholder formula for now.

Sum(1+1)

I took a look at the Holiday Raffle app you helped me with that uses dynamic labels to "CountRows" for filtered items in a gallery, but I don't think it is going to help me in this scenario since it is more of a lateral count. Do I need to add a Yes/No type column in my SharePoint data source to record whether or not an item is a match and then add the "Yes's" together somehow? If so, what would that look like? Would a CountIf formula work?

Thanks in advance for your advice. I really appreciate your responsiveness.

Teresa

[tagustin]

@RandyHayes 

Hi Randy,

Thank you for the revised Fill formula. Here is the modified version that matches my header label for the Gold column. I had to add "ThisItem." before the _matches and add a comma after the Silver to get it going.

With(
    LookUp(
        ThisItem._matches,
        Item = lblGoldCSO.Text
    ),
    If(
        Value = 0,
        Transparent,
        Value >= 10,
        Tan,
        Value >= 5,
        Silver,
        Transparent
    )
)

It doesn't seem to be yielding the results we are looking for yet. Alice's guess of 60 should be showing up in silver, but it's not. 

Here is the latest Items property formula I have for the bottom gallery. Is there anything I need to adjust there? The Color and FontWeight formulas are working fine, just not the Fill. The label names ending with the word "Result" are references to the white "Winter 2022" row in the screenshot.

Sort(
  With(galFinalResults.Selected,
    AddColumns(
      AddColumns(Filter('Olympic Contest Responses', 'Contest ID'=varRecord.ID),
        "_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(lblTopSportResult.Text in 'Top Sport', 0, 100)},
            {Item: "Women", Value: Abs(Value(lblUSWomenResult.Text) - Value('US Women Medals'))},
            {Item: "Men", Value: Abs(Value(lblUSMenResult.Text) - Value('US Men Medals'))},
            {Item: "Top Female", Value: If(lblTopFemaleResult.Text in 'Top Female Athlete', 0, 100)},
            {Item: "Top Male", Value: If(lblTopMaleResult.Text in 'Top Male Athlete', 0, 100)},
            {Item: "Top Country", Value: If(lblTopCountryResult.Text in 'Top Country', 0, 100)}
          )
      ),
      "_score", CountRows(Filter(_matches, Value=0))
    )
  ),
  _score,
  Descending
)

If we can get this formula working, I think we can do without the slider for now. You have a lot of other people you are trying to help and I need to get back to my Onboarding/Offboarding app. I've made a lot of progress, but I'm sure I'll have questions for that one before all is said and done. Thanks so much for your help Randy!

Teresa

[RandyHayes]

@tagustin 

Yes, that fill should be Silver from the formula outcome. There is nothing wrong that I see on the formula, so perhaps a little troubleshooting...

Place a label in the gallery next to the guess number you are showing. Set the Text property of that test label to:

LookUp(ThisItem._matches, Item = lblGoldCSO.Text, Value)

You probably don't have a lot of space for the label, but try to squeeze it in there the best you can so you can see the value.

That will give some clue as to where to look next.

Sound like we're getting close!!

[tagustin]

@RandyHayes 

Hello Randy,

Thank you for the test label suggestion. It helped me figure out what I was doing wrong which was namely pointing to the wrong label.  I should have been pointing to the label called "lblGoldBG" which is the header label instead of "lblGoldCSO" which is the name of the gallery label that displays the number. Sorry about that, should have caught that based on your earlier thread messages. That being said, I still need a little help adjusting the formula so that only the numbers that are plus or minus 5 of the result reported in the top gallery are highlighted in Silver and the ones that are 5-10 points higher in either direction are colored Tan. Apologies again as I don't think I explained what I am going for to that degree of precision in earlier posts. How can I achieve that nuance? Please note that I only used your Fill formula suggestion for the Gold column at this point. As you can see it is highlighting more numbers than we want at this point.

Gold Bottom Gallery Label Fill Property:

With(
    LookUp(
        ThisItem._matches,
        Item = lblGoldBG.Text
    ),
    If(
        Value = 0,
        Transparent,
        Value >= 10,
        Tan,
        Value >= 5,
        Silver,
        Transparent
    )
)

Gold Test Label Text Property (blue font):

LookUp(ThisItem._matches, Item = lblGoldBG.Text, Value)

I did an experiment on the Silver label in long hand to see if I could get the results I wanted. Obviously, this repetitive approach is nuts, but I thought I would show it since it captures the intent of what ultimate Fill result I am looking for in case it sparks off any ideas of how we might be able to adjust your latest formula suggestion.

With({guess:Value(lblSilverCSO.Text)},
     If(guess = Value(lblSilverResult.Text), Transparent,
     guess = Value (lblSilverResult.Text) + 5, Silver,
     guess = Value (lblSilverResult.Text) + 4, Silver,
     guess = Value (lblSilverResult.Text) + 3, Silver,
     guess = Value (lblSilverResult.Text) + 2, Silver,
     guess = Value (lblSilverResult.Text) + 1, Silver,
     guess = Value (lblSilverResult.Text) - 1, Silver,
     guess = Value (lblSilverResult.Text) - 2, Silver,
     guess = Value (lblSilverResult.Text) - 3, Silver,
     guess = Value (lblSilverResult.Text) - 4, Silver,
     guess = Value (lblSilverResult.Text) - 5, Silver,
     guess = Value (lblSilverResult.Text) + 10, Tan,
     guess = Value (lblSilverResult.Text) + 9, Tan,
     guess = Value (lblSilverResult.Text) + 8, Tan,
     guess = Value (lblSilverResult.Text) + 7, Tan,
     guess = Value (lblSilverResult.Text) + 6, Tan,
     guess = Value (lblSilverResult.Text) - 6, Tan,
     guess = Value (lblSilverResult.Text) - 7, Tan,
     guess = Value (lblSilverResult.Text) - 8, Tan,
     guess = Value (lblSilverResult.Text) - 9, Tan,
     guess = Value (lblSilverResult.Text) - 10, Tan,
Transparent
     )
)

Here is the result of the "long hand" formula. I adjusted Alice's guess to be 10 over the actual silver medal result for the sake of testing. All of the blue font labels are based on the Text label formula you suggested for the sake of testing. Thank you Randy. I appreciate you. Teresa

[RandyHayes · Accepted solution]

@tagustin 

So, there is a subtilty between what you initially provided for your coloring and the "long-hand" formula you have.

In your initial formula (message #9) you used a >= in your formula for the tan color, that read "if it is greater than or equal to 10, then tan".  BUT, in your long-hand formula, you are stating that anything between 6 and 10 should be Tan.

So, if that is the case, then the fill formula should be:

With(
    LookUp(
        ThisItem._matches,
        Item = lblGoldBG.Text
    ),
    If(
        Value = 0 || Value>10, Transparent,
        Value >5 10, Tan,
        Value >0, Silver,
        Transparent
    )
)

That will account for what you have in the long hand formula.

See where that gets you.

[tagustin]

@RandyHayes 

Hello Randy,

Thank you so much for the revised formula. I should have been more clear about what exactly I was trying to achieve. I thank you for sticking with me. The formula suggestion above didn't work exactly as is. I'm guessing there needs to be some kind of notation between the 5 and the 10 in the "Tan" line, but I played around with the formula until I got it to do what I wanted so thank you for heading me down the right path!

With(
    LookUp(
        ThisItem._matches,
        Item = lblGoldBG.Text
    ),
    If(
        Value = 0 || Value > 10, Transparent,
        Value = 5 || Value >=1 && Value <= 4, Silver,
        Value = 10 || Value >= 6 && Value <= 9, Tan,
        Transparent
    )
)

Before I remove the blue test labels, here is a snapshot of the app. I tested a variety of numbers against the results in the top gallery and the Fills appear to work properly plus/minus 5 (silver) and plus/minus 10 (tan) respectively. That being said, let me know if you spot any errors in the logic I applied.

I have a couple more adjustments to make, but I'll post a link to "how to" doc once I'm done. 55 employees submitted guesses via the Winter Olympics Contest MS Form. I can't wait to load the actual results once the Olympics wrap up and see how it all works out. I'm confident this app will help me determine the winners much more quickly and efficiently than in the past.

Thanks again. You are the best 🤩

Teresa

[RandyHayes · Accepted solution]

@tagustin 

Actually, you need not be so specific in your new formula.  

I think in looking at what I replied with, that I just had a typo in it.

It should have been:

With(
    LookUp(
        ThisItem._matches,
        Item = lblGoldBG.Text
    ),
    If(
        Value = 0 || Value>10, Transparent,
        Value >5, Tan,
        Value >0, Silver,
        Transparent
    )
)

The way this works is in the order of evaluation.  If statements are evaluated that way...in order.

So, if the Value was 8, for example, the first criteria would be false - 8 is not 0 and 8 is not greater than 10.  So, the next part of the If is evaluated.  Is 8 greater than 5 - yes, so then the If stops there and returns the Tan.

If the Value was 3, then the first part would be false (3 is not 0 and 3 is not greater than 10).  The second part would be false (3 is not greater than 5) and the third If criteria would evaluate as true, because 3 is greater than 0...so Silver would be returned.

The trick to doing "leveled" If statements is to think about it backward.  Take the biggest first, then the next biggest and work your way down.  The normal thought process is the opposite - we start to think from the small to the large - that does not work well for leveled If's.

So again, I think I just had that typo in there before and you can use the above to save extra typing!

That is exciting that people are submitting guesses and I am anxious to see how it turns out.  Looks like I will no longer be the winner!! 🤣

Oh...you had mentioned a slider before - have you given up on that or do you still need to incorporate something in?

[tagustin]

@RandyHayes 

Hi Randy,

Thank you for the formula revision and detailed explanation! I entered them and they are all working great. Here is a partial snapshot of the Olympic app with current medal results loaded (there are a total of 55 entries minus you, me and Alice which I deleted). Top Female and Top Male are the only two categories I haven’t updated. Unfortunately, I did not include Nathan Chen in the list of male athlete MS Form options. He is the only US athlete with 2 medals at this point. I'm kicking myself on that one. He's awesome! 🤦‍

Here is a screenshot with the formulas in place. It is easy to see who is in the lead in the top 2 rows, but it gets a little muddier as you move down the list so I decided to adjust the formula range. I didn't realize our people would be such good guessers. They must have checked the Vegas odds before they submitted their guesses LOL.

Here is a screenshot with the new plus or minus ranges. I added a Gold fill. The line items I bordered in red are technically in an exact tie. In a perfect world one would be right under the other, but this is good enough. With 5 different medal count columns I think it would be too complicated to finetune it any further than this.

Because there are so many names on the list I would like to be able to sort either by the Full Name or Score column (score column should be the default sort, descending as it is today). This will make it easier for the contestants to find their name within the list. I tried a Reza method of sorting. It worked fine for sorting the Full Name column in Ascending or Descending order, but it didn't work for the Score column. Once the list is sorted by Full Name, we need a manual way to sort the Score column. I've attached a document with screenshots. I would also like the ability to Filter the score for the sake of using the Print function. Right now, I can only get 37 line items in a single screenshot so the way around it seems to be the ability to filter the list. The reason I am interested in using the Print function versus exporting the gallery results to Excel is that I would like to retain the Fill color-coding. I hope this doesn't throw a monkey wrench into how the Items formula was set-up since I didn't think of these things at the start. Apologies if it does. As you know, additional thoughts on what would be helpful once you start actually using the app often occur. The most current version of the Items formula is at the bottom of this post.

Here is the Fill formula for the Gold medal column. I think this solution eliminates the need to incorporate a slider. My primary goal is to spot who was the closest in the event of a tie so I think this formula achieves that.

With(
    LookUp(
        ThisItem._matches,
        Item = lblGoldBG.Text
    ),
    If(
        Value = 0 || Value>3, Transparent,
        Value >2, Tan,
        Value >1, Silver,
        Value >0, Gold,
        Transparent
    )
)

Bottom Gallery Items formula:

Sort(
  With(galFinalResults.Selected,
    AddColumns(
      AddColumns(Filter('Olympic Contest Responses', 'Contest Name'=ddContest.Selected.Title),
        "_matches", 
          Table(
            {Item: "Gold", Value: Abs(Value(tiGoldResult.Text) - Value('Gold Medals'))},
            {Item: "Silver", Value: Abs(Value(tiSilverResult.Text) - Value('Silver Medals'))},
            {Item: "Bronze", Value: Abs(Value(tiBronzeResult.Text) - Value('Bronze Medals'))},
            {Item: "Top Sport", Value: If(tiTopSportResult.Text in 'Top Sport', 0, 100)},
            {Item: "Women", Value: Abs(Value(tiUSWomenResult.Text) - Value('US Women Medals'))},
            {Item: "Men", Value: Abs(Value(tiUSMenResult.Text) - Value('US Men Medals'))},
            {Item: "Top Female", Value: If(tiTopFemaleResult.Text in 'Top Female Athlete', 0, 100)},
            {Item: "Top Male", Value: If(tiTopMaleResult.Text in 'Top Male Athlete', 0, 100)},
            {Item: "Country", Value: If(tiTopCountryResult.Text in 'Top Country', 0, 100)}
          )
      ),
      "_score", CountRows(Filter(_matches, Value=0))
    )
  ),
  _score,
  Descending
)

Thank you Randy! Teresa

[RandyHayes · Accepted solution]

@tagustin 

Hi Teresa!

For the most part, what you have should be giving you the results you want.

A couple of general comments...

Slight modification to simplify the formula on your Icon property (picking on Full Name here, but the story is the same for the Score column as well):

If(varSortColumn = "Title" && varSortDirection = Ascending, Icon.Chevron.Up, Icon.ChevronDown)

This just makes it a little more compact and less redundant. 

For the Icon color - again, slight change for compact:

If(varSortColumn = "Title" && varSortDirection = Ascending, Gold, White)

The Items property on the Gallery looks fine.  What is it doing/not doing?

[tagustin]

@RandyHayes 

Hi Randy,

Thank you for the reply and the more efficient Color and Icon formulas. I tried entering all of the formulas again today and it worked fine so not sure what happened the other day.

The final question I have is around filtering. I have print screen functionality operating, but I can only fit a maximum of 37 line items on the one page screenshot (there are a total of 55). I think the best solution is to incorporate the ability to filter the results by score so I can select 1 or more scores (currently they range from 3 correct guesses down to 0 correct guesses), but I'm not sure how to do that since the score is something that is generated within the app, text based (versus a Choice type column) and dynamic. I haven't had this type of scenario before in an app. I have a Score column in my SharePoint list so I tried incorporating checkboxes into the gallery with a "Select All" checkbox at the top and then patching them back to SharePoint, but it recorded all of the responses back as "3" instead of the correct score. Even if this is simply owing to a mistake in one of my formulas it doesn't seem very efficient when the information is already in the app and is being updated dynamically (which is why I'm not bothering to include the formulas in this post). Can you advise on the best/simplest way to go? One reason I enjoy your posts so much is that you find the simplest ways to effective solutions and take the time to educate us. I guess that is two reasons I enjoy your posts 😊

Here is the latest version of the bottom gallery Items formula. I haven't bothered to add a combobox control in the header bar yet as I'm not sure what Items property to enter into the combobox.

SortByColumns(
  With(galFinalResults.Selected,
    AddColumns(
      AddColumns(Filter('Olympic Contest Responses', ContestName=ddContest.Selected.Title, txtSearchBox.Text in 'Full Name'),
        "_matches", 
          Table(
            {Item: "Gold", Value: Abs(Value(tiGoldResult.Text) - Value(GoldMedals))},
            {Item: "Silver", Value: Abs(Value(tiSilverResult.Text) - Value(SilverMedals))},
            {Item: "Bronze", Value: Abs(Value(tiBronzeResult.Text) - Value(BronzeMedals))},
            {Item: "Top Sport", Value: If(tiTopSportResult.Text in TopSport, 0, 100)},
            {Item: "Women", Value: Abs(Value(tiUSWomenResult.Text) - Value(USWomenMedals))},
            {Item: "Men", Value: Abs(Value(tiUSMenResult.Text) - Value(USMenMedals))},
            {Item: "Top Female", Value: If(tiTopFemaleResult.Text in TopFemaleAthlete, 0, 100)},
            {Item: "Top Male", Value: If(tiTopMaleResult.Text in TopMaleAthlete, 0, 100)},
            {Item: "Country", Value: If(tiTopCountryResult.Text in TopCountry, 0, 100)}
          )
      ),
      "_score", CountRows(Filter(_matches, Value=0))
    )
  ),
varSortColumn,
varSortDirection
)

Thanks so much for your help. Once we work out this final piece, I will record the complete solution and post it here so others can benefit in case they have a use case where this information will come in handy.

Kind regards,

Teresa

[tagustin · Accepted solution]

@RandyHayes 

Hi Randy,

I realize you are busy so I went ahead and documented the Power App solution we have to date. Instructions with screenshots are attached for anyone that is interested. I can't thank you enough for your help! I may eventually add form controls to the app so our employees can enter their guesses directly into the app (would eliminate the need for separate MS Forms and Flows, but I'm happy with what I have for now. It's light years ahead of what I have done with past Olympic contests. The primary reason for starting off with an MS Form and Flow is that I didn't have the app built in time for people to get their guesses in before the start of the Olympic games. I have 2 years before the next Olympics so much more time to work on the next app iteration before the Summer games!

Kind regards,

Teresa