Answered

Populating lookup field values in SharePoint Library document properties

(0) Share

Report

Posted on by 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

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

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

Categories:

Business Process Flows

I have the same question (0)

All responses (12)

Answers (1)

sudharsan1985 1,325 on at

Like (0)

Report

Re: Populating lookup field values in SharePoint Library document properties

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.

Was this reply helpful? Yes No
ramsabi 84 on at

Like (0)

Report

Re: Populating lookup field values in SharePoint Library document properties

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

Was this reply helpful? Yes No
sudharsan1985 1,325 on at

Like (1)

Report

Re: Populating lookup field values in SharePoint Library document properties

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.

Was this reply helpful? Yes No
ramsabi 84 on at

Like (0)

Report

Re: Populating lookup field values in SharePoint Library document properties

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.

Was this reply helpful? Yes No
Verified answer

sudharsan1985 1,325 on at

Like (1)

Report

Re: Populating lookup field values in SharePoint Library document properties

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)

Was this reply helpful? Yes No
ramsabi 84 on at

Like (0)

Report

Re: Populating lookup field values in SharePoint Library document properties

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?

Was this reply helpful? Yes No
sudharsan1985 1,325 on at

Like (1)

Report

Re: Populating lookup field values in SharePoint Library document properties

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.

Was this reply helpful? Yes No
ramsabi 84 on at

Like (0)

Report

Re: Populating lookup field values in SharePoint Library document properties

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

Was this reply helpful? Yes No
ramsabi 84 on at

Like (0)

Report

Re: Populating lookup field values in SharePoint Library document properties

@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

Was this reply helpful? Yes No
sudharsan1985 1,325 on at

Like (1)

Report

Re: Populating lookup field values in SharePoint Library document properties

Hi @ramsabi

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

Was this reply helpful? Yes No