0

It may be a simple question, but I want to use VLOOKUP-type functionality in my Sharepoint but I can't figure it out on my own and hours of browsing didn't help me as well.

I have a Document Library with lots of files and metadata, these are the files that are received from customers. One of the metadata fields is "CustomerEmail". Using this column I may group by and filter out the view by customers email. However, in order to make it more user friendly I want to have an automatically updating metadata field "CustomerName" which update file properties according to the "CustomerEmail" value. Doc Library looks like this:

FileName CustomerEmail CustomerName

Image001.jpg john@gmail.com
TestFile.docx mary@gmail.com

In order to make it I've created a Task List with names and emails of all customers. Task List looks like this:

CustomerName2 CustomerEmail2

John Doe john@gmail.com

Mary Williams mary@gmail.com

Then I wanted to create a Power Automate flow to trigger when the file is modified > loop through the Task List > find the matching value > update file properties (CustomerName: CustomerName2).

For this I've tried using ODATA filter (both using Get Items and Get files (properties only)), looping through the Get Items value and checking it with Condition control, but none of it worked.

If it would be only John and Mary (two customers), I would probably not think too much and just create two conditions in my flow (if CustomerEmail eq 'john@gmail.com', then update file CustomerName to 'John Doe', etc.). But since I have tens of customer emails and names, that's not an option and I'm looking for the way to make it possible since it doesn't look difficult...

2
  • Thank you Matiur and Julie, your help is a big step up for me in this situation. However, I am facing an unusual problem, following your steps I fail to get the 'value' from the trigger in the Dynamic Content section of Apply to each. I can only get 'body' which is an object and doesn't fit – javizonan Oct 28 '20 at 14:34
  • Please check the updated answer. – Julie_MSFT Oct 29 '20 at 2:05
0

See the screenshots and explanation below.

enter image description here

Detail - 1 enter image description here

0

According to Matiur's reply, I found that the flow is triggered based on the modification on task list, not based on the Document Library changes.

So I make some changes and test it, thanks for Matiur's contribution.

Document library with file properties:

enter image description here

Task list for storing customer names and emails:

enter image description here

Flow triggers when the file has been modified in document library:

enter image description here

Get items in list and filter it using customer email property:

enter image description here

Initializate the Compose using following expression:

body('Get_items')?['Value']?[0]?['CustomerName2']

enter image description here

Add the condition to check the customer name and update it in document library:

enter image description here

---------------------------------------Update--------------------------------------

Get the CustomerName2 column value with Compose action in "Expression" box:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.