none
CAML INNER Join in JavaScript RRS feed

  • Question

  • Can't figure out why this simple INNER join bombs. Please review and comment. (See Data Structure below, as needed.)

    	function getAssignmentsByYear(year){
    		//The expected use of this will be that the entire set of data for the year, and then the month slider will be used to pick the month, 
    		//and that will be resolved via the use of ajax
    		//
    		//year = year of data to return
    		//Returns: ajax dataset with select data from year input
    		return new Promise(function(resolve, reject) {
    
    			//Default to current year, in case nothing is passed
    			if(year == null){year=moment().year}
    
    			//fromDate = moment()
    			var fromDate = moment().set('year',year).set('month',0).set('date',1).format("YYYY-MM-DDT00\:00\:01")+"Z";//set fromDate to first day of selected year
    			var toDate   = moment().set('year',year).set('month',11).set('date',31).format("YYYY-MM-DDT23\:59\:59")+"Z";//set toDate to last day of selected month
    
    			var CAMLJoins = 
    				"<Joins>"+
    				"<Join Type='INNER' ListAlias='Person'>"+
    				"<Eq>"+
    				"<FieldRef Name='Person' RefType='Id'/>" +
    				"<FieldRef List='Person' Name='ID'/>" +
    				"</Eq>"+
    				"</Join></Joins>";
    				
    			var CAMLProjFlds = 
    				"<ProjectedFields>"+
    				"<Field Name='LastName'  Type='Lookup' List='Person' Showfield='LastName' />"+
    				"<Field Name='FirstName' Type='Lookup' List='Person' Showfield='FirstName' />"+
    				"</ProjectedFields>";	
    			
    			var CAML = 
    				"<View><ViewFields><FieldRef Name='ID' /><FieldRef Name='Person' /><FieldRef Name='LastName' /><FieldRef Name='FirstName' /><FieldRef Name='Role' /><FieldRef Name='StartDate' /><FieldRef Name='EndDate' /></ViewFields>" +	
    				"<Query><Where>"+
    				"<Or><And><IsNull><FieldRef Name='EndDate' /></IsNull><Leq><FieldRef Name='StartDate' /><Value Type='DateTime'>" + toDate + "</Value></Leq></And>" +
    				"<Or><And><Gt><FieldRef Name='EndDate' /><Value Type='DateTime'>" + toDate + "</Value></Gt>" +
    				"<Leq><FieldRef Name='StartDate' /><Value Type='DateTime'>" + toDate + "</Value></Leq></And>" +
    				"<And><Geq><FieldRef Name='EndDate' /><Value Type='DateTime'>" + fromDate + "</Value></Geq>" +
    				"<Leq><FieldRef Name='EndDate' /><Value Type='DateTime'>" + toDate + "</Value></Leq></And></Or></Or>"+
    				"</Where></Query></View>"
    		
    			useRESTwithCAML("PersonAssignments", CAMLJoins + CAMLProjFlds + CAML).then(		
    				function(response){			
    					resolve(response);
    					}
    				, 
    				function(error) {
    					reject(error);
    				 	}
    				); //.then(
    			}); //Promise
    		}; //getAssignmentsByYear(year){

    I have verified that the CAML in the variable CAML works alone, but when I include CAMLJoins and CAMLProjFlds, then the result is just the base GetItems (which is the whole list.)

    Data Structure

    Person (Parent List)
    -------
    ID
    LastName
    FirstName

    PersonAssignments (Child List)
    ---------------------
    ID
    Person (Lookup to Person.ID)
    StartDate
    EndDate
    other fields...


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA


    Thursday, August 17, 2017 7:41 PM

