none
Check duplicate items in the sharepoint list programmatically

    Question

  • Hi,

    I have a sharepoint list called "User". And there are items added in the list as below. As we know Admin >Contribute>Read

    System should identify the items with duplicate values in "Name" column and compare the "Access" value .Access value with highest one will be retained and rest of the items will be deleted from the list.

    e.g. Extract the rows with same name say Row-1 and 2 having same name Manoj. Their access values Read and Contribute.

    Remove the Row-1 because Row-1 access value is lower to row-2.How to achive this using sharepoint object model in C#.

    Item id

    Name

    Access

    Index

    1

    Manoj

    Read

    122

    2

    Manoj

    Contribute

    345

    3

    Saroj

    Admin

    565

    4

    Saroj

    Read

    787

    5

    Saroj

    Contribute

    111

    6

    Pankaj

    Read

    999

    7

    Rahul

    Admin

    126


    Knowledge is power.

    Thursday, July 11, 2013 12:22 PM

Answers

  • Hello,

    Try something like this: (Just wrote in notepad and have not tested it)

    <OrderBy> <FieldRef Name="Name" /> <FieldRef Name="Priority" Ascending="FALSE" /> </OrderBy>
    
    string duplicate = null;
    
    SPListItemCollection items = mylist.GetItems(query);
    
    if(items.Count > 0)
     {
      foreach(SPListItem item in items)
      {
       
      string title = Convert.Tostring(item["Name"]);  
    
       if(title == duplicate)
       {    
       duplicate = Convert.ToString(item["Name"]);  
       item.Delete();
       }
      
      }}
    Let us know your result


    Hemendra: "Yesterday is just a memory,Tomorrow we may never see"

    Whenever you see a reply and if you think is helpful, click "Alternate TextVote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Alternate TextMark As Answer

    Please feel free to unmark answer if does not resolves your problem.

    Thursday, July 11, 2013 10:11 AM

