none
How to filter Calculate column value using REST RRS feed

  • Question

  • Hello 

    1. I have Binding list data to Data table dynamically using REST. I was created Month and Year calculate  columns for Meeting date column.. My requirement is When End user select the month or Year from Drop down list it should  be display only selected value data to data table.

    2.How to filter calculate   column value in REST, I have written but it showing error see the below script

    var url = _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('ProductList')/items?$select=ID,Title,Description,Quantity,Price,City&$filter=Month eq'"+ monthval +"'";  

    2. I have binding Month values to drop down but i need to remove the duplicate values. 

    

    Final Code here

    <script type="text/javascript"  src="//ajax.aspnetcdn.com/ajax/4.0/1/MicrosoftAjax.js"></script>
    <script type="text/javascript"  src="/_layouts/15/sp.runtime.js"> </script>
    <script type="text/javascript"  src="/_layouts/15/sp.js"> </script>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js"></script>
    <script type="text/javascript"  src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
    
    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.1.min.js"></script>
    
    <style type="text/css">
    .tablecss{
        border: 1px solid black;
        table-layout: fixed;
        width: 200px;
    }
    
    .tablecss, th, td {
        border: 0px solid black;
        width: 100px;
        padding: 10px 15px;
      vertical-align: middle;
    }
    
    thead {
      background: #395870;
      color: #fff;
    }
    
    </style>
    <div id="Meetingdates">
    
    </div>
    
     <div style="background-color:#F8F8FF; padding:5px; display:table;">
      
      
      
        <table ID="GetItems" class="tablecss" cellspacing="0" cellpadding="0" style="margin:5px;">  
                <thead>  
                    <tr>  
                        <td>Id</td>  
                        <td>Title</td>  
                        <td>Description</td>  
                        <td>Quantity</td>  
                        <td>Price</td>  
                        <td>City</td>  
                    </tr>  
                </thead>  
                <tbody>  
                   
                </tbody>  
                </table>  
                
                
        </div>
        
        
        <script type="text/javascript">   
        
        
        
    /*$(document).ready(function () {
    
    MeetingDatedropdown();
    
    });*/
    
    $(document).ready(function () {
    
    MeetingDatedropdown();
    GetListItems();
    
    /*
        $('#Meetingdates').change(function (event) {
            GetListItems();
            event.preventDefault();
        });
        GetListItems();*/
    });
    
    
    function MeetingDatedropdown() {
     
    var listName = "ProductList";
    
    var url = _spPageContextInfo.webAbsoluteUrl;
    
    getListItems(listName, url, function (data) {
    
    var items = data.d.results;
    
     
    
    var inputElement = '<select id=  "Meetingdates"> <option  value="">Select</option>';
    
    // Add all the new items
    
    for (var i = 0; i < items.length; i++) {
    
    //var itemId = items[i].Meeting.split('T')[0];
    var itemId = items[i].Month;
    
    //var itemVal = items[i].Meeting;
    
    inputElement += '<option value="' + itemId + '"selected>' + itemId + '</option>';
    
     
    
    }
    
    inputElement += '</select>';
    
    $('#Meetingdates').append(inputElement);
    
     
    
    $("#Meetingdates").each(function () {
    
    $('option', this).each(function () {
    
     
    
    if ($(this).text() == 'Select') {
    
      $(this).attr('selected', 'selected')
     
    };
    
    });
    
    }); 
    
    // assign the change event to provide an alert of the selected option value
    
    
    $('#Meetingdates').change(function(){
    //alert($('option:selected',this).val())
     var monthval= $('option:selected',this).val();
      //alert(monthval);
      
       GetListItems(monthval);
    });
    /* .change()
    $("#Meetingdates").on('change',function() {
    
    var text = $("#Meetingdatesoption:selected").text();
    var value = $("#Meetingdatesoption:selected").val();
    $("#Meetingdates").val(value);
    
    });
    */
     
    
    }, function (data) {
    
    alert("Ooops, an error occured. Please try again");
    
    });
    
    
    
    }
    
    
    function getListItems(listName, siteurl, success, failure) {
    
    $.ajax({
    
    url: siteurl + "/_api/web/lists/getbytitle('" + listName + "')/items?$orderby=Title asc",
    
    method: "GET",
    
    headers: { "Accept": "application/json; odata=verbose" },
    
    success: function (data) {
    
    success(data);
    
    },
    
    error: function (data) {
    
    failure(data);
    
    }
    
    });
    
    }
    
    function GetListItems(monthval)     
    {  
    
        var row = "";  
        var url = _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('ProductList')/items?$select=ID,Title,Description,Quantity,Price,City&$filter=Month eq'"+ monthval +"'";  
        $.ajax  
        ({  
            url: url,  
            type: "GET",  
            headers:  
            {  
                "Accept": "application/json;odata=verbose",  
                "Content-Type": "application/json;odata=verbose",  
            },  
            success: function(data)   
            {  
                var result = data.d.results;  
                $.each(result, function(key, item)  
                {  
                    var l = item.Title;  
                    row = row + '<tr><td>' + item.Id + '</td><td>' + item.Title + '</td><td>' + item.Description +  
                        '</td><td>' + item.Quantity + '</td><td>' + item.Price +  
                        '</td><td>' + item.City + '</td></tr>';  
                })  
      
                $("#GetItems>tbody").html(row);  
                //$("#GetItems").show();  
            },  
            error: function(data)   
            {  
                alert("Failed to get list items.");  
            }  
        });  
      
    }  
    
    </script>
    


    • Edited by ps41 Friday, March 17, 2017 6:54 AM
    Friday, March 17, 2017 6:53 AM

