2

Below is a working datatable for my SPO list called Companies. There are some duplicates in this list, but I want all of the duplicates removed. I have attempted the solutions on the DT site, but nothing seems to work.Below is a simple datatable with one calculated column (text) called calcCompanyRecent. Full script is below. Thanks in advance!


<!—Data tables CSS -->
          <link rel="stylesheet" href="https://cdn.datatables.net/1.10.11/css/jquery.dataTables.min.css"/> 
          <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.1.2/css/buttons.dataTables.min.css"/> 
          <link rel="stylesheet" href="https://cdn.datatables.net/select/1.1.2/css/select.dataTables.min.css"/>
          <link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.0.2/css/responsive.dataTables.min.css"/> 

<!—Data tables JS -->
            <script src="https://cdn.datatables.net/1.10.11/js/jquery.dataTables.min.js"></script>
            <script src="../../SiteAssets/solutions/datatable-editor/dataTables.altEditor.free.js"></script>
            <script src="https://cdn.datatables.net/buttons/1.1.2/js/dataTables.buttons.min.js"></script>
            <script src="https://cdn.datatables.net/select/1.1.2/js/dataTables.select.min.js"></script>
            <script src="https://cdn.datatables.net/responsive/2.0.2/js/dataTables.responsive.min.js"></script>

        <script type="text/javascript">

        // Datatable Script

        $(document).ready(function() {  
            loadMyItems();  
        });  

        function loadMyItems() {  
            var siteUrl = _spPageContextInfo.siteAbsoluteUrl;  
            var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('Companies')/items?$select=calcCompanyRecent,Modified&$orderby=Modified desc&$top=20";  
            $.ajax({  

                url: oDataUrl,  
                type: "GET",  
                dataType: "json",  
                headers: {  
                    "accept": "application/json;odata=verbose"  
                },  
                success: mySuccHandler,  
                error: myErrHandler  
            });  
        }  

        function mySuccHandler(data) {  
            try {  
                var dataTableExample = $('#table_id').DataTable();  
                if (dataTableExample != 'undefined') {  
                    dataTableExample.destroy();  
                }  
                dataTableExample = $('#table_id').DataTable({  
                    scrollY: 200,
                    responsive: true,  
                    "aaSorting": [[1,'desc']],  
                    "aaData": data.d.results,  
                        "iDisplayLength": 5,
                    "aoColumns": [
                    {     
                        "mData": "calcCompanyRecent"  
                    }]  
                });  
            } 
        catch (e) {  
                alert(e.message);  
            }  
        }  

        function myErrHandler(data, errCode, errMessage) {  
            alert("Error: " + errMessage);  
        }  

        // Invoke CompanyList NewForm Modal

        function openCoListModal() {
            SP.UI.ModalDialog.showModalDialog({
                width: 800,
                height: 500,
                        url: "/Lists/companylist/AddNew.aspx"
            });
        }
        </script>

        <script type="text/javascript">  

            // Using the DialogOptions class  
            function OpenDialog(strPageURL) {  
                var dialogOptions = SP.UI.$create_DialogOptions();  
                dialogOptions.url = strPageURL; // URL of the Page  
                // Width of the Dialog  
                dialogOptions.width = 800;  
                // Height of the Dialog  
                dialogOptions.height = 530;  
                // Function to capture dialog closed event  
                //dialogReturnValueCallback - A function pointer that specifies the return callback function. The function takes two parameters, a dialogResult of type SP.UI.DialogResult Enumeration and a returnValue of type object that contains any data returned by the dialog.  
                dialogOptions.dialogReturnValueCallback = Function.createDelegate(null, CloseCallback);  
                // Open the Dialog  
                SP.UI.ModalDialog.showModalDialog(dialogOptions);  
                return false;  
            }  

            // Dialog close event capture function  
            function CloseCallback(strReturnValue, target) {  
                location.reload(true);  
            }
          var columnDefs = [{
            title: "Title"
          }, {
            title: "calcCompanyRecent"
          }];

            var myTable;
          myTable = $('#table_id').DataTable({
            sPaginationType: 'none',
            data: 'data.d.results', 
                                        dom: 'Bfrtip',        // Needs button container
                  select: 'single',
                  responsive: 'true',

                  altEditor: 'true',     // Enable altEditor
                  buttons: [{
                    text: 'Add',
                    name: 'add'        // do not change name
                  },
                  {
                    extend: 'selected', // Bind to Selected row
                    text: 'Edit',
                    name: 'edit'        // do not change name
                  },
                  {
                    extend: 'selected', // Bind to Selected row
                    text: 'Delete',
                    name: 'delete'      // do not change name
                 }]
          });


        });
        </script>
  • 1
    Which column is your unique column which will be used to remove duplicates? – harshal gite Oct 24 '19 at 7:06
  • I don't have one. What kind of column does it need to be? – TheJDScott Oct 25 '19 at 16:06
  • Column from which you identify that this is a duplicate record. – harshal gite Oct 25 '19 at 18:26
  • Sorry, I was asking if it was a text column, calculated column, etc. Once it's created, how do I include it into a solution? – TheJDScott Oct 25 '19 at 19:15
  • Can you share schema of your list and how you are try identifying a duplicate entries? – ThinkB4Code Mar 3 at 22:38
0

So you have the results ordered by Modified but want also to remove duplicates. I can see that you are selecting two properties calcCompanyRecent and Modified so calcCompanyRecent could be used as a unique key to filter out duplicates. Update your function mySuccHandler with following code:

function mySuccHandler(data) {
    try {
        var dataTableExample = $('#table_id').DataTable();
        if (dataTableExample != 'undefined') {
            dataTableExample.destroy();
        }

        const uniqueCustomers = [...new Set(data.d.results.map(x => x.calcCompanyRecent))];
        const uniqueResults = uniqueCustomers.map(c => {
            return {
                calcCompanyRecent: c,
                Modified: array.find(y => y.calcCompanyRecent === c).Modified
            };
        });

        dataTableExample = $('#table_id').DataTable({
            scrollY: 200,
            responsive: true,
            "aaSorting": [[1, 'desc']],
            "aaData": uniqueResults,
            "iDisplayLength": 5,
            "aoColumns": [{
                "mData": "calcCompanyRecent"
            }]
        });
    }
    catch (e) {
        alert(e.message);
    }
}

As you can see it will create new variable called uniqueResults from fetched data which is then used in DataTable > aaData.

Reference
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Set

| improve this answer | |
  • Thanks for this, but when I tried it, it didn't work.I just swapped out your function (above) with mine in the original code. No dice. Any ideas? – TheJDScott Mar 9 at 19:16
  • @TheJDScott What browser are you using? Use F12 console to debug. – Christoffer Mar 10 at 10:02
  • @TheJDScott You are right that it was not what you needed. See updated answer. – Lukas Nespor Mar 10 at 11:16
  • Thanks, but it's still not working. How about we get rid of the 'Modified' column entirely and instead just have a single column 'calcCompanyRecent' be the ONLY column in this data table. Will that make thius easier to accomplish? – TheJDScott Mar 10 at 20:21
  • What is not working? Some error (F12>Console)? Or it just does not do what you expect? – Lukas Nespor Mar 10 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.