Using Dataverse with Power Apps

all right good morning today is about the dataverse so we're working with a powerapps and dataverse so I've got a little bit of the agenda on the screen here what we're going to do what we're going to talk about today um now um I do have a whole module on the dataverse uh some of the beginning type of stuff like what is dataverse um talking about it's viability as a data source is it good is it bad where does it fall with the other data sources and creating tables but I think the uh the most I would like to focus on today is building out tables relationships uh dealing with a lookup type of columns between tables and then finally we'll go back into powerapps and start wiring this all together and see how it all works for us inside of uh powerapps so um let's start off with um just talking about dataverse so dataverse if you are inside of powerapps here you've logged into your tenant um all you got to do is go down here to tables on the left side okay and you'll see all your tables here now in dataverse there's a lot of tables that are created for you um what I always do is I like to click on this little custom tab so I only want to look at the the tables that I've created if you want to see all of them right there you can click on all but uh there's there's a lot of them um there so just as a a warning you're going to see a lot of stuff there but um so let's talk about so before we start talking about creating tables and everything um this is how you get to the dataverse okay and uh you can go in I've got a a database table here called my students and it's got a lot of sample data and we created that sample data uh probably three or four weeks ago when we were talking about data in general with powerapps and we used a website called a mockeroo and it looks like this macaroo.com okay and we generated a bunch of uh people type of data and uh put it in here and so we'll sort of reuse that and um so I started an ERD diagram over here so what I'm thinking is that we will have um at the very least three tables now if you guys have ideas for additional tables uh let me know and um so we've got person then perhaps uh people cars the other person's cars so if this ends up being student this would be student cars yeah so um and then I thought we might have a good pool of uh different car models that are out there um so that's what I'm thinking just sort of let you know the the data set that I'm thinking of as we're going about this so typically when people talk about what uh the date of first is it is in my mind not officially it is a great data source for the Power Platform and if you say oh data versus just a database well that's not really true it's actually um it's a database but it also has a bunch of other features built into it um so there is security built into it it's got um business rules business logic that you can Define in there um you've got uh Choice columns and you can have these choices defined there's a lot of stuff that's in there that you'd find in any relational database system but um it has been designed for the Power Platform so just keep that in mind so there's going to be a lot of other things there that um that are to be used more for the other flavors of powerapps and when I say other flavors of power apps I'm talking something besides canvas apps so you've got model driven and you also have powerapps portals the powerapps portals has sort of been rebranded for uh Power pages okay but model driven and power Pages they use dataverse exclusively for their data source so there's a lot of good stuff in there um for those other two types of powerapps applications so uh yeah so um I just saw minty's comment here in here this is actually a valid uh comment here there it is a number one data source if you have access to the premium license uh that is true minty um now you also have uh dataverse for teams which a lot of people don't like it has some uh limitations and you can use dataverse for teams without a license uh one of the limitations for that is um I think it's a two gig limit uh which is great for starting off because it's a lot easier to migrate from a dataverse for teams over to a fully fledged dataverse database okay but if you want to get away from SharePoint don't have the premium licensing you could just use dataverse for teams okay so it's a dataverse environment set up for a particular team within Microsoft teams product okay so um I wanted to respond to that okay so yeah the dataverse is really the best data source most of the time I say most of the time because there are times if I want to do something really complex um very uh data intensive I might be reaching more for a SQL Server you know Azure SQL server or a SQL Server that's on premise um and there are upsides to that now there's no real way to do joins within powerapps dataverse sort of lends us a hand by doing um like lazy loading of the data in a many relationship so as we design our our database our schema here we're going to have let's say students students have cars so if we have a reference to a student record or a variable referencing a student record we could say dot cars okay and then we would have all the cars associated with that student or that person okay and you wouldn't necessarily have to go do another filter to go get that um so that's sort of nice but within SQL Server I could have five different tables that are all linked together and I could create one query that would join all the data together and I could pull that back into powerapps as a single result set a single data source uh and I found that to be very handy and a reason why I might want to use SQL Server instead of dataverse but as far as in the speed the performance dataverse outperforms all the other data sources because it has been designed for the Power Platform and it has less apis to go through and there's some really good documentation out on that with with diagrams showing all the different apis let's say you have to use to get the SharePoint data or SQL Server data and the way that you access the dataverse data is much simpler there's maybe one or two steps as opposed to three or four okay um so looking through for pertinent questions here so I do see uh Ramesh how should we decide between a sh or how should we decide between SharePoint lists and dataverse as a data source uh usually people go with SharePoint um yeah people usually go with SharePoint if they don't have the premium licensing um also if you already have your data in SharePoint don't have to migrate it um you don't have performance concerns you could just go ahead and go with your SharePoint data it's already in SharePoint um and let's say other applications or people go into SharePoint lists and edit directly I would hate to have to move that to dataverse then you've got to provide some other way for those people to edit that SharePoint so now you have to create uh different editing screens or or what have you sometimes people just create SharePoint lists out there and people will go in and add edit delete records and those uh SharePoint lists and if you still want to have that available to the users um you might want to keep that in SharePoint but dataverse is actually the superior data source and I don't even like to mention Excel as a data source there's a lot of reasons why it's a bad data source so I don't even usually mention that as a viable data source for an application in powerapps so remasterfully that's uh fully answers your question there so I think what I'd like to do is get into our examples here so I'm going to go over to my entity relationship diagram that I'm going to create here now something to realize when you create a table within dataverse it likes singular names so instead of me saying people here I'm going to say person and it will automatically try to create a plural version of this and you'll reference the plural version in powerapps when referencing this database table so I'm gonna stick with singular names here um and then what you'll find is um let me go into my tables so I've got this my students table here and if I go into the columns you'll notice that there is column with the same name as the table name okay so here it says my students now when I created this table I I think I created it with the plural name so let's say um instead of creating a column let's go back to tables let's create a new table and I know I definitely want let's say car model so let's create a new table here called car model okay so you guys and I am going to try to make sure that I'm accommodating people uh that that perhaps need to see this a little bigger here so I'll zoom in a little bit there hopefully that helps out so you see it says car model that's singular here's the plural name so it adds s to it and sometimes it gets a sort of weird sometimes it has two s's on the end um so you're able to change this if you'd like um now something else that you should notice when creating a new table is there's a tab at the top called primary column so I'm going to click on that you know how sure when you create a new SharePoint list there's always this column that it likes to create called title well the same thing as for the dataverse is that um it wants to create a new column every time for you uh it's not title in this case it's just going to be name okay and um I like to keep it I don't like to go against the grain and try to take out or modify the columns that it's trying to create so there I always I always know that all my SharePoint lists they've always got that title in there and if you try to rename that title in SharePoint it gives you problems in powerapps um so I don't mess with it uh so the same thing I'll do with dataverse I'll just leave that name right there now you could go in here the advanced options and say you know maybe I only need 10 characters for what I'm going to use the name for but um a hundred characters is fine for me for a um let's say a car I might want to say year make model you know and stick that in the name so I'm going to go ahead and so this is a car model I'm gonna hit save all right I'm going to zoom out a little more here okay so we've got our columns okay this is a little funny Kurt does this look a little weird to you as as you've done stuff in a dataverse yourself are you surprised that there's only two columns in here I'm just blown away yeah what's up with that I'm used to seeing like 20 or 30 different fields in here so yeah so I think um people maybe complain that they only want to see their columns not the other columns so there's a created created by modified modified buy there's all kinds of columns in here that a lot of times you're not interested in so this is uh like an on-the-fly change that Microsoft gave us probably this week oh wow okay so that wasn't about it then okay I thought yeah you did you've never yeah I've never seen this where it only gives you your account I sort of like it but I'd like to I'd like to um like unfilter it so it shows all the columns um so what's sort of weird is that it's not very obvious how to see all of them but I think that's fine for now um so you notice that it creates a column here for me car model and that name is the exact same name as the singular name that we gave our table so I did all that to show you guys that okay so what we want to do when we create our columns here we got car model I'm going to go in here the ID that field is actually going to be a grid or a guid globally unique identifier Okay so we've talked about grids in the past um and it's a a number that's globally unique it should always be unique in the in the universe okay so instead of ID I'm gonna have here a car model and that's the primary key for the database table okay and um some of us I'd like to keep track of is um let's say fake and I'm not gonna try to change the data types because this particular website draw SQL does not have um it's not aware of dataverse data types it's not aware of SharePoint data types um the closest thing I can find is SQL Server okay so um Kurt if you don't mind would you keep an eye on the comments section for me there's a lot of comments coming in and um I don't want to have to pause every time so if you see a good question that's relevant to what I'm talking about please interrupt me with with the question if you don't mind sure okay all right cool uh so we got uh car model um we got make model I'm here okay now we're thinking about cars in general um like if I told you oh I want to buy a uh 2023 Ford Mustang and talking about that in general okay that's what I'm talking about with this car model that's what I'm imagining things will be there and then if I go buy a Mustang I go to the dealership they run in a deal on the the 2022 models so I wanted that better deal so I bought a 2022 brought it home well that's got more information that can be tracked like my individual Vin the condition of it um all that type of stuff so that's what I was imagining having three different tables here okay and um all right so for person we should have something here you know for the primary key okay but perhaps well okay so all these tables will have a name as well we'll have um first name there's a couple questions here you might want to go backwards on a little bit here okay how should we decide between SharePoint lists and data versus the data source that's a good quick question yeah yeah I just answered that a few minutes ago okay I thought for some reason I missed that so then we got um how did you switch it from the uh the erds to the ERD skate schema yeah um how did you switch the ERD schema so what I have is a browser Tab and uh the URL to that is you know I can actually share this so you guys can see it the night cream link copy and then I will just paste it right here okay foreign if you click on the link you can actually see this so I just have a different browser tab at the top okay and then uh so also this other question here we we want to see a view like SQL Server yeah that would be that would be nice um so here's a few fun facts about view SQL Server database okay so the dataverse does have views and it's not uh like a SQL Server view if you're familiar with SQL Server views um those are met uh mostly for model driven applications so you've got model driven forms model driven uh views um and such there so that's not the same thing but um a fun fact about a dataverse databases is actually SQL Server underneath the scenes that doesn't mean you can use all the features from SQL server but you can connect to a model a dataverse database through Microsoft's uh SQL SQL Server management Studio or ssms sometimes you'll hear that you can actually connect to that um and there's there's articles out there on the internet if you do a search how to connect to dataverse from ssms you'll see some of those articles okay very good all right I'll go back to this and we'll probably want to have a last name and other things that you might want to keep track for a person okay I'll zoom out a little bit here all right then we've got um a car so of course we're gonna have a uncalled car that will be a grid and then we also will have a name okay then we will also in this case a car will belong to a person so I'm going to say person look up and that's going to be a lookup type of a column okay and what you do here in this website draw an SQL is you click on the foreign key okay and it's going to point over here I'm going to click on that dot and I'm going to drag it over there and typically that's going to be a many-to-one okay one person could have many cars hey Darren what's the foreign key a foreign key is a column and a different table that will cross reference or or reference the table that it is related to so this person lookup is a foreign key see this thing over here this person this will be a good this will be the primary key the unique identifier and typically a database table is indexed based on the primary key and then so you'll have typically have whatever value is here you'll have the exact same value over here so you're there's going to be a grid over here for the car that will be the same grid as a person over here so let's say there are three cars on my driveway or in my garage and they're in the database well all three of those for the person look up will have the grid under my name okay and that's how we use if if you have a lot of uh Records in the other table that are related to the one on the the person side that's how you do a lookup to find all of everything that has that same ID in it will show up right that's you want records with all the same ID as the person that you're trying to look up yeah so if you're trying to get it those three cars out my driveway uh reference to them in the in the database uh you'd go find me and what's great about dataverse is different than SharePoint so I sort of want to talk about a lot of people are familiar with how to do relationships but in SharePoint they're not familiar with how to do them in the dataverse what's great is let's say you've got a variable that's holding the person record that's got Darren East there you could say variable Dot uh uh let me let me think here um yeah it should be Doc cars and you get all the cars there so you don't have to do a join it sort of loads it in for you yeah I was going to say in the actual dataverse table doesn't it when you and the person look up column it actually has the name there rather than the grid so that you're not trying to compare it between yeah the thing about grids is they're not very human readable they're not very human friendly to look at they're just a bunch of I think it looks like a hexadecimal number with uh with with like three or four different parts um and in fact if you want to see what a grid looks like um I'll create a new screen here and I'll add a label and inside of powerapps you have a grid function hear that that's what a grid looks like so and that's trying to memorize that for every number you gotta yeah right what's that try to memorize that for every number you got to use right right so if you see a bunch of goods there you're like okay what person is that now I gotta go like copy that and paste and try to search over here based on that grid yeah um and then what I'm imagining for carb is uh we'd have a lookup we would have a car oops our model look up so both Kurt and I like a naming convention that we put look up at the end of a foreign key that is a lookup type a column in dataverse okay it was SharePoint I would probably call it car model ID also in conventional databases you usually see ID as your as your um what you're going to be looking up but it just seems like there's just so many fields that we can be dealing with here in dataverse just to call something a lookup tells you okay that's a lookup field right you know so it just makes it easier so something that I'd like to point out here is let's see if I zoom in here I know I've got a lot of uh space to work with [Music] um okay very good so you notice that I've got some columns over here that aren't over here on the right side like the condition of the car this car could be 20 years old um an individual car will have a uh it's like a globally unique identifier for that car it's not really a good but it probably looks quite a bit like a grid everybody's familiar with a vehicle um what does Vin stand for vehicle identification number maybe and um so and perhaps we could keep track of what color that car is oh and Darren incidentally in the old days you would be able to do data lookups on the bin number that's why the VIN number was created to begin with but so it had a globally unique identifier for vehicles they were attempting right so you can actually do you can do searches back in the old days on the VIN number and that would be considered a unique ID now though we have the globally unique ID called car so we don't even need to use the VIN number anymore but you still could if you wanted to yeah yeah it'll be a good a good item to to do a lookup on absolutely yeah what about a license number also yeah yeah okay you mean like a driver's license right or I've lost this plate yeah oh a tag okay cool tag yeah that's excellent and then maybe a person we could put their uh BL number driver's license number or their birthday and Chris asked how did you establish the relationship um okay so right now we haven't created them in dataverse we're just doing this in the ERD diagram first before we go do that but how you establish a relationship here in this draw SQL tool okay is that you click on the foreign key and you see these dots then you drag that dot over to the other one and that's how you set those up is there anything else that you guys feel like we need to do uh or add on to this and we can always come back if if uh what we do is is really um straightforward here but um okay great Okay so um I have over here let me go back to tables I've got um a table that I could it's called my students and it's got I think at least a thousand records in there which is nice um so I think I might just use that and then let's create a new table we already have car model let's create a new table for our car please are okay so we know that a car in here is owned by an individual a person okay and I don't want it to be confused with like car models so um that's what I'm going to call it that I'm going to keep this name as is and I'm going to click on Save okay you need a person look up now there are a few ways that we could add on to our um the dataverse table we could go in here and say you know do this little Chevron here okay there's there's the grid there um that will show the ones that are not listed there but you could always click on this plus sign and actually add the columns right there so it's like a quick uh shortcut otherwise if you go into columns you'll see all the columns oh there's all the columns oh no I'm I'm a little sad earlier meant that I said that it takes a while but they're going to show up oh [Laughter] yeah so you're going to see all the columns here okay I sort of like doing this here so I don't really want to see the grid here and all these things here are automatically generated so I'm going to hit the plus sign the next thing I want to add is a person lookup okay and I know that the table that I want to refer to is called um my student I'll say look up okay now just like in SharePoint any other database system you can pick the different types and you might notice here in text there's all kinds of different choices and let me make ourselves a little smaller here so we can see the whole thing you'll notice there's different types here and these are all text just realize that a lot of these types are created for model drivens so the model driven forms the screens know how to deal with that particular piece of text should it deal with it as a phone number or um you know a URL email that type of thing okay now in this case I'm going to go down here to look up and I I just click on look up here okay look up and then after you set that to be look up for some reason it didn't pick it okay there we go look up and then down here on the related table I'm gonna go find my students if you start typing it it actually comes up really quickly and let's try that out if I start typing my am I doing it wrong yeah no let's see if you look right there to the to the right of your uh or to the left while you're typing like start condenses the list okay here we go yeah okay very cool I like that so my students we'll look at selfie and stuff can we stop right there Darren real quick real quick I just wanna I think it's really important to um accent a point that a look look up field is its own data type it is a data type you have data types of text you have data types of numbers and you have a data type of lookup I just wanna I just want people to understand that that that's what you're going to use that field for is a lookup that's not that you're not going to be adding or subtracting or or concatenating strings with it you're going to be using that field specifically for lookup it's its own data type just kind of want to make sure that people understand that absolutely and let's say you want to look at so that will actually when you reference so you've got a person's car and you say dot uh person or my student which is actually the person we're trying to relate to if you just want to get at that person's name you know you can say dot name or dot full name DOT first name dot last name or whatever to get it that and that gives you the whole record so you could look at any of the other columns as well so every once while somebody might create as they're doing these relationships they create a lookup column maybe for every field that's over in student and you don't need to do that just create one student lookup and then you can access all of them uh when you click when you hit that dot inside of powerapps that's that's the the beauty of the whole thing because in the old days you would have to create another an entire table reference to that so so you would have to to look at the other at the other uh table an entire different reference here you're actually using the same table reference but you're saying you're saying the person dot carlookup or whatever you're doing or card.person lookup and looking at the whole record it's just amazing it's it's almost like cheating it's really cool I had a question too Darren um has anybody ever figured out what that other thing is what was the other thing it was uh uh yeah you have look up and then you had a something else in there what was that custom customers what is that for is that the model driven type thing or something maybe it's something that I I never use but um let's go over here to tables and let's go cool let's see if we have customers so there is a a customer object already here that's generated from dataverse okay so if we go in there I believe this is what it's talking about wow okay okay um so I don't want to get too far off the beaten path here but I do want to ask so do you think then that these customer tables can be worked the like the for instance this table the customer table could it be used as a template like let's say if you had a customer um you needed a customer table in your app you could just use this one it could is that what they're trying to say they're trying to say look use this as a template maybe you could um there's a lot of stuff that is coming over from the Dynamics 365 side of things um and um so if you use Dynamics 365 you know you're so it's a CRM system so it's going to have customer data some Erp data um I don't like to intertwine my data with something that might be in there associated with some CRM system in the same environment so it means that software developer building my own apps and I'm not being asked to integrate it with the CRM I like to keep everything separate so I'll throw that in there so we have uh we have uh Karsten Groth customers is a special lookup object which allows for a polymorph usage of both account or contact table very good thank you for for sharing that okay um all right so I'm gonna go here and go back to tables all right let me go back to um person car and we created our our lookup here there is our grid was that right Kurt was did I call this person car yeah here's look up my students okay person's car I don't know if you saved um okay so I'll hit save there yeah I think I had a another tab in there that was sort of messing with me there okay so after we create that now we already have car model out there we could create a look up to that one okay so click on Plus and let's say car model hook up what are you thinking Jeff you following everything okay yeah it's uh it seems pretty straightforward yeah that's good this might be the most difficult thing that you've got to deal with in powerapps right here is in the lookup so if you can get this the rest of it seems easy to me yeah Kurt I remember when you you taught me over 20 years ago in your class you'd stand up there with your your dry erase marker in front of a whiteboard like listen guys if you can read a record and write a record delete a record you can do you can work right beside me I remember we all used to get excited because that's all we wanted to do is get a job doing this stuff you know this this is it right here this is this is your job right here I tell you this is what caught me up quite a few times is getting the fundamentals on this it's and I think Lou I think Lou you're just overthinking it because you're so used to having to do things the hard way that it's just almost too easy for you now once you see this you're gonna say once it clicks that's what happens to me all the time with this thing it's like oh I made it too hard that's what I did for the first six months learning power apps it's like why why this is so strange how things are set up in that there's just no documentation to it so hopefully this is going to be some great documentation that we're making today oh yes because believe me I searched through every resource trying to find videos on this and I can't find it yeah everything's to do with SharePoint which that's a valid thing a lot of people use it but there are some people that are 30 percent 70 of Darren's customers use SharePoint [Laughter] oh there's one that convinced me to move on to date averse I'm like okay here we go new adventures I found out that so many percent of people are using uh SharePoint not because they want to but perhaps they have to it's the their best option based on the licensing and they don't want to do a a dataverse for teams type environment you know absolutely I am enjoying using dataverse though I mean it's worth it to me yeah you feel like you're actually using a real database right yes all right so we've got that now I'd like to go over to uh car models I'm going to go to custom here's car model got name what are the other things here don't do there are 18 more and it might be in there Chris it is a paradigm shift you're right yeah and then Paul says uh good thing about dataverse is it shows you the relationships and types of relationships that you've created that's true as well uh um okay so I'm gonna go in and make sure we got uh make your model we also have a name in there too which is automatically there okay what do you think a year so we make that numeric number thank you that's a whole another topic too right um how should we what data types should be used for different things you know do we make them numbers or do we make them characters and there's different philosophies on both sides of that um absolutely we'll be honestly me and Darren's discussions initially we said I should use SQL server but I was like a little scared of that so let me move let me step up let me go to dataverse and then I'll move into that yeah there is a bit of a learning curve with SQL server and just if people are curious why I recommended that you use SQL Server Lou uh and if you're comfortable if I'm at Liberty tell a little bit about your project is that you're creating software um not only that your business is going to use but other franchisees uh might want to um ask you to deploy it for for them and um if you want to maintain the data make sure that it is um you know working well for your clients um or you just want to host the data in general if you want to create software as a service let's say they pay you a hundred dollars a month to use your software if you put in dataverse you give them the power apps you deploy it well that's all in their environment now their business is between them and Microsoft yeah to pay for the services um and then they say oh we're not going to use your software anymore right and you don't know if they're using it or not but let's say they they continue to use it you know if you host all the data in an Azure SQL Server you can host all your customers data in SQL Server um and uh you know so you got a little bit more of of control over the data and how the application is being used because they won't be able to use the app or um they'd almost have to go in there and give it an overhaul to take it and put it in the dataverse to get it out of SQL server but yeah as you stated there's a there's a learning curve there and I'd recommend getting a little more familiar with dataverse learn how to work with that before trying to do a full large project of a SQL Server yeah um that's what I went for baby steps yeah and if you keep all your table names the same it should be an easier uh transition uh minty says what issue I have with SQL servers you can't use the security model like in dataverse it uses a delegated authority to connect uh to SQL Server yeah that's true so you've got two choices with SQL Server authentication or you know security authenticating users you've got active directory and then you've got SQL server has its own authentication like you can create a SQL Server user and then you have to give it a password um typically what I do over the years is I have built applications while using SQL Server most of the time is that the application has a username and password or it has a surface account that it uses that's an active directory account and then if you're using the app it's just known that you're using that implicit connection and um so that's typically how um I've done it and how I've seen it done in a professional environment so um also as I recently saw someone use a SharePoint list and dataverse using virtual connector has maintained all the dataverse functionality really cool feature and that is um so that is a new feature that came out I believe just a few months ago uh virtual tables so you could tie into SharePoint lists SQL Server tables uh you could tie into Excel spreadsheets if you want um I think maybe even um I I think data's King mentioned something about access but I believe what happens when you use the upgrade wizer for Access getting things in a dataverse the data resides in dataverse and then access just points to it but yeah so it has a virtual a virtual uh feature out there where you can tie into other data sources there is a dated table that's uh implicit in all the the dataverse databases that you create for an environment uh it's called aad users that is a virtual table that points to your Azure active directory so within within dataverse there is a user's table and those are all the users that have been added to that powerapps environment okay the Power Platform and environment but um let's say there's twenty thousand users or Twenty Thousand uh you know people in your organization not all 20 000 users are in that table as they've been all added to the the environment right so that's where the aad users comes into play it's a virtual table that points to active directory so just thought I'd mention that as well okay very good that's a whole nother topic of discussion yeah and the handles on the back of my neck when uh when we talked about the virtual connectors or we had an issue with that a little while ago when I was doing mine I was like oh dear no good you're already Seven Levels away from the kernel with just the browser so now you're going to add all these other things that's another delay and another delay and another delay so what I did here in locker room I'm going to open up a new tab for makuru just so you know how I got here this is great for mocking up data having some good test data is I saw this little generate Fields using AI so I clicked on that and I say uh okay car ours you can say how many fields that you want there I mean we say 20 let's see what it comes up I'm going to replace the existing fields which have to do with like a person one feature I do like with dataverse is when you are having to uh add data to your tables you can actually hit click it to where you can add data through Excel and you can quickly add data to your tables and date of birth very nice um I believe some people do that with SharePoint if you go into edit as a grid I don't know if you've seen that in SharePoint in the rear end it's still pain yeah uh so a question here uh Phil is a good question since minty was mentioning the security model what are my security modeling options with dataverse um so the security model within dataverse um I don't want to go too much into it but I do want to touch on it since um this is like an overview type of thing um so if you go into I'm gonna go into the admin Center and I can manage let's say the users for this particular environment okay and because I'm the uh the super admin or the ultimate admin of of this whole tenant I'm going to see all the environments here right so um I am going to do a search my name here you go there's my sandbox so you go into an environment okay and you see no I don't want to do a survey um so you got security roles you have teams users apps and business units so um I'm not gonna go into this of too much depth because we could have a whole session on how to implement security uh properly and you know I could go through all these things but um so if you go into users let's say somebody tries to get to an app and they're not in there well you can add a user you can search for them add them to the environment and then once they're in there um you click on this Ellipsis you say manage security roles okay a basic user has a basic capabilities like let's say a member of a SharePoint site has basic capabilities of of reading data adding data that type of thing now if you want somebody to be able to see all the dataverse tables in your environment and be able to add and edit and do a lot of things um you might want to go down here and add in the system administrator um and there's also a service Reader Service writer so they can um and that's typically meant for service accounts that need to read write or both um and then if you want people to be able to create apps within the environment environment maker um is for that and then um I'm a system administrator across all the environments so I just thought I'd mention that now one other thing that I will show because as you create new tables you need to keep this in mind okay eventually before you deploy this and people start you know clicking around and doing different things here um I want to go into um I think it's advanced settings if I'm not mistaken and you're gonna see the um the under the the Dynamics 365 underbelly of this thing um now you can't really see it too well because I've got a little bit of an overlay let me let me uh move this around here okay the Dynamics 365 what you want to do is go into this little downward Chevron and there's a little security thing here so you go into security okay there's all kinds of stuff going on here uh you can go into an individual user or a security role what I like to do is I like to make somebody a business basic role then I can go to security roles okay go find basic user and let me see if I can maximize this you guys can see it better and maybe zoom in a little more there we go there we go Okay so we've got uh these tabs at the top because you you create your own custom tables those are referred to as custom entities okay and you'll go down and find the uh you know the car model look at that now you guys are gonna put your face in your hand once you see what it may look like that you need to do here so we've got car model okay well here like organization we got the the user the business unit that type of stuff so if you go all the way to the top you say read write delete append a pin two assign and share okay so I'm gonna go back down to uh car model and what I would do is I would click on this let me see if I can get it to actually happen here not seeing that it's because I'm zoomed in so much that's weird I've never seen that happen before when I click on it it's not doing anything [Music] it's never easy it's never easy so what happens is once you click on it it moves it over to this yellow thing here this this from this Legend user business unit so on and so forth and somebody uh said you know what if you just click on the name here it will actually do it for you um but it's not working like at all so I'm going to close this window down and try to reopen it and see if I can figure out what's going on here okay FYI I'm gonna be asking for you to do this very soon to show yeah the class on this yeah this should be a live stream during an entire live stream on this yeah we could literally go an hour or two just on setting up your security and deploying I've got a tiny app that I over into my other and doing this as well let's do it let's do it I'll sign it up foreign so this is where you do it I don't know why it's not allowing me to do that I've done it many times but so that's where you do it and instead of clicking on those individual things you could just simply click on the the the name of it there on the side I'll just show one more time now if you guys are following along um if you click on these individual names these things um should actually be going in so you see how this one has a whole green uh Circle okay so for um car model yeah I'm trying to see it and maybe I need to try a different browser I'm using Edge here and then once you change it what you want to do is uh click on Save of course okay Darren do you think it's doing it because you actually have the car models open the call models open oh over this other also yeah I wouldn't think so but let me click on tables here we can try it out um let's go down here to user click on that yeah so I'll figure out what's going on with that and um I'll let you guys know once I once I get to the bottom of that um of course if you ever go live you ever present um if anything will go wrong that's the time that it will right go wrong right okay so yeah so that's how you interact with the security and how you uh you know set things up there yeah okay so uh Hearthstone hopefully I pronounced your name right so the new security model designer might have hit your tenant so editing security rules in classic mode or then read only okay that's a good point um so I'll look into that get to the bottom of it and let you guys know what I find out there okay so we have our tables there what I wanted to do was move on to getting good data in here uh to be used so um I showed you how I I got to this screen right um and so it's got a lot of stuff so it's got some data that I would want to have in the um the car models and also some data over in the customer cars so what I want to do is I want to generate all the data and then I'll import it in a certain way okay um so I'm going to say generate data this and I believe last time I did this it actually gave me a little file download I thought I saw it up top yeah I enjoy I'm going to click on it again there it is oh there it is okay yeah and it disappeared on me okay be good and I'm gonna call this our data and then um make sure that's over inside my OneDrive inside of a photo called powerapps so this is a little bit of a treat I'll show you how to import data so I'm going to close down some of these security tabs I don't need that other mockery tab okay so what you want to do is if you want to import data um I would always recommend hitting this important data this very first item you might say oh yeah it's an Excel where I've got a CSV file right I'm going to click on import data and you're going to see all the different options you have all the different data sources that it can import from okay and also so I'll reposition us so we can see all the options here on the screen now um this sort of gets your your your you know your mind popping with ideas like oh I could easily import from a SharePoint folder or from access or anything um and then they've got it categorized you know files database so on and so forth okay so what I'm going to do is I'm going to look for a CSV because even though it looks like when you download it has an Excel icon it's actually a CSV file so I'm going to click on that and what I want to do is link to a file I already have whoa Daniel Christian hey man how you doing Daniel's a uh what do I I would call a powerapps legend so thanks for joining us Daniel we're working with powerapps today um okay so I'm gonna click on browse OneDrive yeah you thought you learned a few tips and tricks for me I don't know man maybe I should have you as a panelist have you teach me a few things so if you guys haven't checked out Daniel Christian's uh Channel he's got all he not only teaches power apps but all kinds of stuff uh I've seen him uh cover really cool uh topics that he has there so check out his channel um so okay so I'm gonna go down in here and go into powerapps and you'll see I got the little car data okay so I'm going to import this into dataverse hit select okay and then I'm going to click next it's a little wizard it's going to help you at every point of the the journey here hopefully hey there's our data this looks nice huh and then there's the car mileage all kinds of good stuff this looks great so what I want to do is I want to make sure if there's any data types like dates I always want to look out for dates uh because sometimes those don't come in right especially from Excel in a SharePoint I've seen those come in as strings but everything's looking good to me I think what I'd like to do is just add this into a uh just a new table because those two tables that we created for cars both cars and car models don't have the same schema as this but I want data the data that's here to go into the two tables and I'll show you how I would do this in in powerapps okay so I think we've got a thousand uh Records here as well so we got a a decent amount of data okay so it says card data okay this is looking good now if you wanted to rename the columns you could do that here this is the the best place to actually if I right click on the column you've got all kinds of things that you can do here with these individual columns hopefully you guys can see that okay all right so I am going to click next and should ask me if I want to import it into an existing table or create a new one and I would like to create a new a new table here card data and I just want to sort of make sure that I don't already have a table called card data uh that's happened to me when I used copilot I said oh yeah I want to keep track of my projects and tasks and then um there's already a projects and the tasks uh table in in dataverse as it's created you know um so and it it still creates it which makes it very confusing for powerapps Okay so just make sure I don't have anything I have car model but not card data okay so what I'm going to do here is um where's my little import thing here card data is good and we got a unique primary uh column name automatically generated and it's going to be in the name um I guess that's okay yeah that's fine we'll do that and I'm going to click next just so that everybody understands that this is like the only time that you should ever Veer off from your ERD you spend all this time to create an ERD and then but but you know what that's not true at all is it because that's exactly what happens you go in there you create your ERD and management change the specs on you or or or your the contract that you've got changes everything changes and nothing's easy so you've got to go in here and do something different and this is a great example of that yeah yeah you know typically go ahead there yeah typically for something like this I would call it like a temporary table something that after I get all the data import I'll delete it uh in those cases I won't add it to the ERD diagram but um you know I had something recently when I was just using a little temporary table and somebody was wanting to work with me on the uh schema you know the ERD diagram and it's like well there's a table out here called blah blah and it's not in the yard what is that used for I'm like oh don't worry about that that's just a temporary thing you know but for the guys that are new for the people that are just new and they're watching this and just trying to learn how to create uh data so so Darren you know he went through the process of creating that ERD diagram and and he was going to build the the fields The Columns from that but now he's just he's just trying to get some existing data that's all he's trying to do here the process is exactly the same though so I I guess I'm just saying don't get confused by what he's doing here because really he's just this is kind of extra he's just trying to set this thing up so we got some data you know absolutely um something I would like to point out here we have a refresh manually here selected by default so if you want to go in here and do this every time let's say once a month you got new data from a CSV file if you want to do this on a regular basis what I would recommend is say refresh automatically and say refresh every night at you know 2 A.M and refresh from let's say you got some automatic process maybe not a power automate flow that brings down fresh data and you want to import this every night you could do something like that um but for my purpose this is just a one one time shot and any AD edits deletes will be done hopefully with inside my power apps all right so I'm going to click on publish um at the bottom let me let me move that out of the way I was I was too slow there um there was just a little purple button that that brought it in here so um I am waiting for it to let me do a refresh I'm going to click on flows and I click back to tables and see if we see card data and I almost expected to sort of sit there for a moment and import it for me to show up yeah yeah it did [Music] okay there's car data there it is they sometimes things just take a a little bit of time and then we're sitting here looking at this right it's like okay where's the data you know what it had to go out and create the data or create the database table when you create a new database table it usually takes like 10 seconds or so right um the data's not in there yet but what we could do is we could just hit F5 on the keyboard and see if stuff start showing up here right it's probably because it's using a bunch of virtual connectors yeah [Laughter] yeah so we'll give it a moment there now what I can do is I can go over here to my um powerapps uh project okay go over to the data sources and we've got add data here oh look there's what's the plural of data or you think about that Jeff just just an English English major so okay Jeff here's a question for you what about a single data is that datum uh that's what I'm gonna have to look up I'm not sure on that one the the English language has a lot of really interesting words you know they die you know like octopi I don't know one of the uh uh it incorporates so many influences from so many different other language strands it's uh it's hard to it's hard to know it all and that's why data is King right right speaking of which state is I wish I had power query data flows for teams um okay so what I'm going to do is I'm going to add in card datas card datas and then we'll go in here and we'll look at all tables here and we'll do a search for car we have car models there we go car models and then I also want to add in my students which has all the people uh data there okay so you have that now I'll show you a little trick that I do in powerapps a lot of times it's not something that your users are going to see hopefully but this is the one little time if I want to look at the data inside of powerapps let's say I have a little screen for myself so I just want to look at the data I'll actually use the data table um control and I don't like it but only for this this reason okay so here's data table and I'll say well let's look at this this uh card datas well what's in here okay we'll go in here we got color condition location make mileage model owner price then here and then uh of course that would be a good I'm not interested in seeing the Grid at this point um and I think that's all I'd want to see there I don't know that I'm gonna go and click on ads I'll add those in there I really don't care of when it was created on um so Dana have a great day everyone and enjoy the rest of weekend well you too Daniel thanks for dropping in and thanks for the uh the the thumbs up that that you sent there I appreciate that not too many people do that so thanks um okay so I'm also looking at the private chat um here so um just making sure okay so Nick says technically data is plural noun it is the plural form of the noun datum however it is used with both singular and plural verbs the data show a decrease in visitors to state parks the the data shows a decrease in visitors to State Parks right okay very good the Merriam-Webster also says that data now lives quite independently of datum which is the singular right interesting yeah Latin roots going back to the mid 1600s so you see we got our all of our data here all the all the stuff we have in here so we can see what's in the table here within uh powerapps just for our purposes if you actually want to show this to uh you know your users you probably want to use a gallery and we could do that for all three tables I know that um we do have quite we've got like a thousand records in my students um but then for car models we we don't have any data okay so what I want to do is I want to create a new screen okay and um I'm gonna use a gallery I'm going to use a blank vertical gallery okay and I want to make this I actually want to look at that car data yes and then um this is a great trick too by the way I love this trick here Darren's getting ready to show here I I I saw this I was working on a project with Darren a little while back the first time I saw that I saw this was really cool and so basically what he's doing is he's creating a screen that and you can call the screen the same name as your table let's say you're out there working with some stuff and oftentimes you'll do this you'll be working for a on a project that you've never seen the data you want to look at the data so instead of going out there and trying to figure out what feels to bring in all that kind of stuff you could go out there create a screen call it the same thing as the table as the table that you're wanting to look at and then bring this data table in here and you can have that as part of your you can actually have that as part of your uh your uh screen collection in your app and now you've got a you you've got a a resource that you can just access all the fields to this data table that's brand new to you that you don't have to memorize you know what I mean it's just a really cool uh trick plus you can actually see the contents of the data you can see how many rows are in there sometimes you might say wow this is a this is a big data source here maybe I can do something with it you know um but almost every time when you start working with applications the first thing you want to do is you want to go out there and look at the data and this is this is a beautiful way of doing that I think anyway absolutely so I like that Kurt thanks for uh for sharing that yeah absolutely um so what I want to do is get at a distinct year make model and then put that in a car model from the sample data here that's what I'd like to do so uh yeah I said all that to say that people pay big money for this so geek Chris Chris Christopher says so if data is King that is data I'm a prince Kiki I'd like to make the the background or the fill of a gallery to be a dark and then uh then for the uh template fill I'll say color dot white there we go and then change that padding to more like two here we go um some sort of curious if I added a drop down top and I were to do a distinct okay on the car datas and we'll look at year and then you'll probably want to sort on that based on the value now value is not the the field name is it but because we use distinct it sort of created it created a single column um a table and it uh it sort of changed that that column name as a one column there so that's why we use I always I want to go ahead and finish your thought there yeah so what I wanted to do was was sort of explain that of why I'm using value there on the distinct because like if you if you select all this you'll notice it is a table in the the uh the field name is value now if I had double clicked on card datas look at all this stuff you know the field names are really different right yep so what were you about to say there Kurt okay well I just the way I always look at this I come from the old old school traditional programming and so what I now remember that the the first thing to remember is that a function always returns a value right it always returns a value so so the thing is when it says value there that's not a record there's no fields in there called value there's no there's no it's not a whole bunch of different um field names it's just value so the way I look at that is it returns an array it's actually returning an array and then and maybe I'm incorrect in it somehow but this is just the way I think about it when it's coming back it's bringing back an array a pocket of memory with a list of of items and that list of items are just going to call it value you know dot value um so it's it's separate from the actual table is where I'm kind of trying to go with that it's actually separate and and I believe that you can actually take that value that that that pocket of memory that array and put it in another collection or another variable somehow yeah all right so we have all this data here um I'm trying to figure out how to create a nice distinct um way of doing this um um something you should keep in mind is let's say a Chevy Camaro maybe wasn't made every year I know that you know Chevy Camaros uh you know were made back in the the 70s maybe before that um but I think there might have been a few years then where they discontinued it and they bring it back a few years later um and if that's not true the the Chevy Camaro there's other make some models that are like that so I don't want to give it like every single year you know for each of these um so so why wouldn't we just idea maybe you could just collect uh create the met the first one would be a make and then create the the year after that like like what years you would want in that so you for your your drop down is that what you're looking at is how to Cascade that across yeah I want to populate that an empty table over here uh for car models um and that's made up of year make model yeah um but I don't want anything to be duplicate duplicated between those three columns now if one of those three columns is a little different then the duplication is fine for that you know um generally most people would go for a particular model first and then they might okay that's what I said to me I mean if you're shopping for a vehicle what's that if you're a Chevy or a Ford guy you know yeah um okay yeah I'm sort of thinking here on the spot which doesn't always go as well um okay so what I'm going to do is let's do how many years do we have from 2000 so we got 22 different years and then um I think what I like to do on this on select so we're going to write some imperative coding here so this should be exciting right so we'll do a clear collect okay and we'll create a little um collection so we'll start with col and we'll say temp are model we'll bring in a car models all right that's um card data's car data so we'll get all that okay I'm going to open up this code window a little bit here I'll put a semicolon there okay now what I would like to do so I'm going to have all thousand rows there and then um what I'd like to do is do a for all based on this collection for that okay and what I'm gonna do is I'm gonna make sure you know what I think I'm just gonna work with collections here so let's do a clear collect I don't have to go to the data so the the actual data source over there and check to see if it's in there or not I'll say clear click call temp models okay and here we'll say car models and of course there's no data in there so it's going to be empty for now okay and what we're going to do is we're going to check okay um so we'll do an if and what I like to do is say count rows okay I'm gonna count rows inside the models I'm going to filter our models based on and because this is starting to get I have a lot of nests in there like to break it down a little bit so I don't get confused I get confused quite easily all right so we're gonna filter here we're gonna do a search for uh for this current record here I always like to use aliases you could use the the the moniker This Record but sometimes that it doesn't quite work all the time so I've tried out using this Alias I always say my okay and um so we'll see where the the car models if it has the year okay my DOT the year it's car year okay yep and I'll speak no and model equals my DOT r model okay we've got that if that is greater than zero now you might be asking like well Baron you just want to know if it's there or not right why don't you just check for Blink and that's how I started out doing this type of things if I want to check to see if there's any data in there and what I found is um sometimes the the is blank doing a instead of using a filter doing a lookup and then the result of the lookup is that null or not every once while I have this issue where it brings back a null or a blank when there really wasn't a blank so I started to use this filter and um and typically in a loop you want to you want to be most more as efficient as possible otherwise you're gonna have a sluggish thing this isn't for the users we're just bringing data in also we're working with in-memory collections which are going to be very fast okay so I don't have that concern either okay so if that is over zero then what we want to do is a patch okay and we want to patch the car models and I'm hoping that it's going to allow me to do this and why I say that you can't change the data in the first parameter of a for all so if I try to patch the temp car model okay it won't like that because it's the it's it's what's being used to know when it's done with the loop so that's why it doesn't like that okay so I'm gonna patch this temp our model okay and I'm going to say defaults um on that temp model okay and then what I want to do is these little curly braces here and I don't know that it's gonna like um the defaults with the collection here but we'll see here so we've got um here I'll say my up here speak oh what's the problem here if foreign I think that needed another parenthesis there so sometimes when power-ups gets confused um it's because you're you're missing a a parenthesis now if you guys see something that I didn't do right here um let me know greater than zero then I'll patch okay okay so it doesn't like the if is Holt filter Okay so okay that the that's right how do you see anything that um looks looks off here I'm working it seems like it's looking okay false did you confuse powerapps I see I see the comment from data's King you would create a collection for each drop down and include the word of all so that it shows up in the drop downs and what I wanted to get away from is if let's say there's a year where a particular make a model wasn't created by the manufacturer that's what I was trying to sort of be slick by uh by doing that okay that count rows something wrong with that count okay so count rows you got your data source is the filter but then you don't have the greater than zero okay I guess that's okay yeah now there's actually no data in these collections yet so if I were to comment this out and hit this button now what do you guys do when you're um the call tap models is missing a comma the patch it's missing a comma um okay so we do have data there okay yep it is right after that right after the first um right for the patch oh it is called collection temp models yeah there it is good catch Andrew good cash bud bam yeah look at that look at that bingo very good now Andrew if you don't have my crash course you sign up for I've got a link at the top of the screen to join my community um just message me if you join the community message me I'll give the uh the the my crash course to you for free for helping me out there I appreciate that yeah I was busy uh checking out that empty bite I read something about when you were talking about the blank sometimes doesn't return return blank for you I I read that um so the blank is is supposed to be part of a string it's used with strings and when you're dealing with tables you're supposed to use the is empty because what happens is the structure this the structure if the table is empty the structure is still intact and so then that is blank we'll return it's not blank because there's a structure in there versus is empty there's a structure and I was looking for that I wanted to read that I I want to give you a free uh copy of my my crash course course as well well I wanted to give you the the exact wording because you know sometimes when I paraphrase you I can confuse things so yeah exactly when you're dealing with tables and use is blank if you're dealing with strengths there we have it very good very good all right so now here's the moment of truth um I usually do a save uh looks like I'm already saved here so I'm going to hit the button here and um it might take a while so we're dealing with a thousand rows it's going to Loop through the Thousand it's going to create a collection and then it's not going to update it yet it's just gonna have a good collection to work with so I'm going to go ahead and run this and I would expect this to take a moment and but it came right back do you see the the button is not um disabled anymore and I don't see uh any errors okay so let's do a little something here let's create a a label here the label here and for this one we've got uh count rows oh we have the car models okay we got 500 there and we have uh count rows I want to say uh call remote car model [Music] um we got Zero Records in models now I wonder why that is so it actually never got into this that's interesting now we do have data in this yeah we do have data here but we don't have data here so any ideas on that guys so it actually didn't go through and actually add it to that collection there okay I'm going to run it and um hit this button again and it comes right back oh here's something that we could do we could go in here for the data source instead of car data so we could just say this car model something else I'd like to do I'd like to work with as many records as possible so I'm going to set this up to be 2 000 okay and I'm gonna rerun this and this should be at least a thousand right so that's what macaroo gave us is a thousand um now data as king said because I have and here and that's what I want I want a good combination of those three so that's why I have that there um double click on that we still don't have any data okay so but now we do have a thousand here what I want to check is the um Boolean value that we've got for this for this if statement and if statement the first parameter needs to deal with a Boolean value there so I'm going to add another little label here okay and for the data let me just say count rows I don't need that comma though and instead of saying my DOT it will be this item hit shift tab what you're spacing there and so it's going to say false um if it's greater than zero oh you know what if it is zero you see how this this uh coating works like one little thing will mess you right up so I like to stop and uh get Kurt's take on this what do you think of this Kurt I'm going out here live I'm I almost feel like I'm making a fool out of myself and showing everybody I really don't know what I'm doing right so so then then that would don't take this the wrong way but that would be like um um trying to be somebody that you're not so what it is if I had recorded this video I would have edit I would have figured out and edited that out right that's that's trying to be somebody that you're not you don't want to look like a fool but guess what sometimes we just are because that's exactly what programming does that's how many of us I don't know I I was just stuck on something very similar to that the other night is it exactly the same thing except it took me longer than you but of course I wasn't telling anybody because I didn't want to know any I didn't want anybody yeah but the thing of it is it's exactly what programming is how many times that's where you're going to get stuck 90 of the time when you get stuck it's going to be over some dumb stuff like that just something simple like that you're thinking oh it's just you know especially when you've got a lot of different things going in your mind at the same time you're trying to deal with this data and everything at the same time so so you're going to miss something like that and that's so great that's why it's always good if you spend more than 30 minutes on a problem take a step back and go away if you come right back at it fresh pair of eyes boom you'll see it right away don't don't ever spend more than 30 minutes locked in on something at the same at the moment go take a break and come back um but yeah that's and I love the fact that you do this live because it shows people that this is what this is what real programming is this isn't sanitized stuff it's never easy I say that we joke about that a lot but it's really not we're always going to run into these little things like this and that's what the programmers that's what programming is about man people pay big money for that that's right yeah and uh so I went ahead and clicked on the button and it's it's working it's creating my collection of a thousand um but if there's duplicates it hopefully is less than a thousand somewhere you know maybe 10 of them are duplicates you know you know now minty was asking there what is the filter part actually doing um and so so what what you were trying to do in this was trying to replace the is blank or the as if you could have done this with the is blank run is empty but you were trying to do that to make because you just wanted to ensure that this was going to work right so so he you did it you did it the old school way and you were creating a filter to count road because you wanted exactly one record of each thing you don't want you don't want duplicates they were just you're just trying to keep the duplicates away right yeah yeah so I'm wanting to see if there's any of them in there um so going back to what Kurt uh brought up a few minutes ago um perhaps I could have just done a lookup and then done and is empty to find out if I had an empty object reference so yeah so it looks like it's uh still working um didn't expect it to take this long to bring go and get a thousand records and bring it back doesn't take a lot of time but um we're not going against the data source back and forth It's just doing a one shot give me all the Thousand records and then it's going to Loop through each of those in atom if they're not already in there so [Music] um all right so I'm going to take a look at the comments while while it's still working to see you guys live on sharingdv what's DV am I not um dataverse oh yeah that's very good okay let me look through any of the other comments I I don't have up here um yeah and I notice um just as in the private chat you're actually teaching us how to work through issues a very real inside look and he also said people pay big money for this 8 15. so it does show us that we need to have some duplicates in there huh all right so the data is not in dataverse yet it's just sitting in an in-memory collection so what I'm going to do is I'm going to create a button I'm gonna show you guys a little trick that I learned from the legendary Matthew devaney I never get this trick to work either by the way really no I can't never we're gonna patch I'm gonna patch that thing right and we're gonna say uh car models okay and we're gonna give it the column another column The Collection temp models like that no I don't want that button here we go oh that doesn't look very long or complicated right so what we're going to do is we're going to take we're going to point at the car models uh dataverse table that we created that has just a year make model right those are the three main columns there and we're just gonna give it instead of like telling it what record in the data we'll just give it the whole collection that's got all the data definition and all the data that we want to put in there okay now keep in mind your your field names have to line up okay yeah so let me zoom out a little bit here I'm at 200 percent so I need to zoom out a little bit okay so I am going to open up this code window real quick I'm gonna double click on that collection and see what fields we have okay so there's make model year okay now you notice it's got this cr833 well that's stuff that uh if you connected the dataverse database through SQL Server management studio and actually looked at the raw data um in SQL Server the field names are actually have these little prefixes cr833 where as if it's a built-in column from dataverse it doesn't have that prefix I don't know if you guys have ever noticed that so what I'm going to do is I'm going to go ahead and run this app and click on this button and hopefully it doesn't take very long it should be a straight shot here okay name is required okay this is actually good because what I want to do is I want to make sure name is set up with the year make model okay that would actually be very uh useful here so at the end I'm going to do a uh semicolon I'm going to comment this out just for a moment okay and then what I'm going to do is I am going to um for all what's the best way of doing this we could do a uh we're all and then we're going to go through that uh temp models okay now the question is are we going to be able to update this thing While We're looping through it and the answer is no okay so what we need to do is create a a temporary copy of this okay so I'm going to say uh clear collect okay on that and I'm going to say underscore temp this is like a really temporary temporary table and what you guys are doing is you're seeing a workaround because you can't do a patch within the for all because the for all the one of the rules of the four all is you cannot change the structure in any way that you're in the for all with right because otherwise it would change the parameters of the for all right it wouldn't know when to stop if you kept adding records to it so what he's got to do is he's got to go he that this happens a lot in programming you've got to do a workaround you got to find a way around that so what he's doing is he's creating a collection to dump that data in first so it doesn't mess up the rules of the for all and then when he's done with the for all he's going to dump that stuff back into the into the uh the table that he wants to collect it's it's pretty slick sometimes you just gotta you gotta think outside the box man and that's exactly what he's doing right now I'm a color commentator here guys you know it's great their niece up the up at the plate you know he's getting ready to swing or here comes the curve ball he's gonna yeah this is great I love it yeah you can can talk about something that's that's interesting that's pertinent while I'm sitting here thinking about the code that I'm writing um this is fantastic yeah so I'm gonna do a look up okay and uh data's King says workarounds are my life yeah nothing's ever simple right being a programmer I mean anybody could just sit out there in a clean environment and put something together but what happens when it doesn't work right well you got this right here yeah and so we need to see where the um model what's in that field car model equals my DOT car model remember guys when you create a table in betaverse it creates a column with the same name as the table that's a grid that's the primary key that's what I'm matching it up on so if you give look up two parameters not three two parameters again it's going to give you the record match expects that second parameter to be a record so that's what I'm doing I'm gonna pass in two parameters into that lookup it's going to give that record and then the third parameter is okay what is it that you want to change in here well we know that name was never provided we'll say name equals okay we'll say my DOT here I'll do a little splicing with the Ampersand just seven okay here what should we do there uh Kurt maybe just a space between the year make model yeah now is your is your a number or is it a string uh we set up as a whole number so do we need to do do we need to do a text around that um I don't think we need to but that's actually a good idea to be very explicit and be very in control of our code and what's going on in our application right so that's actually a good a good point there so we'll also get at the make okay Keepers saying get another space and then we'll say my DOT model yeah thanks we'll put that in there so now the name is going to have something in it and it's going to be meaningful it's not just going to be a bunch of numbers or something random so we're going to do all that we're going to patch all that and then uh so it doesn't like the clear collect I have it yeah that way that way you can look at your data in a table you can actually see what you're working with here you don't have to it's not it's all right there together just look at that name you got to have that name filled in there so you might as well make it useful right yeah yeah okay so what I'm gonna do is so this should update it all yeah and then what I could do is use that temp one down here for that yeah that should work your fingers network error when using patch name is required is that collection just not working just because it hasn't seen it yet um I think it's giving us the same um air yeah okay that was there the last time it was run I have a feeling that if we just hit save and just try to run it yeah it will straighten itself out but we'll we'll see here so I'm gonna hit that button and the button comes back there we go name is required so let's see where it gives us that problem yeah she's giving us down here so we do have the name there we did our patch and we're using this collection if I double click on this collection okay we do have data in here okay if I use the horizontal scroll bar we do have data now where's the name not everyone has a name why is that it didn't go through the whole thing just did the first one why did it only did the first one yeah that's one record in there data in there now guys this is this might be this might seem boring but this is sort of where the um I I wish I had a better phrase but um as I've grown up in the past there's a phrase called This is what separates the men from the boys this is what separates from let's say the beginner from the pros people that can actually you know do this type of stuff as you become a developer writing apps this is the type of stuff that you'll be dealing with it's not all just drag a button over and just set up your gallery there's this type of troubleshooting stuff that you really do need to get used to doing so this is um this is what separates the regulars from the average Bears the real programmers from the average Bears don't be an average bear yeah very cool very cool um okay so yeah it only seemed to give us um actually update that on one row okay even though we've got quite a few records in our collection here we're supposed to do a um look up we updated it what did I do wrong because here the name it was only set up on the first one that just uh blows me away that it only did the very first one um so could it be trying to run that code before the other one's finished could this be could it could you be doing a you follow what I'm saying maybe I am let's try let's try the stair try to take that four all out and put it in a button do this one get this thing get the collection started then click the button and let's just see what happens yeah so I'm gonna put uh so guys Kurt and I have have worked on a project a really um hairy project a really complicated project where something wasn't working right and we put a a separate piece of code into a separate button and called that button select from the previous one and then it worked it's like sometimes power-ups gets confused or something probably because it's still busy trying to go through another connector while the other line of codes being executed problem your problem maybe yeah let's uh okay let's try it again I'm gonna hit that button and man that came back really quick yeah yeah it's still showing okay so I patched all that now let's see how many records here actually have the name populated and it only has the one did it do this one right look Volkswagen Civic no this is a Silverado it's doing the same one oh see this is what's messed up I'm going and getting that first record every time in that Loop isn't that fun you are that's great yeah car model you know what the primary key is not created um it's it's created in the dataverse in that interesting so what we can do here is we'll we'll actually look up based on the year equals my DOT year okay and make equals my DOT make have you guys ever seen somebody when they're creating a little filter statement or a lookup we'll do it just another comma here you guys ever seen that model it's another way of doing this dot model and I've seen people do that and um if I just go back to the way I always do it which is using ands but I've seen people just use commas for that and maybe that's only for filter here we go you have criterias on both lookups and filters all right so we're just going to match up and we know they're they're distinct right okay so hopefully that will work um because those grids are generated over in dataverse for new records and because that has we're just working with collections before we send it back over to daily verse is those those unique identifiers are actually blank right now how's it happen yeah yeah so I'm Gonna Save and then I'm gonna run it this is why I never get it to work [Laughter] all right it came back remember remember when he started this whole thing he cursed it he said he said this is a real simple way of doing this okay so we got we got data now right I gotta look over in [Music] look at that very nice good stuff Mercedes-Benz Silverado E-class okay this is all matching up now so now that we have that done I want to go over something that Bob Leach just brought in okay he had his first comment was just a tip when you were trying to create a string with multiple controls or items from your table use the interpolation method so it could be and then it shows the code for that now my my question is in this is why because now and I'm not saying that facetiously I'm saying that because I always thought the interpolation was made for when you're dealing with web development and you're dealing with HTML and you're bringing stuff in from from an HTML Source into your into your Java okay so that you had the interpolation was a signal to tell that the HTML script it's got you've got a string that's going to go both directions I thought that was the only time you used interpolation he's saying to use that here I'm just wondering if that would be a if maybe my knowledge is wrong in that you know so let's try out what what he's saying here so we should be able to do a little square bracket then say okay that the text my year okay and I'm certain this will work I just I I just thought that it was something that you didn't do unless you were dealing with HTML you know now I think what what he's saying is that we can get rid of all this point was there right okay but um okay but we need more curly braces according to his um in here okay okay so if we do a curly brace curly brace and then over here we got a curly brace I don't know do you think that you guys think that looks better yeah it gets rid of the Ampersand anyway yeah and all these uh double quotes so that was good Bob that was good uh if you wanted to sign up for the the free community The Links at the top of the screen I'll give you a free crash course thank you [Laughter] you've helped me record some of those videos but that's why I'm laughing um yeah and then data's King says I thought when you're doing multiple lookups you should use the at now the at at symbol is a shortcut it's another way of saying and yeah okay uh another thing instead of using the the word or you could use double pipes yep so just let you guys know about that too okay so now do we have data over here in the data source okay which is car model did we execute that did we put the data over there we just have one yeah we didn't execute it completely I think I'd like to delete that because I don't want the chance of having a um a duplicate there so I'm clicking on this Dakota edit edit or yeah now remember guys we did all this just just so that we could show in relationships here so we're going to get back to relationships here in a minute this was a very long side trip here remember when I was talking to you and Lou uh before the live stream I'm like okay so I just want to focus on the relationships and you're like well we should create stuff from scratch Darren I'm like okay here we are it's a good people aren't enjoying it people are learning and that's that's the end goal anyway right um okay so there's no data in there now but if I go back to my app I can see that when I run this I've got some good data I'm going to double click on that variable there and we do have some data not every field has it but the important stuff does okay very good now I am going to run this and click on this third button cross your fingers big bucks no whammies you guys remember that show yeah yeah watch too many movies did you see how fast that was that was a thousand records now I wonder if it's all over there no air okay well how about if we do another little count here and we say r models we're seeing the dots going across the top there come on refresh I yeah I would expect it to be there I'm gonna go ahead and refresh just in case but and the powerapps application if you do a patch it should know that there's data in there it should car models and that is yeah car models and I patched it okay if I go over here and refresh is the data the data out there and it is it is and it says uh 805 additional records okay so what gives power apps uh let's refresh again okay um I'm a little disappointed and uh in this but uh the data is there in fact if I were to create a screen like this one okay for the our models if you wish back if you left if you left the interface and then came back into it again I bet it would be there if you went okay let's try that out yeah every once in a while I've I've learned that you have to do that um where I've learned it is if you go over there to powerapps you create a new table you come back over here and say okay I want to add a new data verse and you don't see it there um I think it's like come on come on and then okay I just hit back it's gonna leave it that's what Nick just said stuck in cash that's exactly right it gets stuck especially because Darren goes so fast sometimes he's he's messing with that memory and and Pirates can't keep up and so uh I'm sort of like the Bruce Lee of of power apps is that what you mean I I heard that with with Bruce Lee when they recorded movies he had a slow they had to slow down the footage to actually see his movements oh wait a minute zero zero zero oh yeah that's not good right sometimes and sometimes if he said all that to say nothing it still didn't work yeah all right let me go get that data oh now it's gonna go pull it up okay now we got 100 815 there isn't that interesting huh um what I'm gonna do is I'm going to go over here screen five and I'm gonna add over a gallery blank vertical Gallery okay and I'm going to set this up for um car models and of course it's not going to happen it does have a scroll bar so I know it got something here I'm going to add over a label and I'm going to say here well actually name has year make model doesn't it yep that's sort of cool all right so we'll do something like this and I'll make the rows okay like that and I'll move this Gallery up and I'll set this up to me maybe four wide and look at that it's all right there so the question is why didn't it show me the Full Count huh isn't that interesting cheers okay so at least we know it works uh powerups is bugging out on us a little bit here um hopefully it catches up and gets with the program car model language Tori language I wonder if you do and sometimes I'm just so used to doing little workarounds let's filter car models the criteria will be the keyword crew look at that guys yep toy language okay how long would it take you to do this in C plus plus at least two three days right and I'd say now if I if I could have some libraries maybe I could get it done in about eight hours if I had some libraries my library you know otherwise in C plus plus you'd have to be able to create the browse window and then the buttons oh my goodness the buttons would be another couple hours and yeah huh okay so let's think back to our ERD diagram we have good data in car model we sort of have good data in car now we don't have a lookup over the car model we don't have a look up over to person right so um I think I'd like to match these two things up okay um so let's let's work on that real quick okay so over here on this screen this is all the year make models and they're distinct they are unique between the year make model okay um and that's based on car models um if I go back to screen four this Gallery here has data I'm going to take out that count rows this is looking at um temporary car model okay and it might actually have um duplicates there but what I want to do is I want to filter this filter this based on the um our model comes far from card datas okay and um so it's going to be our betas and that should be a um or data well let's let's um get the answer to that question here so I'm going to say right here and I want to get it the grid okay um so for this little label here I'm going to move this around and I'm going to assign it to the right side and I want to say car okay I'm looking for that grid um it's calling a car data ID now is that automatically generated it might have been if I go over oh I gotta go to custom right and then we'll say um yeah card data is what was imported but let's go find car it's called car data so a singular okay because the table name is singular so I'll save that and we do see the grid there so it's called car data so then over here what I want to go look for let's get rid of this coding window here okay so when I match up that grid okay with um the card data yeah so there's a good grid and I'm gonna make this more like two so I've got a little more space in there so I can look at it a little bit better okay now the question I have is why does it all look the same all of it ends with EA um that looks a little okay the beginning the beginning part of it's different you see that okay so that's different um what I wanted to do is for each of these I wanted to check which ones are uh looking for this lookup thing car model lookup okay our model look up I'm looking for this car table which is um it does make it hard to concentrate when I've got two toddlers I I hopefully you guys can hear them but both the toddlers are screaming and have been screaming for the last 10 minutes I hear my wife sort of dealing with that and I'm like oh my goodness oh my goodness all right um so help me out here Kurt uh bring in your brain cells to uh supplement my brain cells here um you're just trying to you're just trying to perform that look up you're trying to do a filter based on the the car yeah so what I'm looking for or I want to get at all the cars that have a blank car model lookup do we call it car model lookup um and I've got multiple tabs so it's called Uh like was it called customer cars or persons it's called a person's car I think right so we have yeah car model lookup here okay car model lookup so it should actually be there look up well I wanted to see if it was is blank I wanted um but it's no longer in the tent in the temp collection isn't it is isn't it actually in the table okay so car model lookup is going to be a record coming back that's right because card data was that temporary table right you see how complicated things can get really quick and now we've actually put the data in the table so if we just revert back to actually just using the tables right now so I never populated that middle table did I so we've got this um we get car data I think we were populating it we did it for the model yeah because we were going to populate that individually with the models depending on the student yeah oh okay what we needed to have happen is um one more thing in here that's why guys that's why ERD diagrams are so important and and you remember Kurt was you know Kurt you brought up that oh it was a temporary table so we don't need to put in our ERD diagram because we're going to delete it remember that conversation we had yeah if we had done that Kurt it would have saved us a little bit because when I come back and look at my ERD diagram to like understand it you know we should have had cards data is that what it called it and this was the temp the temp table and we could actually said hey this is temp and if I had this here I would have looked at a glance like oh this hasn't been populated we populated this to populate this but the data is still here we haven't populated this up here okay so I'm going to move this stuff around a little bit so I want to keep the order proper here okay so we've got the car data here and um I mean at this point weren't we actually because we actually have the we have the students data and now we have the car models data and at this point we're actually trying to connect the model to individual students that's what I thought right yeah so yes okay so students have cars but this hasn't been populated yet I populated this temp data because when I did the mockeroo thing it had good fields from both of them so I was trying to populate both of my tables and still have it use normal form only having the specific data in each one but one of the things I I didn't remember to do is is this by the way guys um you know we're sitting here at two hours something I wanted to share with you guys is um let's say this this thing right here July 4th guys I was just this morning I looked up my subscriber account I noticed I got 9 999 subscribers I wanted to do something special to thank you guys for being a subscriber here to the channel and say you know what I'm gonna do something big and I'm gonna go live for 10 hours on July 4th doesn't that sound like fun that's wild so I'm gonna be here for 10 hours on July 4th um the wife and kids are are going out uh out west to celebrate something then I've got a trip the you know the next day or two so uh what I'm gonna do is I'm gonna be here all day on on the fourth so if you want to dig around in power apps and hang out with me on July 4th I'm gonna be here uh before the sun goes down so you're not going to miss any fireworks I just wanted to mention that and if we want to delve deep into something like this this would be a perfect uh Place yeah yeah 10 hours well I mean we could probably build all kinds of crazy stuff in 10 hours right um okay so what I want to do over here I'd like to sort of finish this up to set us up uh for the for the next dataverse session here okay and we're gonna Loop through this car model and what we want to do is do a patch okay on a um do a clear collect we'll say call we'll call this student cars and then we'll go look at the the database table which is called [Music] um what's the database database uh name of it car data we got okay persons car go get that maybe it needs the plural version I'm not having all kinds of issues today guys do you guys see what I'm doing wrong here did I add it as a beta source I did not there we have it there we go okay let's Okay persons cards Okay so we've got that and I want to do a patch on that make sure I get that comment in there on that first parameter right defaults and then we'll put that in there all right so now I actually need this but we definitely want the name in there right the for all we want to use an alias as my well say my DOT although the name has a grid so we probably want to have something in there I think for now what I might do is have it an empty string and we can go back and do that later but we definitely want the lookup okay so this will be car model lookup and um I'm gonna leave that off for um go go do a look up on that but we want to do a look up I think I'm gonna do that below what are the things that we do care about in here we want condition then color tag or equals my DOT color we got a car color we have a condition my DOT condition and then then here we have Vin my DOT prevent is in there okay and we've got a tag Maybe he had a tag it's right there I guess we never added it yeah I don't think we had a tag there so we're not going to populate that they can add that later but uh color condition then at least we've got that and we at least need to put a um an empty string there I'll take out the comma I'll be in there and um so we'll have that populated okay so I'm gonna hold down alt hit the button that's going to populate that collection for us I guess I'm not understanding why we need so many collections at this point why why um because if we if we were doing the patching and we patched the data source and we had a thousand records it would be hitting the data source a thousand times and we would be sitting here for 10 minutes waiting for it to do I guess because I was going for a more individual one student to assign them one or two cars if that's their car look it up and say okay that's their car that they're using that they're driving yeah so that so that one still needs to be created as as uh well and in our macaroon data we don't have uh customers or students names that type of thing this is simply car data right and we needed the car data and then the student data and then to Adam and then the middle table is to say connect the student to the car or which well we haven't we haven't got to that part yet okay got it what we're doing let's go to the ERD diagram so this is full of data now this is full of data now right but this all this data is blank in the database okay so I just wrote some code that will populate the in-memory collection for this car with the data here I guess that's where I'm confused is because I would figure be more of it being able to bring up a person and their model rather that is in Mass yeah so what we what so that part will come later though okay got it yeah that part will come later um okay so we're gonna go through here we're gonna do this we're gonna put that stuff in there okay and it looks like we've got that that code was executed I need to bring this up a little bit yeah I just want to make sure the data is in there and I'm looking for the um horizontal scroll bar here and I'm not seeing any date I'm not seeing data for the um so we populated the color seeing the color there maybe if we go over to dataverse and do a refresh here maybe we could actually see that I'm not seeing that so it just created the collection okay yeah see here's the color and we're not seeing the color and we're creating uh new records here I'm gonna I'm gonna run this and hit this button and now I'm wondering how many rows that it's got for that once it's got a thousand the second one we probably want to make sure that you look clear this guy that we're patching again and I'm hoping to see actually a thousand is what we had in the sample data so that's actually right so now that we're looking at this one we probably have I think it's all confused now here's car model lookup okay there's color I should see here why doesn't it show me here I know we had I guess the years over here actually display this information so we got color we have uh condition okay and that data is not um see what about the name of course the name is going to be blank um so if you guys look at this code um it looks as if when I go look at this collection these three fields did not make it in you guys notice that and so we know that call tent models car model has data right yeah okay I got the model oh you know what it should have models instead of using uh the car model for the loop I should have been using all students car yeah I think student cars that's what's messing me up there okay and now it doesn't like this so that's because this needs to be run again and I'm going to be honest guys if this was uh this this is very typical this type of stuff uh working through and getting something to work if I'm working from working on something uh for an hour or two this is typically where I'd get up and go take a walk get away from my desk a good 20 30 minutes maybe get a bite to eat or something come back with a fresh mind um that's typically what I would do in this situation I'll be honest with you I'm just worried that we've gone far off of the point of which what we're trying to achieve from the beginning because now we have so many collections in here I mean we already have the the data populated in our car you know our car model we have data populated in our students and then now we were actually just trying to assign car models to students right so we have the data there so I'm just confused as to where and where this extra patch is and extra collections are coming in so you're saying that because we got the data here in this uh cars data no we have that we have the data in car model and that's what we were trying to achieve it only has a year make model though it doesn't have any other uh data so the the example of of your project that you were showing me it it seemed like there was um like an intermediate table so that's what I was trying to do I was trying to model that and I can't just use for for people you know let's say that over time this this video this live stream or even this this module in the course um is they don't you don't want to use something that is very very specific to somebody's specific project because they at first they need to learn your project and and your requirements before they can wrap their mind around the data structure and then they're going to try to so that's why I'm trying I'm trying to use something that everybody would be familiar with like people and cars you know and um sorry I thought we were creating the drop down uh cascading drop downs that does the year make and model and then that would turn around and so I just I got a bit confused sure okay yeah let's let's uh let's do that so we'll create a uh a new screen here and we could have um let's see here a drop down list in fact we had it um sort of over here but that looked at um the the the wrong table okay so so for here we don't even have to use distinct because they're they're all going to be distinct now something that might repeat is the year so let's look at car model here we've got car models there and we're going to look at the year it's going to be here so those are are all there and then um so here we want to look at car model okay and then um you also want to make sure the value here okay this is looking at um hello hey nope sorry that's not I'm trying to mute you sorry that's all right and then here we're going to have model but we need to look at a car model okay and then um model there but here we want to do a filter and we want to do a filter where the the make equals this drop down I've done one four yeah the make equals that make um but we need to do dot selected yeah dot selected dot make because this is usually distinct okay that makes sense selected dot value okay I'm going to sort that on the model there we go okay so it's bringing all the records there um but if you're not using distinct in a drop down list you need to make sure that uh so you've got all the all the values here so we want to go with the model there okay so we've got all the years here and then we've got all the makes here now the only thing that's showing up here are the different models okay um so we probably still want to use distinct but we want to use distinct after it's been filtered [Music] in the column that we want to do the distinct on model okay and then because we use the distinct we'll have to do value here save I'm going to run okay so now these are all the models for the Audi the BMW um make equals make oh Yoda should have different models here so something isn't working quite quite right on this third drop down so I'm going to take what it is that we're looking at here okay and looking at it in a gallery so we got more space to sort of troubleshoot what's going on here [Music] so Toyota do you see how all of these things are Toyotas yeah right so then here that's what I'm referencing a filter statement that gives me all of the Toyota's or if I select BMW it should down here give me all the BMWs which it does it just switched over to BMW um okay so then from there I'm just wanting to look at the models uh but for some reason it's giving me them all okay you see what this is look at this a BMW Mustang a BMW does not make a Mustang so what I'm wondering is in this mockeroo data did it give me a bunch of data that really isn't right you know what I mean that is that a little funny that makes sense oh goodness I noticed that Silverado was with a bunch of different uh makes as well yeah so macaroo gave us data that uh that doesn't really make sense does it again never ever again I almost wish that we would just generated 10 test records but I thought this would be a cool learning experience it would only take 10 minutes right you see how nothing's ever simple easy so when you come up with their somebody says how long is going to take you to create that screen like oh 10 minutes you know yeah right uh you need to account for a little uh things that sort of mess you up here and there but um so what I'll say is um perhaps next uh Saturday or um this next Wednesday we have an accelerator group so we can actually dig it in I can also dig and make make this a little bit more streamlined but we'll revisit this topic again I wanted to let you guys know that uh here on July the 4th I'm gonna be going live for for 10 hours I don't know Lou are you going to be around oh yes I am I've got I've got some new employees to start out that first morning and then I'll be here okay so maybe this is something we'll finish up on on the fourth do you guys like that idea I'd like to make sure you're squared away some somewhat soon and I'll have to wait a full week to get to the get into that but yeah we could uh revisit that so um do have a request for you guys 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 yeah so you guys have a nice rest your day and I'll be seeing you guys on the fourth all right 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 autoplay

Channel: PowerApps Tutorial

Views: 3,544

Keywords:

Id: uZcx_6gLa6E

Length: 149min 46sec (8986 seconds)

Published: Sat Jul 01 2023