Time to Stop Using SharePoint Lookups in PowerApps

we are allowing the user the regular users of powerapps to update the choice fields and it's technically it's like a lookup but we're doing the lookup in powerapps we're not creating a lookup column in sharepoint [Music] hey everyone this is my sharepoint questions and today i'm going to go over using lookups in powerapps and what i'm first thing i'm going to say is don't use lookups in powerapps you can use the lookup formula but what i'm talking about is a sharepoint column lookup but what we're going to do today is we're going to do all the lookup stuff in powerapps we're going to give the power back to the user that way in your organization you don't get someone that says i need to change this choice column i want to add a new choice to it we're not going to create any lookup columns so right now i have three lists so i have a projects list and it has a few columns in here and each of these columns are different statuses and it has a clients column now if we go into my list you'll see that each of these is a single line text we're not creating any lookups we also have a clients list and so these are our clients i just made up some things these are my clients i have three different clients right now and each of these are single line text and then we have our status options so we have green yellow red in a whatever you want to use for your organization you can use but these are just my status options that i have available right now so i have three sharepoint lists all of them are using all single line text so this is my power app on my powerapp i have three different screens so i have my project screen this is where we're going to fill out the form my choices screen and so this shows the different clients right now and my home screen pretty much i have just two buttons create a new project or edit the choice fields so we go to create a new project i've already connected to everything i need to connect to what we're going to do is we're going to create a new form and edit form and this form is i've already connected to all my data sources it's going to be to my projects list in sharepoint and we're going to add all the fields to this project all the custom fields that i created so we have issue status risk status title status and clients make sure i got that one i do so i'm going to add all those fields to my form just going to rearrange them real quickly all right so each of these is a single line text right so the first thing we're going to do is we're going to do the status columns now these statuses is another list that i have so i have if we go back to sharepoint and we go to our status options we have green yellow red and a now instead of creating a lookup field in sharepoint what we're going to do is we're going to click on this form edit fields we're going to go to cost status and we're going to change it to allowed values right so this is going to change it to a drop down and right now there should be nothing in that drop down we're going to change the items property to status options that's the name of my list status options dot title so status options.title is actually an array and we're going to get a little red x here it's just saying hey i don't understand what you want me to update in the update property it doesn't understand.value we're just going to change that to title because that's the column that we're updating to so now if i update my button here we just want this to say new form uh this is let's see form five so when i click in here and i do new form now you can see we have different options in our cost status field now this is literally reading to sharepoint and um we're going to be able to update this later so i'm just going to go ahead and do the re uh the rest of the fields for the statuses so we're actually using that um sharepoint list to update our choices so we're just going to oh before now that's one thing you got to remember now before you unlock it you cannot unlock it before you convert it into a choices field so you want to unlock it after so if you see that i have the status column it's now custom it's saying hey no no so what you do is you just remove it i'm glad i made this mistake and then you add it back this is just you know some of the things that i've learned and so now that the status is not unlocked you can convert it to allowed values issue status allowed values risk status allowed values so all of my status columns are now allowed values and they're all going to be the same thing on the items property so in the items property we're going to now we can unlock it we're gonna change it to status.optionstitle and it's gonna put that little red x there but that's fine we're gonna fix that later now we'll fix each of these red x's instead of dot value it's going to be dot title for each of these another thing that you may want to do is you may want to allow for a blank option so if you click on this data card here i'm on the cost status data card and then you click on the data card value value you'll notice in let's see where is it allow empty selection property you can change that to true so now if i were in here and i click on green again it'll be blank so if i have it on yellow i click yellow again it's blank so it allows you to have a blank option i guess users may not know that you can double click on it and it will be blank but you can do that if i do it on one that does not have the allowed values or the allow empty selection you'll notice that it does not allow the empty selection so that's just you know a good thing for the user all right so now we have four columns that are using a lookup option technically we're looking up in sharepoint uh but we're not using a lookup column now let's go ahead and do clients i got a couple tricks here so we're going to go back to the form and we're going to edit fields and for clients we're going to change it to allowed values right now we're going to do the same thing here we're going to unlock it and we're going to say this is equal to i believe it's the clients title and once we do that we have our different options here we have our this place that place and once upon a time which if we go back to sharepoint clients i have three options right i have this place that place once upon a time so i have three options that i'm reading from so now i want to show you how you give power back to the user right we don't want to update this every time and need a developer to come in here and update powerapps or go in there and update the lookup no we want to give power back to the users of powerapps so they can do all this themselves you don't want them coming to you with a ticket or saying you know we need to update this choice field we need to update this lookup column no we want the powerapps users to do it themselves and not even have to enter into sharepoint so what are we going to do on our home page now you can do this however you want i just made this very simple i have an edit choice fields option right and so i have a form here let's go ahead and delete it just to show you what it was like before so i'm going to add a new form it's going to be an edit form and it's going to have the data source it's going to have our clients so this is our clients and i'm going to add the three fields the title state and i believe it's address address all right so i have three fields the title state and address and you notice my buttons broke here but that's just because this is now form six so we'll update this so this is just new form six and this button is just submit form six right we'll create a little bit more space in here for us now this is a gallery connected to my client data source i added a button or actually an icon and it removes this item so we can remove from here in our form for projects we only had three options right let's go ahead and add another so we can say another option right and it can be i'm not sure what state and then you know again street so then we're going to submit form we have now added another option to our gallery if we go back and we go to create new project in our clients another option is there look at that we are allowing the user the regular users of powerapps to update the choice fields and it's technically it's like a lookup but we're doing the lookup in powerapps we're not creating a lookup column in sharepoint and creating all this you know difficulty everybody's like oh i want to do this and i want to do that look the the key to all software development is keep it simple so we're just keeping it simple now we're giving power back to the user we can have you know them come in here add another say uh oops let's just say they made an error right you come back in here to projects there's now an oops option in here where they say oh i made an error oh let's just delete that option so now our sharepoint list is updated if we go to sharepoint we refresh we can see our another option is there oops is gone we go back create new project the choice fields only show the four that we want so we're creating the lookup on the powerapps side we're only using single line text we can submit this we can create our submit button so the submit button is going to submit form form five now we have our options in here this is our our new um project for here we now have our options you know we could say another option red yellow green we could submit it it's going to write to sharepoint we can check out our projects we now have a new project for here the clients is another option red green red yellow you'll notice that these are not look up columns we're not using lookup columns literally it's single line text all the way down single line texting line text we're just making it simplistic for us and so that's my biggest tip to you keep it simple cut out all the lookup columns why are you using lookup columns you really got to think about it this is uh making it simple for you thank you guys for watching this is my sharepoint questions if you like my ideas please like please subscribe my channel is growing i love it thank you guys if you have any questions feel free to put them in the comments because i am answering them because i'm not as big as these other people so i'm here i'm answering your questions feel free to share with me thank you

Channel: Andrew Hess - MySPQuestions

Views: 11,571

Keywords: sharepoint, lookup, columns, powerapps, power platform, power automate, microsoft, microsoft technologies

Id: tyZ7No8biw8

Length: 12min 26sec (746 seconds)

Published: Mon Aug 23 2021