SP List web api REST query Issue

Shri-6058 326 Reputation points
2020-11-24T19:56:02.78+00:00

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

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,346 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,716 questions
SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,612 questions
0 comments No comments
{count} votes

Accepted answer
  1. Baker Kong-MSFT 3,801 Reputation points
    2020-11-25T07:05:34.39+00:00

    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.

    42452-image.png

    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:

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Shri-6058 326 Reputation points
    2020-11-28T17:59:36.767+00:00

    Thank you so much. That's working perfectly.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.