cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi,

 

For a document library in SPO I have to convert a choice column into a looup column. The choice column Currenlty is called as 'Department' with few values as an exmple: HR, Marketing, Finance etc. Now, I crated a list on same site and saved all the Department field values in there and then added a lookup column to the same library and called it 'DepartmentLookup'. 

Capture.JPG

 

I have over 1000 files in the document library, so what I would like to do is, using Get files(properties only)-> update file properties - and set DepartmentLookup to value of Department choice column, I tried to that but does not look like flow would allow doing that.

 

Is there a workaround to this? Please help with a solution, thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry, I'm not sure what your current Flow looks like, but you'll need to include a "Get Items" action in order to get the relevant list item from your Departments list.

 

Trigger: My Flow starts from a Manual Trigger, please feel free to replace with whatever is more suitable for you.

 

If then have an "Apply to each" loop which will process each file in my library. Within that loop, I have the following actions:

Action 1: I use "Get Items" to get the relevant list item ID from my Departments list by using a Filter Query. In my example, the name of my department is stored within the Title field, and I am taking the Department Value (e.g. HR) and looking for a match.

 

Lookup1.PNG

 

Action 2: Still within the loop, I am going to perform an update on the File properties. I need to update "DepartmentLookup Id" with the List Item ID from my departments list. As I don't like unnecessary loops, I have used an expression here:

body('Get_items')?['value'][0]?['ID']

So under the bonnet, I am setting the DepartmentLookup ID field to 1, but when I look at it through SharePoint, I will see HR

Lookup2.PNG

 

Is that more what you were looking for?





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

13 REPLIES 13
Highlighted
Dual Super User
Dual Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry 

 

When updating the item using update file properties action

 

the lookup column will show up as lookup column Id field

If you go to custom value - here you need to provide the Id of the respective item in the lookup list

 

So what you would have to do is first query the lookup list base don the departmner column value and get the ID of the item and use this ID to update the lookup column

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Highlighted
Post Prodigy
Post Prodigy

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

@RezaDorrani in regards to the below, can you please share an example with screenshot, would be really helpful.

So what you would have to do is first query the lookup list base don the departmner column value and get the ID of the item and use this ID to update the lookup column

 

 

Highlighted
Dual Super User
Dual Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry 

 

First initialie a variable of type array as follows

[{
"Name": "Marketing",
"id": 30
}, {
"Name": "HR",
"id": 85
}]

 

where id is the ID of the item in your Departments list in SharePoint

you can keep adding all your data to above array

 

Next in the for each loop for your items list (items where department lookup is to be updated)

within loop 

use filter action as

 

Filter action

From - the array ypu initialized

Condition (left) - Expression - item()?['Name']

is equal to

Condition (right) - dyanmic content of department obtained from your for loop

 

then when updating item for lookup id field set custom value as

expression - 

body('Filter_array')?[0]?['id']
 
 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

 

 

 

Highlighted
Post Prodigy
Post Prodigy

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Thanks a lot @RezaDorrani , can you please double check the Array initilization values, seems incorrect syntax wise. Also if possible,. please share screenshots of the flow, would be helpful. 

Highlighted
Dual Super User
Dual Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry 

 

Please note screenshots below are just illustrations (you will need to update them based on your scenario)

Capture.PNG

 

 

1.PNG

 

2.PNG

Highlighted
Post Prodigy
Post Prodigy

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Thanks @RezaDorrani  unfortunately, not sure if this would help me achive what I am looking to do as per my question description.

Highlighted
Dual Super User
Dual Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry 

 

Your question was to update the lookup field with the department value

 

Did you add the logic provided in your for loop

Can you share the screenshot of the current flow with the updates and where it is failing

Highlighted
Post Prodigy
Post Prodigy

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

@RezaDorrani I have about 160 entries in production for the lookup column, so I guess based on your solution, i will have to define them all manually in the array which is going to be time consuming I guess, not sure if there is a better way. I am also confused with the join operation you showed, not following that part at all. My question was in reference to a document library and updating lookup column from value obtained by choice column.

Highlighted
Dual Super User
Dual Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry 

 

Can you show me which join operation?

 

Also, if your list is large then first query your list which has all the items (ID and title) and then create the array based on the list items which can be used for setting the lookup values

Highlighted
Super User
Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry, I'm not sure what your current Flow looks like, but you'll need to include a "Get Items" action in order to get the relevant list item from your Departments list.

 

Trigger: My Flow starts from a Manual Trigger, please feel free to replace with whatever is more suitable for you.

 

If then have an "Apply to each" loop which will process each file in my library. Within that loop, I have the following actions:

Action 1: I use "Get Items" to get the relevant list item ID from my Departments list by using a Filter Query. In my example, the name of my department is stored within the Title field, and I am taking the Department Value (e.g. HR) and looking for a match.

 

Lookup1.PNG

 

Action 2: Still within the loop, I am going to perform an update on the File properties. I need to update "DepartmentLookup Id" with the List Item ID from my departments list. As I don't like unnecessary loops, I have used an expression here:

body('Get_items')?['value'][0]?['ID']

So under the bonnet, I am setting the DepartmentLookup ID field to 1, but when I look at it through SharePoint, I will see HR

Lookup2.PNG

 

Is that more what you were looking for?





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

@MattWeston365  You are a genius my friend, you have no idea how much this has helped, thanks a bunch, cheers!

Highlighted
Post Prodigy
Post Prodigy

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

@MattWeston365I am running into an issue, hopping you could help. 

I ran the flow in dev env prior to applying it in prod and noticing a big issue, in the same library, I have other fields of type lookup which accepts multiple values and populated of course. 

 

After I ran the flow, they were updated as blank, do you have any thoughts?

Highlighted
Super User
Super User

Re: How to populate SP Doc Lib Looup column based on value retrieved from choice column

Hi @dmistry some fields just need to be set again, so you could use the dynamic content from the trigger or from Get Item (whatever it might be) and then use that in the Update. Usually this affects Title as it always requires something to be entered.





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,824)