SharePoint List - Concatenate multiple rows

Copper Contributor

Hello!

 

Thank you so much for any assistance, truly.


I am hoping to create a SharePoint calculated column.

I have a SharePoint list In which I display agent's leads.
Within the list, it shows the lead, the agent assigned, and which region the lead is assigned to; moreover, I want to create a calculated column that will be next to the my agent assigned column that will display the name of the different region choices they are assigned to.

So, if an agent is assigned to multiple, different, regions, it will display such (even if they are viewing only one of their leads). 

If I need to make a calculated column, I can group the agents based on their email column.

12 Replies

Hi @bryanfrumkin47,

 

How are you storing the information for regions mapped to the agent?

If it's a separate list with 2 columns (agent, regions) then you can use this list to pick the agent name in the Leads list, and additionally display the regions column automatically.

 

Refer the below link for more details, "Adding a Lookup column to an existing list"

Create list relationships by using lookup columns - Microsoft Support

 

Use Like if this post helped to solve your issue and Mark as Best Response if the request can be closed.

Hello @nimesht

I am storing the region information in a single line of text column that is being uploaded from an excel spreadsheet.
Furthermore, Is my goal only possible with two lists?

Hi @bryanfrumkin47,

 

Will it be possible for you to share the screenshots with the details about your requirements and the relevant lists?

Mask the confidential data and then upload the image.

@nimesht 

Within the list, it shows the lead, the agent assigned, and which region the lead is assigned to (choice column); moreover, I want to create a calculated column that will be next to the my agent assigned column that will display the name of the different region choices they are assigned to. That way if an agent is assigned to multiple, different, regions, it will display such (even if they are viewing only one of their leads).

@bryanfrumkin47 You cannot achieve this using SharePoint calculated column or JSON formatting as you cannot read/reference a column value in another rows/records.

 

You cannot reference a value in a row other than the current row.

 

ReferenceIntroduction to SharePoint formulas and functions 


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

I would create two lists instead of using a calculated column... One for leads and one for the regions of the leads. The "Region Assignment" list has a lookup back to the "Leads" list

SvenSieverding_0-1692969849717.png

SvenSieverding_1-1692969861167.png

Then I would create a new site page and add both lists using lists webparts. Now I would configure dynamic filtering on the webpart of the "Assignments" list and set it to be filtered by the value  that is selected ion the "Leads" list

AgentleadsConfig.gif

Now I have both lists on that page.
If I select a lead, the "Region" list automatically filters to show only the entries for the lead

Agentleads.gif


Best Regards,
Sven

Hi @bryanfrumkin47,

 

Thanks for sharing the PDF. This is more or less clear to me, except the below parts.

1. Agent are directly picked from the Active Directory (AAD) and not from a separate list.

Is this correct?

 

2. If an Agent A is assigned to Region VA and DC. Where are you storing this information?

 

3. This list is shared with the agents and only the rows assigned to them are visible.

Is this correct?

 

4. You mentioned "if an agent is assigned to multiple, different, regions, it will display such (even if they are viewing only one of their leads)". 

So, if Agent A is assigned to Region VA and DC, and is accessing this list, and for Lead with Region VA the column "Agent's Assigned Regions" would display "DC"; and for Lead with Region DC the column would display "VA"

Is this what you are trying to achieve?

 

If what I assumed in the above point is correct, the suggestion will be as below:

Create additional list for Agents with 2 columns (Agent Name, Regions)

- Agent Name will be same as you have added in the Leads list.

- Regions can be a comma separated value (VA DC)

(If Regions are mapped in separate rows, we won't be able to concatenate multiple rows and display them together in a single cell)

 

In the Leads list instead of picking the Agent name from AD, pick it from the new list for Agents, and 

display both the columns. Refer the link in my previous post for how to achieve this.

Then we can use a calculated column and remove the region mapped to Lead from the list of Regions mapped to Agent.

 

For the PDF shared, you can delete it from the post as the details are partially visible since it was not completely blacked out. You can use rectangle shape tool with fill color to mask the areas properly.

 

-Nimesh

Thank you so much for your assistance, @SvenSieverding 

From excel, I am uploading data and one aspect of this data is the agent's profile; therefore, this solution isn't what I am looking for because when a user is selecting a lead they are looking to view information of the entire lead - not just agent region info.
Within the list, it shows the lead, the agent assigned, and which region the lead is assigned to, so if an agent is assigned to multiple, different, regions, it will display such  - in this new column (even if they are viewing only one of their leads). 

Hello @nimesht
Thank you so much for reaching back to me, truly.

The data is presented to me raw, quarterly, and the text is automatically case audited and uploaded through power automate into the list as needed
 1. Agent are directly picked from the Active Directory (AAD) and not from a separate list.

There email is given to me in excel and that is put into their person/group column

 

2. If an Agent A is assigned to Region VA and DC. 

In the excel sheet, they have a agent has a lead. The info is coming from the sheet?

 

3. This list is shared with the agents and only the rows assigned to them are visible.

No, the list is shared with different people but everyone should have the ability to view the whole list and all columns.


4. You mentioned "if an agent is assigned to multiple, different, regions, it will display such (even if they are viewing only one of their leads)". 

So, if Agent A is assigned to Region VA and DC, and is accessing this list, and for Lead with Region VA the column "Agent's Assigned Regions" would display "DC"; and for Lead with Region DC the column would display "VA"

Is this what you are trying to achieve? 
Yes, but the "Agent's Assigned Regions" column should display "DC, VA" for both rows; therefore, no matter which lead you are looking at for the agent - you will see the information.

 

In the data, each row is a lead with an agent assigned, and an assigned region. I want to be able to look at any row on the list and if I see that agent's name again I will know all the regions they have been assigned to.

Hi @bryanfrumkin47,

 

Does the Agent to Region mapping change frequently, or very rarely. I assume the regions might be assigned based on their base location and nearby regions so it might not change frequently.

 

If the region assignment keeps on changing; then should this column display latest regions assigned or the one which was mapped when the particular lead was assigned?

 

I believe the proposed solution of adding a separate list with 2 columns should work fine.

Suggest keeping the existing Agent column as is for now, add a new list with 2 columns (Agent, Regions) and then link these columns to the Lead List.

Once this is done, map the agents in the new columns as per the lead assignment.

 

Once you are confident with this and no issues from users, then you can hide/remove the current agent column.

 

-Nimesh

 

Hi @bryanfrumkin47,

 

Check the screenshots for the proposed solution.

Unfortunately we cannot use "Person or Group" for lookup purposes so a normal text column has been used, if it helps.

 

Agents List:

nimesht_0-1693029269025.png

Column information

 

nimesht_1-1693029335967.png

 

Leads List

 

nimesht_2-1693029420981.png

Leads List Columns

 

nimesht_3-1693029521731.png

 

Hello @nimesht,

"Does the Agent to Region mapping change frequently, or very rarely. I assume the regions might be assigned based on their base location and nearby regions so it might not change frequently.

If the region assignment keeps on changing; then should this column display latest regions assigned or the one which was mapped when the particular lead was assigned?"
Once the excel sheet data is uploaded, the leads' region will not change, so "one which was mapped when the particular lead was assigned"