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

[Krau5i]

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.

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

Any clues welcome!

Thanks,

Tobias 

[ScottShearer]

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

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

[Krau5i]

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 

[ScottShearer]

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

[Krau5i]

Hi Scott, 

thank you for your patience! 

I tried to apply it and got this error:

 

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 

[ScottShearer]

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

[Krau5i]

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?

 

[Krau5i]

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

[ScottShearer]

@Krau5i 

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

[Krau5i]

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

Tobias

[ScottShearer]

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

[Krau5i]

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

[ScottShearer · Accepted solution]

@Krau5i 

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

[Krau5i]

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

 

[Krau5i]

Hi @ScottShearer ,

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

Thanks,

Tobias

[Krau5i]

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

Thanks,

Tobias