0

We have a column where the values are stored as '1002859817' and the Field Type is single line of text.

The end goal here is to filter the column based on the last 4 digits i.e. '9817'

Tried the options like substring and contains , However no results are returned

Below is the query:

var myQuery= "$select=QuoteNo";

        if(filterType!=="" && filterVal!==""){
            
            myQuery+="$&filter=substringof('"+filterVal+"','"+QuoteNo+"')";
        }   

        myQuery+="&$top=4999";
        myQuery+="&$orderby=ID desc";

Filter:

myQuery+="$&filter=substringof('"+filterVal+"','"+QuoteNo+"')";

Would appreciate if anyone could help me with this.

1

SharePoint REST API does not support endswith, Source.

If you want to filter using substringof, you can use your query like below:

<siteUrl>/_api/Web/Lists/getByTitle('ListName')/items?$select=QuoteNo&$filter=substringof('9817',QuoteNo)

No need to surround column name by ''.

Note: This endpoint will return all the items which contains 9817 anywhere in QuoteNo column and not only at the end.

If you want to filter the items by "endwith" (last 4 digits) condition, you have to do that client side using JavaScript after fetching all items from list. For this you can use endsWith() method of JavaScript for string comparison inside loop in success.


Update from comments:

Here's working example of passing column name & value to filter dynamically. You can refer this code & make changes in your code accordingly:

<script type="text/javascript" src="/sites/SPConnect/SiteAssets/jquery.min.js"></script>

<button type="button" onclick="getListItems('ChildList')"> Get List Items</button>

<script type="text/javascript">
    function getListItems(listName) {
        var columnName = "Title";
        var columnValue = "Child 1";

        var itemsEndpoint = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?$select=" + columnName + "&$filter=substringof('" + columnValue + "'," + columnName + ")";

        $.ajax({
            url: itemsEndpoint,
            type: "GET",
            headers: {
                "accept": "application/json;odata=verbose",
                "content-type": "application/json;odata=verbose"
            },
            success: function (data) {
                console.log(data);
            },
            error: function (error) {
                console.log(error);
            }
        });
    }
</script>
8
  • Are you getting any error? Remove '' around column name. Also make sure you are adding that column in $select. – Ganesh Sanap Mar 9 at 10:15
  • 1
    As I said in my answer, SharePoint REST API does not support endswith. and substringof is like "contains" search. It will return all items where QuoteNo contains 9817. It is not possible to add filter based on last 4 digits (ending with). – Ganesh Sanap Mar 9 at 11:33
  • 2
    In that case, first you have to fetch all items using REST API & then filter returned items based on your conditions/requirements. For this you can use endsWith() method of JavaScript for string comparison. – Ganesh Sanap Mar 9 at 11:36
  • 2
    @Vivek, Ganesh Sanap is right, you should use the substringof REST filter to just narrow down the results to ones that contain the number sequence you want, but then you are going to have to take those results and do additional filtering in JavaScript using endsWith() to get the ones that specifically end with the sequence you want. It's going to be a two-step process. – Dylan Cristy Mar 9 at 18:43
  • 1
    @GaneshSanap and Dylan Sure since I am getting the required values from substring , marking it as a solution :) Thanks for the help guys :) – Vivek Mar 10 at 8:19

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.