Using SQL Server & Power Apps

guys how would you like to use a big boy database for your powerapps applications what do I mean by Big Boy database well uh I I sort of saying that sort of jokingly uh Microsoft dataverse is actually a SQL Server database underneath the scenes but um it has a lot of features and things that sort of help people that aren't fully well versed in SQL um or being database developer they sort of help you out that I feel a little limited as as a previous full stack developer that I have a SQL Server um and so what I wanted to do was do a step through of creating an app with SQL Server there are some upsides for using SQL Server instead of dataverse even though dataverse is a SQL Server uh I know in the thumbnail I marked out the SharePoint everybody would probably agree that dataverse is better than SharePoint so I don't think anybody's gonna have a problem with that but the dataverse um I think I'm gonna focus in on why you'd want to use a real SQL Server database over dataverse did you know that if you have a dataverse database that you can open up Microsoft SQL Server management studio and actually connect to that dataverse with the management Studio you can do that you can connect to it as a read-only database uh so if you Google that maybe I'll do a video on that uh that's not what I wanted to focus on here but while talking about SQL server and the client tools of looking at data I thought I'd mention that okay so um what I'm going to do here um is uh this is the outline of things that I plan on doing uh here in this live stream so we talked a little bit about Big Boy database so SQL server has been the the big boy on the Block as far as an uh along the lines of the the big Enterprise level database systems like Oracle okay or postgres postgres is a open source database system um and uh it's been Microsoft's Premier database system okay so that's why I say the big boy uh hopefully I I don't or I didn't offend anybody by calling it The Big Boy I was just trying to be funny here but um it is uh thrilling and exciting to use native SQL Server with powerapps because um a lot of people are just using SharePoint uh probably 70 of people are using SharePoint because they don't have the premium licensing but if you have the premium licensing and you have SQL Server you know what if you have an on-premise SQL Server server and databases that you need to tie into well you can do that it doesn't have to be an azure you can install what is called a database Gateway and connect to those okay so I've done that many times um I prefer an Azure SQL Server database because it's already there in the cloud and you don't have to go through database gateways and there's uh there's additional layers of apis that you have to go through to to work through that Gateway okay so what are we going to do here today in this live stream so I talked about the big boy database and what I mean by that y SQL Server so we'll talk a little bit more about that what are the big upsides to using SQL Server over dataverse Okay um and that sort of goes into what's wrong with dataverse uh and I would say there's really nothing wrong with dataverse but you need to pick the appropriate tool for your the requirements for your project okay so why we would pick SQL Server over dataverse um so we'll we'll log into Azure okay so uh last Saturday we didn't go live on my YouTube channel but uh all the accelerators students hopped on uh to um a call last Saturday morning and we talked about setting up Azure and doing uh some of these things but I wanted to actually start building an app with with this stuff like a real app uh using SQL Server so everybody can see how it's done how I do it how I recommend um approaching a project like this okay so we'll go into Azure set up the SQL this really won't be a session where I I really talk too much about Azure um you could just you know uh Google azure and go and create an account um and um you know if there's a big demand for it maybe I'll create like a little video that will go into that if any if if there's there's a demand if people are having trouble with that maybe I'll just do a little dedicated video on that uh but we'll create the database I'll show you how to do that uh and then we'll create our own powerapps application around that now I've got an idea for an application which is an onboarding application now if you think about it there there are a lot of use cases in the business world or even the academic world of of why you might need an app to do some type of onboarding okay so if you have employees you might have well any organization will have employees unless you're a sole proprietor right so you'll have employees that you want to onboard so you want to get all their their tax information uh so you can pay them you might want them to upload their identification their proof of citizenship that type of of thing but uh perhaps you got some training videos you want them to watch and you want them to sort of check off you have completed this yes I've received the handbook yes I've read the handbook uh all those type of things um you know here in my my situation uh you know I I'm leading an army A small army of powerapps developers uh in the accelerator program and I want to actually utilize this application for onboarding students but it's not just onboarding I want to say onboarding an engagement okay onboarding engagement maybe even uh in satisfaction okay so I want to know I want to onboard my students I want to keep them engaged I want to know how they're doing on their projects that I'm that I'm helping them with and then I want to know how happy they are if there's any um topics that they want me to cover and sort of delve into I want to know if they're having problems if they're being if they're stuck so I want to schedule a meeting with them get them unstuck and make sure they're just they've got uh plenty of momentum and um do you see Michelle on there and bring Michelle on and then um so that's what we're going to talk about uh so far so so guys uh Jeff Nick Michelle do you have any questions comments about what we'll uh cover today um [Music] of using it the Azure SQL SQL will I be covering the the cost Associated yeah is that what the question was yeah yeah so I'll actually go through that while we create the database uh you'll see as I change the parameters let's say the size or even um you know the the they call dtus like the resources that are allocated for that database um as we mess with those sliders and change the values you'll see how expensive that could get that is one of the I'll actually put that down here uh y SQL Server oh what about why not to use SQL Server okay so number one is probably the cost so you think well you know with dataverse you have a cost you got to buy the premium licensing okay so that's going to be twenty dollars a month right um so I'm talking about costs above that okay uh we're dataverse you're um you know you will have to pay for any usage for that that dataverse but um it's not like the SQL Server like you're about to see okay so I'm going to say cost uh is probably one of the things uh the complexity okay so um there is a learning curve um what else do we have are the complexities so yeah there's there's a big learning curve here so I'll go in here and say well there's um if you want to get there I'll fill those in with the uh the complexity the cost um think of anything else of why we would not want to use SQL Server uh perhaps it's Overkill or uh the requirements maybe it's a simple application and um one of the reasons to use SQL server in my mind one of the biggest things that comes to mind is uh to join labels okay so inside of powerapps you cannot do a join um like you can in an SQL select statement and we're actually going to go over uh some of that will touch on that today okay doing a raw SQL statement okay and uh you can join tables with something called a view okay that's a database object now that's different from a dataverse view and also if you think about SharePoint SharePoint views or they're similar in a little bit of a way that it's not really the data it's like a query of the data or a subset of the data uh but it's actually a database object and there's syntax to create so uh if you really really really don't like code um then um perhaps that that might be a reason why not to use SQL servers overkill for the requirements um no desire to learn any more coding okay so in SQL Server um there is a language for SQL Server okay you might think oh it's SQL well the SQL is called uh it's an acronym for structured query language and that is a standard that a lot of relational database systems use even something like Microsoft access my SQL postgres Oracle they all use SQL structure query language but each of them typically has their own flavor they like to add on their own extensions sort of like back in the day where you had all these browsers that would have their own little syntax if you wanted to have something happen in the browser and it became like this really annoying thing uh not it's not so annoying with these database systems but they like to implement um you know features and and things that you can do extra on top of the the core of what SQL is okay so um in SQL Server that is called T SQL which is transact SQL okay you know how when we talk about our apps what's the language of powerapps well it's powerfx okay so with SQL Server what's the language with SQL servers okay so if you're trying to create a you know trying to Google how to create a query for the view that you might want to create in SQL Server instead of just saying SQL you might want to say tsql so you'll get the um powerapps flavor of how you would do that in the code okay um so anyway let's see here uh so why do you use SQL server join tables with the view that's really big you can also utilize stored procedures okay but however you must use or automate blows to execute them okay just a little caveat there and that's it's no big deal it's easy to do that okay but a store procedure is tsql code where you can actually update the code you can return a result set like you can in the view uh you could pass a parameter into a a store procedure uh as a parameter and it could return something specific to that parameter that you passed in uh it's very powerful and then once you're in a store procedure you can have user-defined functions and all kinds of other things that you haven't in SQL Server that you don't even have in powerapps okay let me give you another example I was on this project where I was developing an application uh for teachers okay and um it was an application and all these teachers were on a on a particular screen at once and um the requirements specified that I was not to allow all these users to go on to the next screen until they all sort of agreed on the answers they were giving given so they'd be like on a zoom call or something they'd be talking back and forth and I needed to determine the variance of their answers well I don't have some type of a variance within powerapps but I I there there is a function within T SQL that I can use and I I came up with um a lot of situations on that project where I had to utilize things inside of uh views that I could use that would return that data so there's some and maybe you've got that you know you have a function in Excel that you don't have Empower apps uh check out the T SQL language it's a very mature language I've been working with SQL Server since uh back in the day before the year 2000 so um back when it was 6.5 then seven and then the next version which you'd consider version 8 was actually SQL Server 2000 so as you see the years 2023 I've been working with SQL server for for quite a while it's a very seasoned database system um so anyway I'm just keeping an eye on on everything the private chat and the public chat here uh so let's just let's just sort of bring up the comments and um stay engaged with everyone here okay so Jay says looking forward to this I recently started learning SQL again stop using it due to my work my workplace uses SharePoint a lot yeah yeah but now I have access to there's a SQL database the query okay very good um okay so here's a question data is King uh will powerapps be able to connect to SQL Developer in the same way as connecting to SQL Server um now perhaps what you're talking about there is the Developer Edition you could connect to let's say you install SQL Server Developer Edition on your workstation okay and yes you could access that you would just need to install a database Gateway on whatever is using um the Developer Edition okay uh just keep in mind the Developer Edition is made free by Microsoft but it is intended for just developer uses so if you're gonna use it for any type of production uh purposes then you might want to uh or you need to legally connect to a different Edition okay um is it faster with an Azure SQL server with powerapps uh in some cases yes in some cases no okay so let me give you an example in which it is not faster than uh dataverse okay and I'm going to include this let me just sort of shift things uh around again okay very good this works and why not to use SQL Server universe could be faster okay why could it be faster less API layers okay so the dataverse has been designed and developed to be used within the Power Platform okay so there is a chart showing if you use dataverse over a SQL Server the amount of API layers the the layers that the application has to go through to get the data okay so with dataverse um it's it's right there inside that that Power Platform environment okay and um so there's there's fewer calls and it's been designed for that so you'll find in general the queries will come back faster and um will be less of a bottleneck with dataverse in general because it's been designed for it okay now I say could be faster it's not always faster um so for example um if you have an application where you've got a one-to-many relationship okay and you've got a gallery of the of the one part of the one of many and inside that Gallery you need to display some of the many information okay so you might be tempted inside the gallery to do a filter or a look up inside that Gallery even though it's it's dataverse okay um if you're doing any type of queries inside there it's going to make your application sluggish okay now what I would do a SQL Server uh especially with the one of many many-to-many or you've got let's say a dozen database tables and you're going to join them all together and pull them back um that's where SQL Server will really Excel and in that case it's going to be a lot faster with SQL Server because you're going to do it all there on the server with that view and you're going to query that view as if as if it's a data source you know you're going to pull back all that data and you can have that Gallery based on that data that came back from that view um now dataverse does have some native relationships with the lookup columns and stuff but if you want to do something really complex SQL Server can handle it you know in a really complex situation SQL Server will be more efficient overall in your application okay um so in uh just straightforward stuff um if you don't have to do a lot of of uh joins then uh obviously go with dataverse i i commonly say listen you're creating a pair of Supplication the the thing that you always want to be reaching for first or that you think of first would be uh dataverse because it's been designed for it right but if you start getting recordings oh no premium licensing okay well then you need to start thinking about SharePoint okay um and I definitely don't recommend Excel um and then uh if you have a really complex application SQL Server is going to be um your best choice and in some cases will be faster okay it just depends on the situation okay so uh Colin I've been building views uh last few days based on your feedback not bad once you get into the process absolutely views are are are great they're a great tool so there is King uh SQL Server also has a query builder for those that want a SQL statement yeah that's true um and wondering if powerapps can also connect to no SQL such as um I haven't looked into that I haven't had the need for that so um I wouldn't be able to answer that just here off the top of my head um all right so looking back here um one of the things that I did want to touch on let's see why not to use okay another good reason why to use SQL Server think about the limitations of dataverse there is a one-to-one relationship between a powerapps environment in a tenant and the dataverse database if one has been created for an environment okay you can't have multiple dataverse databases for one environment okay so think about this if you want to have an app that is installed in multiple environments within one to net what about multiple environments across many tenants Let's uh let's say you want to sell some software you can install it in all these client environments are you going to host all the data and you want that data to span multiple environments that's when you would want to use SQL Server now what's a little nice about SharePoint is that if you have a SharePoint data source and if you're all within one tenant you could have each of those environments let's say you you install the app in several environments within the tenant they can all point to the same SharePoint but if you're in a completely separate organization completely separate tenant then that's going to be problematic because a SharePoint site is going to be attached to a tenant okay so it's very flexible from that standpoint okay so let's say multiple that's a big one in my opinion okay got a lot of flexibility a lot of power but with more power more complexity comes at a cost of having to understand it better you've got a steeper learning curve there's a lot more things that you have to think about and do for the solution that may be dataverse or SharePoint would do for you let me give you an example of that when you create a SharePoint list it creates an ID for you that's an auto number or an identity or a you know um the same thing with dataverse it creates a grid for you um and in both cases it creates a created like when the date and time stamp of when the thing was created who created it when it was last modified who last modified it there's all kinds of things that just throws in there for you and maybe a lot of times you don't use it um but if you're used to that and you're in SQL server and you create all your fields and you're using it and you're like oh when was that record created well you need to create all those fields yourself you're you're given full control and with great control becomes uh comes with it great responsibility right in that uh quote from Spider-Man or some superhero movie I think it's Spider-Man um okay so any questions and comments uh guys before we sort of jump in and start having fun here I'm ready to have some fun let's all right okay well um now there's something that I always say to all my accelerator students and I I encourage that in any of these YouTube videos which is um when you start an application just don't jump into powerapps do your planning and design talk to the stakeholders make sure you've got all the information unless you're just trying to open it up and just try to learn and and just play around okay um I recommend that you'd um you know get a general description of the application you uh get all the requirements as far as in the entities all the database tables you're going to need to create you'll need to Define all the screens uh maybe have some wireframes create user stories which sort of firm up all the features and who's going to do be doing what inside the application okay and I actually had uh I think it was two Saturdays ago maybe um where I I the thumbnail says you're doing it wrong uh which is all the stuff that you're not doing before you get into powerapps and I talk about that in depth in that so if you're interested in that go check that out I'm actually going to implement some of that stuff I didn't want this video uh this session to be all about that because I've already covered all that so I did all that pre-work uh beforehand okay and um sometimes doing that can take hours or days especially when you're working with somebody a client they're like I need an app that onboards my employees okay well tell me more please well you know like you know and and you you get them to sort of think and go through the business process and um all this stuff and there's a lot of going back and forth and reading the replies and asking them additional questions and and you know a lot of times they're wanting the price and um and time estimate and you sort of have to hold them off it's like listen I can't do that until I understand what you need you know if you you uh enlist the help of a building contractor is like okay uh here's this big uh plot of land behind my house I need a building back here all right do you need a a shatter size a skyscraper or not a two-story office building those the requirements for all three of those would be very different right so um the same thing with an application you know it's like well I I want a CRM system uh okay well you just need something basically to keep track of your customers or do you need something that will give you all the features that HubSpot would provide for you uh which is very extensive it's an extensive CRM system um that would probably take you years to to duplicate all those features with even with power apps probably if it's just one developer um so anyway um so that is important so I'm gonna go through I didn't want to just jump into power apps and and not practice what I preach okay um what I did was I Enlisted the help of uh chat gbt okay um I'm just keeping an eye on the comments here uh so Jeff can you start building the foundation in SharePoint uh decide upgrade or mood to SQL or is it design the database just to be different the database could be very similar if if let's say you were stuck with SharePoint now and you're going to upgrade later um you know the data types are different okay and you're gonna see that as I start creating the tables okay so that's one difference um another difference is um if you design your relationships the way that I recommend was SharePoint not using lookup columns lookup columns are good and dataverse they're not so great inside of SharePoint sharepoint's been around for decades uh I'd say about two decades now and um it wasn't designed to be used within the Power Platform so those lookup columns been around for a while and you'll have delegation issues so I have actually quite a few videos where I I show how I do that and Jeff you're probably familiar with how I do that in SharePoint that's actually the way that I handle it in um or how you would handle it in SharePoint so that's not so much of an issue so the data types will be different you could name the database tables exactly the same which would be helpful if you're going to migrate to SQL Server eventually uh I'd say the big thing will be the data types you're not going to have the uh the database views that you have in SQL Server um so yeah you could start um I mean ideally if you're starting a new project you know you're eventually going to go to SQL Server um I wouldn't want to start with SharePoint unless I'm going to be in SharePoint for like a year and then eventually move to um SQL Server um so and also Andre says yeah Spider-Man created uh lots of SQL databases that's funny um okay and also Tim in the scenario where you have an app that's going to have end users both internal and external interacting with the app are there any benefits of using SQL Server over dataverse regarding security you know this is a good question um what's great about SQL server and I should probably reflect this inside my um my diagram here or my my notes I should say I can make these available to you guys later um something that just came to mind just how you phrase that question made me think of a reason why SQL Server what if beta uh needs to be accessed outside the Power Platform now somebody might cringe here because you can use be diverse outside our platform I just want to throw that out there if you Google that you'll you'll find examples let's say if you you're an asp.net developer and you have some um you already have applications that use a SQL Server um and then you write some power apps and you could tie into that SQL Server so that's great um but um it's more accessible to other systems I believe in a SQL Server SQL server has been out for a while um and I think accessing dataverse again you've got more it sort of reverses the thing where I told you before if you're doing powerapps to data versus less API calls well the reverse of that is if you're not on the Power Platform there's probably more API calls that you have to go to to access dataverse um than it would be to access the the SQL Server okay um and that would be a reason why I really like a SQL Server is if I'm going to build an asp.net application or some other type of application doesn't have to be asp.net.net um is um you know there's already tools and drivers and things to connect to that directly okay so that's the another reason why I like that um yeah so I see uh so see Power pages and that's what uh so thank you Nick that was something else I was going to mention here okay um so with model driven applications portals which has now been rebranded by Microsoft as power pages okay those two flavors of powerapps um you're limited to only the dataverse okay now this is where the dataverse really is cool because you can create virtual tables in dataverse that links to SharePoint lists that links to uh SQL Server you can you can do virtual tables at links to uh Azure ad that's already actually set up there's a in dataverse there's a table called aad users that will allow you to query all the users in your organization that are there within Azure ad okay so you can access that right now and that is a virtual table that goes right into Azure okay so there's a little bit of a workaround with with dataverse so you think or you may know with model driven and portals power Pages uh but you're limited to the dataverse but you can actually pull in uh other data sources in as linked tables okay so that's a little thing to sort of keep in mind as well okay so I'm going to reread the question because there are actually two or three things I wanted to unpack here based on that um so I think I'll mention um could be faster I think I'll make sure I have this as a bullet point here you can link other data sources with yeah words like equal server SharePoint um even in Excel believe it or not um and azure 80 there's probably others but these are the ones that I know about that have been listed and that's a feature that probably came out I don't know six to two months ago um so it's relatively new maybe not everybody knows about it um okay that's an area where you have an app that's going to have in okay both internal and external um so what I was thinking um and thank you Nick for bringing that up where you um are thinking about external users why I hear external users I'm thinking oh man you know canvas apps wasn't really created for that however portals or power power pages that's exactly what that handles is an external facing site it can handle um Anonymous users that type of thing um so keep in mind you can have an external user use your canvas apps but they have to be added as an external guest user within your Azure ID so I'll mention that um are there any benefits to using SQL Server over um over dataverse I think both of those systems have a fairly significant robust security system uh as opposed to like SharePoint which is more of a basic type of security system um let's see here okay so another comment uh data's King I was looking at the Azure ad user table in data version it only has one column for the username it does not have other columns to filter by section Department Etc now there are two tables in dataverse there is a users table which are all the users that have been added to that environment and then there is an aad users table that is that virtual table into Azure ad okay and they have different columns and um that link table to Azure ad has a weird thing that it does that I uncovered and that I created my own user table because I want to have my own information I put in for users so I wasn't using the the normal users table but I had although my fields that I wanted and then I wanted to because I wanted to update little things inside that for my application I created a lookup column to the aad users table so let's say I pull up the user Darren and I've got all kinds of stuff in there that I like but let's say I wanted to get at some of the ad properties okay so I could say you know my user I've got a variable with that record for my user in my powerapps application I can say dot aad user lookup column that goes over there what happens when you do that is it ghosts and loads up all of the users in your organization so if you have 10 000 um employees that's exactly what I had happen and um I was doing a lookup on the current user with their current email address and I did a query and because that lookup column was there it tried it tried it did not fully because you know powers can only deal with a Max of 2 000 records right it tried to pull up all 10 000 records loaded into memory uh and then of course it only gave me the the well I I had a particular user so only gave me one record that was associated with that but because I had to join it had to go and get all 10 000 records loaded into memory and my app would take like five minutes to load up I'm like what is going on and I narrowed it down it's like what's going on with this user's table and uh when I took out that lookup column boom all that problem went away um so that was something um that I think is a big deal that I sort of stumbled across uh and if I hadn't made that that mistake or made that assumption that I could just do that and it would be efficient I probably would have never known that to pass that warning on to you guys so uh be careful if you have um a virtual table that you want to link to as a lookup column because it could have some unexpected results okay Okay so sort of zeroing in on our application that we're going to build so what I did let me pull up um my accelerator Community here if you guys aren't in accelerator program you might be interested to see all the kinds of things that we do here but I just created a thread of uh chat gbt let me see how that comes okay that came up fine uh a Chad gbt prompt okay here's a prompt that I use which igbt that will help pull out all the information you needed in order to plan and design your application okay I tell chat gbt take on the Persona of a successful freelance freelancer as an expert software architect and developer who uses Microsoft powerapps and I specified canvas apps um as opposed to model driven or portals as a rapid application development tool now keep in mind chat gbt is about two years behind I actually asked chat gbt before I did this I'm like hey do you know what dataverse is because uh dataverse originally was called CDs common data Services uh I'm not so sure on the S part but it was called CDs and they renamed it dataverse so thankfully it didn't know what that is so I know I didn't have to explain hey you probably know about CDs that's now dataverse um I'll play the part of a prospective client that'll provide information ask questions regarding an application it'll be your job to ask all the questions about my project my needs in order to do the following and look what I'm having it create a time and cost estimate both itemized with totals create a listing of the screens listing of any power automate flows recommend uh the best data source okay in this case I told it hey I want to use SQL Server um create a data dictionary so it's going to create all of the uh tables all of the the fields and all that type of stuff and we can add on to that and modify it but it's sort of nice just to have a full list so you're not thinking okay what other thing would I want to track here it gives you a full list and you can um add or drop to that you know but it sort of gives you a good starting point okay this is also useful if you're doing freelancing and you've got a client that comes to you and says hey I need an app it's like okay and you could give this prompt and just paste over what they say hey I need a CRM system and just watch what it spits out it's going to say okay well what do you need to keep track of specifically and it will just pull out and Define all these things um create the data dictionary okay which is a listing of the tables and columns the co the field types and the primary Keys foreign Keys relationships all that type of stuff and actually spell that out for it create a feature list okay with user stories create a listing of tasks and a project plan so here is all this stuff that I recommend that you do and I actually tell hey don't start generating this stuff right away because I'm just going to give you a general description and like don't start generating this stuff and I actually say right now please just ask the questions that you need in order and it will ask you questions okay once you have all the answers simply tell me I'm ready to provide this solution then I'll ask these things one by one be sure to ask as many questions as you'd like to do this please let me know if you come aware of anything in my project that might pose a problem with any limitations regarding the Power Platform so if I say like oh I'm writing an app for all my customers that want to sign up for service well that's going to tell you with a canvas app that's going to be a huge problem you could add them as an external user but it's you know that that won't be automated that would be more like a manual process or you'd have to create some code maybe a script A Powershell script or something um that would call apis within Azure ad to add them as a external guest user okay so it's not uh that's sort of like one of the big limitations on in my crash course I talk about the 10 things that you must know before start writing apps and power apps and one of the things that I touch on are the limitations and I came up with seven limitations uh and if you have those limitations you might want to steer people away from powerapps now I'd say ninety percent of the time I I I'm like that isn't an issue but you know if somebody is looking to build a highly performant engineering application dealing with thousands and thousands of rows maybe it's a utility application that um you know that's gonna that's gonna be a little bit the powerapps play isn't the best solution for that what what powerapps biggest strength is it's a rapid application development or rad Tool uh and it's I love it and that that's the reason why I love it I can just whip out apps and but there are some cases where like yeah yeah you're not going to be able to use uh you know powerapps for that but you'll be astonished by the things that you can do on the Power Platform to get around uh different limitations uh most of the time I I've got situations where I can do that get around those limitations okay so um so this is the prompt that I have so if you're in the accelerator program uh you can respond to this maybe even recommend some some uh additions to this or some edits um if you guys you know what I'll do is I will provide the link to my free uh School community so if you guys want anything there um let me include that real quick just so you guys can see that so I've got a link here at the top um okay that's for that yeah if you want to um book a call with us uh with me and Kurt and talk about your needs we can talk about um you joining the accelerator program um so that's what that link is there tinyurl.com powerapps accelerator uh that's not the one I meant to bring up it is this one Power Platform community that is a tiny URL that will take you to my free community um where I could I could actually post this there as well I have no problem doing that it's not like I'm trying to keep that a secret Okay so um what I'd like to do is show you what happened between me and chat gbt uh this morning as I Was preparing okay so um based on what it said here um where are my notes my notes are right here I've got my AI prompt here so let's pull this over and I'll show you what happened so I wrote this prompt I had a a prompt that I used in the past and I'm like I want to create a better one so this is what I created this morning okay and um so you guys just saw this okay and this is what I told it I need an onboarding and engagement application such app will be used for use it for new employees new customers clients students okay this app will onboard users and keep them engaged by having the input and manage information and documents about themselves you know maybe the contact information their goals and priorities all that type of stuff like if you're I'm bringing a new employee a lot of times an onboarding like hey where do you want to be here in this organization in five years you want to be a manager do you want to uh be doing something like let's say you're coming as a developer maybe you want to manage networks and that's been really your ultimate dream but you did you know um so you might have people share that with you you know provide checklists to help them get on board like uh watch this video complete this exercise fill out this information sign this agreement um email reminders on users on the on the steps they still need to complete ask if they're happy if they like you know all that type of stuff that um you would do probably in any of those situations um some data will be input and maintained maintained by staff and not the person being onboarded such as the start date enrollment date any other type of information um let's say if it's a customer have they paid in full are they on a payment plan or you know all kinds of stuff like that that would be more internal and not um you know accessible by the end user that's being onboarded um so for questionnaires so this is this is going to be a cool little feature so a lot of times people ask me about creating um and Jeff I I believe uh you're in let me make sure I got my chat up here okay uh yeah Jeff you asked a question I know you're here so hopefully you're listening to this uh I know that your application you've got a lot of sort of questionnaire checklist type of stuff so you're really gonna probably take a big interest as I'm building this app and uh I plan on building this app with you guys okay so we're not going to finish the app all today we're going to get started on the app so you're gonna see me as I build the whole app with SQL server and how I do all this stuff okay so I get a lot of questions about how to do questionnaire or uh checklist type of applications uh so you're gonna see how I do all that uh sort of a high in demand or a frequently Asked um scenario so and I I say all that I explain all that okay I also want to specify the the data types so if I ask a question that's a yes no type of question then I want to like a radio button or check boxes or something that's appropriate to the type of answer I'm expecting not just single line of text for everything okay and uh uh Ellie good to see you on here perfect timing I lobe this so excited I'm sure you meant to say love yeah nice having you here Ellie Okay so um I want this project to be done in SQL Server okay and look these are numbered questions that chat gbt came back with could you please elaborate on the different types of users and the roles what are the permissions and this is my answer just the users being onboarded and application Ministry uh app admins okay internal staff uh required documents and I wanted to know hey what type of um documents do we want to rescorrect what specific document I'm like hey keep that Dynamic I want this app to be very flexible for any type essential I want to be able to sell this app if I want to you know somebody needs a onboarding app and I can put a webpage other hey you need an onboarding app let me know I can sell you this app for I don't know 10 bucks a month or or whatever whatever to cause cover my licensing cost to Microsoft of course and all the the SQL Server costs and then hopefully you'd have some type of a profit margin in there as well but yeah you can sell your apps like this and um host the data inside a SQL Server okay that's actually another reason that um I recommend to use SQL Server is if you want to sell your powerapps software as a service and request some type of a monthly fee each month you don't want to put that in in dataverse and in the canvas app because you need to install it in their tenant okay and at that point they could just tell you after a year or maybe after a month after you deploy it and uh you know they're happy with it they could just um say okay we don't need your software anymore uh so we're gonna stop paying you uh but they could just keep using your software because it's all in their tenant and it's all between them and Microsoft at that point with all the licensing costs and the space um so in a lot of software companies are going this way um in that instead of paying some one big cost for some software you know maybe they just spend 10 or 20 bucks a month per user or something like that so this is a great way of implementing something like this is that you have the SQL Server host all the data for all your customers okay you might need to design your database slightly differently like on each table you need some type of a client ID or or a tenant ID or something like that okay to keep all the data separate even though it's all there together within the same tables um and then um yeah so you host the data for them um which is sort of neat and you know some clients like that they don't want to have to pay a huge uh big price tag at the beginning I like the fact that I can I can go to Microsoft and say Hey I want to pay six bucks a month give me my lieutenant my own SharePoint all my stuff and just pay six bucks a month that's sort of neat I'll continue to pay that in perpetuity um that's no problem six bucks a month that's that's a great deal and not have to pay hundreds of dollars for office and then um you know go buy SharePoint server and then build a server and install it on that and then buy exchange and install that and then have to manage that server uh software as a service isn't uh some people oh you wanna you know you wanna uh you know nickel and dime me every month no it's actually a huge benefit a lot of people do like that including myself so just throw that out there um so I you know I give it responses here okay the customization how flexible do you want in terms of customizing the user interface um I have my application template that does some Dynamic theming and stuff so I got that taken care of I'll be using my application template okay um communication preferences and I answered no to that so I'm just gonna slowly go uh you know scroll through this so you can read this you can pause and look at some of these responses I gave it but what's important is after you look at these things and I I tell it on some I make it you know the dynamic flexibility um you know where it's important to me um because I'll need a lot of tables created for that flexibility I make sure it's clear on that um you know you know I'll be setting up ad it was wanting to know if if I wanted to uh you know pay extra for the Azure setup and all that type of stuff well um you know I can show you guys how to do that of course in this case I'll be doing all that myself I do have the licensing and I think I actually specified that in my prompt it's like hey make sure if you recommend SQL Server dataverse that you need to ask me and see if I or the client has the premium licensing okay so maintenance support maybe three months a bug fixes some minor changes okay and it it tried to get me to give give it my timeline or my budget I'm like no just tell me how long it will take budget as cheap as possible without sacrificing quality um but but I do the same thing while freelancing I'll try to get some type of an idea if they need it or if if the deadlines next week then uh if they really want it done I'm going to take over the project I have to drop maybe some current projects or maybe push them off a little bit so that I'd have to charge a premium for that so that would affect the price and stuff um and it it made some uh suggestions here reports analytics to measure effectiveness of onboarding and engagement statistics onboarding process completion rates feedback response rates I'm like wow that sounds great yeah all right so what I'm going to do is going to bring up this word document again and um so what I did after it said is like hey I'm ready to pretty much tell you uh to give you this stuff I'm like okay give me a General application description okay and this is what it gave me um something hopefully you guys can't hear this I have two toddlers downstairs that are just screaming of course my wife is down there with them um so I'm listening to all that uh it's a little a little stressful trying to concentrate and everything while listening all that uh so anyway um so the General application description you know what let's make this bigger um you guys can read this better okay so let's zoom in on this okay so generally when um someone comes to you like hey I need an application well okay well tell me more and then you'll get about a paragraph of information so that's generally um what you'll get you'll get like a little uh paragraph like this and then you try to get some more details so I I told jet chat gbt hey give me give me more of a thorough um you know description maybe three paragraphs long okay and as I've talked to you guys Gathering the requirements you wanted to find all the all the entities all the nouns okay that your application needs to manage okay and those becomes your database tables maybe even um you're going to be creating screens based on those entities right okay so I'm just going to show you what it created here I'm like hey give me a full feature list boom it gave me a bulleted list of all the features nice okay so this is what I recommend this is the most efficient way of starting off a project doing your planning and designing it saves you so much time um when I was a a kid a child in the 80s uh I was born in the 70s but um I should say mid 70s um you know I was going through school in the 80s and uh you know I learned how to do math I learned how to uh spell words and read and write and everything but with the ubiquitousness of or you know the the Techna of ubiquitousness of Technology the technology being everywhere and freely available like calculators um you know yeah I can I can do complex multiple multiplication and division um I don't I don't know doing the long form and division that might I might have to sit there and and smoke might come out of my ears why did I how did I do this back in the third grade you know but with calculators you know on everybody's cell phone um you know you sort of get rusty with that right um and Gathering requirements can be a very lengthy process that can take days uh spread out over many meetings but perhaps weeks uh meetings upon meetings getting more and more information and then getting the stakeholders to sort of go back and forth like you'll be in a meeting with them and they're like oh yeah we need a screen that does this and and the other person in the room will say yeah needs to do this and then they'll just argue back and forth like no it doesn't need to do that because we've got this other system that does this and they'll go on for like you know 10 minutes about one little thing and you're trying to take notes and you're sitting there it's like hey would you guys mind meeting about that or having multiple meetings about that and just let me know what you as a group decide on uh so it's it's actually easiest to deal with one use one main person that will talk with the whole group of people and come back to with a single answer because you'll be taking notes and you'll write something down okay I need a a feature that does this and then they'll start debating back and forth and then you're like Mark it out or you'll erase it and write something else down and then they'll change their mind again uh I I see multiple I see Nick over in private chat that's my former life yeah guys it happens it is so it's sort of like saying uh requirements never change no the requirement's Gonna Change um And when they see visuals they're going to change their mind there's no exception guys there's just no exception um even if you're creating an application for yourself for your own personal reasons in the middle of it you're like oh actually I wanted to do this instead you know um absolutely um so I think of chat gbt and what I've done here this morning sort of like using a calculator to do a bunch of calculations okay and I talked about reading and writing and spelling well while using email software like Outlook or using spell check within word and you know I I feel like I'm a horrible speller now because uh it always changes it for me A lot of times I'll Google a word and Google let me know did you mean this you know I'm like oh yeah I misspelled that word that's usually if I'm not in some something like word I'll Google it um so I've become really dependent upon technology is that a good thing or a bad thing well that's uh not something I want to tackle here but um I can tell you this is saving me a lot of time for 20 a month for chat to be team um and in some cases free um it's an amazing tool and I recommend utilizing it okay um however there's one caveat here um you almost need on your first project maybe not use chat DPT just in the way back in third grade I I learned my multiplication tables um without using a calculator okay because there might be a case where you're on a client site and you don't have your laptop with you I mean you do have your mobile device right I guess you could do that but you're not going to sit there and um I always like to get things in writing from clients and customers because I can paste things that they've said in a chat gbt and say you know go through all this and spit out you know uh you know things that you see here uh when you're in a meeting or it's a zoom call um you're gonna have to either record it and take notes later and type that up and then feed that in um you might need to do this type of stuff without the use of chat gbt okay um so it's sort of good to know how to do this without it but uh after you feel like you've got a good handle on it I would say utilize this all day long you know if you're learning how to spell don't use a word processor you know learn how to spell but um I think you you guys understand where I'm going with this with the technology it's a great technology um so there's the feature list here's my data dictionary here's all my tables here's all my field uh on my fields for the database so the the data types I told it you know I'm using SQL Server so it's giving me SQL Server types okay instead of like single line of text for you know SharePoint okay no unfortunately I couldn't have it just create an ERD diagram for me that would have been nice um but chat gbt gives you uh text answers it can format things in tables like you're gonna see here I say hey create all the user stories for me as a new this is an example of a user story as a new user I want to register in the app and manage my profile so that I can begin the onboarding process and manage my information okay um yeah just keeping an eye on all the comments here um so yeah here's all of my um I see Michelle's taking off yeah take care of Michelle good having you on um so this is a really good good stuff here guys this um instead of having to sit here and think about every I think everything through and um it does a lot of thinking for me okay you need to review what it gives you um a lot of times that I've I've seen here is that they will uh or I say they Chad gbt we'll talk about oh you need a login screen and sometimes I'll actually tell it Chad gbt we're using m365. why do I need a login screen it takes care and and chat gp2 like oh yeah yeah sorry about that you know so sometimes that we'll talk about that and so that's something that I've noticed that sometimes you need to omit unless you're creating some type of a kiosk type application or maybe they've logged into like a service account or something and then everybody uses that same account um and in that case you probably do need a login screen so I created one for um a work order system if you go over to my playlists it's called work order system for a garage where I actually did a system like that if you're interested in seeing an example of that um you can see that there okay here's all my screens okay all kinds of good stuff okay and that's where I left it um and you could get you know what about your wireframes you know it's not going to create my wireframe sketches for me but um so that's that's the amount of planning design that I wanted to do for this example okay so what am I going to do here I am going to pull up yeah sure okay and make sure I've got the right screen here okay so this is azure okay so this is my account here and I'm going to go over to uh SQL databases and I've got two databases here I'm going to create a new one okay and I'll zoom in here so everybody can see everything really nice okay okay so um I've got a subscription I already have a resource Group set up and that Resource Group has been set up um here in the eastern United States okay so you create a resource Group based on where it needs to be um geologically is that is that the right word Jeff geographically geographically that's right geographically thank you a database name what should we call this I'm going to call it onboarding um okay so that's available um I don't know if I want to become more specific than that I think that's generally what I want to call it I want to get uh too complicated with the name there um I've already set up a server here in the East US um I don't want to use an elastic pool you can find out more about the elastic pool by hovering over this and reading all this or hitting pause and reading this if you want that's not what I want the video to be about um however I'm going to zoom out a little bit so you can see the cost all right so for the cost of a used car payment of uh you know under 400 a month you can have yourself a SQL Server database how about that well that is for a production database okay so going back to why you wouldn't want to use SQL Server well you know that costs a little bit more than uh dataverse doesn't it okay so uh you're gonna pay for it now if you just want to have a developer database well that's a little different that's that's a little more affordable here I'm going to scroll down and you'll see here it wants to know the workload environment well if you're just developing um okay Jeff says up in Canada that's only a thousand a Canadian that's cheap all right all right for some people that might be cheap and some people might be a little expensive um if you're a developer just trying to learn uh that might be a bit on the expensive side so in that case you hit development okay so let's see um up here how much a developer database would cost and I can actually get it cheaper they have five dollars a month okay that's USD um so I mean we all have uh things that we pay for every month like you know I pay for YouTube every month I I don't really care for ads um you know I've got a uh you know a Netflix account now this account you know so what's another five bucks well if you're a developer and you've got uh 15 Dev databases out there well that's 50 bucks a month well that that sort of adds up over time right um so I'm gonna go down here and look at any other options um and if I if I were to click elastic pool um then I think it takes development option off the table here so that's why like you don't want elastic um well look at this compute and storage I'm gonna zoom in here so you guys can see it's a little better right here all right so even though we're a development uh database here we're going to get um One V core 32 gigabytes of store do we need 32 gigabytes to start off with I would say no um especially for a Dev environment uh even if you start storing images uh you know you might you might get up to a gig or two uh for production database and you're storing files in there yeah I you might need some space to hold all those documents and everything you know what I mean uh but we can go in here and configure this and say hey this is Phil I'm just trying to learn guys you know do you have to charge me so much um and you want to stick with the serverless if we want to do the um um you know the cheapest option uh possible here okay and we'll go down here we can mess with the cores that's not what I want to do what you want to do is go down here what I like is this enable auto pause so if I'm developing an app and I don't use it for a month and I go back in there and start you know I want that thing to pause not be using up resources okay also I don't need 32 gigabytes go down to two gigs or four or five gigs okay and I'm going to scroll back up we're down to 30 cents a month now the guys that's that's a little bit more doable okay so if you're doing development you got 50 databases out there that's not so bad well you know maybe to begin with one gigabytes fine okay um so that's not a bad option I think I'm going to stick with um let me go with two for now and uh I might change the uh probably we'll change these options later because I plan on using this um in a more resource intensive way and if I want to do to use this as production I'll probably create a separate one um and um in a lot of cases in in development projects what you will have the larger the organization the more you'll see this okay uh if you're doing development you've got a development environment a powerapps environment for Dev and that will point to a developer database and then when you deploy it out to test for users to test it you'll have a powerapps environment called test it could be called anything but probably have the word test in there um that will point to a test database and sometimes they'll they'll created another environment called uat which is for user acceptance testing um that will point to a uat database and then you might have staging and the one last step before it goes to production okay or maybe you won't have staging users have production at that point so you have different levels and different resources allocated to these different databases and environments okay so I thought I'd mention that since it's very pertinent okay so I'm just going to create this and um Zone redundant I don't care about Zone redundancy for now I'm just going to hit apply okay and now I've got some more options to look at now that I've picked that I'm up to 30 cents a month I'm going to click on next for networking okay so this is actually important as you're creating this database allow Azure services and resources to access the server you want to say yes uh for some reason it's not allowing me to do that um and perhaps that's set on the server side because uh what you guys haven't seen is I actually set up you set up the you have to set up a SQL Server first and then you can set up all these SQL databases under that okay um so I might need to go in there and change that at the server level um in fact I might go try to change that right now if I want this set up right and I'm a little surprised that I didn't already have that set up to be accessed and the reason why that's important guys is we want azure Services um to be able to access it okay so let's say we got powerapps well that needs to access it right um or you have something else in Azure that you might want to access you know so if I have something housed in Azure or some type of cloud services with Microsoft yeah why not why not allow it to be able to access that so here are all the settings um maybe that I don't the world to see about my server um but it really doesn't matter because there are firewalls and you wouldn't be able to access it uh anyway um you see at current IP client IP address okay and I'll say yes okay so I can open up SQL Server management studio and connect to it it'll allow my IEP address to access it but it won't allow any other IP addresses out in the world to access it so there's a little bit of security so as far as in hackers are concerned uh SQL servers are um really good targets um because accessing databases it's got all kinds of useful information in there like people's Social Security numbers if they're not encrypted or or credit card numbers all kinds of interesting stuff people's email addresses um so you should pay attention to security um and as long as you don't open it up to the world you really shouldn't have to worry about that um I'm I'm fairly certain you know there's a firewall blocking out all these IP addresses and I manually add an IEP dress that needs to access it I feel fairly safe um and I've done a little I've done a fair amount of server Administration installing servers and and maintenance that type of stuff um one one of the one of the things I did back or um probably about 15 years ago I was had my own dedicated server in a hosting environment so I had full access to the server and I got myself I got a real standard license to SQL Server I installed it on the server to be used and then I opened it up so anybody could connect to it because I had a Windows application that I want to connect to that SQL Server and that server was busy dealing with all of these um IP addresses trying to connect to it and trying to to try and username and passwords and trying to get in there all the time and I'm like oh my goodness like all these resources are being devoted to all these I guess hackers and people trying to scan um uh ports on this IP address and uh it was a little bit of a nightmare I always had to like do things security wise so I'm I'm glad that um you know all this is sort of set up this way um so anyway let me um see if I can find this real quick um if I can I'm sort of curious on this Azure Services if powerapps is included in that um so right now I'll just leave it as no um let's see here you can add private endpoints if you want let's say if you had an application or service that needed to access this um you know not through powerapps you could add those there and you've got connection policies I'll go on to security now they have a um Microsoft Defender for SQL so they've got some services that you could buy if you want and you can find out more about it by hovering over this okay if you so I'm looking at the private chat here Nick says if if user if I guess if you use a mobile power app how you manage their IP address well with powerapps their IP address isn't really what's used so you download you're on a mobile device you use a mobile power app so on a mobile device you download Power apps on your device and then you log in to your tenant you're going to see the app all the apps have been shared with you or that you've created and you'll click on it well what you're doing is you're actually your traffic is going through uh the PowerApp servers not directly from your device so in that case you don't have to worry about IP addresses we just need to make sure that powerapps the PowerApp servers can get in there okay and we're going to find that out here in a little bit um and if we we can't we gotta somehow go in there and and allow that okay um so I'm not gonna go into all this um for my Dev database I'm just going to keep going here um any type of additional settings um start with a blank database restore from a backup no I mean if you already have a SQL Server database that you want to use to start off with you could do that you could use a sample database um I'm trying to think that actually might not be a bad idea to grab user data um but I think I'd like to start with a fresh new database um if you use the sample it's going to use the adventure Works uh sample database that's been out there since like 2005. so it's been out there for a while so some of you might be gonna be familiar with that if you do a search for sample database Adventure works you'll you can find out more information about that um and I am going to click next I don't really care about tags for this I'm just going to keep going um and I am ready to create this okay I'm going to click on the create button and it's going to go create it right and uh as you see there it's it's going to take a few moments and it should alert us once it's created um we've got to mess with all the uh the username and password uh type of information connecting to it right so I am going to hit refresh sometimes you hit refresh maybe it's you know this is a great time for questions guys while we're waiting on this I don't have any yet I'm interested to see what it what it spits out though yeah so when you well I guess I do have one question you've done this before obviously it's so um what kind of tinkering do you have to do with it do you have to as far as like coding is there stuff that's maybe just as a step off that you have to tweak uh what do you have to do is there any of that um well there is a little bit of coding if you want to create your own views so you're gonna see me doing that um and uh we did a little bit of that this this last week I think in our accelerator group calls you can see anything but I mean we'll eventually do it here but um yeah so my main thing after you create the database your next step is to connect to it um connect with um SQL Server management studio now that is a free download so I recommend just doing a Google for SQL Server management Studio you can type ssms and uh it'll come right up say ssms download and you'll find it and you can download that from Microsoft it's a free tool and then you can connect to SQL Server uh databases as well as dataverse databases and um but for the SQL Server native SQL Server databases will create all of our tables through that and do everything you know um design wise create all the tables um and then the next step after that is to connect from um powerapps Okay so let's take a look at this so it is done deployment is complete and it's got a little button here go to resource okay so here's the information and um you guys can see all the uh the server name um so what I'm gonna do is I am going to hide my screen just for a moment and I'm going to bring up my SQL Server management studio and make sure that um I don't have any I thought I already had the window open but I didn't uh I want to actually pull up information without uh typing any passwords or anything uh live uh public to the world over there okay EK now it connected to my previous tenant um so I actually have two different Azure accounts so uh one thing that I stepped you guys through last Saturday morning um in the accelerator program was I showed you how to set up Azure so I have a completely separate Azure because I did that and so that's what has been set up so I am going to create a new server registration I'll actually show you what I'm doing and then uh if I need to type in a password or something I'll just hide it okay so this is what SQL Server management Studio looks like okay and um so I created a database called I need an app not I need an a nap uh so if you register a server you type in the logon and password you connect to it you can double click on it and brings the server up down here so you can see the database that we created this this last week uh we actually used the sample database so we'd have a little bit of data to work with okay and while that's opening up that takes see the thing is that first my first DB actually set up to Auto pause so it's going to take a moment to wake up funny um okay so what I'm gonna do actually I'll show this and use this on the other screen do is go to my SQL server and find the logon and password while we're waiting for that thing to load up is that okay it's saying that it's not available that's sort of funny um okay so I'm seeing the information up here on my other screen what is my password so um I'm going to tell you one of my jokes after I tell my joke uh Jeff I want you to tell us a joke and then nick uh you're up in line to tell tell us a joke what do you call a fish with no eyes that's a dad joke it is I keep all my dad jokes and a dad a bass yeah but I'm not sure I can do much better what you got you got any jokes for us none that are live Worthy okay [Laughter] Nick do you got you have any uh good jokes dad jokes or otherwise that would be appropriate or uh you know G-rated your mic's not on Nick you might have some jokes coming in the comments there yeah do you know who I saw today I don't know who you saw today well we're waiting job everyone I looked at I don't know if I get it what happens to an illegally parked frog what happens to a what illegally parked frog an illegally parked frog I have no idea it gets towed away uh yeah what did the DNA say to the other DNA I'm not sure do these jeans make me look fat [Laughter] all right I think I've got um my last one is content what's the difference between an outlaw and an in-law I don't know Outlaws are wanted oh yeah that's funny that's a good one oh I'm gonna put uh this is my server name in there I'm gonna use SQL Server Authentication um and I'm not gonna show my login and password or the password and then uh you notice let me see if I can just show some of this at the bottom of that little dialogue there's a little test button which I like to use test hopefully it comes back as successful you always cross your fingers because um sometimes you can beat your head against the wall but why can't it connect it's usually a firewall all right so I'm going to save that I'm going to minimize that one and I'm gonna double click on that server that I have oh my goodness Rick I'm afraid for the calendar it's these are numbered I'm gonna have to start a whole new YouTube channel guys dad jokes all right guys here is our database we created in Azure it's right here so um especially if you guys are brand new to SQL Server SQL Server management Studio just now that you're seeing this let the floodgates open for any questions of curiosity okay here's the database this is the server here are all the database tables I'm going to double click on tables that's going to open that up I don't have any tables yet not great I like starting from a blank slate here okay but I already know all the tables I'm going to create okay so let me open up my word document with all my requirements and let's go to our data dictionary okay so the very first thing would be um users yes um yeah let's do users okay so I'm going to right click on tables and I'm going to say new table so you notice it doesn't it didn't create a bunch of columns for us it doesn't have any columns in this database table at all okay so I always like to have that ID I like how SharePoint does its IDs I'm gonna make it an integer okay I'm also going to make it the primary key now primary keys aren't really something you you have to worry about with SharePoint or um dataverse okay uh because it creates them for you but we're here in SQL server and uh so we're we're in here with the big boys so we gotta have to take big boy responsibility and think of certain things that we normally wouldn't have to think about okay like the primary key now my screen here it's difficult I can't like zoom in on the interface here but what you'll notice there's a little key up here on the toolbar within this window it's a little highlighted in yellow do you guys see that I hover over it says set primary key okay so every single database table that I create uh especially in SQL Server uh where the control over it is always create an ID and every table has an ID and that's always the primary key it's always unique and if I have any worries that I need to import data from different places and they might hide different ideas uh IDs I might create a grid a unique identifier like dataverse does but that that really doesn't fall into the requirements very often for me so in that case I always create an integer an auto incrementing in Microsoft Access is called an auto number in SharePoint it's not really talked about very much it's just the ID and it's an auto incrementing number in SQL Server it's called an identity okay and identity so what I'm going to do is I'm going to click on that set primary key and now it's a primary key and there's a little pane right here that I I pull up okay and I can these are things that I can set for this column because I'm gonna make sure that's selected and look at this so if I go down here I'll see something about identity it's called identity specification and it expands okay what I do is it says identity I just double click on that and it changes it to yes so the very first record will be one and any new records will be incremented by one now I could start off you know how you start a new checking account with a your local bank like hey do you want your check numbers to start off with one you're like no start them off with ten thousand so it doesn't look like you just started the account right so you could do the same thing with um with your identity um you know somebody like oh you know I'm in I'm a new user in the system I haven't got the idea of one um you know you could start off with a million or something or a few thousand uh if you want to I always start off with this one one thing here um so that has been set um the primary key has been said that's good I want to keep track of um first name now you don't want to put space you could put spaces in here but you always have to have things around it if it has a space in there so um good database naming practice is not to include spaces um so you guys probably thought I was weird whenever I create all my SharePoint databases I never put spaces in there uh that's because that's good database designing just don't put spaces in there in fact you use proper casing most of the time some old school database dbas database administrators uh or if you come from a Mainframe World a lot of times they'll have these on the field names in all uppercase and they'll separate the words with underscores an underline so sometimes you'll see that okay and sometimes the best practices are the the standard that you have to adhere to is dictated by the organization you're working with or for okay now first name I want that to be varchar uh maybe 25 characters for a first name last name okay we're gonna have here of our Char um last name's gonna get a little bit longer sometimes they're hyphenated I might make that a hundred characters just to account for all kinds of different stuff okay um I'm also going to add something here a display name okay I'm gonna make that varchar 255 and a display name a lot of times I like to use for um maybe I want the first and last name put together maybe I also want to say uh Mr Mrs and or maybe have a nickname and parentheses it's just something that I can do um you know that's already got the first and last name or whatever the user wants to be displayed in a system you know um so sometimes it's nice to have a display name there like that okay um what else has chat gbt suggested that I do so I've got an email one email two and what I'm going to do here is I'm not going to call an email one and two I'm going to say um well what I want to capture is the email that they're using to log in with so it's going to be the I would have I would say o365 email but they don't call it Office 365 they call it M365 and I'm I'm worried if I put in 365 they're going to change to something else in a year but um let's say um maybe I'll just I'll do that 365 um email okay so that will essentially be the primary uh email for logging into the system and the user function within powerapps if you want to grab somebody's email address dynamically you know you can utilize that user function that email address and it's going to give you that M365 email okay so that's that's what we're going to put in there uh varchar 255 email addresses aren't usually that long but just in case they are I guess they could be and then I'm gonna say uh personal email so somebody comes in they typically have a Gmail Hotmail some type of alternate email um so I'll say personal email for alternate I like alternate archar would be good um other type of stuff would we want to have in here uh communication preference okay and uh we could make this like uh has several values but I I almost like what is suggesting here which is a free form text entry okay so uh immunization reference is that what it called it the field is a little long there yeah communication preference okay and perhaps the bar char here I'm going to say 500. and I might make it a text area um if people want to type a bunch of stuff in there I yeah I'd like to capture it I don't want something like nope you can't type all that stuff if they typed in a paragraph I want to probably get at that paragraph and actually read it um okay there's a great question here which is what's the data type in SQL equivalent for multiline um it's not differentiated between single line or multi-line it's just how many characters you want to have in there okay so I would say up to 255 characters you would consider that a single line anything over 255 characters I would consider that a multi-line okay now how far up can you go here it used to be the maximum amount of bytes that are stored in the SQL Server database were 8 000 I don't believe that's a limitation anymore um so it used to be eight thousand okay um and even then you could go above and beyond that by using a completely different data type okay so um let me let me do another field here and we'll call this uh comments notes that type of thing maybe notes I'm gonna have two different type of notes I'm going to call this uh user notes they can type in all the notes that they want they can type paragraphs upon or you could type a gigabyte worth of a Word document in there and this text field is an unlimited it stores it in a different way like blob storage it's Unlimited okay perhaps you don't want to type in that many uh so in that case I'd probably say VAR charm thousand characters is probably enough um 4 000 is probably enough um but you could use text there um something else you can do here is say Max the maximum amount of characters that that you give me um so right Max was yeah it puts Max and all up our case like that so in the case I don't know what the maximum amount of bytes is for a for a whole record these days it used to be eight thousand I've never come up again I've never created so many columns and so much space there that I ran up against that limitation um but if you could let's say it's a thousand bytes of information per record okay um then let's say I've already got 30 columns of space there by using Max it's going to say give me the maximum amount based on what's what's left there that's how I understand that and that's how I would use that okay um so we could do that I think I'm going to stick with 4 000 now the beauty here is you see where it says varchar varchar varchar okay well I could go in here and say riddle initial initial right Jeff and this I think I did and in that case okay you also see this in in is for international so the English language we got 26 letters in our alphabet right and we've got numbers and other things well in terms for a computer to store the information it stores a number for each of those letters and there's uppercase letters lowercase letters everything can be fit within 255 bits of information zeros and ones okay that gives us 255 different possibilities and that works for the English language but you think about uh Chinese Mandarin or other languages uh you can eat a lot more than 255. okay um and in that case if you're making a international application let's say for other languages then you'll want to use that n because it uses two bytes per character instead of one byte okay so if you ever see because up here for like first name it doesn't have to be it could be in varchar if I wanted and if you go down to the data types I can show you all the data types okay look at all this I don't wanna cover anything up here let me put um let's make this bigger here in okay she was how many do we have okay I'm gonna have to move us to the side or something to see all these so I want you to see him okay there we go okay so we do have if you want specific amount of characters you want to use Char so if I want a middle initial I only want one character in there I would say char short for character okay now you see down here I've got n char because of money um oh we got taxed and we have Intex so they're all for a lot of these there especially when there's characters involved you do have this International that in um okay so you have uh the VAR what does the VAR mean it means variable amount of characters okay so going back here so the general rule back when I was studying for my Microsoft certified database Administration uh certification that I got years ago so I'm a an mcdba um is that the what they said was if you have 30 or less characters you should just say char 30. anything more than 30 characters if it's going to be a variable amount of characters use the VAR chart and what the VAR chart does let's say I've got let's say for first name I've got varchar 25 okay so I'm sort of violating that rule um if I like my my name was um da instead of Darren d-a um is it going to store 25 characters not there no because it's variable it's a variable link they'll only store those two characters and then there'll be another character saying that hey this is the end of the string rather than if you had Char 25 it would literally take up 25 and if I said dot d a it would have a bunch of spaces in there or it would have a space for a character I'd actually have a place in there and actually store off that little bit of space in the row so that's sort of uh important to know about as you're reading and talking about data types or SQL Server okay this is interesting uh data's King says another disadvantage of using dataverses IF you create the columns in the table you can't go back and change the data type after the table has been saved um all right and I'm going to bring this back up for my life so I data type and sequels will criminal to multi-line okay so I answer that question which is text okay um so if I was going to do a middle initial I could just say Char one okay and um you know maybe I'll put a 30 second drag that up there that's sort of nice okay um any questions about data types ask them now and I'll cover those uh quickly but um I want to keep keep this thing moving so hopefully we get back over to powerapps and show you how to connect and everything um Okay so we've got um user notes and then I'll also say internal notes okay let's say um this is onboarding for my students and and uh Jeff is one of my students and I wanted to keep some notes that he's a troublemaker um I might put that in there and then he wouldn't be able to see it right so that'd be an example of internal nodes so like in my ticketing system uh people put a description of their ticket but all the technicians over an I.T they might say they might have some notes there that maybe aren't nefarious by any means but it's like the user doesn't need to know all this stuff that's going on that we're keeping track of about them or their ticket you know um so these are just some ideas of of additional things I would throw into a user table like this okay um is there any issues if we use spaces inside column names yes so I can tell you right away inside of power actually have to put those little single uh quotes there in order to it's not that big of a deal okay but if you are writing t-sql um you always have to have these square brackets and and this development environment for SQL Server we'll try to put these square brackets all over the place I think it looks so ugly uh and if you don't have spaces or weird characters you don't need them okay um now for dataverse it's a it's a recommended best practice because it's used by model driven and the names that you give it are actually display names they're not actually the column names I don't know if you guys have ever seen that in dataverse you actually look at a column and it has something like a CR 100 underscore then whatever you gave it without any spaces that's the real field name in the SQL Server underlying that dataverse database okay um so anyway um it's it's typically just not a good practice for a real relational database management system to put spaces in there um in my opinion it it it sort of gives away to people if you have a DBA looking over your schema over your database like why'd you put spaces in here you know uh you might get asked that so it in my mind it um it just sort of looks bad uh so I wouldn't put in spaces in there unless there's like a really good reason for it um so anyways we got user notes internal notes and um let's go back over to my word document what else do we have here okay um something that I know that I will want in here is um I probably want to capture a user's image okay and I'm gonna put that now you could say binary you could do that um you could also store it as a specific since we're using Azure you can use it uh put in an Azure blob storage which I didn't want to cover I think for now I'm just going to use text now especially with SharePoint a lot of times I store the images um as base64 texts and and put that in a SharePoint um because of how SharePoint works now with dataverse you do have an image type um and SharePoint does change over time just making sure there's not an image in here and there is okay there you go image so we'll do that and um what else should we have in here I think that's probably good for now now you might want to know when it was created right uh created on the time now there are two different types of date and date time so you got the flexibility there uh created five Okay so varchar we could probably just put their email address or the email address the first part of the email address for the username we could do that um I think I'm going to just take in their their M365 email uh because if I make this application really flexible so that it could handle multiple tenants and different purposes and different clients an email address would be really good for that and then I want uh fast modified on eight time you might want to decide early on if you get in a SQL Server development for every database table that you create you're always going to have an ID like I decide ID is going to be an identity it's going to be an integer it's always going to be you know the primary key for every table and then you also create these created on created by last modified on last Modified by that is a good idea are five hey there we go okay now I'm just going to go ahead and save this at this point and something you'll notice is naming the table is like the last step not funny right so we got users and I'm just gonna keep the table name nice simple users okay so that's in there now something I'll show you since probably a lot of you are new to SQL Server you can create a diagram there is no need to use view SQL is that the that's that's what it's called right I always forget the name of what it would um is that right Nick and Jeff um yeah draw SQL um yeah thank you so I usually use that and uh for SQL Server it has a diagramming system that is even much better than that okay the first time you try to create a diagram it's going to say hey the database does not have one or more of the supported objects required to use diagramming you want to create them and you just say yes so it's going to store the diagram and some system tables probably down here and it's probably needed yep there you go they created a table to keep track of these diagrams isn't that interesting so I'll go to diagrams I'm going to right click on this and I'm going to say new database diagram okay there's my user so I can add it is my microphone picking up my toddler screaming no no okay good my my uh my fancy microphone is doing some noise canceling here okay so there's my table now if you had one of many relationships you can create those relationships and see the lines and everything um in fact you guys remember what we did inside uh the accelerator sessions that we did together we already have a a diagram looks like we've got two diagrams for some reason oh yeah this is great we pulled over all these tables yeah and it's got all these relationships in that in that cool um and I think we can zoom out and zoom in and all types of very cool stuff here thank you so it's a great diagramming tool I love it and also what's nice is if I were to go down here and say um well you know I could actually go in here and there's some things up for the table designer look at this I could look at some more things um I thought I'd see some um you can set this up so you can actually yeah table view standard Keys name only custom so if I said standard look at that I've got all the data types there so I could just sit here add more columns do all the data I can do it all right here from the diagram and see all the other related tables SQL Server is great love it um now I'm going to save the diagram diagram zero it's not a good name for it let's say this Main and I can close this for now I'm gonna close that one for now okay so we have a a table here now what I'm going to do is I am going to create let me open up um this open wraps go into the right environment here okay so I'm going to create a I need my let's use you know what I need is my the most recent version of my template so I'm going to upload that real quick and my way of uploading my template is I just create a throwaway um project here so that I can upload it very easily okay so what I'm gonna do here within the accelerator School environment I'm going to go over to classroom with all my downloads as accelerator soon you have access to all my stuff here so I'm gonna go into the jump start kit and I want the landscape template I'm going to download that really quick all right and I am going to save that to downloads that sounds like a good spot leave it hey there it is I can get rid of that um all right so now that I got this little uh throw this little uh test app that I'm gonna throw away here I can hit just Ctrl o okay I'm going to click browse and I can go to start kit do I want to save that that little throwaway project no I do not and so then it's going to upload it into its place there now what's the first thing that we should do when we start a new project Nick or Jeff can you tell me you've got to say that David's favorite yes save early save options I still make the mistake sometimes it saves oh my goodness it drives me nuts um hopefully I was sort of hoping it was going to prompt me for a different name this is fine I'll hit save as and I'm going to call this onboarding Dave okay now Within powerapps off of that settings you'll know that it does um I'll change this to 2000. I'm a risky type of guy live on the edge it will save every two minutes but unless you do that initial save it won't start doing that so that's really important to do um I don't need my video there take that out now the first thing that I want to do is actually connect um to my SQL Server database so I'm going to hit add data I'm going to go down here into connectors SQL Server okay now here we go um I don't have to do this very often so sometimes [Music] um well it's going to be SQL Server authentication that's the login of the password that I have for it so that's what I'm going to pick and if you wanted to connect to an on-premise SQL Server you could pick this on-premise database data Gateway that's the case that's a whole other ball of wax um a question is how often should I publish my powerapps applications well whenever you want the users to see see your new changes if you're in the middle of fixing a bug you're in the middle of developing a new screen you don't want to publish until you're done right so it's sort of like if you were creating a web page on your website and uh you started maybe a new blog entry and you were you're creating a draft and you weren't quite done well when you were done with it and you're ready for people to start seeing it that's when you would want to publish right so the same thing uh holds true for your applications okay so I'm going to use cloud services see that it calls it cloud services there um okay so I'm going to use I think what I'm going to do is hide my screen for a moment because I don't want to show all my credential information here um and so you'll put in the server name database name username and password okay that's what it's expecting there so my database name is onboarding okay that there my username is that okay and now what I'm going to do is click on that we'll connect button at the bottom and it warns me hey this is a premium featured and I'm like I'm fine with that okay and now I'm going to show my screen now and look what it shows me after I hit that connect button um here are three tables one of which we knew about and these other two are if you ever see something that starts with CIS those are system tables that were created uh by SQL Server so there's the table for the diagrams and here's one for firewall rules okay so I'm going to click on just users for now I'm going to add that okay and um so we're going to be doing lots of really cool stuff on this project this will be an ongoing project hopefully you guys are excited about uh watching me build a SQL Server type of application um together and uh we'll we'll go into all the views and everything but for right now I just want to see that this works okay so I'm gonna do something here that uh I actually um you know what I already have a gallery I was I was about to pull over a data table I always recommend people don't do that what I'm going to do is I'm gonna say uh this new screen look at this this new screen here and I'm going to duplicate this new screen because I might want to duplicate it in the future and I'm going to call this my users user uh man uh screen okay and inside of this make this a little bigger here for us I'm gonna go look at users here we go and what do we want to display here well let's just look at first name about the apostrophe this one will be your last name and then um here in this text box I don't want to do anything just yet let's do a patch not spelled that right okay we want to patch the users table is it user or users oh man users uh I don't know why it said user is it talking about if I said user what is it yeah it's talking about the user function okay users okay um and then the second parameter will be defaults which tells us we want to add a new record okay and of course the screen can can uh patch existing records but right now we just need to get some data in there the third parameter for patch have some curly braces and specify the values okay so we're going to have a first name what's first name well I'm going to use this little text box here and I'm gonna call this text box txt first name um here same and we're going to say last name txt last name again and for the default value it'll be lasting and then for notes we'll call this maybe internal notes okay here we go and we'll rename the text box txt um internal notes first themes txt first name dot text bigger last name pxt last name text well I got I got a favor to ask you guys you ready for this hey if you're getting anything helpful out of this a comment or even a like really helps the channel and that's people like you know this is a good content much appreciated got internal notes and we'll say txt and kernel notes dot text that should work all right so let's let's uh let's try this out here I'm Gonna Save I'm gonna run this and this is going to be Darren nice um this guy is really cool and then I'm gonna say um I should um or I should probably do some uh self-deprecating uh humor here this guy um can't spell okay and I'm gonna hit save oh here we go and let's add a uh John Doe and uh this guy is new or it's saved now we got two records and if I click on John Doe his information pops if I click on Darren his information pops up and then if I click add new I wanna um which is what I should be doing for for new records because if I click on Darren and I want to add something I don't want a duplicate Darren right so what I want to do is have a little local variable here that keeps track of if it's a new record or not are we in the new mode or not so I'm going to say update context we braces well new record it's true but if they click on this thing over here I'll go to the on select and I'll make sure if I click on it that means we're not going to do a new record we're going to say false and then um perhaps if the the screen loads up I want that to be initialized to be false whatever that is and then also if I save whether it's a new or an existing record it should always be false okay and then based on that local variable f that local variable is true and we'll use the defaults otherwise we're going to use that Gallery which is over here I got to go click on and get the name of it I'm going to rename it gal user listing let me use that in the code window I know I'm going over this fast because I've got probably 50 videos out even here on YouTube where I I go through the patch um so and I also have a 20 or 30 minute video where I go through with patch how to do that uh very nice and slow and everything so if you guys want more information about it just do a search for darrenise and the word patch and you'll find that video okay so we'll do the selected on that that will give us the record the existing record so the second parameter is the record that's going to be patched So based on if it's a new record we're going to use one of those two okay so if I go over to John Doe I'm going to put two exclamation points in here and then I'm going to go down to the bottom and hit save and I'm click up on Darren and then come back and notice that there's two exclamation points so there that that shows me that this is working if I hit add new I almost expected these things to be blanked out okay so that needs to be fixed so on the default I want to do a little if statement like I did here if it's a new record then I want it to be blanked out for the last name and then the notes very similar that should work leave it I'll run it so we have a new record now notice I've got title screen name uh this needs to be redone as first name last name also these column headings of course we'll need to do that so let's say um Mary Smith um and I'll just say dot dot dot save so now we have three guys we have just created a SQL Server application with powerapps Isn't that cool and we'll continue to build on this you guys know the requirements what I'm going to be building where we're going but uh I hope that you guys found that really helpful for some reason YouTube thinks you're gonna like this video next let's see if they're right or you can select this playlist which I've selected for you based on the content you're currently watching guys gotta hurry click one of them otherwise YouTube's gonna auto play some other video

Channel: PowerApps Tutorial

Views: 10,912

Keywords:

Id: 8joTrq7lZAw

Length: 131min 15sec (7875 seconds)

Published: Sat Aug 05 2023