Getting Started with PowerApps and a Microsoft SQL Database

since prehistoric times human evolution is signposted by many milestones in history from the first humans that created fire to those it picked up Spears and made stone axes to elevate themselves above all else if we've learned one thing from those ancient ones is that we all must evolve to better ourselves in this world as a humble parrots developer so must you evolve so you can reach further and build bigger and more complex applications as your experience grows just like those prehistoric humans who picked up tools for the first time a milestone in your power-ups evolution

will be when you start building applications they use enterprise level storage solutions like Microsoft sequel let's take our first steps into a bigger world on the screen right now is my company or my organization's Azure portal as you can see we have a whole ton of services that we can spin up and run in marks of Asia what we're concerned about in today's video and what we're going to be highlighting in today's video is the ability to create sequel servers and spin up sequel service really quickly in Microsoft Azure so let's just go ahead and

do that and that way we can get into our powerup and I can show you a bit more on how to connect power apps into Microsoft Azure and then build an application around a relational database versus using a flat database source like a SharePoint list let me click on add database I'm just I'm using the free trial I use an existing resource group that I've created already my database name will be Chino DB and I'm gonna create a new database server to host this on and it's going to be called Gina s DB SRV and

the full server name will be Chino DB SAV data based on windows.net my server admin login will be Chino admin and my password will be for me to know and I'm gonna click OK I'm going to take everything else as default and click create okay that's the deployment of those two resources complete let's go take a look at what we have here so in our sequel database section we can see our database which if I go home and I click all resources I can see both those resources there which is there the server and the

database that's created underneath the service so let's go to the server first tap setup here is the firewall now there's a couple of settings here we need to do to make sure that this works properly one we need to allow Azure services and resources to access the server you'd make sure that that is set to yes that will ensure that services like power apps and power automate can connect to our database now the one last thing that I need to do is I need to create a rule for my particular desktop and why I need

to do that is because I'm going to be using an application called sequel management studio to connect to the database so I can configure it well I can configure things like tables and procedures and that basically build our database for our application and a simple way to do that here is just to add my client IP to the firewall rules and that will add my IP at the moment to to the firewall rules here which will allow my desktop to connect to it click Save now we can go in and configure other things like resources

the size of the database how much how big the database will grow and how many service cycles we want to set to our particular database while we how much we want our server to use like from cloud resources point of view I'm not going to go into that today because I really don't want to deep dive too much into Azure too much because it like it is a huge subject and well I'll probably spend a half an hour just going through at least half an hour just going through the real just touching on some of

the basics of this platform I think for today's example I'm gonna keep it show you they're just the bare minimums that we need to get this up and running and it's really gonna be up to you guys to to explore that further but things like setting up these different parameters will obviously also affect the costing and the pricing of your server setup and will affect how much how much credit your server setup will chew through so obviously these kind of things will probably need to be tuned a little bit especially if you're just sort of

trying this out you don't really want your server to be using up a ton of resources because that'll just cost you a whole heap of more money but again not going into that today that's really all we're going to need to get our our database to connect to our database them to configure it in sequel management studio so I'm gonna leave a link to sequel management skier in the in the comments below again I'm not going to show you how to install that either because it's a really straightforward application just download it and install it

and meet me back here at the video in the next step you should be looking at something very similar to what I'm looking at here this is a connect to server screen and it's expecting our logon details so we can connect to our database and allow us to proceed or continue on further with our installation so the first thing we're gonna need is the server name and if I go back to our asier screen we go home chief oak bring up ok i'm gonna click on i'll server we can see our details here so our

server name is listed conveniently on this screen so let me click on copy to clipboard go back to sequel management studio and paste that ow ow server admin that we configured when we created our server was just was I completed it was chino' admin and I'm gonna type my password in and I'm click connect like I said in this particular example we're gonna go for a two table structure which is really about as simple as a relationship can get and I thought for today I am going to create two tables one table that will store

the details of what I have in my stock and it'll just be a simple list of items that I keep on hand in my stock levels or in my on my shelf stock shelves and it you know it'll also contain things like how many items I have in each particular stock item now the second table will be a order form so once stock levels reach a certain level say say I've got I keep a whole heap of diodes on hand and when my diodes reach say when I reach a level of say I've got ten

dyads left in stock I want to create a new order record that will generate an order to my supplier who will then restock my warehouse so let's create our first table and this table will be used to actually store our items in our warehouse or our inventory so the first thing we need and we will typically do this for every single sequel table that we create we want to create a primary key which which will be on identifier for each particular record and will be your unique for each particular record now if you work a

