Forum Discussion
Automatically fill a single text column in Lists by the value of a Person Field column
Hi,
Having trouble with this:
- I have a Person Field column in lists where I fill in an emailaddress
- I want to fill a single text column with the emailaddress (to use in flows etc)
How do I do that? I can't get it to work.
Thanks 🙏
Femke11 If you just want to show the email address in the list view, you can easily do it using JSON formatting like:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "[$PersonGroupCol.email]" }Where PersonGroupCol is internal name of person or group field. You can get the internal name of your person or group column by following this article: How to find the Internal name of columns in SharePoint Online?
You can read more information about this approach in my answer given at: Get Email from user and enter into SharePoint list field
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
22 Replies
Femke11 If you just want to show the email address in the list view, you can easily do it using JSON formatting like:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "[$PersonGroupCol.email]" }Where PersonGroupCol is internal name of person or group field. You can get the internal name of your person or group column by following this article: How to find the Internal name of columns in SharePoint Online?
You can read more information about this approach in my answer given at: Get Email from user and enter into SharePoint list field
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
I'm so sorry - I will test it more thoroughly in the future before I give a response...
In List A
In my additional request I only wanted to retrieve the email-addresses which work fine with the JSON formatting (suggestion from ganeshsanap ). It's quick and I don't have to press a button. I type a name in a 'Person or Group' column and the email address is automatically placed in a single line of text column (with JSON formatting as shown above).In List B
Now there is another List and I want get the 'Name-field' and the 'Email-field' from List A (that is actually a Person or Group column) via a Lookup column. Person Fields are not available for Lookup columns. So, in List A I added another single line of text column and with JSON formatting I retrieved the name.
BUT.... the columns with JSON formatting (in List A) will not show in List B... (the columns that I've made with the help of @RobElliott do show up though).
I've tried making another single line of text column in List A where I show the content of the formatted column, but I'm not experienced enough to get that working. Don't even know if calculated columns will show up in Lookup columns?
My additional question
Is there a way that I retrieve info (like email and name) without having to press a button, but will also show up in the Lookup in List B?
Femke11 You cannot use person or group columns in the calculated column formula. So, you cannot get user name from person field using calculated columns.
Above JSON I provided in my response only works for showing the email in list view. It is not saving the actual email/name value in the column. So, you cannot use it in lookup columns.
If you want the actual value to be stored in the column, you will have to use the Power automate flow on item creation/update. Follow this response: Automatically fill a single text column in Lists by the value of a Person Field column
Let me know if you are stuck while creating such power automate flow, I will try to show some examples in that case.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
- Hi Ganeshsanap,
Wow, this works much faster! Thank you so much!!
I've tried to do the same with the name of the person (because I cannot use a Person Field column as a Lookup in another List) and therefore I've tried the same JSON formatting (I used "[$PersonGroupCol.name]" and "[$PersonGroupCol.displayname]" instead of "[$PersonGroupCol.email]") - but that doesn't work :-). I cannot seem to find a list of names. Do you happen to know that as well?Femke11 The people fields in JSON formatting supports the following properties (with example values):
{ "id": "122", "title": "Kalya Tucker", "email": "email address removed for privacy reasons", "sip": "email address removed for privacy reasons", "picture": "https://contoso.sharepoint.com/kaylat_contoso_com_MThumb.jpg?t=63576928822", "department":"Human Resources", "jobTitle":"HR Manager" }You can get the name of users using the title proeprty.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Femke11 the quickest & easiest way of doing this is to build another flow which is triggered from a JSON-formatted button in the list. So in the example below Pick is a person column, Get email is a single line of text column and so is PickEmail.
In the flow the trigger is the SharePoint "for a selected item". Next add a get item action to get the details of the item selected.
Next, add an Office 365 users get user profile (v2) and select one of the Pick columns in the dynamic content box. I've selected Pick Email but you could equally select Pick DisplayName or one of the others.
Finally, add a SharePoint "update item" action and select Mail from the get user profile from the dynamic content box.
Name & save your flow and get the flow ID from the flow's address bar which you'll need in the JSON.
Back in the list format the GetEmail column in advanced mode, delete the existing code and paste in the following JSON, changing the ID to your flow's ID
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "button", "txtContent": "Get email", "customRowAction": { "action": "executeFlow", "actionParams": "='{\"id\":\"88823897-8dd5-4452-934d-a980defd39a9\", \"headerText\":\" ' + [$Title] + '\",\"runFlowButtonText\":\"Get email\"}'" }, "style": { "background-color": "#cf000f", "color": "white", "border-radius": "10px" } }After you select the person, click the button and after a few seconds the Pick Email column will get populated.
Rob
Los Gallardos
Microsoft Power Automate Community Super UserThank you so much for this good explanation, it works perfectly! Do you know if other people can use the 'Get email' button in this list as well?
Femke11 If you don't want users to click on every list item/row to get the email address, you can use the When an item is created or modified trigger in the flow and use logic given by RobElliott for updating list item with email address.
To avoid the infinite flow running loop, you will need to add additional logic at the starting of flow to check if person or group column is changed since last update and based on that condition, you can update the email address column.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
