SP List web api REST query Issue

Question

Hello, I have a web API query "http://intranet.spportal.com/_api/web/Lists/GetByTitle('EmpList')/Items?$select=EmpNumber,EmpName,Email,ManagerId&$top=5000"
It displays very correctly all 5000 records. Each record has "ManagerId" field. I am trying to filter manager records. Hence I want to filter the query listed above as
If EmpNumber is present in "ManagerId" field of any/all records of 5000, include record in the REST query. If EmpNumber not present in ManagerId field, exclude from the query result. Also since ManagerId will be one or many reportees depending on the how many reportees, need to capture only unique records.

Something simillar:
&$filter=(if EmpNumber in ManagerId array) and unique

Please note empNumber, EmpName, Email, ManagerId displays every single records of the list. ManagerId will have each manager's EmpNumber. So all managerId exist in EmpNumber but not all empNumber is in ManagerId as all employees are not managers. I want to filter only managers

Something simillar to VLOOKUP and return true of all items.

Please advise.

Thanks
Shri

Answer 1

Hi @Shri-6058 ，

I understand EmpNumber stand for the id of employees (including general employees and managers), while ManagerId is the "EmpNumber" of managers. Now you want to query rows those "EmpNumber" in fact stand for a manager.

In my opinion, it's not able to achieve it in one step. First we have to get all manager records and remove all duplicated elements.

For example, we get all managerid: /items?$select= ManagerId, the result will be an array.

Remove duplicates using JS code:

response.d.results.reduce((accumulator, current) => accumulator.find(item => item.ManagerId === current.ManagerId) ? accumulator : accumulator.push(current) && accumulator, []);

After that, we can generate a caml query with In element to get rows that their "EmpNumber" fall in the above array.

let optionValues = filteredArray.map(e => `<Value Type='Text'>${e.ManagerId}</Value>`).join('');  
    var caml = `<View><Query><Where><In><FieldRef Name='EmpNumber' /><Values>${optionValues}</Values></In></Where></Query></View>`;  
  
    var data = {  
        "query": {  
            "__metadata": { "type": "SP.CamlQuery" },  
            "ViewXml": caml  
        }  
    };  

You can check the full demo code here:

• https://github.com/kongmengfei/sharedproject/blob/master/jssharing/autocomplete/caml-in,js

Note that my test columns are 'single line text' fields.

Best Regards,
Baker Kong

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you so much. That's working perfectly.