SharePoint you know that each SharePoint list item for example gets an ID field and we can do the same thing for sequel we in fact we really need to do the same thing for each one of our sequel tables to maintain that primary key and that that identifier for each particular record so I'm gonna make that an integer and I'm just going to call it ID it'll never we never want it to be null but we really don't need to worry about that because this will be automatically incremented by by the database server we don't

need to set this value this will always be unique and will be set automatically when we create a new record to ensure that happens we need to right-click on that field income click set primary key and then if we highlight that and we scroll down here we've got a parameter here or a property here called identity identity specification we expand that I like that we just want to change that to yes now this ball is set as the identity and will automatically increment each time we create a new record what else do we want to

store about our inventory item well we want to store a name and we'll just make that of our child which is effectively a string you want to set a description again we'll make that a bar cha and let's make a vehicle stock level and this is going to be int and I think for this example we'll just keep it at that click on save I'm gonna call this table stock actually just click refresh on that see we've got our table our stock table now we want to create another table here to store our orders in

now just like with the stock table we need a primary key we want to set that as the identifier now in here we want what we want to store here because we we are storing our orders of stock we want to actually create a pointer to which stock item we're creating in order for so because because we have created a identifier in our stock table or we need to store here is the stock ID of that particular stock item and we know that was an integer and let's store on a date that's a date and

time field let's maybe amount and let's put the description of the order alright click Save and let's call that orders so now with those two tables created the other great thing that we can do in sequel management sphere is that we can run sequel scripts directly on to our table so I'm gonna run a particular script that's going to create some dummy records for us so we can see a little bit more of how this works in PAP so it'll make it easier to see things in power apps so under our stock table let's let's

go down and well let's select our stock table and click on new query and in here I'm just going to run this insert into sequel query that will generate these three particular records and it's just going to create some existing stock for us in that table like I said to make it easier to demo this in power apps for you so let's click on execute that's generated three rows in our sequel database and we can even go right click and go select the top a thousand rows and it'll show us that we've got this three

records now stored in that table let's do the same for orders select orders new query let's just paste this particular query in the query window and this is just going to generate a few orders for us against our order table all right so we've created forwarders if we just do a right click on that and select the top thousand rows we'll see that those orders have been created terrific all right we've got our team dummy data populated in our database now we can the next stage will be going to power ups and actually connecting to

our database and taking a look at what this actually looks like and how this is how we access data sequel data in our power app application okay go power ups open list is crowding you Kandice app and let's call this order stock orders okay so just to give you a little bit of a demo here let's create a connector and I'll select the sequel connector and add a connection the connection type will be sequel server authenticated and our server name let's our server name address Gina DB serve a database was a Chino DB now username

will be DB user 1 now let me explain quickly off-camera I actually went in and created a sequel user just for that just for the Chino DB database now why I've done that is probably not really even necessary for this particular demo I could have used the chena admin username password that I created when I set this server and database up in the first place and that would have worked fine why I did this was in sort of more of a real-world scenario you would have a separate user if you were using sequel server authentication

you will most likely create a separate user to connect to that database and that you know that's really around thinking about security and and segregating security across different databases on a database server not the most secure if you're using the same username and password across all your databases in your own you know on your server and that's you know ultimately and that's not really realistic either so off-camera I did create this user and I've done that off-camera because I really don't want this video to become you know more of a sequel tutorial sequel is a

whole other massive subject and there's just a huge amount to cover even to go over the basics with sequel so it's something moving forward if you are going to be using sequel with your application and as a as a sort of database back-end for your power apps applications you know there's you're really either gonna have to start getting your head around how to use sequel and how to sort of set sequel up and the ins and outs of it either that or you work alongside a sequel administrator and you know a lot of the time

when you're developing apps in a real world scenario as a software developer you would actually be working with a sequel admin and you wouldn't necessarily have access directly to a sequel server and from an administrative point of view so yes so in in in the real world this would be more likely what would happen you would be as a software developer you would be provided with the username and password to connect to a database and you would connect and you'd get all the resources access to the resources that you require for this for your specific

application so with that out of the way let's continue okay I can see my two tables there now that I've created I'm just gonna add both of those click connect and see now in our application we've got these two sequel connections one two orders and one stock which I guess it's great but let's see the problem that we've got here now that we've connected directly to the sequel database from our powerup if I hit insert in gallery and I'll just say connect to our orders let's add some fields so you're already seeing a bit of

