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

[RandyHayes]

@tagustin 

Hi Teresa.  New Forum ID I see...welcome back!

So if the top part is a Gallery, the you would want to reference the label in the gallery by the selected item.

You don't really need an IsMatch for any of this from what I see, it is that you want to match what is in the top gallery with the value in the Gold and Silver and Bronze.

So the Fill formula would be:

If(Value(topGallery.Selected.lblGoldCSO.Text) = Value(Self.Text), Green, Transparent)

For the color (always best not to duplicate formulas!):

If(Self.Fill = Green, White, Black)

For the Sum, I would consider this formula for that:

Sum(ForAll([GoldLabelName.Fill, SilverLabelName.Fill, BronzeLabelName.Fill],
        If(Value=Green, 1)
    ), 
    Value
)

That will sum the labels that are green.  Remember...you already put the logic to compare in one place...the fill.

I hope this is helpful for you.

[tagustin]

@RandyHayes 

Thank you for the quick reply Randy. I really appreciate it! I ended up changing some of my formatting decisions as I decided to go for a red, white, and blue theme in honor of Team USA. Congratulations to you as it looks like you won the Gold medal in the scenario below 😊

In order to wrap up the screen, I have one last question. I would like to sort the scores from highest to lowest so it is easy to see who wins Gold, Silver, and Bronze, but I'm not sure how to do it since the scores are calculated locally within the app. Do I need to add a button to Patch the results back to SharePoint before I can sort on that column? As you can see, it is currently set to sort according to Full Name which is the Title column.

For the sake of anyone else trying to learn from this post, here are the formulas to achieve the look above. All of the fields seen above are SharePoint single line of text columns in SharePoint except for the Score column I added which is a number type column. I had to remove the Value function from the purely text fields (e.g. Top Sport) in order for it to work. The formulas are entered in the labels in the bottom gallery. "galFinalResults" and "lblGoldResult" are references to labels in the top gallery.

Sample Color Formula for a Number field (bottom gallery):

Randy, I didn't tell you in the original post that everything was based on single line of text type SharePoint columns. I didn't test it out yet, but is the Value function necessary in the case of numbers that are text? At first I thought it might impact the sum formula, but I'm getting accurate counts of correct answers so it doesn't seem to...

If(Value(galFinalResults.Selected.lblGoldResult.Text) = Value(Self.Text), FireBrick, Black)

Sample Color Formula for a Text field (bottom gallery):

If(galFinalResults.Selected.lblTopSportResult.Text = Self.Text, FireBrick, Black)

No adjustments were made for the FontWeight or Underline properties shown respectively below:

If(Self.Color = FireBrick, FontWeight.Semibold, FontWeight.Normal)
If(Self.Color = FireBrick, true, false)

Here is the Score label formula:

Sum(ForAll([lblGoldCSO.Color, lblSilverCSO.Color, lblBronzeCSO.Color, lblTopSportCSO.Color, lblUSMenCSO.Color, lblUSWomenCSO.Color, lblTopMaleCSO.Color, lblTopFemaleCSO.Color, lblTopCountryCSO.Color],
        If(Value=FireBrick, 1)
    ), 
    Value
)

In a spirit of giving back to the Power Apps Community that has shared so much with me, especially you Randy, I'm attaching my Olympic MS Form Questions/Settings and the Power Automate flow that records the responses back to SharePoint in case anyone else is interested in creating something similar for the upcoming Olympics. The doc doesn't include Power App screenshots yet since I have the Sum question, but I'm happy to attach that later as well once the details are hammered out.

Kind regards,

Teresa

[RandyHayes]

@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!! 😁

[tagustin]

@RandyHayes 

Hello Randy,

I posted a reply last night and it seemed to go through, but when I looked this morning it was gone so let me try again. First off, thanks so much for the revised formulas and detailed explanation! Second, sorry for forgetting to include the gallery formulas. Here are my original ones:

Top Gallery

SortByColumns(Filter('Olympic Contest', Title=ddContest.Selected.Title),"Title")

Bottom Gallery

SortByColumns(Filter('Olympic Contest Responses', 'Contest ID'=varRecord.ID), "Title", Ascending)

Since I wanted to be able to filter according to the Contest ID, I added a filter to the Items formula (bottom gallery) you provided. I'm not getting any errors so I think it is okay...

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
)

I replaced the Score and Underline formulas and tried to replace the bottom gallery label formulas, but I ran into a little trouble. I was getting errors until I used Intellisense to figure out that it seemed to want a "ThisItem" reference. No more errors, but we aren't quite getting the color-coding results we are looking for quite yet. Can you take a look and let me know what I need to adjust? I tried swapping the color references as an experiment. You can see that either way it doesn't seem to surface match indicators correctly.

Gold Label Color:

Silver Label Color:

Results:

Thanks Randy!

Teresa

[RandyHayes]

@tagustin 

