Forum Discussion

orenji's avatar
orenji
Copper Contributor
Mar 03, 2023

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:

 

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.

  • 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

  • SvenSieverding's avatar
    SvenSieverding
    Bronze 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 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

    • orenji's avatar
      orenji
      Copper Contributor
      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.
  • iamsarah's avatar
    iamsarah
    Brass Contributor

    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.

    • orenji's avatar
      orenji
      Copper Contributor
      Thank you for the reply, I will look into doing that in Power BI as well in the future.

Resources