Answers

  • Hi,

    The Showfield should be ShowField.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, August 23, 2017 11:52 AM
  • Hi,

    Seems this is a known issue based on my test and research(lookup fields won’t show).

    Here is one similar thread for you reference.

    https://social.msdn.microsoft.com/Forums/office/en-US/eb404a9c-89dd-4c55-9edc-34da283182c3/api-getitems-viewxml-caml-query-not-returning-file-name-and-lookup-field-values?forum=sharepointdevelopment

    As a workaround, use JSOM instead.

    Sample Code:

    <script src="https://cdnjs.cloudflare.com/ajax/libs/bluebird/3.3.5/bluebird.min.js"></script>
        <script src="/_layouts/15/js/jquery.min.js" type="text/javascript"></script>
        <script type="text/javascript">
            function useRESTwithCAML(listName, CAML) {           
                var clientContext = new SP.ClientContext.get_current();
                var oList = clientContext.get_web().get_lists().getByTitle(listName);
    
                var camlQuery = new SP.CamlQuery();
                camlQuery.set_viewXml(CAML);
                this.collListItem = oList.getItems(camlQuery);
    
                clientContext.load(collListItem);
                clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
    
            };
    
            var CAMLJoins =
                   "<Joins>" +
                   "<Join Type='INNER' ListAlias='Person'>" +
                   "<Eq>" +
                   "<FieldRef Name='Person' RefType='Id'/>" +
                   "<FieldRef List='Person' Name='ID'/>" +
                   "</Eq>" +
                   "</Join></Joins>";
    
            var CAMLProjFlds =
                "<ProjectedFields>" +
                "<Field Name='DLastName' Type='Lookup' List='Person' ShowField='LastName'/>" +
                "<Field Name='DFirstName' Type='Lookup' List='Person' ShowField='FirstName'/>" +
                "</ProjectedFields>";
    
            var ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>A</Value></Eq></Where></Query><ViewFields><FieldRef Name='Title'/><FieldRef Name='DLastName'/><FieldRef Name='DFirstName'/></ViewFields>" + CAMLProjFlds + CAMLJoins + "<RowLimit>100</RowLimit></View>";
    
            function onQuerySucceeded(sender, args) {
    
                var listItemInfo = '';
    
                var listItemEnumerator = collListItem.getEnumerator();
    
                while (listItemEnumerator.moveNext()) {
                    var oListItem = listItemEnumerator.get_current();
                    listItemInfo += '\nID: ' + oListItem.get_id() +
                        '\nTitle: ' + oListItem.get_item('Title') +
                        '\nDLastName: ' + oListItem.get_item('DLastName') +
                        '\nDLastName: ' + oListItem.get_item('DLastName');
                }
    
                alert(listItemInfo.toString());
            }
    
            function onQueryFailed(sender, args) {
    
                alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
            }
        </script>
    
        <input id="Button1" onclick="useRESTwithCAML('PersonAssignments', ViewXml)" type="button" value="testCAMLJoin" />


    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, August 30, 2017 2:13 AM

All replies

  • Hi,

    Seems your query not build correctly, the join and ProjectedFields should be inside CAML view.

    Here is my test code(CSOM, while the query should be same) for your reference:

    List list = clientContext.Web.Lists.GetByTitle("PersonAssignments");
                    CamlQuery query = new CamlQuery();
                    var CAMLJoins =
                    "<Joins>" +
                    "<Join Type='INNER' ListAlias='Person'>" +
                    "<Eq>" +
                    "<FieldRef Name='Person' RefType='Id'/>" +
                    "<FieldRef List='Person' Name='ID'/>" +
                    "</Eq>" +
                    "</Join></Joins>";
    
                    var CAMLProjFlds =
                        "<ProjectedFields>" +
                        "<Field Name='DLastName' Type='Lookup' List='Person' ShowField='LastName'/>" +
                        "<Field Name='DFirstName' Type='Lookup' List='Person' ShowField='FirstName'/>" +
                        "</ProjectedFields>";
                    
                    query.ViewXml = string.Format("<View><Query></Query><ViewFields><FieldRef Name='DLastName'/><FieldRef Name='DFirstName'/></ViewFields>{0}{1}<RowLimit>100</RowLimit></View>", CAMLProjFlds, CAMLJoins);
                    var items = list.GetItems(query);
                    clientContext.Load(items);
                    clientContext.ExecuteQuery();
                    Console.WriteLine(items.Count); 

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, August 18, 2017 4:06 AM
  • Sorry for the two deletes, but it took me a couple tries to really figure out what you had done. 

    For the casual passerby, the answer is that he inserted the <ProjectedFields> and <Joins> in a different spot than I had in my code above.

    I'm not fond of the notation used {0}{1}, because it's undocumented and difficult to read. With that, I tried to implement a more straightforward version of it, and came up with this:

    var CAML = "<View>" "<ViewFields><FieldRef Name='ID' /><FieldRef Name='Person' /><FieldRef Name='LastName' /><FieldRef Name='FirstName' /><FieldRef Name='Role' /><FieldRef Name='Affiliation' />"+ "<FieldRef Name='CostCode' /><FieldRef Name='StartDate' /><FieldRef Name='EndDate' /><FieldRef Name='Ext' /><FieldRef Name='Section' /></ViewFields>" + + CAMLProjFlds + CAMLJoins + "</View>";

    So, I've done more of a manual insertion of the code, rather than use the {0}{1} parameters like Lee did. With that, I get an error of:

       400, Bad Request, Value does not fall within the expected range. 

    I assume that I have more syntax out of whack, but I can't find it.


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA




    Tuesday, August 22, 2017 8:19 PM
  • Hi,

    The Showfield should be ShowField, sorry for not mentioning this.

     

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, August 23, 2017 1:27 AM
  • Hi,

    The Showfield should be ShowField.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, August 23, 2017 11:52 AM
  • Ok, I see now. I made that update, and the error does not occur, BUT the ProjectedField, now pjfLastName, does not show up in the result set.

    It appears that the entire CAML statement is now ignored, and just the GetItems is being used.

    I posted the code for useRESTwithCAML() above (in a wider window)


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA




    Wednesday, August 23, 2017 1:08 PM
  • Putting the function here, so that it's in a wider window.

    function useRESTwithCAML(listName, CAML){
    		return new Promise(function(resolve, reject) {
    debugger;
    			var uri="http://2012baseserver/OrgChart/_api/web/Lists/GetByTitle('" + listName + "')/GetItems";		
    			var reqData = 
    				{"query" : 
    					{"__metadata": 
    						{ "type": "SP.CamlQuery" }
    						, "ViewXml": CAML
    					}
    				}; //reqData
    			 jQuery.ajax({
    				url: uri,
    				method: "POST",
    				data: JSON.stringify(reqData),
    				success: function(response){ resolve(response) },
    				error: function(xhr, ajaxOptions, thrownError){ reject(xhr.status + ': ' + thrownError + '(' + JSON.stringify(xhr.responseText) + ')' )},
    				headers: {
    					"X-RequestDigest": $("#__REQUESTDIGEST").val(),
    					"Accept": "application/json; odata=verbose",
    					"Content-type": "application/json; odata=verbose"
    					}	
    				}); //jQuery.ajax({
    			}); //Promise 	
    		}; //useRESTwithCAML(listName, CAML){


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Wednesday, August 23, 2017 1:32 PM
  • Hi,

    Could you create a console application(CSOM) to test your CAML? It would be easy to debug.

    Here is my sample code for your reference.

    using (var clientContext = new ClientContext("http://sp:12001/"))
                {
                    #region MyRegion
                    List list = clientContext.Web.Lists.GetByTitle("PersonAssignments");
                    CamlQuery query = new CamlQuery();
                    var CAMLJoins =
                    "<Joins>" +
                    "<Join Type='INNER' ListAlias='Person'>" +
                    "<Eq>" +
                    "<FieldRef Name='Person' RefType='Id'/>" +
                    "<FieldRef List='Person' Name='ID'/>" +
                    "</Eq>" +
                    "</Join></Joins>";
    
                    var CAMLProjFlds =
                        "<ProjectedFields>" +
                        "<Field Name='DLastName' Type='Lookup' List='Person' ShowField='LastName'/>" +
                        "<Field Name='DFirstName' Type='Lookup' List='Person' ShowField='FirstName'/>" +
                        "</ProjectedFields>";
                    
                    query.ViewXml = string.Format("<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>A</Value></Eq></Where></Query><ViewFields><FieldRef Name='DLastName'/><FieldRef Name='DFirstName'/></ViewFields>{0}{1}<RowLimit>100</RowLimit></View>", CAMLProjFlds, CAMLJoins);
                    var items = list.GetItems(query);
                    clientContext.Load(items);
                    clientContext.ExecuteQuery();
                    Console.WriteLine(items.Count);
                    #endregion
    
    
                    Console.ReadKey();
    
                }

    My test data:


    Result:


     

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, August 24, 2017 2:30 AM
  • Please update your example to use my function useRESTwithCAML() above.

    I completely believe that your example works, but since you're using it in a "known quantity" situation, I don't think that we're comparing apples to apples. I'm guessing that I have kumquats or rutabagas, which SharePoint doesn't really know how to cook nor digest.

    I believe I have found the edge of the envelope, but hoping that you can prove me wrong.


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Thursday, August 24, 2017 2:40 PM
  • Hi,

    Here is my test result:


    Code (jQuery v1.6.1):

    <script src="https://cdnjs.cloudflare.com/ajax/libs/bluebird/3.3.5/bluebird.min.js"></script>
        <script src="/_layouts/15/js/jquery.min.js" type="text/javascript"></script>
        <script type="text/javascript">
            function useRESTwithCAML(listName, CAML) {
                return new Promise(function (resolve, reject) {
                    debugger;
                    var uri = "http://sp:12001/_api/web/Lists/GetByTitle('" + listName + "')/GetItems";
                    var reqData =
                        {
                            "query":
                               {
                                   "__metadata":
                                      { "type": "SP.CamlQuery" }
                                   , "ViewXml": CAML
                               }
                        }; //reqData
                    jQuery.ajax({
                        url: uri,
                        type: "POST",
                        data: JSON.stringify(reqData),
                        success: function (response) {
                            resolve(response)
                        },
                        error: function (xhr, ajaxOptions, thrownError) {
                            reject(xhr.status + ': ' + thrownError + '(' + JSON.stringify(xhr.responseText) + ')')
                        },
                        headers: {
                            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                            "Accept": "application/json; odata=verbose",
                            "Content-type": "application/json; odata=verbose"
                        }
                    }); //jQuery.ajax({
                }); //Promise 	
            };
    
            var CAMLJoins =
                   "<Joins>" +
                   "<Join Type='INNER' ListAlias='Person'>" +
                   "<Eq>" +
                   "<FieldRef Name='Person' RefType='Id'/>" +
                   "<FieldRef List='Person' Name='ID'/>" +
                   "</Eq>" +
                   "</Join></Joins>";
    
            var CAMLProjFlds =
                "<ProjectedFields>" +
                "<Field Name='DLastName' Type='Lookup' List='Person' ShowField='LastName'/>" +
                "<Field Name='DFirstName' Type='Lookup' List='Person' ShowField='FirstName'/>" +
                "</ProjectedFields>";
    
            var ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>A</Value></Eq></Where></Query><ViewFields><FieldRef Name='DLastName'/><FieldRef Name='DFirstName'/></ViewFields>" + CAMLProjFlds + CAMLJoins + "<RowLimit>100</RowLimit></View>";
    
            
        </script>
    
        <input id="Button1" onclick="useRESTwithCAML('PersonAssignments', ViewXml)" type="button" value="testCAMLJoin" />

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Friday, August 25, 2017 4:23 AM
  • In the 2nd pic, the Watch window doesn't reflect having the field names of DLastName and DFirstName for response.d.results[0].

    Shouldn't they be part of the results? (This is the same problem that I'm having.)


    Steve Clark, MCTS
    Easy Bins Roll-off Dumpster Rentals in Northern VA
    Specializing in:
    Driveway-sized, roll-off dumpster rentals in Fairfax VA | Dumpster Rentals for Junk Hauling in Springfield VA
    Roll-off Rental Dumpsters in Annandale, VA

    Tuesday, August 29, 2017 6:01 PM
  • Hi,

    Seems this is a known issue based on my test and research(lookup fields won’t show).

    Here is one similar thread for you reference.

    https://social.msdn.microsoft.com/Forums/office/en-US/eb404a9c-89dd-4c55-9edc-34da283182c3/api-getitems-viewxml-caml-query-not-returning-file-name-and-lookup-field-values?forum=sharepointdevelopment

    As a workaround, use JSOM instead.

    Sample Code:

    <script src="https://cdnjs.cloudflare.com/ajax/libs/bluebird/3.3.5/bluebird.min.js"></script>
        <script src="/_layouts/15/js/jquery.min.js" type="text/javascript"></script>
        <script type="text/javascript">
            function useRESTwithCAML(listName, CAML) {           
                var clientContext = new SP.ClientContext.get_current();
                var oList = clientContext.get_web().get_lists().getByTitle(listName);
    
                var camlQuery = new SP.CamlQuery();
                camlQuery.set_viewXml(CAML);
                this.collListItem = oList.getItems(camlQuery);
    
                clientContext.load(collListItem);
                clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
    
            };
    
            var CAMLJoins =
                   "<Joins>" +
                   "<Join Type='INNER' ListAlias='Person'>" +
                   "<Eq>" +
                   "<FieldRef Name='Person' RefType='Id'/>" +
                   "<FieldRef List='Person' Name='ID'/>" +
                   "</Eq>" +
                   "</Join></Joins>";
    
            var CAMLProjFlds =
                "<ProjectedFields>" +
                "<Field Name='DLastName' Type='Lookup' List='Person' ShowField='LastName'/>" +
                "<Field Name='DFirstName' Type='Lookup' List='Person' ShowField='FirstName'/>" +
                "</ProjectedFields>";
    
            var ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>A</Value></Eq></Where></Query><ViewFields><FieldRef Name='Title'/><FieldRef Name='DLastName'/><FieldRef Name='DFirstName'/></ViewFields>" + CAMLProjFlds + CAMLJoins + "<RowLimit>100</RowLimit></View>";
    
            function onQuerySucceeded(sender, args) {
    
                var listItemInfo = '';
    
                var listItemEnumerator = collListItem.getEnumerator();
    
                while (listItemEnumerator.moveNext()) {
                    var oListItem = listItemEnumerator.get_current();
                    listItemInfo += '\nID: ' + oListItem.get_id() +
                        '\nTitle: ' + oListItem.get_item('Title') +
                        '\nDLastName: ' + oListItem.get_item('DLastName') +
                        '\nDLastName: ' + oListItem.get_item('DLastName');
                }
    
                alert(listItemInfo.toString());
            }
    
            function onQueryFailed(sender, args) {
    
                alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
            }
        </script>
    
        <input id="Button1" onclick="useRESTwithCAML('PersonAssignments', ViewXml)" type="button" value="testCAMLJoin" />


    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, August 30, 2017 2:13 AM
  • Hi Lee,

    Thanks for your solution.

    Below code is iterating through list items. This will not be efficient if the query results are in thousands. Is there any way to convert the query results directly into JSON without looping.

    while (listItemEnumerator.moveNext()) {
                    var oListItem = listItemEnumerator.get_current();
                    listItemInfo += '\nID: ' + oListItem.get_id() +
                        '\nTitle: ' + oListItem.get_item('Title') +
                        '\nDLastName: ' + oListItem.get_item('DLastName') +
                        '\nDLastName: ' + oListItem.get_item('DLastName');
                }

    Monday, February 12, 2018 7:10 AM