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

[orenji]

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.

[#2]

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

[#3 · Accepted 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

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 data

4) 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

[#4]

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.

[#5]

Thank you for the reply, I will look into doing that in Power BI as well in the future.