locked
Query filter in jquery Rest API - multiple filter and distinct condition RRS feed

  • Question

  • Hi,

    I have a custom list in which I want to filter the data based on the below list column conditions:

    "Request_status" equal to "on-boarded" and "Resource email address" is not empty and is distinct.

    I have tried the below, but not sure how to add multiple filter and distinct condition:

    function processListItems() {
     $.ajax({
            var siteurl = _spPageContextInfo.webAbsoluteUrl;
        // Executing our items via an ajax request
        $.ajax({
    
            url: siteurl + "/_api/web/lists/getbytitle('ListName')/items?$filter=Resource_Status eq 'on-boarded' "

    How to achieve this?

    Thanks

    Tuesday, September 20, 2016 7:20 AM

Answers

  • Hi,

    Base on your requirement, you want to filter the data with the conditions: "Request_status" equal to "on-boarded" and "Resource email address" is not empty and is distinct.

    We can use CAML query in REST API to filter the data with "Request_status" equal to "on-boarded" and "Resource email address" is not empty. The following code for your reference:

    <script src="//code.jquery.com/jquery-3.1.0.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    $(function () {
     var siteURL = _spPageContextInfo.webAbsoluteUrl;
     var listname = "MyTest02";
     var url = siteURL + '/_api/web/lists/getbytitle(\'' + listname + '\')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><And><Eq><FieldRef Name=\'Request_status\' /><Value Type=\'Choice\'>on-boarded</Value></Eq><IsNotNull><FieldRef Name=\'Resource_x0020_email_x0020_addre\' /></IsNotNull></And></Where></Query></View>"}';
     $.ajax({
    	 url: url,
    	 method: "POST",
    	 headers: { 
    		"accept": "application/json;odata=verbose",
    		"content-type": "application/json;odata=verbose",
    		"X-RequestDigest": $("#__REQUESTDIGEST").val()
         }, 
    	 success: function (data) {	 	 
    		var items = data.d.results;		 
    	 },
    	 error: function (data) {
    	 }
     });
    });
    </script>

    If you want to get the distinct data, please check the thread below:

    http://stackoverflow.com/questions/25744763/get-distinct-data-of-sharepoint-list-using-rest-api

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Proposed as answer by Patrick_Liang Friday, September 30, 2016 8:56 AM
    • Marked as answer by Patrick_Liang Tuesday, October 11, 2016 7:46 AM
    Wednesday, September 21, 2016 3:27 AM

All replies

  • Hi VenkatZeus,

    Try this for multiple filter

    /_api/web/lists/getbytitle('Students')/items?$select=Title,ZIP&$filter=((ZIP eq 12345) or (ZIP eq 22222))

    For distinct

    function getListItems(url, listname, query, complete, failure) {
        $.ajax({
            url: url + "/_api/web/lists/getbytitle('" + listname + "')/items" + query,
            method: "GET",
            headers: { "Accept": "application/json; odata=verbose" },
            success: function (data) {
                complete(data.d); 
            },
            error: function (data) {
                failure(data);
            }
        });
    }
    getListItems('https://tenant.sharepoint.com/project','Tasks','?select=Title',
        function(items){    
           var taskNames = groupBy(items,'Title');
           console.log(taskNames);
        },
        function(error){
           console.log(JSON.stringify(error));
        }
    );
    function groupBy(items,propertyName)
    {
        var result = [];
        $.each(items, function(index, item) {
           if ($.inArray(item[propertyName], result)==-1) {
              result.push(item[propertyName]);
           }
        });
        return result;
    }

    Mark as answer if it helps!!


    Thanks Shekhar.diary

    Tuesday, September 20, 2016 7:35 AM
  • HI,

    Thanks for the reply.

    There are 2 getlistitems functions, which one to be used?

    Thanks

    Tuesday, September 20, 2016 8:22 AM
  • The other one is function call

    function getListItems(url, listname, query, complete, failure) {
        $.ajax({
            url: url + "/_api/web/lists/getbytitle('" + listname + "')/items" + query,
            method: "GET",
            headers: { "Accept": "application/json; odata=verbose" },
            success: function (data) {
                complete(data.d); 
            },
            error: function (data) {
                failure(data);
            }
        });
    }
    Then you could retrieve unique values from JSON array 

    function groupBy(items,propertyName)
    {
        var result = [];
        $.each(items, function(index, item) {
           if ($.inArray(item[propertyName], result)==-1) {
              result.push(item[propertyName]);
           }
        });
        return result;
    }
    the usage FUNCTION CALL

    getListItems('https://tenant.sharepoint.com/project','Tasks','?select=Title',
        function(items){    
           var taskNames = groupBy(items,'Title');
           console.log(taskNames);
        },
        function(error){
           console.log(JSON.stringify(error));
        }
    );


    Thanks Shekhar.diary

    Tuesday, September 20, 2016 11:22 AM
  • Hi,

    Base on your requirement, you want to filter the data with the conditions: "Request_status" equal to "on-boarded" and "Resource email address" is not empty and is distinct.

    We can use CAML query in REST API to filter the data with "Request_status" equal to "on-boarded" and "Resource email address" is not empty. The following code for your reference:

    <script src="//code.jquery.com/jquery-3.1.0.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    $(function () {
     var siteURL = _spPageContextInfo.webAbsoluteUrl;
     var listname = "MyTest02";
     var url = siteURL + '/_api/web/lists/getbytitle(\'' + listname + '\')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><And><Eq><FieldRef Name=\'Request_status\' /><Value Type=\'Choice\'>on-boarded</Value></Eq><IsNotNull><FieldRef Name=\'Resource_x0020_email_x0020_addre\' /></IsNotNull></And></Where></Query></View>"}';
     $.ajax({
    	 url: url,
    	 method: "POST",
    	 headers: { 
    		"accept": "application/json;odata=verbose",
    		"content-type": "application/json;odata=verbose",
    		"X-RequestDigest": $("#__REQUESTDIGEST").val()
         }, 
    	 success: function (data) {	 	 
    		var items = data.d.results;		 
    	 },
    	 error: function (data) {
    	 }
     });
    });
    </script>

    If you want to get the distinct data, please check the thread below:

    http://stackoverflow.com/questions/25744763/get-distinct-data-of-sharepoint-list-using-rest-api

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Proposed as answer by Patrick_Liang Friday, September 30, 2016 8:56 AM
    • Marked as answer by Patrick_Liang Tuesday, October 11, 2016 7:46 AM
    Wednesday, September 21, 2016 3:27 AM