an issue we have here I won't even bother adding any more fields to this you can see that I can see the description and I can see our stock ID but we're not bringing in any information about our stock ID we're connecting directly to the table that is storing our orders so this really isn't the correct way to connect to our sequel database what we need to do is create a stored procedure on us on our database server that will join our two tables together produce an output and then read that into our power app

so let's go ahead and do that and stored procedures we just need to create a new stored proc the way we do that is is we right click new stored procedure so a selecting stock name stock description amount description and order date from orders and from the stock table now the way we're doing that we're doing a join on those two tables on our stock IDs so we're taking two different tables and we're we're creating a procedure that this procedure effectively joins both of those tables into one flat table so we can easily read it

in our marks up our app now if we just quickly look through here where some of these fields we're referring to as t2 and some of them were referring to them as T 1 now the t1 columns are BL the t1 columns are coming from this from orders because we've got this alias here t1 after our our table name and we've done the same here with stock we've just put it to 2 after here which gives us this pointer or alias to the stock table so we know a t2 stock is coming from stock table

t2 stock description is coming from the stock table as well and all the T ones are coming from the orders table now the other thing you can do here in when you're selecting from sequel you can give this you can return the field even though the name of the column is amount I can go t1 amount as ordered amount and this will just make it easier to read and easier to identify in our table it'll already have a sort of a heading that will make sense now like I said I really didn't want to get

too deep into the sequel things in this in this tutorial we really want to cover we're really covering power-ups in how we connect power-ups equal here but some of this stuff we have to touch on this will be a you know this this kind of thing where we're joining more than multiple tables together will be the basis of a lot of your stored procs when you are connecting to sequel so let's say execute that which saves our stored procedure to the server and we right click on our get orders so get orders it was what

we called our stored proc proc up here under the create procedure get orders and if we execute click OK we see our output from our stored proc is and like I said it's one flat table of data we've got our stock named stock description but we also have our ordered amounts ordered descriptions and ordered dates the next stage after this is how do we get this data into power apps directly now we unfortunately to power ups now unfortunately we can't connect directly to stored procedures and not exactly sure why we can't do that through power

apps but as of this moment we can't connect directly to stored procs but connecting to stored procs is really the key to using sequel with power apps and to you getting useful information out of sequel into our powerup applications so what we use as what we do use to do that is power automate and if we go into our power automate let me just let me just delete that and we've crap I've got a new power automate here and it's triggered by power apps because we want to trigger this automate we want to trigger this

flow through our power application and the next step will be if I'd still click search and I scrolled down here looking for the sequel actions and we've got an action here execute stored procedure so that's the one we want enter custom item you know database name it's Gina DB and I've stored proc is get orders I'll click Save on that at this point and then what I want to do is I want to run a test and I'll perform the trigger action and I want to run the flow so that's our output from our flow

if we click on that action we can see our results set and this is a format that we're getting our records back in big JSON object very similar to what you'd expect for a web service and that's the key here so what we want to do at this point is we want to highlight all of that and just hit ctrl C you know if we get back into our our application and edit that warning to a new step so in our body we want to select the body that's come back from our previous statement which

is the execute stored procedure so we select body there and in the response body JSON schema we want to paste ctrl V what we had quicksave still quick test on that seemed to have got a success if we hit the response you can see our output looks all good and we can see our data packet there so now that's done let's go back to power ramps and in here let's insert gallery blank gallery won't connect it to any data just yet down a little bit maybe just put a border on it so we can see

it clearly I'm also gonna insert an icon here just to refresh and on here the action on that button I'm gonna call power automate now power automate we want to call his get orders now and here I want to wrap that around sequel order data so I'm just setting a variable to the output of our story arc or the output from our power automate which is the output from asteroid proc now in our gallery the items of our gallery are going to be a sequel order data dot our result set and our table 1 data

now if I insert a label actually even before I do that let's run it for the first time and so we can see some data in there so we probably won our stock name first and instead of a label our order description and our audit amount probably ordered date would make sense as well so if we insert a date picker we set that that property has display mode just want to set that to you and the default default date is this item ordered date can add some headings here as well I think that's probably enough

covered in this video you know that's that that's probably more than not to digest at this stage in the next you know will definitely have a part two and in the in that video will I'll take this app a little bit further and we can have a separate screen where we update our records and then we're refreshing that data back on our dashboard so we sort of getting closer to building a complete application but you know what we've covered today is really the core functionality or the core aspects of actually using sequel and power apps

together to build your application hey hit the like button if you like this video comment something in the comment section below and of course subscribe to stay up to date with all my latest videos I thank you so much for watching I'll see you in the next video