How to add Source Lookup Values from a People or Groups column to Target List using JSON

Question

How to add Source Lookup Values from a People or Groups column to Target List using JSON

Macdonald, Alana 26

I have a Source list that contains team contact information and a Target list with project taskings.

Source List


Team(single line of text)	Manager(person or group)	Admin(person or group)	text email(single line of text)(JSON formatting below)
TeamA	M**e	Bb, Ky	m*** Email address is removed for privacy ; b** Email address is removed for privacy ; k** Email address is removed for privacy *

Target List


Project Number(single line of text)	Team(lookup)	Team: text email(lookup)
A-12345	TeamA	m*** Email address is removed for privacy ; b** Email address is removed for privacy ; k** Email address is removed for privacy *

I want to select a team in the Target list which will add the individual team member emails in a column in the target list (using Lookup). The text email column in the target list will be used to send an email through power automate, triggered when a list item is created.

As I am unable to use a 'Person or Group' column for a Lookup column, in the Source list I used JSON to pull the text email address from two 'Person or Group' columns into one column (single line of text).

{
 
"elmType"
"txtContent"
}

It works, but it does not appear to be selectable text, which I'm assuming is why it doesn't show up in the lookup column in my target list.

Any help would be greatly appreciated!

Macdonald, Alana 26 Reputation points

2024-08-08T18:31:17.5966667+00:00

My code block won't save - which says to me that it's probably wrong, but it appears to work in my SharePoint column.

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",

"txtContent": "='' + [$RMCMember.email] + '; ' + [$ExecutiveAdmin.email] + '; ' + [$TextEmail1] + '; ' + [$TextEmail2]"

}
Xyza Xue_MSFT 30,176 Reputation points Microsoft External Staff

2024-08-09T02:07:12.13+00:00

Thank you for your question.

We are currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.

Accepted answer

2 additional answers

Your answer

Macdonald, Alana 26 Reputation points

2024-08-08T18:31:17.5966667+00:00

My code block won't save - which says to me that it's probably wrong, but it appears to work in my SharePoint column.

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",

"txtContent": "='' + [$RMCMember.email] + '; ' + [$ExecutiveAdmin.email] + '; ' + [$TextEmail1] + '; ' + [$TextEmail2]"

}
Xyza Xue_MSFT 30,176 Reputation points Microsoft External Staff

2024-08-09T02:07:12.13+00:00

Thank you for your question.

We are currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.

Answer 1

Hi @Macdonald, Alana ,

Since this thread has been sitting for a while, Do you have any other questions about this thread? If so, feel free to keep posting them here and we'll be happy to help you out.

I would make a brief summary of this thread:

Issue Symptom:

Want to add Source Lookup Values from a People or Groups column to Target List. Using json to display the person column's email is a dummy value for this list view, it's actual value has no stored value. So there is no way we can choose to have the email value in another list look up column.

Current status:

Straightforward solutions, Manually typed the emails into my source list (~25 emails).

You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community member's to see the useful information when reading this thread.

Your understanding and patience will be highly appreciated! Hope you have a good day and keep safe!

Answer 2

Macdonald, Alana 26

Sorry, code block didn't save.

{
 
"elmType"
"txtContent"
}

Answer 3

Hi @Macdonald, Alana ,

Thank you for posting in this community.

Using json to display the person column's email is a dummy value for this list view, it's actual value has no stored value. So there is no way we can choose to have the email value in another list look up column. User's image

1.You can create a Microsoft flow/power automate to update the Email address field based on the Person or Group field to text email column.

Please refer to:https://tomriha.com/get-items-for-each-user-in-multiple-people-picker-field-power-automate/

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

Hope the information can help you. Since Power Automate is currently not supported in the Q&A forum, and my knowledge of it is limited, it would be great if this solves your problem. If this doesn't solve your problem, or if you have a more complex problem, it is recommended that you start a new discussion via the Power Automate Community to get more targeted support.

2.Then creates a list relationship using a lookup column that automatically displays emails based on the Team selected in the Target List: https://support.microsoft.com/en-us/office/create-list-relationships-by-using-lookup-columns-80a3e0a6-8016-41fb-ad09-8bf16d490632

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Xyza Xue_MSFT 30,176 Reputation points Microsoft External Staff

2024-08-12T08:30:54.4066667+00:00

Hi @Macdonald, Alana ,

Please kindly consider this comment as a warm follow up, I want to know if your problem has been solved. If you still have any other questions, please feel free to comment here and I'm glad to provide further support.

If the answer is helpful, please click "Accept Answer"
Macdonald, Alana 26 Reputation points

2024-08-12T16:35:15.42+00:00

Unfortunately, I don't feel like the solutions in the links provided are what I'm looking for. So far I'm unable to create a flow that works. I'm quite new to this so I'm finding the instructions in the link difficult to follow (I don't know exactly what dynamic fields they are grabbing, how the flow is triggered, why are my actions looping/not looping when theirs are). For now, I've manually typed the emails into my source list (~25 emails).
Xyza Xue_MSFT 30,176 Reputation points Microsoft External Staff

2024-08-14T07:02:09.75+00:00

Hi @Macdonald, Alana ,

Issues regarding power automate are really complicated and you need to seek professional help. Since the amount of data you have is not very large, as you have done manual entry is probably the fastest way to go. Any further questions? If so feel free to contact me and I'll be happy to help you out. Have a good day!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

How to add Source Lookup Values from a People or Groups column to Target List using JSON

2 additional answers

Your answer