All replies

  • Hi,

    I have a requirement.

    I have a list called "Student" and the list has below items. Programmatically identify the duplicate values in "Name" field and keep only the items with highest values in "Priority" field and remove rest.

    e.g. Name with Manoj repeats 2 times. So keep only one row of Manoj whose highest priority is 35 and remove the other one

    Name

    Priority

    Index

    Subject

    Manoj

    35

    122

    Manoj

    30

    345

    Saroj

    20

    565

    Saroj

    25

    787

    Saroj

    10

    111

    Pankaj

    14

    999

    Rahul

    24

    126


    Knowledge is power.

    Thursday, July 11, 2013 7:59 AM
  • Hi,

    In your CAML Query, use the "Group By" on you "Name" column. Use the parameter row limit and don't forget to sort by "Priority" Descending. Keep this items only.

    Regards.


    Gilles Martinez
    Please mark as helpful/answer if this resolved your post

    Thursday, July 11, 2013 8:59 AM
  • Hello,

    You query should be like this:

      <GroupBy> 
        <FieldRef Name='Name' /> 
      </GroupBy>
        <OrderBy>
                <FieldRef Name="Priority" Ascending="FALSE" />
        </OrderBy>
    
    http://nikspatel.wordpress.com/2010/11/09/programmatically-configure-the-order-by-and-group-by-properties-of-the-sharepoint-lists-and-document-libraries-views/

    Hope it could help


    Hemendra: "Yesterday is just a memory,Tomorrow we may never see"

    Whenever you see a reply and if you think is helpful, click "Alternate TextVote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Alternate TextMark As Answer

    Please feel free to unmark answer if does not resolves your problem.

    Thursday, July 11, 2013 9:10 AM
  • Hi,

    I need to traverse thru list item programatically and check the duplicate names and keep only the name with higest priority and rest to be removed.

    I dont think so ur logic suits my requirement.


    Knowledge is power.

    Thursday, July 11, 2013 9:13 AM
  • Hi,

    I need to traverse thru list item programatically and check the duplicate names and keep only the name with higest priority and rest to be removed.

    I dont think so ur logic suits my requirement.

    Ur logic tells that to make grou by and order by simply. but my requirement is to identify the rows with duplicate Name and keep higest priority name and remove rest of the Name rows with lowest priority.


    Knowledge is power.

    Thursday, July 11, 2013 9:18 AM
  • Primarily list is:

    Name

    Priority

    Index

    Subject

    Manoj

    35

    122

    Manoj

    30

    345

    Saroj

    20

    565

    Saroj

    25

    787

    Saroj

    10

    111

    Pankaj

    14

    999

    Rahul

    24

    126

    And, After executing the code the final List should look like below.

    Name

    Priority

    Index

    Subject

    Manoj

    35

    122

    Saroj

    25

    787

    Pankaj

    14

    999

    Rahul

    24

    126


    Knowledge is power.

    Thursday, July 11, 2013 9:21 AM
  • By CAML Query, it seems difficult to select the MAX "Index"  value by "Name". You should to compare your collection items not in CAML query but with a C# code ( SPListItemCollection ).

    Maybe you could add a custom action to achieve this goal.

    Regards

     


    Gilles Martinez
    Please mark as helpful/answer if this resolved your post

    Thursday, July 11, 2013 9:43 AM
  • I still prefer use LINQ to do something like this.

    You can write:

    var items = myweb.Lists["my list"].Items.Cast < SPListItem > ( )
    .Select(s => new {
       // properties that u want
       Name = s["Name"].ToString ( )
    });
    
    // then ...
    
    var duplicates = items
        .GroupBy(i => i.Name)
        .Where(g => g.Count() > 1)
        .Select(g => g.Name);
    
    foreach (var d in duplicates)
        Console.WriteLine(d);


    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful" salvatoredifaziosharepoint.blogspot.com

    Twitter: @Salvodif
    MVP SharePoint Server

    • Proposed as answer by Ratikanta07 Friday, January 24, 2014 1:53 PM
    Thursday, July 11, 2013 10:02 AM
  • Hello,

    Try something like this: (Just wrote in notepad and have not tested it)

    <OrderBy> <FieldRef Name="Name" /> <FieldRef Name="Priority" Ascending="FALSE" /> </OrderBy>
    
    string duplicate = null;
    
    SPListItemCollection items = mylist.GetItems(query);
    
    if(items.Count > 0)
     {
      foreach(SPListItem item in items)
      {
       
      string title = Convert.Tostring(item["Name"]);  
    
       if(title == duplicate)
       {    
       duplicate = Convert.ToString(item["Name"]);  
       item.Delete();
       }
      
      }}
    Let us know your result


    Hemendra: "Yesterday is just a memory,Tomorrow we may never see"

    Whenever you see a reply and if you think is helpful, click "Alternate TextVote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Alternate TextMark As Answer

    Please feel free to unmark answer if does not resolves your problem.

    Thursday, July 11, 2013 10:11 AM
  • Your steps would be as follows:

    1. Create a custom class that contains properties for ItemId, Name and Access and creat a list<> to the custom class.
    2. Obtain a reference to the SPSite
    3. Obtain a reference to the SPList
    4. Itterate the items in the list thus:  foreach (SPListItem itm in SPList.Items) { }
    5. Within the foreach loop, check the custom class to see if it already contains the Name.  If it does not, then add the current item to the list thus:  if (!CustomClassList.Contains(SPListItem["Name"].ToString()) { CustomClass = new CustomClass(); CustomClass.Id = SPListItem["Id"]; CustomClass.Name = SPListItem["Name"].ToString(); CustomClass.Access = SPListItem["Access"].ToString(); CustomClassList.Add(CustomClass); }
    6. If the list already contains the record, check the access level and determine which should be removed, adding that item ID to ToBeDeleted list.
    7. After a run through the list, use the ToBeDeleted list to delete the items from the original list that are duplicates with lower permissions.


    I trust that answers your question...

    Thanks
    C

    http://blog.cjvandyk.com | LinkedIn | Facebook | Twitter | Quix Utilities for SharePoint | SharePoint Admin Nav Console (SPANC)

    Thursday, July 11, 2013 1:46 PM
  • Hi,

    Your solution is just  a general solution but could you please provide me specific logic for my requirement ?

    After executing the solution from above original list, result should come as below.

    Item id

    Name

    Access

    Index

    2

    Manoj

    Contribute

    345

    3

    Saroj

    Admin

    565

    6

    Pankaj

    Read

    999

    7

    Rahul

    Admin

    126


    Knowledge is power.

    Friday, July 12, 2013 4:50 AM
  • DO NOT start duplicate thread.

    Did you try my last post solution?


    Hemendra: "Yesterday is just a memory,Tomorrow we may never see"

    Whenever you see a reply and if you think is helpful, click "Alternate TextVote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Alternate TextMark As Answer

    Please feel free to unmark answer if does not resolves your problem.

    Friday, July 12, 2013 8:46 AM
  • Hi Hemendra,

    Your last thread is not helping my requirement.

    Please go thru my requirement clearly then reply me.

    Your solution will be a great help.

    Thanks


    Knowledge is power.

    Friday, July 12, 2013 9:06 AM
  • Manoj if I look at all the replies, you got something like 3 or 4 good point where u can start. So don't except to have a program written by us.

    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful" salvatoredifaziosharepoint.blogspot.com

    Twitter: @Salvodif
    MVP SharePoint Server

    Friday, July 12, 2013 9:10 AM
  • >Please go thru my requirement clearly then reply me.

    As i understand, you want to keep only high priority data in list and want to delete duplicate? Is my understanding correct? I have provided sample code in my last post to loop through all the rows using CAML query then delete duplicate from the list.

    As per my query you should get data like:

    Name

    Priority

    Index

    Subject

    Manoj

    35

    122

    Manoj

    30

    345

    Saroj

    25

    787

    Saroj

    20

    565

    Saroj

    10

    111

    Pankaj

    14

    999

    Rahul

    24

    126

    Now when loop will execute then first row will not pass through IF condition but second duplicate row will go inside IF and delete that duplicate row from list.

    As i said earlier this is not actual code, you may need to modify some of part.

    Always respect other responses and their hard work, who are trying to help you.


    Hemendra: "Yesterday is just a memory,Tomorrow we may never see"

    Whenever you see a reply and if you think is helpful, click "Alternate TextVote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Alternate TextMark As Answer

    Please feel free to unmark answer if does not resolves your problem.

    • Proposed as answer by balaji_rcs Friday, July 12, 2013 10:38 AM
    Friday, July 12, 2013 9:21 AM
  • Hello Manoj,

    Finally, it seems that our solution worked for you.


    Hemendra: "Yesterday is just a memory,Tomorrow we may never see"

    Whenever you see a reply and if you think is helpful, click "Alternate TextVote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Alternate TextMark As Answer

    Please feel free to unmark answer if does not resolves your problem.

    Friday, July 12, 2013 11:48 AM