Hello,
I have read many posts already but none has worked for me yet.
I have 2 SharePoint lists:
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
Solved! Go to Solution.
see screen shot below - substitute your email column for Created by email.
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:
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
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.
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:
Thanks,
Tobias
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.
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?
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
Use a toLower expression to convert the email address to lowercase. You can do so inside of the ODATA filter.
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
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.
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?!
see screen shot below - substitute your email column for Created by email.
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:
Hi @ScottShearer ,
would you know how to put the last piece together? Is there something I'm missing in above Flow?
Thanks,
Tobias
I figured it out. I had to read my Sharepoint list again; was referring to the items I deleted previously.
Thanks,
Tobias
User | Count |
---|---|
78 | |
55 | |
52 | |
43 | |
41 |
User | Count |
---|---|
81 | |
79 | |
75 | |
67 | |
43 |