All replies

  • Hi,

    We can't use filter or orderby on Calculated fields in REST method in SharePoint 2013 as it is also not supported in query filter expression.

    Thanks.

    Friday, March 17, 2017 7:13 AM
  • Thanks for your replay,

    If any another option is there for filtering meeting date  

    Friday, March 17, 2017 7:58 AM
  • Hi,

    You can use CAML query to filter list items by month something like below.

    function getListItems(webUrl, listTitle, query) {
        var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems";
        var queryProp = {
            'query': {
                '__metadata': {
                    'type': 'SP.CamlQuery'
                },
                'ViewXml': query
            }
        };
    
        return $.ajax({
            url: url,
            method: "POST",
            data: JSON.stringify(queryProp),
            headers: {
                "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                "Accept": "application/json; odata=verbose",
                "content-type": "application/json; odata=verbose"
            }
        });
    }
    
    // How to use
    
    var monthKey = "March";
    var query = String.format('<View><Query><Where><Eq><FieldRef Name="Month" /><Value Type="Number">{0}</Value></Eq></Where></Query></View>', monthKey);
    getListItems(_spPageContextInfo.webAbsoluteUrl, 'Contacts', query)
        .done(function(data) {
            var items = data.d.results;
            for (var i = 0; i < items.length; i++) {
                console.log(items[i].Title);
                // add your logic here
            }
        })
        .fail(function(error) {
            console.log(JSON.stringify(error));
        });

    Please mark it as an answer if it helps.

    Regards.

    Friday, March 17, 2017 11:02 AM
  • Hello,

    Thanks to all for your help.

    I wrote the below code now it was working fine

    <script type="text/javascript"  src="//ajax.aspnetcdn.com/ajax/4.0/1/MicrosoftAjax.js"></script>
    <script type="text/javascript"  src="/_layouts/15/sp.runtime.js"> </script>
    <script type="text/javascript"  src="/_layouts/15/sp.js"> </script>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js"></script>
    <script type="text/javascript"  src="/sites/DevSite/SiteAssets/Common.js"> </script>
    <script type="text/javascript"  src="/sites/DevSite/SiteAssets/SPAPI_Core.js"> </script>
    <script type="text/javascript"  src="/sites/DevSite/SiteAssets/SPAPI_Lists.js"> </script>
    <script type="text/javascript"  src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
    
    
    
    <div>
       
    <style type="text/css">
           
           #tblAllItems {
          border-spacing: 10;
           width: 100%;
           }
      th, td {
        border: 1px solid #ccc;
        padding: 10px;
        text-align: left;
         }
         
          td:nth-child(2) { background-color: yellow; } 
          td:nth-child(3) { background-color: yellow; } 
          td:nth-child(5) { background-color: yellow; }
          td:nth-child(4) { background-color: yellow; }
          td:nth-child(6) { background-color: yellow; }
          td:nth-child(1) { background-color: yellow; }
          
      
    
    
         </style>
    
    
    
    <script type="text/javascript">
      var yearArray=[];
      var monthArray=[];
        $(document).ready(function () {               
            getAllListItems(); 
               
        });
      
      
        function getAllListItems() {         
          //debugger;
             item2Show = 110;
          
            var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('DashBoardList')/items?select=id,Sub_Department,Ontime__x0020_delivery,Quality,Meetingdate,Month&$Top=" + item2Show + " ";
            var requestHeaders = { "accept": "application/json;odata=verbose" };
    
            $.ajax({
                url: requestUri,
                contentType: "application/json;odata=verbose",
                headers: requestHeaders,
                success: onSuccess,
                error: onError
            });
        }
    
        function onSuccess(data, request) {
            riskTable = "";
            var myLog = data.d;
    
            for (var resultType in myLog.results) { 
    		yearArray.push(myLog.results[resultType].Year);
    		monthArray.push(myLog.results[resultType].Month);
                
                riskTable = "<tr><td><span>" + myLog.results[resultType].Sub_Department  + "<span></td>";
                
                riskTable = riskTable + "<td><span>" + myLog.results[resultType].Meetingdate .split('T')[0] + "<span></td>";
                var status =  myLog.results[resultType].Ontime__x0020_delivery;
               
      				 switch(status)
      				 {
      				 case 'Not ok':
               
                   riskTable = riskTable + "<td  style='background-color:Red;'><span>" +  myLog.results[resultType].Ontime__x0020_delivery + "<span></td>";
                   break;
    
                  
                  case 'Ok':
                  riskTable = riskTable + "<td  style='background-color:Green;'><span>" +  myLog.results[resultType].Ontime__x0020_delivery + "<span></td>";
                  break;
    
                  
                  case 'Improve' :
                  
                 riskTable = riskTable + "<td style='background-color:goldenrod;'><span>" +  myLog.results[resultType].Ontime__x0020_delivery + "<span></td>";
                  break;
    
      				 }
      				 
      				 
      				 
               
                var Quality_Value =  myLog.results[resultType].Quality;
                switch(Quality_Value)
                {
                case 'Not ok':
               
                   riskTable = riskTable + "<td style='background-color:Red;'><span>" +  myLog.results[resultType].Quality + "<span></td>";
                   break;
    
                  
                  case 'Ok':
                  riskTable = riskTable + "<td style='background-color:Green;'><span>" +  myLog.results[resultType].Quality + "<span></td>";
                  break;
    
                  
                  case 'Improve' :
                  
                  riskTable = riskTable + "<td style='background-color:goldenrod;'><span>" +  myLog.results[resultType].Quality + "<span></td>";
                  break;
    
                }
               
        
                $('#tblAllItems').append(riskTable);
    			
    			
            }
            
    		var uniqueYear = yearArray.filter(function(itm,i,a){
    			return i == yearArray.indexOf(itm);
    			});
    		
    		var uniqueMonth = monthArray.filter(function(itm,i,a){
    			return i == monthArray.indexOf(itm);
    			});
    		//alert(uniqueMonth);
    		
    		for(var i=0;i<uniqueYear.length;i++)
    		{
    		$("#years").append("<option>"+uniqueYear[i]+"</option>");
    		}
    		for(var j=0;j<uniqueMonth.length;j++)
    		{
    		$("#month").append("<option>"+uniqueMonth[j]+"</option>");
    		}
    
        }
    
        function onError(error) {
            alert("error");
        }
        
        
        
        
    
    // Filter values based on Drop down selected values
    
    
    function getFilterValues() {         
          debugger;
           var selectmonth=$("#month").val();
           var selectyear=$("#years").val();
           
          
    	  riskTable='';
    	  $('#tblAllItems').empty();
    	  riskTable ="<tr ><th align='Left'>Sub_Department </th><th align='Left'>Meetingdate </th> <th align='Left'>Quality </th><th align='Left'>Ontime_delivery</th></tr>";
    	  var listName="DashBoardList";
    		 $('#tblAllItems').append(riskTable);
    
    var query="<Query><Where><And><Eq><FieldRef Name='Year'/><Value Type='Calculated'>"+$("#years").val()+"</Value></Eq><Eq><FieldRef Name='Month'/><Value Type='Calculated'>"+$("#month").val()+"</Value></Eq></And></Where></Query>";
    	
    
     listItems = getCommonListData('',1000,listName,query, '','','','','')  
     
    //var listItems = getCommonListData('',1000,listName,query, '','','','','');
    		
    if(listItems != null && listItems.length > 0)
    		
    {
    	    	
    $(listItems).each(function () 
    	    	
    {
    	    	  
    //alert(this.getAttribute('ows_Title'));
    //riskTable =  "<tr><td><li><span><a class='tt-Anchor'  target=_blank href=" + _spPageContextInfo.webAbsoluteUrl + "/_layouts/15/listform.aspx?PageType=4&ListID=B1074C07B-EB5A-44E2-B63C-48955BC00C0C&ID=" + this.getAttribute('ows_ID') + "&source=" + _spPageContextInfo.webAbsoluteUrl + ">" +this.getAttribute('ows_ID') + "</a></span></li></td>";
    
                 riskTable = "<tr><td><span>" + this.getAttribute('ows_Sub_Department')  + "<span></td>";
    			riskTable = riskTable + "<td><span>" + this.getAttribute('ows_Meetingdate').split(' ')[0] + "<span></td>";
                  var status1 = this.getAttribute('ows_Quality');
    			  if(status1=='Not ok')
    			  {
    			  riskTable = riskTable + "<td style='background-color:Red; class='Yescell'><span>" + this.getAttribute('ows_Quality') + "<span></td>";
    			  }
    			 else if(status1=='Ok')
    			 {
    			 riskTable = riskTable + "<td style='background-color:Green; class='Nocell'><span>" +  this.getAttribute('ows_Quality') + "<span></td>";
    			 }
    			 else if(status1=='Improve')
    			{
    			riskTable = riskTable + "<td style='background-color:goldenrod; class='Nocell'><span>" + this.getAttribute('ows_Quality') + "<span></td>";
    			}
    
    			//
    
    			var status2 = this.getAttribute('ows_Ontime__x0020_delivery');
    			if(status2=='Not ok')
    			{
    			riskTable = riskTable + "<td style='background-color:Red;  class='Yescell'><span>" +  this.getAttribute('ows_Ontime__x0020_delivery') + "<span></td>";
    			}
    			else if(status2=='Ok')
    			{
    			riskTable = riskTable + "<td style='background-color:Green; class='Nocell'><span>" + this.getAttribute('ows_Ontime__x0020_delivery') + "<span></td>";
    			}
    			else if(status2=='Improve')
    			{
    			riskTable = riskTable + "<td style='background-color:goldenrod; class='Nocell'><span>" + this.getAttribute('ows_Ontime__x0020_delivery') + "<span></td>";
    			}
                
                $('#tblAllItems').append(riskTable);
                
                
    	    	
    });
    }
    	    
    
    
    }
    
        
        
    </script>
    <div>
    Year:<select id= "years"> <option value="">Select</option></select>
    Month:<select id= "month"> <option value="">Select</option></select>
    <input type="button" onclick="getFilterValues()" value="Filter">
    </div>
    <table id='tblAllItems'>
           
           <tr >
               
               <th align="Left">Sub_Department </th>    
               <th align="Left">Meetingdate </th>
               <th align="Left">Ontime_delivery</th> 
               <th align="Left">Quality</th>          
           </tr>
           
    </table>     
    </div>
    

     
    Thursday, March 23, 2017 5:43 AM
  • Hi,

    Thanks for your sharing, you could mark your solution as answer, so it may help other community members find the helpful information quickly.

    Best Regards,

    Lee


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

    Thursday, March 30, 2017 10:21 AM