The field 'EmpId' of type 'Calculated' cannot be used in the query sort expression.

Question

url: siteurl + "/_api/web/lists/getbytitle('EmpInfo')/items?$select=EmpName,Team,Plattform,EmpId&$orderby=EmpId&$filter= Status eq 'Active'",

is throwing exception "The field 'EmpId' of type 'Calculated' cannot be used in the query sort expression."

Any work around for this.

I need to sort my data according to this calculated column EmpId.

Answer 1

Hi

How do you calculate this field, try use original fields to sort.

---

Justin Liu Office Apps & Services MVP, MCSE
Senior Software Engineer
Please Vote and Mark as Answer if it helps you.

Answer 2

Hi Shristy,

SharePoint Rest API $filter and $orderby does not work with the calculated columns. This is the limitation:

Workaround is use CAML Query with GetItems in Rest API like this, suppose the "Status" is a Choice field and order by calculated column "EmpId", here is a code demo for your reference:

<script  src="https://code.jquery.com/jquery-3.2.1.min.js"></script>

<script type="text/javascript">
$(document).ready(function(){
  var caml="<View><Query><Where><Eq><FieldRef Name='Status' /><Value Type='Choice'>Active</Value></Eq></Where><OrderBy><FieldRef Name='EmpId' /></QrderBy></Query></View>";
  restCallwithCaml("TestList",caml);
 });
     function restCallwithCaml(listName, caml) {
             /// get the site url
             var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
             /// set request data     
             var data = { "query" : {"__metadata": 
             { "type": "SP.CamlQuery" }, "ViewXml":caml}};
             /// make an ajax call
             $.ajax({
                url: siteUrl+"/_api/web/lists/GetByTitle('"+ listName +"')/GetItems",
                method: "POST",
                data: JSON.stringify(data),
                headers: {
                   "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                   'content-type': 'application/json;odata=verbose',
                   'accept': 'application/json;odata=verbose'
                },
                success: function (response) {
                    ///do your code
                    console.log(response);
                },
                error: function (data) {
                  ///do your code
                }
            });
       }

</script>

Reference:

SharePoint REST API Limitation

Thanks

Best Regards

---

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [e-mail].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

Answer 3

My site is not working.I will update this isssue later.

thanks

Answer 4

Hi Shristy,

Please make sure the CAML is valid in your side and you could check if there is any error by Browser Developer Tool to see the details.

Thanks

Best Regards

---

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [e-mail].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

Answer 5

Hi Shristy,

Would you please provide an update of this question as the demo above is working in my side to order by calculated column in Rest API.

Thanks

Best Regards

---

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [e-mail].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.