cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dmistry
Level 8

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
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!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

13 REPLIES 13
Super User
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

dmistry
Level 8

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

 

 

Super User
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

 

 

 

 

 

dmistry
Level 8

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. 

Super User
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

dmistry
Level 8

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.

Super User
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

dmistry
Level 8

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.

Super User
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

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!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

dmistry
Level 8

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!

dmistry
Level 8

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?

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!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 217 members 4,523 guests
Recent signins:
Please welcome our newest community members: