Deleting SharePoint List elements with specific values in a column

Question

I'm a bit stuck right now. Using JavaScript I wrote a function that deletes an element from a list with a specific ID:

deleteItem: function() {
  let siteUrl = 'mysite';
  let clientContext = new SP.ClientContext(siteUrl);
  let oList = clientContext.get_web().get_lists().getByTitle('Statistics');
  let oListItem = oList.getItemById(parseInt(this.statisticsRecID));
  oListItem.deleteObject();
  clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded1), Function.createDelegate(this, this.onQueryFailed1));
},

The problem I have is that now I need to delete elements from a list where I don't know the ID of the element. (I can look it up, but that's not what I want). This list has a column called CorrespondingListID. And I have the values of that CorrespondingListID.

Is it possible to delete elements from that list using their value which is written in the column CorrespondingListID? If not, how can one solve this problem?

Answer 1

You can use the camel query to get the list item based on your column value which will load only those list items which meet that criteria, then do the delete operation as usual way.

Sample Example: Using JSOM

To filter SP.ListItem's on multiple conditions, there are no single methods that take the arguments as multiple filter fields. Instead, you'll have to resort to using a CAML query to specify the list items you want, as below.

var clientContext = new SP.ClientContext( siteURL );
spList = clientContext.get_web().get_lists().getByTitle( myListName );

//Create a CAML-query with your filter conditions
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml('<View><Query><Where><And><Eq><FieldRef Name=\'Age\'/>' + 
    '<Value Type=\'Number\'>30</Value></Eq>
    <Eq><FieldRef Name=\'Country\'/>' + 
    '<Value Type=\'Text\'>US</Value></Eq></And></Where></Query><RowLimit>10</RowLimit></View>');

//The query will return a collection of items matching your conditions
this.collListItem = spList.getItems(camlQuery);

clientContext.load(collListItem);

//Execute the query
clientContext.executeQueryAsync(function () {

    var itemCount = collListItem.get_count();
    //For each list item in the collection, mark it to be deleted
    for (var i = itemCount - 1; i >= 0; i--) {
        var oListItem = collListItem.itemAt(i);
        oListItem.deleteObject();
    };

    //Execute the delete operation
    clientContext.executeQueryAsync(deleteSucceeded, deleteFailed);
}, fail_handler);  

Note:

In your case, the set_viewXml should be something like below, if you get syntax error use camel query builder tool.

camlQuery.set_viewXml('<View><Query><Where><Eq><FieldRef Name=\'CorrespondingListID\'/>' + 
    '<Value Type=\'Text\'>somevaluefrom CorrespondingListID</Value></Eq></Where></Query></View>');

Sample Example: using REST API

function getItemsToDelete () {
    //You might have to modify this so it filters correctly on your columns
    var requestUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle(" + myListName + ")/items?$filter=Age eq 30 and Country eq 'US'")

    //Return and ajax request (promise)
    return $.ajax({
        url: requestUrl,
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function(result) {
            $.each(result.d.results, function(index, item){
                //Note that we push the ajax-request to the array
                //that has been declared a bit down
                itemsToDelete.push(deleteItem(item));
            });            
        },
        error: function(error) {
            //Something went wrong when retrieving the list items
        }
    });    
}

function deleteItem (item) {
    //All SP.ListItems holds metadata that can be accessed in the '__metadata' attribute
    var requestUrl = item.__metadata.uri;

    return $.ajax({
        url: requestUrl,
        type: "POST",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "IF-MATCH": item.__metadata.etag,
            "X-HTTP-Method": "DELETE"
        },
        success: function() {
            console.log("Item with ID " + item.__metadata.id + " successfully deleted!");
        },
        error: function(error) {
            //Something went wrong when trying to delete the item
        }
    });    
}


//Declare an array of deferred objects that hold a delete request
//for each item that is to be deleted
var itemsToDelete = [];

//First get the items to delete
$.when(getItemsToDelete()).then(function () {
    $.when.apply($, itemsToDelete).then(function(){
        console.log("All items are deleted!");
    });
});

A similar question is discussed here(source):

SharePoint 2013 ClientContext : How to DELETE specific list items by MULTIPLE CONDITION filter?

Answer 2

Sample Example: using REST API and ES6

I wanted to provide an example that doesn't use jQuery.

The answer is longer than it strictly needs to be. However, if you are doing this a lot, the helpers and utility functions, with some light error handling, come in handy.

The code below assumes you have JSON Light enabled in your SharePoint Environment. (Supported in 2013 via SP1 and add some assembly references to the web.config. JSON Light is present in 2016, 2019, SPO. Not supported in 2010)

const myListName = "Your-List-Name-Here";
const getObj = {
    method: "GET",
    // INFO passes along the windows credentials, adjust to suit your environment
    credentials: "include",
    headers: {
        Accept: "application/json;odata=nometadata",
        "Content-Type": "application/json;odata=nometadata",
    },
};
const delObj = {
    method: "POST",
    // INFO passes along the windows credentials, adjust to suit your environment
    credentials: "include",
    headers: {
        Accept: "application/json;odata=nometadata",
        "Content-Type": "application/json;odata=nometadata",
        "X-RequestDigest": "",
        "IF-MATCH": "*",
        "X-HTTP-Method": "DELETE"
    },
};
const listUrl = 
`${_spPageContextInfo.webAbsoluteUrl}/_api/web/lists/getbytitle('${myListName}')`;

