SOLVED

Help with a list view that groups by unique title showing only latest date

Copper Contributor

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:

 

LeadDate of contact
DaveFeb. 28, 2023
MarkFeb. 26, 2023
JohnJan. 4, 2023
DaveDec. 8, 2022
DaveNov. 29, 2022
JohnSept. 11, 2022
Mark Jun 8, 2022

 

The list view should  come out as:

 

LeadDate of Contact
DaveFeb. 28, 2023
MarkFeb. 26, 2023
JohnJan. 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:

LeadDate of contact
DaveFeb. 28, 2023
DaveDec. 8, 2022
DaveNov. 29, 2022
JohnJan. 4, 2023
JohnSept. 11, 2022
Mark Feb. 26, 2023
Mark Jun 8, 2022

 

If I try to limit the total items to 1 it will just show:

LeadDate of contact
DaveFeb. 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.

4 Replies

@orenji 

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.

best response confirmed by orenji (Copper Contributor)
Solution

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

leads1.png


2) Create another List "LeadDateContact" and add a "Date of contact" date column. Now add a "Lookup" column
leads2.png
And create a Lookup back to the "Leads" list
leads3.png
3) Now Enter your data

leads4.png

4) Create a new view in the "LeadDateContact" List by saving the current view under a new name
leads5.png
and name it "Most recent Contact"

leads6.png

Now edit that view
leads7.png
Scroll down to "Sort" and sort that view by "Date of Contact" in descending order
leads8.png
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."
leads9.png

Scroll down to the "OK" button and click that.

5) Create a Site page.
Select "Gear"->"Add a page"
leads10.png
And name it "Last Contacts"

Add a two column layout and two "lists" webparts
leads 11.png
For the left webpart choose the list "Leads", for the right choose "LeadDateContact"

leads12.png

Select the little Pencil on the list webpart for "LeadDateContact" to edit the properties
leads13.png

Now set the view to "Most Recent Contacts"
leads14.png

And configure the "Dynamic Filtering" Option like this
leads15.png
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
leads16.png
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)

leads17.png
then the webpart on the right side is filtered to the most recent contact to Mark.

Best Regards,
Sven

Thanks 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.
Thank you for the reply, I will look into doing that in Power BI as well in the future.