Patch Dataverse Columns with Power Apps

let's learn how to patch data verse columns yay all right maybe not that exciting but let's face it data verse is becoming more and more popular and so if you don't know how to work with those columns outside of forum so if you don't know how to directly update those you're a little bit behind so what we're going to do is we're going to walk you through how to do that so we're going to talk about the simple ones first so like patching text and date and numbers right pretty straightforward then we'll get into the more complex ones so like choices and yes nose and lookups we'll get through those and then we'll kind of end at the most complex one in my op op which is patching file and image but unlike SharePoint we can actually patch these directly so very fun as we go along we will equate things back to SharePoint a little bit because I feel that's a basis that most of us have so hopefully that'll help we'll also talk about Auto number which you can't patch all right that's enough of the blah blah blah let's switch over to my desktop and take a look okay so here's the example app right obviously I try to practice this stuff I know it doesn't always seem like I practice but I do and so this is my practice app and so we're going to kind of rebuild some of this along the way but maybe the first place to really start is talk about the different tables we've got so if we jump over here I did put all this in a solution so if you want to download this whole working app in the solution so you can just copy and paste the code great just go over to training. powerapps 911.com and not only can you sign up for training class live ones with me yay but you could also go to the YouTube library and this will be one of the downloads in the YouTube Library so looking at my tables the main one we're going to work with here is practice patching parent I know I try to make these names super simple and you're going to see with all of my lovely little column names here look we just got a text column a choice column currency date column you know I I try to name all of the columns very specific to what we wanted to update so we did not have to worry about that let's make ourselves a new screen we're going to leave all this coolness here uh for people to just explore later and so the first thing I would typically do in this scenario is s myself a gallery on here and then I would connect it to practice patching parents now as we work through this we will just add each column in here so we can kind of see the data and so the first thing we want to do is we want to patch a new record so add a new one in here and so we're going to insert ourselves a button and so the Syntax for patch if you're not familiar with it is patch and then the name of your data source and so in our case it's practice patching parents and then we're going to say defaults and then practice patching parents so that just says Hey patch you know update this parent table right update this table and the second piece is a record you want to update and so by putting defaults here that says create a new record and so that just means make a new blank one and then set its values to whatever we put out here in the third spot now you're always going to do it in the syntax of a record so you'll have little curly brackets like that and if we close that is actually valid that would patch a completely blank record we don't want to do that but just so you can kind of see that's like the core structure also if you're brand new to patching and you're like whoa whoa whoa you're going way fast I'm assuming you kind of understand patching if you if you don't there's a video up there that will talk you through patching 101 right whereas this is like patching 2011 yeah we'll call it that okay so inside this curly brackets what we want to do the first thing we want to do is we want to patch the title column or text column so we're going to do text column right name of column Coen and then we're going to just put in here um video one I don't know very fancy words right and so what that'll do is update this list it'll create a new record and it'll set the text column to video one so we press the button bingo bango there is that video One record we just created so every time we press this button it's going to make a new record so when it comes to patching a text column then that is you know it just wants text it doesn't care how you get there so if we wanted to use a text input instead right we could have said insert a text input because text inputs output text right and so now if we go here right so what is that that's text input one we see over here on the left I go here and I can say hey instead of patching the hard-coded text that you could say patch text input one. text and so then now if we say play and we type in here and we say hi Mom my mom's coming for dinner tonight is't that exciting I think it is and we press the button there you go it says Hi Mom okay so patching text columns about as easy as you get you just need to give it text it doesn't care if you get it with hard-coded text from a control from a function from a lookup from calculation from an if statement doesn't matter it just wants text and if you think about your text columns back over here in data burst so like we go to columns and we say new column if we choose a data type of single live text here right so if we do text you know we've got all of these and so all of these are patched the exact same way all these things like email phone number URL those make a difference on how it's displayed when it's done via data verse but it's not really changing what we have to do if we're patching it or updating it directly like this okay same thing down here multi-line text we would update them the same exact way the only difference is that they can do more characters right I think see line text is limited to a smaller number I don't remember the number let's just look real quick so set this to plane we'll go down here to Advanced and let's try to change this I think it's 4,000 there you go so the maximum length of this field could be 4,000 so if you need more than 4,000 characters then you would just change from single line of text here to plain textt or rid text multi-line of text and then maximum character count is some big number like what is that a million 48 5 I don't know that's a really big number right I can't say that number but there you go so patching all those all the same next up on our list we want to patch a number field so we'll go right here we would say comma and we're going to say number column when we choose number column here right it just wants a number so if I type in 12 that would patch the number 12 12 now notice there that the 12 is not in double quotes right why is the 12 not in double quotes because if you put it in double quotes then that makes it the text one two instead of the number 12 and if you hover it'll tell you right does not uh work right expected number found type text so be cognizant of that so it wants a number but doesn't care how you get there oh I lost my curly bracket let's put that back so okay so if we went here and we said hey show me number number and so if we hit play and now we should get one with high mom with 12 underneath it right here okay so that works now what if you wanted to have that one to be an input well you're probably going to do a text input as well so you grab this text input this says text input two and over here is where I'm looking for this and I would normally rename those but it'll make the video longer so we're not going to rename all of them but you should be um and so here if we go to default right we'll get rid of that now you can change the format from number to text and you probably want to do this because what that does that means now if I try to type in letters it won't let me but if I type in numbers it's like good job okay so it lets me do that um so that's important now we go here what we did we'll just change this 12 to be text input 2. text it yells at us why if you hover same error number wanted you gave me text I want a number but Shane we told it to only be a number yes but text out inputs always output numbers no matter what so what you have to do is you have to wrap this into value okay if you wrap that in value that takes the text 222 and changes it to the number 222 I got that number right yay me um so that's important to understand you guys want to see a bonus tip let's see a bonus tip I show you a bonus error message that gets people right let's make sure this works right trust put verify press that we should have a high mom 222 that worked okay now sometimes what I run into with people is they go to write their patch formula and so they go here and they're like hey I want you to oh they just type in text input one okay there's no error message right it's like hey I'm happy so this should patch High mom with 222 now we'll change High mom to hi mom and dad's coming so we'll say hi Mom and Dad now if I press this button we're going to get an error network error when using patch function the requested operation is invalid this happens to people all the time and they get really angry the reason for that is because even though power apps doesn't know how to tell you it is patching from text column is trying to patch the whole control so it's trying to patch the border the font all of the everything makes up that text input you've always always always got to be text input. text in this case to get it to work now if we press this the same button again so press the button and now we've got high Mom and Dad 222 okay just a little bonus one I keep remembering that people have a hard time with another thing you might want to do now we got a couple of these in here if you hit format text it will reformat this for you and you can see that each columns on its own line and they're just each being comma separated this is a lot easier to read I typically don't like format text I'm not a developer so I don't like to see tabs and all that but in the case of patch it makes your life easier now thinking about number columns a little bit more back over here so if we go to number you know once again all those different number types those are a combination of structure and display right so for example whole number is going to be 1 2 3 4 right if you want to use a number with decimal points you need to use the decimal or the float option language code duration time zones they're over my head but they're just more number types with different behaviors okay but all of them from a power apps perspective they'd all be patched the same way also what about currency down here currency is also a number field it has some other moving parts to it we're not going to get into today but straight up if you just want to patch a currency column like you would literally just go here and be like currency column right let's name my currency column and we would just say uh $9.99 right whatever is 9.99 and then we'll go here to our Gallery we'll edit it we'll copy this paste this and now we would just change this to say currency column like so and if we hit play and hit button right now down here at the bottom there should be a third line which will be $9.99 right so currency columns patch the same way with numbers currency columns have more moving parts right we know that they have the different data types all that craziness we're not getting into all that um but you know it's core it's just a number all right next up date and time but you're getting used to how this works we'll go here here we'll say date and date column is what I named it and so for the date column it just wants date doesn't care how you get there so I could use a today function that will patch it to October 8th 2023 that's when today is right if you're watching this like three years from now say hi I think it's really cool you guys do that for really old videos um but so date column uh today and that would get you a date and so that would patch in no big deal right we go back over here we'll edit this and we'll go insert ourselves another oh we'll copy and the reason I keep copying is because the new Gallery templates they kind of have that special um formatting right I don't want to apply that to all of them so I just don't mess with it so we do date column and so now if we press this oh not that button we press play and we press the button now if we scroll down here hi Mom 222 999 108 at 12: a.m. now notice that remember today when it patches right what does today do it does today's date at midnight if you wanted to patch right now you'd have to do now now that's only going to work if when you're over here setting up your date and time column because remember when we set your column so date and time there's these date and time or date only and so if you do date and time you're going to get the whole date uh if you do date only it's always going to set it to that midnight time as well so there's a few other nuances with a date time once again I'm trying not to get two side trct but right date only just a date at midnight date and time is the more specific time so what column type choice you make here I'll determine what shows up over there and there you go so date columns if you wanted to patch them all to be Christmas right just a little bonus thing here remember all these columns they just want they want they don't care how you get there it almost Rhymes not really though right I got you just say hey I need you to use date value and then 12 25 202 three and so then that would patch Christmas right so if you want to hard code them all to Christmas if you want to look it up from somewhere else great if you want to do a calculation due today plus seven days great there's formulas for all of that whatever goes right here just has to be a date okay now let's see a little trick here and let's talk about a choice column right so as you guessed I named it Choice column so Choice column just wants a choice once again doesn't care I get there the thing though is like with my choices so if we go look it back over at the actual Choice column that we built so are you sure you want to close this yes we click on Choice column so I had use a choice set of dog cat fish okay now how do you get it right you can't just go here say Choice column dog that's how it would work in SharePoint that is not how data verse works if you do it it's going to yell at you I want an option set value they used to call call Choice columns option sets so that's why you'll see this option set word come up from time to time so what you're going to do you're going go right here you're going to start to type in in Choice column and now what you're looking for is you see here where it says Choice column practice patching parents this over here was what the option set got named as right like it doesn't make it super obvious I hate when this happens but it's a really goofy name typically you're going to use the column name and then the table name but if we do that it still errors but now if you do a DOT now you see the three choices and so now we could say that this is for dog so that's how you get to hardcoded dog whereas before right we were able to just use dog and SharePoint we've got to do that now if you're having a hard time finding what what this part is if we go back here contrl x for a second and we say dog remember the error message look at the error message now Choice column practice ping parents right it's telling me I can't move my cursor maybe I'll highlight it in the video but it says expected type option set value Choice column practice patching parents it's saying hey that's the thing you need so that's those are the words words that we had typed right here and then you did the dot and then you could see all the different choices so it's kind of it tries to help you like it tries to help you without telling you it's helping you it's you know not ideal but it works okay so that's how we set them on a dog what if we want to have an input to let the users choose then what you're going to do is you're going to insert a dropdown so we go here to dropdowns I can never find dropdowns I don't know why and here what you want to do is you want to use a function called choices so you're going to say choices and then your column name and so our column name is Choice column because we're very cool right not the that whole set right but the actual choices um column name right so it's choices and then it's practice patching parent dot Choice column right I realize I said that a little goofy but but that's what it is it's not that set that we just learned about it is just that this function function will look at this column and say hey what are your options and look you can now see in the drop down we've got dog catfish so that's how you get those into a drop- down and then uh what you'd go here is you would just say what was that it was drop down one so drop down one dot selected dot value and now if we say um oh I guess we got to update our Gallery so let's click on our gallery let's make it bigger again let's copy that copy paste what do we want right here we want this to be Choice column and so now we want to make sure we make a new one so we'll say play we're going to we'll set the dog to fish I parents don't have fish so we'll do that and then we'll change the number to 220 I don't know let's make it threes 33 we'll press the button if we scroll to the bottom we should have hi Mom and Dad 33 9999 boom boom and fish so that worked um I also realized that I didn't put a date picker here right so back in the date one you would have just added a date picker and it would have been date picker. select date my bad okay so that's a choice column all right before we jump into the next section if you're finding all this helpful please go check out training. powerapps 911.com we've got on demand classes we got live classes we got all types of different ways to learn we even have a Power Platform University a six-month fully immersive program with Hands-On Labs exams and a m tour everything okay or if you just need help building this over at powerapps 911.com we can also do everything from a 30 minute break fix to a 30-year project right we are here for all your Consulting needs we're like a team of 25 these days so you need it we can do it now we also over here we have so let's get out of that we have a yes no column now weirdly enough so in SharePoint yes no columns are a Boolean true false right like we can use them that way it says yes no but really it's just a true fault it's a bit field data verse it is not it is a choice yes and no it is not a bit it makes me really sad so guess what if you want to patch a yes no column it's exactly the same as a choice column because a yes no column is a choice column you just don't get to set the choices yeah so we just say yes no column and then if we want to set it to um the value right once again yes no column practice patching parents and then we do a DOT yes or no why this is super important well a patching makes patching easier because you now know it patches the same way the choice columns does but a lot of times in my demos like when I use a SharePoint yes no because it's true false I can make decisions based off of that directly so I can just say visible equals that value you can't do that because if we add a yes no column here right let's go over here copy paste again and if if we do yes no Comm it's not going to return true false unfortunately it's going to return yes or no I don't like that right if you did that same thing in SharePoint it would have said true in the case of yes but either way less for you to learn I guess right that's the upside we'll go positive okay how about lookup columns so look up columns right those are where you build relationships and so from here we want to look up uh a child item and so to look up a child item and to have that connected in what you need to do is you need to give it a record so when you're patching this is looking for a record so we do a comma and then we do look up column and so then now it's like all right give me the data I want and so right now what we're going to do is we're say hey I just want to look up the first record out of the child's table right so we just use the first function and then we'd start to type in child table we'd say practice patching children like so and then we' just say just that okay right because practice patching children that is the lookup like if we go look at the lookup column itself get out of there go find the lookup column you'll see that it's looking up against the child table so that's why it just wants a record once again it doesn't care that I'm getting there with the first function or I could have a lookup function or a filter function anything that well I guess I could do a filter filter return a table but I can use a filter and then first the filter just use look up right but it just wants a record it doesn't care how you get there so this is one way right so now if we did this and we just go over here so we'll say copy and paste again and we'll say hey you show me this item. lookup because one of the cool things about lookup columns this is one of the neatest things in data vers in my opinion is it does what's called polymorphic you do a DOT and it's like hey here's all the things I know and so if we say name then we'll be able to see what dog gets patched and so let's try it let's go here we'll say hi Mom and Dad and pet we'll change this to 335 we'll say it's dog we'll press the button if we scroll to the bottom it is Buddy and if we go look at that table and so we could just go here and go back to tables find the child table the first row in that table is Buddy if we done the last row it would have been Eddie we there's not a way to do a middle row we would have said first in whatever we're not going to worry about that but you can see that this is the table so what you probably want to do in a lookup thing though right is you don't want to make them all be so what you'd do here is we would insert ourselves once again a dropdown this is wonderful I think because now all you're going to do is say choices and then you're going to say practice patching parents right the the table you're currently in and then we want to do the lookup column it stinks that these never Auto suggest you have to remember what they're they are and typed them in but if we do that and now we look in here we see that there's three blank rows so this tells me it got the data but I'm showing the wrong thing so with the G or with the drop down selected here now go over here and change the value like what field do you want to see we'll say show me the name field and it should be showing us the name it's not why wouldn't it want to show us the name all right it's not cooperating that should work if it doesn't I would probably try saving and restarting the browser all that whatever right the other way to do this one let's just do it the way number two I going to show you anyway and so so for the items remember the items property of a drop down just wants a table it doesn't care how you get there so what if instead of using choices what if we just directly add the children list right if we look into Data sources I've already added practice patching children as well so we're going to go here that sounds really weird to say practice patching children whatever so we're going to say practice patching children that would return all the records from that table and now over here in the drop down it is actually going to cooperate right cuz Buddies a burning doodle so that way works too okay no big deal what are we going to do now that we have found a way to let them choose so let's choose uh who would we say chewy because he's in the middle he was the hardest one for us to choose earlier so instead of saying hardcoded to this record what I want you to do is what is the name of that that is drop down two so we're going to say drop down two and this time what we're going to do is dot selected we're not going to do drop down selected. value right because drop down selected value is the word chewy drop down two. selected is the whole record right if you highlight it'll tell you right right here data type is a record remember what does lookup column want it wants a record so we're getting to the record by drop down selected when we did the choice column it was selected. value because it wanted us to dig all the way in there and get that right so even though we're using dropdowns we might be using similar methodologies um you know the choice column versus lookup colum they want different things from you so the syntax is different this is one of those places where sometimes new people get confused because you're like sometimes it's this sometimes it's that and you're like why why is because It Wants What It Wants It doesn't care how it gets there okay then the last thing that leaves us is the file and image controls now interestingly enough we can directly update them but little we take a little jumping around I guess but what we're going to do is we're going to go here and uh add a add picture control right so add picture this is one way to choose a file in Power Platform right so we're going to do and we're going to start with an image this would work the same we're going to patch the file column but we're going to do it with an we're going to patch an image to it just so we can more easily visualize what's going on so if we click in here we're going to grab a picture of Roger and Simona right so we're going to choose them okay so now what we're going to do is we're going to go in here and we're going to say hey we want to patch the file column so we're going to say file file column now when you patch a file column it is a record and it's going to need two things now if you don't know what it needs if you hover right now it's going to tell you look invalided argument type expecting a record value but of a different schema missing column your formula is missing a column file name with type of text so if we go here we'll start type in file Name colon and then now it's wants to know the name of that file like it's Roger and Simona so what we're going to do is with a ad media control if you look there's two parts there's add media button and the image is showing there so add media button to has what we want so we're going to say add media button 2 Dot and so it's doesn't show up if you start to type in file there's file name now it's still mad but if you hover it's mad about hey I want the value with type of hyperlink it wants the value so we're going to say value and colon and then we're going to say add media button to and then we say media this time okay so that is going to add a file in our case with that in it right now we'll go over here we already know that in our Gallery we had an image and so we're going to say hey show me this item dot file column oh not file attachments file column and it's like hey that's a record I can't use it so we'll do a DOT value which we know is the actual file oh look there a bunch of them already have it so now if we say play we'll say um cute pups just so we know we're getting the right record we'll press the button we'll scroll to the bottom cute pups and there they are okay so this is the file column type in power apps or in data ver I should say so we say new column and so in this case I chose file and file right that's what the file one is it just has those two pieces a name and a value and it could store fullon prop proper files now we're using images so we can visualize that we're seeing we're putting this stuff there but we could have done the same thing to upload an Excel file the downside of using an Excel or doing like an Excel file here though so if we said change picture we go look on my desktop notice it defaults to image files so we'd have to change this to all files and then we'd go down here and then there's book one and it doesn't show up but if we press the button it absolutely saves right nothing's shown in the image control because it ccel files aren't images um but we know that the file is getting stored in there and we could reference it all the ways we would reference a file now if you want a better picking experience we're not going to go through it um it is in this app right already right I used an attachment control so if you go look at my video and upload a file um in there I show you how to get this image control because it's not in the drop down here you have to steal it but you would just replace the ad media with the image control and all that is here in this working app that you can get over at the uh on the training site okay so that right because the nice thing here just so you see it if we say attach file here notice it it defaults to all files that's the big win of going through the Hoops of getting that control now what if we want to do a image uh control or sorry an image file type right so we'll go back to screen one and and if you were thinking well how would I hardcode a file if I wanted them all to have the same file you'd have to get that file in here and just make it invisible like that's too many hopes no not it's not very compon s to go through those Hoops but you could technically do it because all these fields just want what they want they don't care how you get there okay so the last one here we hit a comma and so I have another column called image column so we're going to put the curly brackets in here image columns and file columns even though they're kind of the same they have different fields so this is saying hey you're missing a formula with a a column full with type of image so I'm going to go in here I'm going to type in full and then we're going to say add add media uh button 2 media again still mad will hover okay so actually I didn't expect this but that's all right so the full column doesn't want media right it doesn't understand the outputs of media we run into this sometimes because internally power apps share stuff differently so it's saying hey I wanted an image type but you gave me text so in this case what I want you to do is this is a a thing that we've solved before but you have to pass it through an image control remember the media the upload here it comes with both an add media button and an uploaded image so what I need you to do is I need you to change this syntax here and say instead of using add media to Media we're going to use uploaded image. image and if you go look at that image control let's just go look for a second it's image oh right there its image property is that output but there's something about passing through an image control that makes power apps happier so something sometimes we have to pass things to an image control in this case we do but now if we look at our button it is Happy Well it's happier but it wants something else now so now it's like hey I also need a column called value with the image and so we're just going to do the same thing we say value and then upload an image to. image again and then if we look it's going to maybe yell a third time um it wants a file name it's not I guess it doesn't have to have a file name but we want to give it a file name because we want it to know the name of the file and so then for this we would copy this in here this is just something I know I guess it doesn't actually yell about that but now if we do it so now if we say play uh C Pups 2 we'll change the picture to actually be a picture because we want to use it in the image column we'll do baby chewy there's baby chewy um oh we got to go over here we'll add another image column so or another image type so copy paste and so for this one we're going to say hey I want you to use image column now when you do image column it automatically spits out an image and look it's even got little rounded Corners why is that because let's upload chewy first so let's do this cute pops boom okay now it's saved we go to the bottom there is the adorable so notice that even though we uploaded the same file to both places we see something different this is super confusing but it's not so what's really happening here is that when you use an image column there remember there's the value and there's the full so even though we uploaded the same thing in both places as soon as we did that data ver said oh for the value I'm going to create you a thumbnail a smaller lower resolution easier to process version and with a cute little grounded Corners right so if I say im image column. value we see the exact same thing because that is that thumbnail that they created if you change this though to full notice that it looks exactly like the above one if we scroll down here as well you can see the same thing it looks exactly like the above one because it is using the full file the good size of that is that you get the full image all the things you don't get the cute little thumbnail it's everything the downside of that is that now to load my gallery it had to push you know these five and six and eight Meg photos down for every Row in there and so it was less performant so typically speaking you want to stick with the value because that's going to be the lower resolution and then when they click on it they open it somewhere then show them the big High Fidelity file you don't have to but you could you should and if you leave off all of that then it's going to show you the thumbnail the dot value without you even knowing it there you go right we want to understand why this stuff works that's why I explain little dumb details like that I need you to understand how these pieces work all right I don't know about you guys but that's a lot of columns it's a lot of stuff right hopefully you enjoyed this hopefully this helps remember I pointed off to some other videos along the way that will hopefully help you supplement your knowledge if you're this is all new to you but patching data verse columns directly right it's a skill we got to kind of continue to build data verse is becoming more and more popular with our customers and more and more popular in the community it is just becoming more of a thing and so we've all got to accept data verse so thoughts questions comments leave them below did you enjoy the video hit the like button right that makes uh the Youtube Gods happy and we want them to be happy as well okay and with that I'm going to say thanks and have a great day

Channel: Shane Young

Views: 10,135

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, dataverse power apps, dataverse explained, dataverse microsoft, powerapps patch dataverse, powerapps patch dataverse choice column, powerapps patch dataverse lookup column, powerapps patch dataverse table, powerapps patch dataverse record, powerapps patch dataverse choice field, patch function powerapps dataverse, dataverse image column, dataverse file column, dataverse yes/no data type, dataverse date and time, dataverse lookup

Id: UJ2jwOC_TkY

Length: 33min 44sec (2024 seconds)

Published: Mon Oct 09 2023