1

I'm a newbie in javascript and i'm using jsom to retrieve list items in Sharepoint Online.

I have two lists BnzPersons and Sessions. This is my first code variant:

<div id="divListItems"></div>


$(function () {

    ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");

});

function retrieveListItems() {

var accountEmail = _spPageContextInfo.userLoginName;
var clientContext = new SP.ClientContext('/sites/taxonomy/Office');

var oList = clientContext.get_web().get_lists().getByTitle('Sessions');

var camlQuery = new SP.CamlQuery();

camlQuery.set_viewXml('<View><Query><ProjectedFields><Field Name=\'Title\' Type=\'Lookup\' List=\'BnzPersons\' ShowField=\'titleBnz\' /></ProjectedFields><joins><join TYPE=\'LEFT\' ListAlias=\'Sessions\'><Eg><FieldRef Name=\'InvitationParticipants\' RefType=\'id\'/><FieldRef List=\'BnzPersons\' Name=\'ID\'/></Eq></Join></joins></Query></View>');

this.collListItem = oList.getItems(camlQuery);

clientContext.load(collListItem);

clientContext.executeQueryAsync(

Function.createDelegate(this, this.onQuerySucceeded),

Function.createDelegate(this, this.onQueryFailed)

);

}

function trimCouncilId(objectArray) {

var firstPartQuery = "";
var secondPartQuery = "";

var arr = $.map(objectArray, function(value, index) {
    return [value];
});


var result = [];

// $1m_1 e stoinostta za id, $5X_1 e stojnostta za imena
$.each(arr, function( key, value ) {
 result.push(value.$5X_1);
});

return result;
}

function onQuerySucceeded(sender, args) {

var listItemInfo = '';

var listItemEnumerator = collListItem.getEnumerator();

while (listItemEnumerator.moveNext()) {

    var oListItem = listItemEnumerator.get_current();

    listItemInfo += '<tr><td><strong>Заглавие:</strong></td><td>  ' + oListItem.get_item('Title') + '</td></tr>' +
  '<tr><td><strong>Kind:</strong></td><td>  ' + JSON.stringify(oListItem.get_item('SessionType'), ['Label']).slice(10, -2) + '</td></tr>' +
  '<tr><td><strong>Status:</strong></td><td>  ' + JSON.stringify(oListItem.get_item('SessionStatus'), ['Label']).slice(10, -2) + '</td></tr>' +
  '<tr><td><strong>Council:</strong></td><td>  ' + JSON.stringify(oListItem.get_item('_x041a__x043e__x043b__x043e__x04')) + '</td></tr>' +
  '<tr><td><strong>Council ID:</strong></td><td>  ' + JSON.stringify(oListItem.get_item('_x041a__x043e__x043b__x043e__x040')) + '</td></tr>' +
  '<tr><td><strong>BnzPersonsCouncil ID:</strong></td><td>  ' + oListItem.get_item('titleBnz') + '</td></tr>' +
    '<br />';

}

$("#divListItems").html(listItemInfo);

}

function onQueryFailed(sender, args) {

alert('Request failed. ' + args.get_message() +

'\n' + args.get_stackTrace());

}

I'm trying to join the column Title from BnzPersons with the list Sessions. When i put the last result row - 'BnzPersonsCouncil ID: ' + oListItem.get_item('titleBnz') + ''

i get the error in chrome: "the property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested"

My second code variant is when i decided to use two queries instead of one. I get the values for the value from the first query in a variable and then i'm trying to concatenate that variable in the second query like this:

<div id="divListItems"></div>

$(function () {ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");});


function retrieveListItems() {
    var accountEmail = _spPageContextInfo.userLoginName;
    var clientContext = new SP.ClientContext('/sites/taxonomy/Office');
    var oList = clientContext.get_web().get_lists().getByTitle('BnzPersons');
    var secondList = clientContext.get_web().get_lists().getByTitle('Sessions');
    var camlQuery = new SP.CamlQuery();
    var camlQuery2 = new SP.CamlQuery();
    camlQuery.set_viewXml('<View><Query><Where><Eq><FieldRef Name=\'PartisipantUserName\' /><Value Type=\'Text\'>' + accountEmail + '</Value></Eq></Where></Query><ViewFields><FieldRef Name=\'_x0421__x044a__x0432__x0435__x040\' /></ViewFields><QueryOptions /></View>');    
    this.collListItem = oList.getItems(camlQuery);
    camlQuery2.set_viewXml('<View><Query><Where><In><FieldRef Name=\'_x041a__x043e__x043b__x043e__x040\' /><Values>' + listItemInfo + '</Values></In></Where></Query></View>');
    this.collListItem2 = secondList.getItems(camlQuery2);
    clientContext.load(collListItem);
    clientContext.load(collListItem2);
    clientContext.executeQueryAsync(
    Function.createDelegate(this, this.onQuerySucceeded),
    Function.createDelegate(this, this.onQueryFailed)
    );
}

function trimAndAppend(objectArray) {

var firstPartTags = "<Value Type=\'Lookup\'>";
var secondPartTags = "</Value>";

var arr = $.map(objectArray, function(value, index) {
    return [value];
});


var result = [];

// $1m_1 e stoinostta za id, $5X_1 e stojnostta za imena
$.each(arr, function( key, value ) {
 result.push(firstPartTags + value.$5X_1 + secondPartTags);
});



return result;

}

var listItemInfo = '';
var listItemInfo2 = '';
function onQuerySucceeded(sender, args) {

    var listItemEnumerator = collListItem.getEnumerator();
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += trimAndAppend(oListItem.get_item('_x0421__x044a__x0432__x0435__x040'));
    }

    var listItemEnumerator2 = collListItem2.getEnumerator();
    while (listItemEnumerator2.moveNext()) {
        var oListItem2 = listItemEnumerator2.get_current();
        listItemInfo2 += '<tr><td><strong>Заглавие:</strong></td><td>  ' + oListItem2.get_item('Title') + '</td></tr>' +
      '<tr><td><strong>KindOf:</strong></td><td>  ' + JSON.stringify(oListItem2.get_item('SessionType'), ['Label']).slice(10, -2) + '</td></tr>' +
      '<tr><td><strong>Status:</strong></td><td>  ' + JSON.stringify(oListItem2.get_item('SessionStatus'), ['Label']).slice(10, -2) + '</td></tr>' +
      '</br>';
    }
    $("#divListItems").html(listItemInfo2);

}
function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

I would be very thankful if someone can help me with the situation :)

1

You could check the code below for reference.

<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" />

Here is the thread for your reference.

https://social.msdn.microsoft.com/Forums/office/en-US/789d7f23-12ae-4454-a37b-e69580c9284d/caml-inner-join-in-javascript?forum=sharepointdevelopment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.