Update SharePoint Column using Power Automate (Choice, Date, Lookup and Person Column)

in this video we will learn how you can update a sharpened column using power automate with null or empty value or in other word how you can clear a sharepoint column we will cover choice column lookup column date column and user field so let's start without wasting any more [Music] minute [Music] hello everyone i am deepak srivastav and welcome to my channel if you have not yet still subscribed to my channel click on that button and also the bell icon so today we're gonna learn how we can create a power automated flow that can pass empty or null value to a particular sharepoint column or in a way i can say that can help us clearing some of the columns value we are going to pick up the choice lookup date and user column today okay so for this purpose i have already created a sharepoint list that has all of these four columns here choice username date and lookup column and the next what i'm going to do i'm going to create a flow using instant cloud flow now in your case it can be an automated flow or schedule flow before we start let me create an item into that list so i am going to use the create item action of sharepoint i'll select my site address list name and now i have all of those different column here so this is the test one twice i can select one of these choice user claim i'm gonna just pass my email address here you can also get this from any previous action or any other system for the date i'm going to use the utc now and selecting my lookup column save and test okay so the flow completed successfully and now if i go back to my sharepoint i can see the item were created now the scenario number one let's say i want to update this item and i want to clear this choice column of course very straightforward i'll go back to my flow i can remove this action for now here i'll add another action of course sharepoint update item i need to provide the site address list name id i know the id is 67 now this is also possible that you are creating a flow where you are getting this id and the item from some previous previous step you can use that as well i will update the title also so we know that it has been changed so i'm saying test one updated okay so now the question is how i can clear the choice value if i save it okay now let's say i'm not passing anything and that's this one right okay so plug them completed successfully but if i look at the item nothing changed right it has updated but choice is still a so what i need to do go ahead and edit let's say enter the custom value and i pass null again flow completed successfully and if i go back to my item the choice is still there so even if i'm passing a null not really having any impact on the actual field here so how i can do it so instead of passing the null there what i'm going to do i'm going to create a variable here okay type string and in this i'm going to say no and instead of passing the null straight here what i'm going to do i'm going to use that variable now i will save it okay so completed successfully and now as you can see here the choice column is empty now so the learning is if you want to clear the choice column you need to pass the empty string in it there's another way you can also do that clear this field if you don't want to create the variable creating the variable is much more recommended approach so that you can see what i am passing i can also do let me just quickly update this so what you can do instead of passing the empty string i can actually pass the empty string by typing the single quote okay so you notice what i did i just go to the expression and i just pass the single quote here and i click okay why i'm saying this is not recommended because you see here if you look at this you don't know what's going on but if i run this flow it is still clearing the choice column so you can either create a variable that has the empty string by passing null or you can actually just pass a single quote empty string into the field directly now one more thing that if you have noticed even if you have a required column in the sharepoint list like i have this choice if you are updating that column or creating using power automate this required flag is ignored so you can still go ahead and create an item or update an item with the value that is null even if the column is required so please keep in mind when you're writing those these flows that if you absolutely need value on a particular column you need to make sure that you are checking in the flow before actually creating it because you can pass the null or empty from the flow okay so this was a choice column very straightforward now let's check the lookup we are having this empty variable why not i just passed that empty null here okay so i'm just passing the same empty variable that i have into the lookup column and see what happened here i'm going to run it and i got the state error an error is saying the expected format is integer so this is the one important point to note when you are working on the lookup column even in the flow you pass this text right what you are seeing here but behind the scene flow actually passing the actual id of this column so that sharepoint can understand which lookup value i need to get from the my lookup list so you cannot pass that variable because it's a string what about null i can just go to expression and use the null this will also fail because null also not going to understand it so for the lookup what you need you need to pass -1 okay so to clear a lookup column or lookup field you need to pass the minus one now let me confirm this is not empty so you can see here let's run this flow now okay awesome so flow completed successfully and now if i go to list the lookup column is empty now now let's jump on to the date for the date is very straightforward you cannot still use this empty string because date required is to be in the date format but for the date column you can use the null so go to the expression pass the null here and now let's test okay again flow got completed successfully and if i go back to my list the date column is also now empty perfect another tip for the date column if you are using format of this column is date and time you may have noticed that sometime what happened you are passing it one date but it is not really getting saved or displayed in sharepoint as the same date that you are passing so maybe i'm passing 23rd but when i looking into sharepoint it is showing me 20 second or if i'm passing 20 second it may be showing 21. so i have a quick fix for that if you ever have noticed that kind of issue let me go to the column here and include the time and click save okay so now let's say uh you want to create this item for for future time of future date right so i'm going to use the date time function here and use the get future time to get some future data from the current utc and in the create item i will use the future time the calculated time or date and time that i'm doing let's test what happened okay now if you go back to your sharepoint list look at the date and time it's saying 26th of june 6 30 pm if i look at the flow the time that i'm getting is actually 23rd of june so sharepoint actually not displaying me the correct time practically it is called it is playing you the correct time because what happened when you create an item using power automate or any other application what sharepoint does it take the time and it convert it to the time zone in which the site is running my site is running in pst so that is the conversion of this time to pst in some scenario this may hold correct but let's say you want to actually store the exact time that you are getting you don't want any conversion to happen at the sharepoint so that end user can see what you are saving so what you need to do for the in that scenario number one thing you need to know in which time zone your site is running as i know it is running in pst and if i look at the pst time zone it is utc minus seven right so i know that whenever it's going to convert it's going to remove the seven hours from the time that i'm passing power automate is going to take the utc okay so what you need to do if you are calculating the time you need to add the difference so in my case it's 7 hours you can either use another date function here that is saying yeah add to time base time is the future time or the time that you are getting then you need to add the seven hours to it and now in the create item instead of using this time i'm going to use the time that i'm calculating by adding the difference now test okay flow got completed and now if you look at this item that just got created the date and time is exactly what i was actually passing so the tip is if you are using the date column with date and time enabled if you want to have the same date and time that you are passing from your flow to be displayed in the sharepoint exactly same you need to add the offset between these different tool time zone so that it can match okay now let's jump on to the next column that is the user field okay so we learned about the choice column date column and lookup column either we were passing empty null or minus one pretty straight forward what about user column can i pass empty in it no can i pass minus 1 you actually need to plus minus 1 but can i pass it here no can i pass the null no okay so the user column is a little bit more complex because when you create an item or update an item that is having the user field you need to pass the username and to empty these column you need to pass the minus 1 into username id but this column is not really looking for the id like the lookup column so the flow display these column differently it is actually looking for an actual email address that flow is going to actually resolve get the id of that that email address that you're passing and then pass that information to sharepoint we can't really do much here because if you're going to pass some empty you it's going to try to find that empty user in the in the active directory it's not going to find it's going to throw an error if you pass minus 1 there is no user such minus 1 so to empty the user field we need to go step 2 and that is another action in sharepoint that you have already used in your previous flow that called http it's very straightforward it's not very complex action you need to select the site address method is going to be post the uri so the uri is going to be underscore api web list get by title single quote the name of your list okay so this is the name of the list flow demo items bracket and the id of the item right so in our case the id is so that's going to be the url for updating an item then we have the header click on this small icon here so you can easily copy paste and what i'm going to do i'm going to paste this information into the comment so you can easily copy paste then the actual request body again you can copy paste it's in the comment field if you want to so this is going to be a curly bracket single quote underscore underscore remember there is two underscore not one underscore underscore metadata single quote colon curly type sp dot data dot flow demo so this flow demo you need to replace with your list name so sp.data your list name list item okay so this is how you can create this type and then comma you need to pass the username id username it's the column name or you can actually copy it from here what you are saying here so username is my column name and then you need to add id to it colon minus one this is what you need to pass to empty this particular column okay save it complete it as you can see here the item that we have created at the beginning all of these columns are now empty now this approach is not only going to work with single selection if you have a choice column that is multi-select this approach will work also for the username yep so this is all for today guys i hope this will help you when you're working in sharepoint and powered automate and you are trying to use power automate to update the sharepoint list item thanks for watching keep learning

Channel: Deepak Shrivastava

Views: 55,009

Keywords: power automate, microsoft flow, sharepoint lookup column, microsoft power automate, sharepoint, update choice column, sharepoint choice column, sharepoint choice column update, update date column, update user column, update lookup field using ms flow, update lookup field using microsoft flow, update person field powerapps, powerapps sharepoint lookup column, deepak shrivastav, microsoft flow tutorial, update sharepoint list using power automate, update sharepoint list using flow

Id: BrjobdJqvEQ

Length: 15min 49sec (949 seconds)

Published: Wed Jun 23 2021