// INFO Main Function to call.
function deleteListItems () {
    /* INFO You will need to modify this so it filters correctly on your columns
    Returns just the Id values, and limits results to the top newest 5000 items.
    5000 items is the max and is part of the built-in throttle limit. 
    You can get the next 5000 items without too much hassle. */
    const url = 
    `${listUrl}/Items?$select=Id&$orderby=Id desc&$top=5000&$filter=(Age eq 30) and (Country eq 'US')`;
    const result = fetchWrapper(url, getObj);
    // INFO all the items' ID's that match the filter
    // INFO 'async' is only needed it freshness function used.
    result.then(async (items) => {
        /* INFO Use a freshness function to avoid errors on pages that
           a user might sit at longer than 24 minutes */
        const digest = await testIfDigestFresh();
        // INFO or just get it from the page
        // const digest = document.getElementById("__REQUESTDIGEST").value;
        // INFO create an array of urls for each item Id to delete
        const urlArr = items.value.map(item => {
            return `${listUrl}/Items(${item.Id})`;
        });
        // INFO inject the SharePoint Request Digest value, will fail otherwise.
        delObj.headers["X-RequestDigest"] = digest;
        /* INFO Process all the items to delete as an asynchronous 
           promise wrapped group */
        const itemsDeleted = Promise.all(urlArr.map(url=>fetchWrapper(url, delObj)));
        // INFO Do any post processing or messages.
        itemsDeleted.then(data => {
            /* INFO SharePoint only responds with just 200, no body.
                    Counting how many array items are return from the
                    promise will provide a total count.
            */
            console.log("Deleted:", data.length);
            alert(`'${data.length}' Items Deleted`);
        })
        .catch(err => {
            alert('Request failed.', err);
        });
    });
}

// UTIL Functions
    /* INFO Abstracts fetch, handle non-json responses, add custom error messages, 
       default settings */
    async function fetchWrapper(url, options, custom_err_msg) {
        if (!url) {
            throw new Error("url is missing");
        }
        let opt;
        if (options) {
            opt = options;
        } else {
            opt = {
                method: "GET",
                mode: "no-cors",
                headers: {
                    "Content-Type": "application/json",
                    Accept: "application/json",
                },
            };
        }
        return fetch(url, opt)
            .then(async (resp) => {
                if (!resp.ok) {
                    throw new Error(resp.status);
                }
                // HANDLE not json
                try {
                    let json = await resp.json();
                    return json;
                } catch (error) {
                    console.log("catch error, not json, try text", error);
                    return resp.text();
                }
            })
            .then((data) => {
                return data;
            })
            .catch((err) => {
                // HANDLE errors and custom error messages, must be a string
                if (custom_err_msg && typeof custom_err_msg === "string") {
                    console.error(`${custom_err_msg}`, err);
                    return `${custom_err_msg}\n${err.statusText}`;
                } else {
                    if (err.toString().substr(7, err.length) == 404) {
                        return "Not found";
                    } else {
                        return err;
                    }
                }
            });
    }
// OPTIONAL Helper Functions
    function msToMinAndSecs(ms) {
        const minutes = Math.floor(ms / 60000);
        // INFO turn on if seconds are needed
        // const seconds = ((ms % 60000) / 1000).toFixed(0);
        return minutes; // + ":" + (ms < 10 ? '0' : '') + seconds;
    }

    async function testIfDigestFresh() {
        let isFresh;
        const lastLoaded = new Date(window.parent.g_updateFormDigestPageLoaded);
        const now = new Date();
        const compared = now - lastLoaded;
        const diff = msToMinAndSecs(compared);
        if (diff >= 24) {
            isFresh = await getDigest();
        } else {
            isFresh = window.parent.document.getElementById("__REQUESTDIGEST").value;
            console.log("digest still fresh");
        }
        return isFresh;
    }

    async function getDigest() {
        const url = `${_spPageContextInfo.webAbsoluteUrl}/_api/contextinfo`;
        /* INFO Didn't use fetchWrapper so function wouldn't depend on the wrapper
           to work. */
        return fetch(url, {
            method: "POST",
            credentials: "include",
            headers: {
                "Content-Type": "application/json; odata=nometadata",
                Accept: "application/json; odata=nometadata",
            },
        })
        .then((r) => r.json())
        .then((json) => {
            let digest = json.FormDigestValue;
            const digestTime = new Date(digest.split(",")[1]);
            // INFO Update the digest on the SharePoint Page
            window.parent.document.getElementById("__REQUESTDIGEST").value = digest;
            // INFO Update the time since last digest received to fresh date.
            window.parent.g_updateFormDigestPageLoaded = digestTime;
            return true;
        })
        .catch((err) => {
            console.error("Error from GetDigest():\n", err);
            return false;
        });
    }