cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Conditional look-up for 2 SharePoint lists and replacement of value

Hello,

 

I have read many posts already but none has worked for me yet. 

I have 2 SharePoint lists: 

  1. list containing 1 column 'employee email' address
  2. list containing 2 columns 'employee email' and 'admin email' address

 

For certain employees (only the ones who have an admin are listed in 2nd list) I would like to replace the email address of the employee with the admin email address. In addition, the look-up should be on 'lower case' since it happens that sometimes I get the first letters of first or last name capitalized.

 

I'm importing the first list from an excel file into SharePoint and have below flow for the import. I would like to add the replacement in this flow.

 

2020-02-12 09_54_42-Window.png

 

Can it be added to the create item function when I first create the employee email cell (Create line item in SharePoint List, see above)? 

2020-02-12 09_57_27-Window.png

 

Any clues welcome!

 

Thanks,

Tobias 

 

  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

see screen shot below - substitute your email column for Created by email.

varEmail.jpg

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

15 REPLIES 15
Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

To loopup an item in another list, you'll want to use a SharePoint Get Items action and an ODATA filter.  The filter will look something like this:

Lookup.jpg

Please note the following:

  • ODATA filters use a columns internal SharePoint name which may/may not be the same as the display name
  • The second get items will return a collection (you'll loop through the output) but the collection should contain a single row of data and retrieve the admin email address

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Hi Scott,

 

thanks for your response. Can I ask you what that statement in the Filter Query does and which part is referring to which list? Also how do you reference to the admin email address?

 

Thanks!

Tobias 

Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

The ODATA expressions finds items in your lookup list that match certain criteria.

The reference to the email address from Dynamic properties refers to the email address from the current item in the first Get items action.

To get the admin email address, you'll refer to the admin email address column from the second get items action - the Get items action where the lookup list is used.

For more info on ODATA filters and SharePoint, please refer to this blog post.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Hi Scott, 

 

thank you for your patience! 

 

I tried to apply it and got this error:

 2020-02-12 13_01_58-Window.png

 

I might have mixed a few things up. Let me know if I understand the process correctly:

  1. create original list via create item (includes column Employee Email1)
  2. Apply to each on 'value' of this list
    • within Apply to each, get items from list 2 (includes columns Employee Email2 + Admin Email)
    • filter get item as 'Title' eq 'Employee Email1' to reduce the lookup-table to only one line, still 2 columns? (this is where the error happened)
  3. edit item (that I'm still unclear)

 

Thanks,

Tobias 

Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

The issue might be with your syntax.  There should be no quotes around title.  However, there must be single quotes around the Dynamic properties reference to the email address.

ODATAEmail.png

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Thank you for pointing that out. With those changes the flow was successful, but the replacement is not configured yet. I tried to add another loop and the Update item connection but that did not work. Is this how it should look like?

2020-02-12 14_31_13-Window.png 

Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Does anyone have an idea how to process the replacement?

 

Also, I mentioned earlier that email addresses will have to be converted to lowercase. How can I do that?

 

Thanks,

Tobias

Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

Use a toLower expression to convert the email address to lowercase.  You can do so inside of the ODATA filter.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Is it possible to do this inside SharePoint Create Item. Note that my look-up table is fine, but my original data source is a little messy and needs that adjustment.

 

I was hoping to be able to use an expression with dynamic content. However, when I select the tolower expression, I cannot select all my dynamic contents anymore (see screenshot).

2020-02-14 08_53_28-.png

 

Tobias

Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

That happens sometimes...  Flow is trying to be helpful by showing you only what it thinks you can use.  As an alternative, use a variable or compose action and convert the email address to lowercase there.  Then reference the compose action in your filter.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

That is unfortunate. There is no way to 'hardcode' it into the expression? How would a variable work in that case though. The create item is looping over all values from an excel sheet and created the line items. I'm unclear how I can loop through a variable at the same time?!

Highlighted
Super User
Super User

Re: Conditional look-up for 2 SharePoint lists and replacement of value

@Krau5i 

see screen shot below - substitute your email column for Created by email.

varEmail.jpg

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Hi Scott, 

thank you again for your help. This variable set-up works perfectly. Now the only thing missing is the lookup replacement by the admin email address. What I got so far thanks to your help is:

  1. Create Sharepoint list with Employee Email field (in lower case) [not shown below]
  2. Apply to each on each value (line item) of above list 
  3. Get item from look-up table and filter on Email address. this should give me a table with 2 columns and 1 row, if I'm not mistaken
  4. How does it go on from here? My thought was to use update item as per screenshot below and use the admin field from the look-up table but that doesn't do the trick. I don't receive an error message though...

2020-02-14 11_44_56-Window.png 

Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

Hi @ScottShearer ,

 

would you know how to put the last piece together? Is there something I'm missing in above Flow?

 

Thanks,

Tobias

Highlighted
Helper I
Helper I

Re: Conditional look-up for 2 SharePoint lists and replacement of value

I figured it out. I had to read my Sharepoint list again; was referring to the items I deleted previously. 

 

Thanks,

Tobias 

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Users online (10,983)