Forum Discussion
orenji
Mar 03, 2023Copper Contributor
Help with a list view that groups by unique title showing only latest date
I'm new to sharepoint lists, and I have a sharepoint list of leads and the last time they were contacted. I want to create a view that shows only the last time each lead was contacted.
For Example:
Lead | Date of contact |
Dave | Feb. 28, 2023 |
Mark | Feb. 26, 2023 |
John | Jan. 4, 2023 |
Dave | Dec. 8, 2022 |
Dave | Nov. 29, 2022 |
John | Sept. 11, 2022 |
Mark | Jun 8, 2022 |
The list view should come out as:
Lead | Date of Contact |
Dave | Feb. 28, 2023 |
Mark | Feb. 26, 2023 |
John | Jan. 4, 2023 |
I was trying to create one using edit view and sorting by date descending and grouping by lead and then adding an Item limit of 1 by display by batch, however what ends up happening is they just get grouped together:
Lead | Date of contact |
Dave | Feb. 28, 2023 |
Dave | Dec. 8, 2022 |
Dave | Nov. 29, 2022 |
John | Jan. 4, 2023 |
John | Sept. 11, 2022 |
Mark | Feb. 26, 2023 |
Mark | Jun 8, 2022 |
If I try to limit the total items to 1 it will just show:
Lead | Date of contact |
Dave | Feb. 28, 2023 |
I'm at loss on how to navigate this properly or if this is at all possible to do, I appreciate any comment to point me in the right direction.
Hi orenji,
this does not really work with one list alone.
WIth two lists this would work like this:
1) Create a List just for the leads
2) Create another List "LeadDateContact" and add a "Date of contact" date column. Now add a "Lookup" column
And create a Lookup back to the "Leads" list
3) Now Enter your data4) Create a new view in the "LeadDateContact" List by saving the current view under a new name
and name it "Most recent Contact"Now edit that view
Scroll down to "Sort" and sort that view by "Date of Contact" in descending order
Scroll down to "Item Limit" and expand that. Now set the number of items to display to 1 and toggle "Limit the total number of items returned to the specified amount."Scroll down to the "OK" button and click that.
5) Create a Site page.
Select "Gear"->"Add a page"
And name it "Last Contacts"
Add a two column layout and two "lists" webparts
For the left webpart choose the list "Leads", for the right choose "LeadDateContact"
Select the little Pencil on the list webpart for "LeadDateContact" to edit the properties
Now set the view to "Most Recent Contacts"
And configure the "Dynamic Filtering" Option like this
This basically means that we want dynamically filter this webpart if we select an entry in from the "Leads" webpart. And then we want exactly the items where "Lead.Title"="LeadDateContact.Lead"
Click "Apply" and then "Publish" to save this page
6) Try it
Now we have this page
It shows us the most recent contact with a lead anyone had on the right side.
But if we now select a Lead from the left side (i.e. Mark)
then the webpart on the right side is filtered to the most recent contact to Mark.
Best Regards,
Sven
- SvenSieverdingBronze Contributor
Hi orenji,
this does not really work with one list alone.
WIth two lists this would work like this:
1) Create a List just for the leads
2) Create another List "LeadDateContact" and add a "Date of contact" date column. Now add a "Lookup" column
And create a Lookup back to the "Leads" list
3) Now Enter your data4) Create a new view in the "LeadDateContact" List by saving the current view under a new name
and name it "Most recent Contact"Now edit that view
Scroll down to "Sort" and sort that view by "Date of Contact" in descending order
Scroll down to "Item Limit" and expand that. Now set the number of items to display to 1 and toggle "Limit the total number of items returned to the specified amount."Scroll down to the "OK" button and click that.
5) Create a Site page.
Select "Gear"->"Add a page"
And name it "Last Contacts"
Add a two column layout and two "lists" webparts
For the left webpart choose the list "Leads", for the right choose "LeadDateContact"
Select the little Pencil on the list webpart for "LeadDateContact" to edit the properties
Now set the view to "Most Recent Contacts"
And configure the "Dynamic Filtering" Option like this
This basically means that we want dynamically filter this webpart if we select an entry in from the "Leads" webpart. And then we want exactly the items where "Lead.Title"="LeadDateContact.Lead"
Click "Apply" and then "Publish" to save this page
6) Try it
Now we have this page
It shows us the most recent contact with a lead anyone had on the right side.
But if we now select a Lead from the left side (i.e. Mark)
then the webpart on the right side is filtered to the most recent contact to Mark.
Best Regards,
Sven- orenjiCopper ContributorThanks so much for the feedback, it took a while for me to circle back as I've been busy with other projects. I appreciate the guide and explanations.
- iamsarahBrass Contributor
out of the box is not possible to show the distinct values in a view because the SharePoint views are based on CAML query and CAML does not support of retrieving distinct values.
If you're comfortable with programming, you can do it through coding.
I personally would rather to just group by based on Lead and sort descending based on Date.
But if it's a must to do for example for reporting purposes you can easily do it in Power BI and once the report is ready embed it in a page in your SharePoint site.
If my response helped you, please Like and Mark as Best Response.
- orenjiCopper ContributorThank you for the reply, I will look into doing that in Power BI as well in the future.