cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ramsabi
Helper II
Helper II

Populating lookup field values in SharePoint Library document properties

With substantial help from this community, I have created a flow to upload documents to a library in SharePoint and update document metadata. The metadata is in an Excel file. Everything works perfectly........except I have several lookup fields to populate and SharePoint is expecting the numerical Lookup ID to be passed to the field whereas my Excel file has the text value. Can someone help me? Will be profusely grateful for assistance 🙏🙏🙏

Column List

Column ListColumn List

Sample Lookup Values

Lookup ValuesLookup Values

Complete Flow

Flow 1.jpgFlow 2.jpgFlow 3.jpgFlow 4.jpgFlow 5.jpg

 

 

@v-LilyW-msft - I saw that you had solved a similar question from @yjamous but I was unable to figure out how to do it. Can you help me please?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sudharsan1985
Solution Sage
Solution Sage

Hi @ramsabi try the below link and let me know if you have any issues.

Power Automate – Create Items with Lookup from Excel | Knowledge Share (spknowledge.com)

 

Hope it helps, please like it or mark it as a solution if it resolves your clarification or issue
-Sudharsan K...

View solution in original post

11 REPLIES 11
sudharsan1985
Solution Sage
Solution Sage

Hi @ramsabi you should get the master data from the SharePoint master list. From that item inside the Apply to each action you can filter using the text value from the excel and then get the ID and pass the ID.

Hope it helps, please like it or mark it as a solution if it resolves your clarification or issue
-Sudharsan K...

Thank you @sudharsan1985 Where is the Sharepoint Master List? How do I access it? We use SharePoint online.

sudharsan1985
Solution Sage
Solution Sage

Hi, @ramsabi I referred to the lookup list as the master list. In your screenshot above Status field is the lookup. So, you have to load the Status list and get the ID of the Status item based on the value from the Excel sheet.

Hope it helps, please like it or mark it as a solution if it resolves your clarification or issue
-Sudharsan K...

Thank you Sudarshan. I am sorry I am very new to Flow and am unable to understand how to do what you suggest. How do I get the ID of the item based on the value in the Excel Sheet? It would be very grateful if you could share the flow for getting the ID from Sharepoint for the value in Excel and then passing it to the field in metadata.

sudharsan1985
Solution Sage
Solution Sage

Hi @ramsabi try the below link and let me know if you have any issues.

Power Automate – Create Items with Lookup from Excel | Knowledge Share (spknowledge.com)

 

Hope it helps, please like it or mark it as a solution if it resolves your clarification or issue
-Sudharsan K...

View solution in original post

Thanks Sudharshan. I tested this and it works perfectly for the parent and child list. But I am unable to configure it to populate metadata in a document library. Can you help please?

sudharsan1985
Solution Sage
Solution Sage

Hi, @ramsabi are you using Update file properties action? You can follow the same approach to update the file properties. Try to use the action mentioned above and let me know the issue that you are facing while updating the file properties.

Hope it helps, please like it or mark it as a solution if it resolves your clarification or issue
-Sudharsan K...

Hi Sudharsan, absolutely fantastic !!! Extremely grateful for your help so far. Will be thankful for this final bit of assistance:
I tested creating a new library and the flow replicates everyhing till the point of Create item, instead of which I used Update file properties. Now I am getting the lookup values into the library. But another problem came up. Only one record from my Excel seems to be getting updated with the look up value. The Others are not getting updated at all. Most probably it has to do with the Apply to each step. When I create the Apply to each step, create the Filter array inside the apply to each step and as soon as I create the Update file properties, enter the site, library and ID, Flow is automatically creating an Apply to each 2 and puts the Filter Array and Update file properties inside it. Thiis happens as soon as I select 'ID'. Please see image below:

1. The "value"in the first Apply to each is outputs('list_rows_present_in_a_table')?[body/value'']
2. The "value" in the Apply to each 2 is outputs('Get Items')?['body/value']
Flow - Apply to eachFlow - Apply to each

I am certain that the problem is here. But I am also inserting images of my library after running the flow, my libraries column list, my Excel table and the parent lookup table.
LibraryLibrary
Library ColumnsLibrary Columns
Excel FileExcel File

Lookup ListLookup List

 

 

 





 

 

 

 

@sudharsan1985 Hi Sudharsan, I am almost there. I am able to match values in the first column of the parent column to the column header in the Excel file.  I just need to know the syntax for the Filter array when there are more than one column in the parent list.
You used the following syntax for the title column which is the first element of the array: body('Filter_array')?[0]?['ID']
What is the syntax for the second and thrid columns? I have 6 columns.
Also do I need to initialize a variable for each column or is the one variable sufficient.
Thanks in advance

sudharsan1985
Solution Sage
Solution Sage

Hi @ramsabi 

The syntax is the same and just change the 'ID' to match your field name.

 

Hope it helps, please like it or mark it as a solution if it resolves your clarification or issue
-Sudharsan K...

@sudharsan1985 Thank you for all the help. It was pretty complicated and I used two Get items and two Initialize variables since my data came from two parent lists and seven filter arrays for the seven columns and it worked without a hitch except that certain values were not coming at all and then I cheked the output of the Get items and realised that all my items were not there and a logical thought process made me search if there is a limi for Get items and bingo there it was. The default is 100 items. If there are more, you need to go into settings, enable pagination and put in a threshold. You would think that there would be some kind of warning that says your list is more than 100 items, but nothing.
All in all a good learning experience thanks to you.
I am marking your post as the solution because it led me to it.
Thank you again.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (3,437)