Populating lookup field values in SharePoint Library document properties

[ramsabi]

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 List

Sample Lookup Values

Lookup Values

Complete Flow

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

[sudharsan1985]

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.

[ramsabi]

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

[sudharsan1985]

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.

[ramsabi]

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 · Accepted solution]

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)

[ramsabi]

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]

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.

[ramsabi]

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 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.
Library
Library Columns
Excel File

Lookup List

[ramsabi]

@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]

Hi @ramsabi 

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

[ramsabi]

@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.

[Mohandas86]

Hi Sudharsan - I followed your Flow but not able to get what I want.

i have 2 list - one deal & Another Deal+Sku

Step1 – Created a Deal list (Deal level Information)

Step2 – Created a Test_Deal+Sku list (Sku level data)

Step-3 – Created a Lookup column (Lookup_col) to link the tables for related Power App gallery.

Values in the columns are empty, since we loaded the sku information from Excel.

We need to you create a Power Automate flow (Manual Trigger) to fill in this Lookup_col with Actual Deal ID  in Test_Deal+Sku from Test_ Deal_list.

What I need in that column is Actual Deal ID (Lookup_col = Actual Deal ID)

In the output it took first 10 id's and filled it