Microsoft 365 Community Community Demos, 21 Jul 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Power Apps Demo Extravaganza 2021 - Sports Stats Tracker

Description

In this 14-minute citizen developer-focused demo, Todd Baginski and the Canviz team deliver a Power App that allows team statisticians to capture statics, without having to take their eyes off the action. Record data points verbally. Flow variables call Azure Cognitive Services to handle speech to text conversion, other functions align stats to players and game, formatting for and storing stats in Dataverse, and updating Power App UI. Review and edit past stats. Use for any sport. This PnP Community demo is taken from the Microsoft Power Apps community call - July 2021, recorded on July 21, 2021.

Demo Presenters:
• Todd Baginski (Canviz) | @toddbaginski
• Matt Schuessler (Canviz) | @mattschues
• Hubert Sui (Canviz) | @HubertSui
• Sarah Meyer (Canviz)
• Damian Gibbs (Canviz)

Supporting documents:
• Demo Extravaganza 2021entry | https://powerusers.microsoft.com/t5/Demo-Extravaganza-2021/Sports-Statistics-Tracker-Speech-to-Text-to-Dataverse/cns-p/945380
• Documentation - What is the Speech service? | https://docs.microsoft.com/en-us/azure/cognitive-services/speech-service/overview
• Video - Speech to text in PowerApps with Power Automate by Yash Kamdar at Hyd Power Apps & Power Automate UG | https://www.youtube.com/watch?v=g034jMgilGM
• Blog - Convert Speech to Text in Power Apps | https://kamdaryash.wordpress.com/2020/02/03/create-a-pdf-file-of-text-converted-from-speech-recorded-in-powerapps-using-azure-cognitive-services/
• Repo - Azure Function - WebM to Wav Audio | https://github.com/markharrison/azfunction-webm-to-wav
• Blog - Create a Power Automate (Flow) that creates an HTML File Using the Text Obtained from the Output of Speech to Text. | https://kamdaryash.wordpress.com/2020/02/05/create-a-pdf-file-of-text-converted-from-speech-recorded-in-powerapps-using-azure-cognitive-services-part-3/
• Documentation - Use expressions in conditions to check multiple values | https://docs.microsoft.com/en-us/power-automate/use-expressions-in-conditions
• Chrome web store - Metadata Browser for Dynamics 365/Power Apps | https://chrome.google.com/webstore/detail/metadata-browser-for-dyna/ahbljnhlfdbecefhilipmpnlfldnplpb
• Article - Setting Lookup in a Flow CDS Connector: Classic vs. Current Environment connector | Power Automate Quick Tip | https://d365demystified.com/2020/12/03/setting-lookup-in-a-flow-cds-connector-classic-vs-current-environment-connector-power-automate-quick-tip/
• Blog - Microsoft Power Automate: Setting Up Lookup Value Using the CDS Connector | https://www.velosio.com/blog/2021/02/25/microsoft-power-automate-setting-up-lookup-value-using-the-cds-connector/
• Article - [POWER AUTOMATE] HOW TO SET LOOKUP FIELD VALUE IN COMMON DATA SERVICE (CURRENT ENVIRONMENT) FLOW CONNECTOR | https://linnzawwin.blogspot.com/2019/11/power-automate-how-to-set-lookup-field.html
• Package - Microsoft.Recognizers.Text.Number | https://www.nuget.org/packages/Microsoft.Recognizers.Text.Number/

Stay connected:
• Twitter https://twitter.com/microsoft365dev
• YouTube https://aka.ms/m365pnp/videos
• Blogs https://aka.ms/m365pnp/community/blog
• Call attention to your great work by using #PowerAppsCC and #PnPWeekly on Twitter.

A

This app has happens to be one that I had something to do with and and everybody that I work with every day, and this app is the sports stats tracker. We haven't had a better name for it yet, but a lot of people came together to build this. So that's me on the right. Obviously, matt's on the left. Matt's gonna do a demo of this one sarah's on the call too, and so give you a little heads up on everybody here and what we did on it so matt.

A

I I had the idea I talked to matt about it. Matt was like that sounds fun. Let's do it so matt and I kind of pulled together a prototype of it and we got it working and the end and it looked really bad and then damien. We looked in damien on our team and damien.

