- [Instructor] Hi, and welcome
back to myexcelonline.com. Today, we are going to go
over how to use Power Apps to create a data entry form from scratch. If you want to learn more about
Microsoft Excel and Office, join our Academy Online course, and access more than 1,000
video training tutorials so that you can advance your level and get the promotions,
pay raises, or new jobs. The link to join our Academy Online course is in the description. So first, let's review how to
get to Microsoft Power Apps. I'm logged into office.com right here, and if I go up to the App Launcher, if I've used Power Apps before, I'll see it in this list right here. But if I haven't used Power Apps before, then I can go to All apps and scroll down to the P's for Power Apps,
and click it right here. Okay, so in order to
create an app from scratch, we're going to wanna
pick this first option right here for Blank app. So let's click on that, and we're just gonna start
with a blank canvas app, so I'm going to hit Create. And what would I like to call my app? So let's name this app Microsoft365Videos, and I'm just going to click
back here to Office for a sec, and I'm going to launch Excel. And if I scroll down, you can see that I have a spreadsheet right here called Microsoft365Videos. I'm going to click on that so you can see the data
source that I'm working with. Okay, so I've just listed
out some of our Office videos that we have on our YouTube channel, the category that they fall under, and a link to that specific video. So this is the data source that we're going to be using today in our sample inside of Power Apps. And inside my spreadsheet here, let me show you something
really important. I'm going to highlight all of my data, and I'm going to go up here to Insert, and I'm going to click Table, and I'm going to say yes,
my table has headers, and say, Okay. And if I go over here to Table Design, let's change this from Table1 to Videos. And this table right here is going to become our data
source inside of Power Apps. So we'll come back to
this in a little bit, but I'm going to close this for now. And if I go back to Power Apps right here, now you can see why I've
named this Microsoft365Videos. So let's set this up for a Tablet format, and just hit Create. Now you can see that
I'm in my blank canvas, and this is where I'm going
to begin building my app. So the first thing I
wanna do inside my app is I want to connect to my data source in two different ways. First, on the left, I wanna have a list of all the records
inside of my spreadsheet, and that is called a gallery. And then on the right, I
wanna have an edit form where I can edit those
individual records quickly, and those changes can be
reflected back to Excel. So let's do the gallery first. If I go up here to Insert, I can insert a vertical gallery, and I'm going to be prompted
for what data source I want. So I'm going to go down
here to Connectors, and then I'm going to select
Excel Online for Business. And each one of these already connects to my Excel spreadsheet, but I would like to show you
how I got these connections. So click on Add a connection, and then go to Connect. And I'm being prompted for a location, and I'm going to go to
OneDrive for Business, and then I'm going to
select OneDrive here. And then I'm going to be prompted for where my spreadsheet is, and I'm going to select the
Microsoft365Videos right here. And now I'm going to be
prompted for what table I want. Remember how we created the Videos table inside of our spreadsheet? Let's select that and hit Connect. This next question is
asking me whether or not I want to generate a unique
ID inside of my Excel table. Now, if you're familiar
with database design, this would be do I want
to insert a primary key? And what a primary key is is
a way to uniquely identify every row inside of your Excel table. So picture this, you have
two duplicate records inside of Excel, and for some reason you want two duplicate records in there. From a data perspective, this tool right here that
we're going to create does not have a way to know what row of those two duplicates should be updated. So an auto-generated ID
will have a unique way for only the selected row to be updated. So if you already have a unique
identifier inside of Excel, you can select this option and then you'll be
prompted for which column inside your Excel table
is the unique column. But in this case, I'm gonna have an auto-generated
ID in my Excel table. So I'm going to click Connect, and I can see the data has been pulled from my Excel spreadsheet. So let's make this a little bit bigger maybe to see four records at a time. So this is our gallery,
and if I click on this, and I look over here, I can
see Gallery1 inside my app. I'm going to click these three dots and I'm going to rename Gallery1 to something a little more useful. So let's just call this VideoGallery, because we are going to
refer to this later on. If you are liking this video, please give us a thumbs up, and subscribe to our channel, and hit the bell button to get notified when we release our weekly videos. Okay, the next thing we're going to insert is the form we talked about. So if we go up to Insert
and go to Edit form, I'm going to drag this over here and make it a little bit smaller. And then I'm going to assign
this a data source right here, and we can use the data source we've already created called Videos. And after that, let's go to Edit fields, and there are no fields
that have been selected yet. So let's go up here to Add fields, and let's add Category, Link, and Video Name and click Add. Now I can see Category, Link, and if I scroll down, Video Name. But I want Video Name to be
at the top of all of this. So I'm going to left-click
with my mouse button and hold it down while I drag up. And then I'm going to scroll up and click and drag up one more time, and now my Video Name will be at the top. So I'm going to X out of here, and I can see my Video Name, the Category and the Link,
and they're automatically put into three different
columns all the way across. But this is going to be
too short for a video name and too short for the link. So over here with columns, instead of having three columns, let's change that to one. And now you can see that
my data's been stacked, and that looks a little bit better. So let's test this out for a
minute, just so you can see how our changes play out step by step. I'm going to go up here
to Preview the app, and I can see the category
here and the link. So if I click on this,
nothing is happening, and that's because we have not yet programmed our form over here. So let's X out of here and do that. So if I select my form, there's a DataSource property up here, and you can see that that's set to Videos. That's the name of our data
source coming from Excel. But let's go down here to Item. And what we would like
to populate this with is whatever we have
selected in the gallery. So if I click over here,
and I type VideoGallery, which you can see down here, and then I type .selected, and I'm just going to click on
the word selected right here. And look, this has been
populated with Sway because that's the last one I clicked on. So if I go back up here
to Preview the app, every time I change my gallery over here, you can see that my form is now updating. And that's exactly what I want it to do. So let's say I come over
here and I edit my forms. If I click on Power Automate,
and go back to Forms, those changes have not yet been saved. So let's work on that next. I'm going to X outta here, and let's rename our form over here. Instead of Form1, let's go here and rename and let's call this VideoDetails. And then let's go up to
Insert, and go to a Button. And I'm going to drag
this button down here, and let's rename this button to btnUpdate. So that's now the name of my button, but the text on it still says Button, and that's meaningless to the user. So let's go over here, under Text, and let's just change this to Update. So now the user will know when he or she clicks this button, it will now update the
record on the screen. But we haven't put any code in here yet. So let's click this, and the
OnSelect property up here, we're gonna have to write some logic here but it's really simple logic. So instead of it saying false up here, let's just type submitform. If you've ever worked with HTML, you're kind of familiar
with the submit process, and how you submit a form to get the data back to the data source. So in this case, we need one
more argument for submitform, so I'm gonna put a left parenthesis, and what form am I going to submit? Well, I'm going to submit the one we just named VideoDetails. Close up the parenthesis. And now let's preview our app. So let's select Power Apps, and just put a Z at the
end, and hit Update. It's gone back to the server, and you can see that Power
Apps has been updated. So let's take that off,
and update it again, and you can see that our changes go back. So now we have a functioning update form. Let's close outta here. Now when you're dealing
with a data source, usually you have three
different functions. You have an update, you have an add, and then you have a delete. So let's look at next
how we can add a record to our record set here. So now for our add process, I'm just going to go
up to my update button and do Control + C, Control + V to copy and paste and move it over here. And then I'm going to
rename this one to btnAdd, and make the text over here say Add. Now if I select that button again, I can move it a little bit closer. But before we edit this code up here, I want to name these individual text boxes so that they make sense when
we're going to be coding. If I click on here for
Video Name, you can see, if I scroll down,
DataCardValue3 is selected. That's not really helpful to us, so I'm going to click on
these three buttons here and scroll down to Rename, and let's call this one VideoName. And then when I click on
the Category box right here, you can see that's DataCardValue1. Let's rename that to Category. And if I click on the link, I can see that's DataCardValue2. So let's scroll down
and rename that to Link, and you'll see why in a minute. And instead of saying SubmitForm, we're going to use a
function called Collect. And I'm going to put a left parenthesis, and you can see that the first thing I'm being prompted for is a collection. What data source am I putting the data that I am going to add
to the database here? I'm going to select Videos because that is the name of the table in my Excel spreadsheet and the name of my connection where I want the data to finally end up. So then I'm going to put a comma, and now I'm being asked for the item. So this gets a little tricky. Let's go back to my Excel
spreadsheet for a minute. Video Name with a space in it, and I'm glad we have a space because you have to handle spaces a little bit differently in your code. So Video Name here, Category, and Link. Those should be easy to remember. The only one that's a little bit different is Video Name has a space right here. I'm going to close outta there
and go back to Power Apps. So the first thing I wanna do here is I'm going to use this squiggly bracket that's right above the square
bracket on the keyboard. I wanna put in here the name of the column that we just looked at in Excel. And the first one, we're going to have to use a single quote because there's a space
in the name of the column. So I'm going to put Video space Name, and then close that single quote. Next time I'm going to put a colon, and then I'm going to put
the name of the text box in my form, which is VideoName, and this time I made that all one word. Hopefully, you can see why. I think using all one word is an easier way to code right here. And then I'm going to
put the property, .text. So that takes care of my first column, and then I'm going to put a comma, and next I'm going to put Category, coming from Excel, colon, and then Category.text
coming from my form. And then I'm going to put a comma, and I'm going to put link
as my category in Excel, but I can't leave that L lowercase. It has to exactly match in case, so I'm going to capitalize that, put a colon and then put Link.text. And then I'm going to close that bracket, and then close the parenthesis. So let's look at this for a
minute just so you understand. We are going to collect the data that we put into this form right here. We are going to put it into
our video's data source, which is the Videos table inside of Excel. In the Video Name column in Excel, we're going to put VideoName,
highlighted in blue. See right here how this
is highlighted in blue? This is that value, and the .txt is saying I want
whatever I typed in here. And then in the second column,
called Category in Excel, we are going to put Category in green, which is right here, .text. And then in the Link column in Excel, we are going to put the pink
box right here, the Link.text. Always make sure you
close up your brackets. And let's click off of there. There's no X and there's no warning signs so our code is most likely right. So let's preview this again. I have zz in my video,
my category, and my link. If I hit Add, and I scroll down, I can see my zz record has, in fact, been entered right here. So let's go back and look
at our Excel spreadsheet. And you can see that this row right here has been added from our app. So I'm going to close out of there and go back to Power Apps. So now let's work on our
delete functionality. Again, let's just highlight
one of our buttons, Control + C, Control + V to copy and paste and move this one over here. We'll call this button btnDelete. And over here in the text,
let's just type Delete. So for the Delete button, let's highlight all of this and delete it. And we're going to use
a function called remove with a left parenthesis, and we're going to remove it
from our collection of videos. And then I'm going to do a comma, and then I'm gonna type
in VideoGallery.selected, and end my parenthesis. So what this says is
that I'm going to remove from the collection of videos whatever I have selected in here, which is going to be the
same data that's in here. So let's test that out. zzz has been selected, and we really don't want
that one in our data set, so I'm going to hit Delete, and now I can see that it has disappeared. And if I go back to Excel
and open up the spreadsheet, it's now no longer in here either. So I know that my Power
Apps app right here is, in fact, updating, adding and deleting to my Excel spreadsheet. This No item to display here, just select another one over here and you can see that it will change again. So hopefully, this gives
you a good introduction to how you can use a data set from scratch in Microsoft Power Apps. You can do all the data functions that you usually need to do, which is updating, adding
and deleting records from an external data source. If you have any questions or comments, please leave them below
and we'll get back to you. As always, thanks for watching and see you again next time. If you want to learn more about
Microsoft Excel and Office, join our Academy Online course, and access more than 1,000
video training tutorials so that you can advance your level and get the promotions,
pay raises, or new jobs. The link to join our Academy Online course is in the description. - If you like this video,
subscribe to our YouTube channel. And if you're really serious about advancing your
Microsoft Excel skills so you can stand out from the crowd, and get the jobs, promotion, and pay rises that you deserve, then click up here and join our Academy Online course today. (upbeat music)