Yes, I noticed the message too!  It showed up on my forum tracking app, but when I try to go to it in the forum, it is an error.  But I see the message anyway in my app.

Seems like your last response has similar info, so I will go with that and respond.  If I miss anything from the "mystery post", then let me know.

Yes, oops on my part.  I neglected to put the ThisItem in the formulas.  

What are the names of the labels that are circled below:

Those are the names of the labels in the LookUp formula that are needed.

[tagustin]

@RandyHayes 

Hi Randy,

Thanks for getting back to me. Are you sure you mean the bold labels? The names are lblGoldBG and lblSilverBG, but they don't hold any values. They are simply column headers. The Text property for Gold is "Gold" and the Text property for Silver is "Silver". If you mean the labels directly above them in the top gallery that actually record the Olympic results, the names are lblGoldResult and lblSilverResult. Those are the ones I referenced in the formula.

Kind regards,

Teresa

[RandyHayes]

@tagustin 

Nope, the labels with Gold and Silver, etc. are the ones that I am referring to.

It's a subtle thing, but this is a concept about streamlining the design process of a gallery with columns.

The formula:

If(LookUp(_matches, Item = lblGoldBG.Text, Value)=0, FireBrick, Black)

If lblGoldBG.Text is "Gold", that is what we are looking for then in the _matches table - a row with an Item of "Gold".

The Concept:

Usually, (for me anyway), I generalize any column label headers.  So they would be called like - lblHeaderCol1, lblHeaderCol2, etc..

Although this is not relevant perhaps to your app, the point/concept is about changed.  

SO...in my lblHeaderCol1.Text, perhaps I have "Gold" and in 2 I have "Silver"

Then I use that formula above and specify the label text of that column header to look up what it should be.

NOW...if I decide to switch Gold and Silver around in the columns, all I have to change is the header label...all the other values in the gallery will change accordingly.  So, I end up changing in ONE place rather than 2, 3 or more!

You need not go so elaborate with what you are doing and I don't mean to confuse anything.  I just know that you like to see things that make things simpler for changes and maintaining the apps.  So, if this concept is too confusing, or I have lost you on anything, just let me know and I will either make them clearer for you or simplify back to a basic formula that would not have those advantages, but would be perhaps easier to understand.

(oh, and yes, I skipped the queue today to get to this message in my list 😁 ... shhh, don't tell! 🤣)

[tagustin]

@RandyHayes 

Thank you for the explanation Randy. I didn't know that was possible so I really appreciate you explaining it. I'm now getting the correct results although I had to modify the Color formula just slightly to include the "ThisItem" reference. I'm thrilled it is working! Thanks so much 😁

If(LookUp(ThisItem._matches, Item = lblGoldBG.Text, Value)=0, FireBrick, Black)

I was doing some thinking about what you said early on about showing who was close to being right in the medal counts. I think that could certainly be valuable in the event of a tie for gold, silver and/or bronze. If you and I were tied with 6 correct answers overall and you were closer to guessing the correct number in the medal counts that neither of us guessed spot on, it would make sense to award you the gold and me the silver prize.

I wrote the following Fill formula to get the results you see below:

With({guess:Value(lblGoldCSO.Text)},
     If(guess = Value(lblGoldResult.Text), Transparent,
     guess >= Value (lblGoldResult.Text) - 5, Silver,
     guess >= Value (lblGoldResult.Text) + 5, Silver,
     guess >= Value (lblGoldResult.Text) - 10, Tan,
     guess >= Value (lblGoldResult.Text) + 10, Tan,
Transparent
     )
)

I'm interested in incorporating a slider whose default would be set to zero so I could drag it to the right an highlight the closest numbers in a more dynamic way, but I'm having a hard time figuring out how to do that. Seems like another With type formula would be in order, but I'm not sure how to construct it. 

There are a couple of reasons I think a slider would be a better option. One, it might not even be necessary to do this type of evaluation if there are 3 clear winners - in that case I'd prefer not to have anything shaded - and second, if the next closest answer is further out than 10 nothing will be highlighted per the way I currently have the labels hard-coded and I will have to manually go in and adjust them to get the results I am looking for.

Thanks in advance for your help. I really appreciate all the advice you are giving me!

Teresa

[RandyHayes]

@tagustin 

So actually, the Items property of the Gallery was equipped to give you the scoring without the formula.

More importantly, the scoring is done with the Abs function, so there is no need to determine both positive and negative...it will always be a positive difference.

In the prior suggestion, it was based on 0 being spot-on, 1 being 1 off, and 2 being 2 off.

But, since you are looking for 0, 5 and 10, then the formula would be this:

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

 The advantage of this is that it is a generic formula.  You can copy this into all of the Fill properties of all of the labels and will only need to adjust the HeaderLabelName like we did before.

NOW...tell me more about the slider in all of this.  I am not quite following clearly (see prior email) what role the slider would play.  I have a guess and it would work fine, but if you could provide me some example of what you are thinking...always helpful!