A

Helped us organize the screens better and put a really nice flow into it, so it made sense in our ux and then sarah came along and made it not just organized, but she made it look really pretty and did our design on it and then, when we were done with all that hubert took the design and applied it to our power app, and so we build power.

A

Apps like this all the time really in in real life too, and so we were all very familiar with these roles and kind of hand how to hand it off to each other, and do that. So I know we're pretty over on time. So I'm gonna go quick through my part here. So why do we make this app?

A

Well, the app started as an idea during my son's lacrosse practice a couple weeks ago or a couple months ago, and it started out because we were talking about one of the moms who helped record stats for the team and how she actually didn't even see her son score a goal one time because she was so busy looking at the stat book recording it. So our process used to work like this. We had two people recording stats.

A

We had one person who all called a spotter, that person watches the game and they audibly say out loud when they see something happen. Like number one got an assist or number two got a ground ball or number three scored a goal.

A

The recorder is listening to that and trying to watch the game, but they're looking up and down the whole time and they're, looking at the stat sheet in our stat book and they're, trying to listen to what that person said and find the spot and mark it down and right at that moment is when someone missed their kids scoring a goal, and when I heard about that at practice I was like that stinks. I was like we got to find a better way. So then you all everyone on. This call probably knows this right.

A

Like people know you as the computer guy and so all of a sudden it starts oh todd. You should make an app like that, and hence the idea was hatched, so the idea was create an app where we don't have to have two people doing it and you don't need to touch a paper and pencil and look down and you can look at the game.

A

The whole time and watch your own little personal all-star go out there and make fun plays, and then you know enjoy that part of your life, and so that's how this app came about.

A

Let me tell you how it works.

A

We have this deck available after the call to if you want to dive in more, I'm not going to touch on every detail because we're over time but I'll give you a high level overview of what's happening. We start in the top left and we say something to our powerapp.

A

Like number 12 goal number 14 assist, that's exactly how we will say it into the power app then that power app takes that that audio data, the actual audio data that we recorded and it throws it at a flow, and it also throws the id of the game as you'll see in our app which game are we doing this stat for after that? From this point, all the way till we get back to purple we're in a flow.

A

The first thing the flow does is it has to go out and call an azure function to use ffmpeg to convert the audio format to wave the reason we have to do that is because azure cognitive services on our next step, we'll take that wav file and then turn that speech into text, and so at this point this is actually what it looks like we said, number 12 goal number 14 assist it's exactly what we're getting back from cognitive services at this point, we're like sweet, it's it's! It knew what we said.

A

So we then split this into an array and we split on the word number, which gives us exactly what our stats are and which player recorded them in the shape of an array. Now I made that sound like oh, that was easy to do. We just ran split matt and I sat around for hours trying to figure out the right way to do this and get us to the point where we could iterate through it.

A

The reason we wanted to split is because sometimes actions fast in sports right, you sometimes you may need to say number 12 goal number 13 assist right after each other or other rapid fire statistics, and you can't just record and wait till it records in the database and then try to remember what else happened to hit record again. So we wanted to be able to hit record, say a bunch of things and then say go process. So that's why we needed to come up with the array.

A

After we've got the array we convert it to digits with an azure function, shout out to matthew, devaney and bob german on this one. I I didn't know how to go about this. I put a shout out on twitter. People came back and said: hey todd, here's a library that'll, do that. I said perfect, because what I needed to do is we needed to take this 12 and turn it into a number 12.

A

So we could do easier database lookups in the signing statistics.

A

So we did that inside of an azure function too, then we're back in flow as you can see the whole way through its flow there's some connections to dataverse as well. We figure out which stat was recorded and we we put it in a variable, so we're looping through the array here in this whole gray, section row by row. The first time I go through here you can see I'm going to get goal is my stat and I'm going to get 12 as the player.

A

Who did it now that I have all that we're essentially doing what we like to call the up cert right. Basically, if the record for this particular player in this game for this stat has not been recorded, then insert a record. If not, then figure out, if they've already recorded a stat, what is their current, how many times they recorded the stat in the game and increment it by one, and so that's really all of what this is doing here, I'm not going to go into them one by one.

A

I bet most of you probably read it already.

A

