Rest API Filter condition

Question

I have two columns :

1- ManagerEmail- This contains the manager emails 

2- Status - This columns contains Pending , Approved , Rejected.

Requirement : Want to fetch only those manager's emails whose status value pending count is more then 25 )

As i have to send mail  those manager whose pending  status exceed 25 request. I have mail code but i am confuse in Rest API End Point query for this senario

---

Shiv Sharma

Answer 1

I guess I would first return a list of all of the manager IDs, who have a Pending Email count > 25. 

I would then loop through that dataset, and build a filter with the IDs of the managers, and use that to return a 2nd dataset of all of the emails.

(There are slicker ways to do it in actual query engine languages, like SQL. But, in this day and age of half=baked technologies, like OData, you often have to break things down into smaller pieces.) 

Answer 2

Hi SharePoint User02,

There is no OOB functionality that could count the subset of items in Rest API. You may take a reference of below workarounds:

1. Filter items with Status  then count the items in a loop
2. Use enderListData method 

Here i attached a demo for method 2: 

var caml=`<View><Query><Where><Eq><FieldRef Name="Status" /><Value Type="Choice">Pending</Value></Eq></Where><GroupBy Collapse="TRUE" GroupLimit="30"><FieldRef Name="ManagerEmail"/></GroupBy></Query><Aggregations Value="On"><FieldRef Name="ManagerEmail" Type="Count"/></Aggregations></View>`;

    $.ajax({
        url: `${_spPageContextInfo.webAbsoluteUrl}/_api/web/Lists/getbytitle('f')/renderlistdata(@viewXml)?@viewXml='${caml}'`,
        type: "POST",
        headers: {
            "Accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val()
        },
        success: function (response) {
            console.log(response);
        },
        error: function (e, r) {
            console.log(r);
        }
    });

I tested this code with below list: 

Output:

{d: {,…}}
d: {,…}
RenderListData: "{ "Row" : ↵[{
↵"ManagerEmail": "[e-mail]",
↵"ManagerEmail.urlencoded": "%3B%23m01%40testemail%2Ecom%3B%23",
↵"ManagerEmail.COUNT.group": "4",
↵"ManagerEmail.newgroup": "1",
↵"ManagerEmail.groupindex": "1_",
↵"ManagerEmail.Count": "8",
↵"ManagerEmail.Count.agg": "4"
↵}
↵,{
↵"ManagerEmail": "[e-mail]",
↵"ManagerEmail.urlencoded": "%3B%23m02%40testemail%2Ecom%3B%23",
↵"ManagerEmail.COUNT.group": "3",
↵"ManagerEmail.newgroup": "1",
↵"ManagerEmail.groupindex": "2_",
↵"ManagerEmail.Count": "8",
↵"ManagerEmail.Count.agg": "3"
↵}
↵,{
↵"ManagerEmail": "[e-mail]",
↵"ManagerEmail.urlencoded": "%3B%23m03%40test%2Ecom%3B%23",
↵"ManagerEmail.COUNT.group": "1",
↵"ManagerEmail.newgroup": "1",
↵"ManagerEmail.groupindex": "3_",
↵"ManagerEmail.Count": "8",
↵"ManagerEmail.Count.agg": "1"
↵}
↵],"FirstRow" : 1,
↵"LastRow" : 3
↵,"FilterLink" : "?"↵,"ForceNoHierarchy" : "1"↵,"HierarchyHasIndention" : ""↵↵}"

It will group the items by "ManagerEmail" and the result contains Count of each group. 

More reference: 

• https://piyushksingh.com/2016/11/21/retrieve-grouped-listitems-sharepoint/

This “SharePoint Server 2013” Forum will be migrating to a new home on Microsoft Q&A, please refer to this sticky post for more details.

Best Regards,

Baker Kong

---

"SharePoint" forums will be migrating to a new home on Microsoft Q&A !
We invite you to post new questions in the "SharePoint" forums' new home on Microsoft Q&A !

Answer 3

Hi SharePoint User02,

We have not heard from you for a long time. Is above info helpful to the issue? If it has been resolved, it's appreciated that you can mark it as Answer so that others who stuck in similar issue could get answered quickly.

And we'd like to notify you that This forum will be locked down, if you still have issues about this question, you can continue to post in here before 8/10/2020. It is recommended to create a new question on Microsoft Q&A, we will continue to support you on the new home.

Best Regards,

Baker Kong

---

"SharePoint" forums will be migrating to a new home on Microsoft Q&A !
We invite you to post new questions in the "SharePoint" forums' new home on Microsoft Q&A !