locked
Expand USER field in REST + CAML RRS feed

  • Question

  • Dear Experts,

    I am getting multiple items from a list called CheckList. I am getting only Items where IsDeleted is false. 

    I am getting these items using CAML Query in REST API ajax call. However, I am getting Author ID/ User ID instead of Author Title and User Title. I need them both and I don't want to have another call to get Author/User details.

    The following is the CAML query with AJAX call. I need to know how to expand the Author field in CAML query. 

    <View Scope='RecursiveAll'><Query><Where><And><Eq><FieldRef Name='User' /><Value Type='Integer'><UserID /></Value></Eq><Eq><FieldRef Name='IsDeleted' /><Value Type='Boolean'>0</Value></Eq></And></Where><ViewFields><FieldRef Name='LinkTitle' /><FieldRef Name='CheckList' /><FieldRef Name='CheckList_x003a_Title' /><FieldRef Name='CheckList_x003a_Phase' /><FieldRef Name='CheckList_x003a_ID' /><FieldRef Name='User'/><FieldRef Name='ID' /><FieldRef Name='IsDeleted' /></ViewFields></Query></View>

                    

    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems";
            var queryPayload = {
                'query': {
                    '__metadata': { 'type': 'SP.CamlQuery' },
                    'ViewXml': viewXml

                }
            };

            return $.ajax({
                url: url,
                method: "POST",
                data: JSON.stringify(queryPayload),
                headers: {
                    "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                    "Accept": "application/json; odata=verbose",
                    "content-type": "application/json; odata=verbose"
                }
            });

    Thanks in Advance,

    Pavan Paruchuru.



    Thursday, August 16, 2018 6:35 AM

All replies

  • Hi,

    Expand operation won't work in CAML Query and Rest API /GETItems() endpoint due to this explanation:

    OData $expand not working – SharePoint REST API

    A workaround is to use Rest API /items endpoint with filter condition instead of CAML Query and use $expand to get User Title:

    http://sp/sites/jerrydev/_api/web/lists/getbytitle('TestList')/Items?$select=User/Title,IsDeleted&$filter=User eq 1 and IsDeleted eq 0&$expand=User/Id

    And here is the code call by Jquery ajax for your reference:

    <script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script type="text/javascript">
    var UserId=_spPageContextInfo.userId;
    $.ajax({ 
       url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('TestList')/Items?$select=User/Title,IsDeleted&$filter=User eq '"+UserId+"' and IsDeleted eq 0&$expand=User/Id", 
       type: "GET", 
       headers: {"accept": "application/json;odata=verbose"}, 
       success: function (data) { 
          if (data.d.results) { 
             // TODO: handle the data  
             alert('handle the data'); 
             console.log(data.d.results);
          } 
       }, 
       error: function (xhr) { 
          alert(xhr.status + ': ' + xhr.statusText); 
       } 
    });     
    </script>
    

    UserId is the current user id due to your CAML and filtered with isDeleted equal to false, this is the returned response data:

    Reference:

    SharePoint 2013: Get User Details from Person or Group field using 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 tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, August 17, 2018 9:09 AM
  • Hi,

     

    I am checking to see how things are going there on this issue. Please let us know if you would like further assistance.

     

    You can mark the helpful post as answer to help other community members find the helpful information quickly.

     

    Thanks

     

    Best Regards


    Monday, August 27, 2018 9:48 AM