So essentially that's what we do we loop through one and one, and then we go in the data verse and insert or update depending on if the player's recorded the stat and eventually it will look like this. In this example. I've have here, but we do have some variables that we need to set that allow us to do this upsert type operation, so we have to reset those before we go to the next row in our array and when fun we're finally done, then we head back to powerapps.

A

So that's what this does and now matt is actually going to show you it working.

B

Awesome thanks todd. uh I won't spend too much time here, just wanted to have an opportunity to show my beautiful family off. So uh that's what this slides all about, but um yeah. uh Okay, that's me. Let's uh jump forward, so I'm gonna go ahead and share the app I'm gonna just share the the app window here. um Okay, so here's the app. um I have the screen in a uh kind of form layout, because we did our phone layout because we built it for our phone form factor.

B

So this is our home screen on the home screen. We can do different things. We can start a new game process which we'll do in a second or we can view previous games. So they have the game date uh who the teams were what the final score was. If it's been logged, if the home team won, we have a little trophy for to signify that and then you can just click on here to view those game details.

B

We have a nice little uh top image with drop shadow and which is kind of a nice unique idea that uh sarah built in with the design. So let's go ahead and go through the process, so we'll click on record game. The first screen is: we need to um set when the gate game date was so we'll say it's today and we'll home team will be the eagles and the opponents will be the sharks, so we'll go, save and continue all right.

B

So here we are at the recording screen so um using the voice input control. So I'm going to go ahead and simulate this. So here we go number 12 goal number 14, assist number seven penalty and then, when we're done hit hit the button again and you can send just one stat or multiple as we've shown so right. Now it's uh running through that process. It's talking, it's converting the file to wave it's. You know um going through azure accounting services and you can go ahead and continue.

B

But what's nice is it's going to return back the stats that we sent and the first time the function runs? It takes a second, but here we can see below. We can see 12 goal. 14 assists 7 penalty.

B

If we need to make any changes, we can certainly do that if it, um if we set a stat that didn't exist or you got confused there for a second, um you can click on these guys and change that up to an approved stat for this game, so go ahead and we can either submit and record more or we can uh review stats.

B

So, let's go and review stats.

B

Okay, so right here we can see uh the current stat record, so we have our numbers and then we have the stats. So you can see a goal here for this guy um an assist and then a penalty so recorded everything nicely.

B

If you'd like you can go ahead and go ahead and edit a row if you'd like if you've got some time- and you want to fill in some names- so let's say todd, so we can go ahead and and lock in the players names at any point or we can even add manual a whole new record manually.

B

We can go back to record more stats or we can do save and continue. If that's that and you can put in the final score here. So obviously the eagles gotta win and so we'll say, save and go home.

B

Okay, and here we have it, uh five to two equals versus the sharks got the little trophy because we won. So you can go ahead and click on view to go back in. If you need to do any edits, add any add any mistakes or anything like that say: matt, okay and then go ahead and save that all right, so we'll go save and go home.

B

Okay! So that's our interface for this all right. Thanks todd.

A

Thanks matt yeah, so that's our wrap everybody. That's that's essentially what it does and and how it goes about it there. The thing uh I'd like to point out about the app as well. Is we dialed this in for lacrosse, because that's the sport, my son was playing at the time we we thought this up, but you could change this for anything right. This could be soccer baseball basketball. I mean you name it. All you have to do is change the stat name, columns and, and things like that and you're good to go.

A

I was gonna deep dive into how we constructed our upsert, but given the time that we're over I'm not going to uh spend some time on that, I'm sure you could check that out. Matt and I'll be um making some videos about these going forward to kind of deep dive on the code inside of them. Another thing that I was going to deep dive is how to set a lookup field value inside.

A

But, more importantly, I would like to say thank you to all these people who wrote all these articles. Only two of these articles came from microsoft like themselves.

A

Every single other article or add-in we found here is something that I kept track of along the way that helped us figure this out and, as you look through you see name after name after name of someone in there, we didn't invent how to do this and- and we took what other people had did in little pieces, and we put it all together to build our app.

A

So thank you to everybody- and this was the first time I'd ever seen- the metadata browser for dynamics 365 or powerapps, and if you're not using that and you're doing powerapps, you really should go check it out. It makes your life way.

B