How to create a SharePoint list item with lookup field using Power Automate (MS Flow)

[Music] hello after a long time I'm back in front of the camera and recording another video for youtube this video is the result of a question that I got from one of my udemy students from far far away as far as I can remember from Peru and this question is about how we can use Markov low or as they call it these days mark soft power automate to insert values inside a SharePoint list easy right but no the question is not that straightforward the question is that that list that we want to insert the values inside it has one or more lookup fields which means these are the fields that you have to select the values from a drop-down that those values are in a different list so let's see how we can do it I think it's very easy but I try to make it more interesting enjoy so this is our setup I have a list in SharePoint called PR d1 and it has two records in it one of them is floppy disk it's electronics well I guess it should be something like collectibles or antiques but we don't have that category regardless this list has a field called category categories actually a look-up list so the values in this field should be picked up from another list called categories let me show you the list so this is my PR d01 and the category is a lookup field there is also another field called unit price that I just added to be here so if I go to categories you will see these are the items that I have here and if I go back to PR D 0 1 and I want to add a new item and I call it demo item I can pick up the category from the other list for example miscellaneous and the unit price is going to be 12 for example and if I click on save I will have it here as a new item so you see this is a lookup field now the question is that can we insert another record here under PR d01 and populate the category with the value from the other table let's see how flow or mark soft power automate can do that I go back to my poor automate portal and I create an instant flow I think it should be very easy I call it lookup insert demo and I select manually trigger a flow I click on create bingo new step and I just want to go for SharePoint select create item I select the grade item and this is asking me for the website I just need to pick the website from the list or if you don't have it in the list just select enter custom value and copy and paste the URL from the other website let me just copy and paste it from here so you see how it works and that's it so now if I go to the list name I can click on this drop down and it will pick up pure d01 as soon as I select it it shows me the available fields that I should provide the value of it and I call it demo from power automate came on the drop down I need to select for example seasonal and the unit price is going to be $100 good and I click Save and let's test it I perform the trigger action save and test should check the connectivity and there we go we have the green light that our connectivity is good I click on continue and hopefully if everything works this title should appear in SharePoint list so let me click on Run flow and let's see if we got it yep demo from power automate season all hundred dollars done yeah I know I know what you're thinking probably you are thinking Ali are you kidding me is this how low you are you really waste our time for with your like this output hold on before you move on an unsubscribe my channel let me tell you the second part let me make it a little bit more interesting instead of just picking up the values and hard coding it into the flow or power cultivate this time let's put the values inside the trigger so basically let's have a trigger that accepts the product name category and unit price and see how we can handle this part now I promise it's gonna be more interesting this is the proper scenario so instead of just a trigger that has a button on it I want to use a dialog box that the user enters the product name category and unit price and I make all of the mandatory now when it comes inside the power automate workflow it should check if it can find the category it should assign the category if not it should assign the miscellaneous to the category before I really dig into that I want to show you something let me go back to the flow itself oh gotta go to edit view instead of entering the category ID selected from the list let me enter something else let me set up seasonal I enter for example one and I say use one as custom value so now instead of category I have one but what does this mean if I go back to SharePoint and this view I actually added the ID field so the number one that I have which is actually category ID that it says I can use the ID directly from here so basically if the miscellaneous has the ID of five if I come back here to the flow and instead of one I put five I'm good now I call it God's sake I call it demo one I'm just typing it without seeing hopefully yep and the unit price is going to be 200 this time and I click on save this time if I run it I expect it to enter this value or this product in the PRD zero one but the category should be miscellaneous let's save it and test it making sure the theory is working save and test run flow done let's get back here and see if we got it PRT 0 1 and we have it so basically if we don't have the value itself all we need is just the ID so now we see how this one works if you have a trigger that accepts all these values I can have the category and I can pass the category to a query and I can look it up from the categories list and get the ID and use the ID to insert the value in the PRD 0-1 now if I don't find it I should pass the value 5 which is gonna be miscellaneous perfect so let's start by creating this trigger trigger is already there so so all we need to do is just going to the flow I click on edit and I change the trigger at the fields text is gonna be product name I add another field called category and we add another field this time it's gonna be a number and I call it unit price I really don't need to create item at this moment let me just get rid of it because still have some work to do before we get there now after I get these values I need to add a new step that performs a lookup to SharePoint so I go to SharePoint and under SharePoint I look for get items and when I use the get items again it uses the same connectivity I use the flow course list name this time I want to query categories so I want to get the category name here query it here based on the category that I received from here and then see if I can find that category so if I click on show advanced options I have the filter query and all data filter query to restrict entries if I put the mouse cursor here it covers everything so I don't see anything so let me just open notepad and inside notepad I want to write my OData query so I say title equals single coat and inside a single coat I want to put the value that I get from the input so let me just copy this here and paste it on the filter query I don't see but I trust it's gonna paste it and now if I go under manually trigger flow I can click on category and after that I can put this guy here and put a single coat here let me tab away and see what I got so title equals single code category single quote of course if you just have a bigger screen it doesn't cover it Microsoft please do something about this UI we are not happy with this let's give us a way that we can shut down this intellisense okay so if you are not familiar with the OData queries I have another video on YouTube about using the old data I will put a link probably somewhere here I also put the link in the video description so you can look it up and you can follow and learn about OData it's very powerful and it saves you lots of hassle and also money when it comes to the query count and using the connections regardless let's not get sidetracked so I have a query which is called title equals category and this category is coming from here I really don't need top count and all those things we know it either returns nothing or it returns one single value if I go back here you see under categories there are all unique values perfect so I'm good with this part I just save it and let's test it and see if it works as we expect test and product name I call it test whatever if you're not processing it here we are using books and the unit price whatever we put here again it doesn't matter the only thing is that I want to make sure books exists here so if I go here and I click on run flow done and if I see there get items let's see if we cut it perfect so the ID is two and the title is books perfect that's all we needed and let's test it with some other dummy value that does not exist okay so product name whatever category just some dummy value that does not exist and I put a dummy unit price run flow done and this time if I go to get items there is nothing here so the value that it returns has a count it's an array it's a JSON array again if you're good with Jason fantastic if you're not good for Jason I have a JSON course on udemy this course is absolutely free you can go there and emerald even today and learn J's and it's very simple to work with regardless this jason has a property called value and that value is an array arrays are specified by square brackets in jason and it has nothing in it so we're good with this so we know when the gift item returns one value it means it's going to be the actual ID of that item but if it doesn't return anything it means that our ID should be the ID of miscellaneous item which is five fantastic so let me click on edit again and when i come back here here i want to add another action and i want to call it any eli's variable under initialize variable i want to call this variable category ID the type is going to be integer and the value that I want to enter is going to be five again this is the five that I got from here miscellaneous so if the getitem return something are you reassigned a value to the categoryid if it doesn't return anything I don't touch it and it will continue working perfect now if I go to get items this one returns a value that a value is an array and we know that right that array has either nothing in it or what item in it great so when we have this information let's check if it has returned anything so if I click on new step I can add a condition here and if I select the condition for the choose value I can get the count of the items in the get items so let's do it here if I go under expressions I can say Glenn which is actually a function that accepts an array and returns the number of items in it which is exactly what we need from get items so if I click on dynamic content and I check the value that it returns remember the value was an array with square bracket this one returns me the number of the items that it returns so I'm using Len the value which is the array I want to see how many items it has and this is going to be an integer so I click on OK I'm happy with it is equal to 1 as we discussed it read either returns 0 or 1 so if it returns 1 it means that hey this care item actually found a category or related to the category that the user entered so if this is the situation I want to get that value and insert it here let me get that value here and I say set variable and this time I want to pick the value of the category ID because now actually have a valid category ID and this value we should get it from the get items right where do we get it from if I use a get item and I use the ID directly because by nature this is an array it puts a for each loop here I don't like it so what I do I actually go to the expression and I say whatever that that value that you get from there convert it to integer and the value that we want if I go to the dynamic content care item has the value do you remember that was an array I want the first item in the array and of that item I need the ID so let me I know this one can make a little bit nervous I keep this in my notepad and when we run it I will show you where I got this ID regardless I have a udemy course which is called master marks off low expressions in two hours so in that course I'm covering working with all these expressions down to the last details from Ground Zero to very complex expressions if you are interested regardless I will put a link in the description but let's get back to where we were I will explain this in the end when I'm running it after tests so just keep it here so this expression is gonna convert whatever we get here as the value which is an array we get the elements 0 of that array and for element 0 we get the ID property and whatever it is we cast it to integer because we want to put it in an integer variable so I click OK and we're happy next step we need to insert the value inside this PR zero one and this time everything has to come from the trigger that we created on the top except for the category ID which if we don't find it it should put five but if you find something they are in the category stable we should get the ID from the category ID using this expression and assign it here perfect so new step sharepoint and under sharepoint I'm looking for create item so I select the create item and this time again I'm using the same connection to SharePoint but instead of category this time I'm using PR D zero one exactly the same thing title this time I have a value so if I go to the trigger that I have which is manually trigger flow the title is going to be product name I put it here the category ID now instead of picking up the value from here I select enter custom value and now I can actually pick the variable which is category ID and the unit price again it's coming from my manually trigger flow which is the unit price so far so good let's save and test it so I click on save again sometimes it doesn't okay let's test it and see what we get out of it so product name is going to be final test product category is going to be books and unit price is gonna be 150 run flow done let's see if it is done come back here and final test products books $150 great now remember I wanted to show you something which is here the reason that I kept it here to explain it to you is here when I use the get item see the value that we get this value is actually the same value that we get here it is an array so this array can have multiple elements but this one this array has only one element although it's not a small one so it's like a full-featured package of where of properties among real property so basically when we use 0 here which means the first element of this array now this first element of this array has a property called ID so when I say that ID I'm actually referring to this one again working the DS expression is not something that you can learn it or examples it's a little bit more complicated than just google it and find the right expression again the course is there take a look into that and I hope you will enjoy it fine so let's try something else I go back to the top of the page save another test and this time I say the product name is going to be dummy test and this dummy test I will voix I want to put it under the category of sports just to make sure we don't have sports here yeah we don't have it great so unit price is going to be 1,400 and I click on run flow done and this time if I come here under PR d01 tummy test goes under miscellaneous and again if we check this if you go through the condition the condition is false so variable did not work and create item actually uses the category ID tommy test and the unit price and this category ID is the ID of the miscellaneous items and that was all about it thank you for watching I hope you enjoyed the video as always don't forget to Like and subscribe like is more important than anything else for me these days because I really want to promote the channels and based on your support I will have more and more time to work on the video videos and produce more and better content for you stay tuned and next video is coming soon hopefully paradise [Music]

Channel: Alireza Aliabadi

Views: 18,223

Keywords: SharePoint, Create list item, Microsoft Flow, Power Automate, OData, Flow Expressions

Id: bTqWrM1p_WI

Length: 25min 30sec (1530 seconds)

Published: Thu Mar 05 2020