Forum Discussion
SharePoint List - Concatenate multiple rows
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).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
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.
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.
Reference: Introduction 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.
