1

I am trying to get data from 3 lists through Join. However not able to access columns of 2nd and 3rd list which i had fetched in query.


Relation between lists is as follows;

Test3rdList has Lookup column named RefCol which points to BLL list ID column.

Further list BLL has a Loolup column named RefField which points to LstChangeType list ID column


I want to fetch below columns from respective lists;

(Test3rdList) -> ID,Title

(BLL) -> ID

(LstChangeType) -> ID


Below is my code;

function retrieveListItems() {
    var clientContext = new SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('Test3rdList');

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(
            '<Query />' +
            '<ViewFields>'+                
            '<FieldRef Name=\'ID\' />' +
            '<FieldRef Name=\'Title\' />' +
            '<FieldRef Name=\'BLL_ID\' />' +                
            '<FieldRef Name=\'LstChangeType_ID\' />' +
            '</ViewFields>' +
            '<Joins>' +
                '<Join Type=\'INNER\' ListAlias=\'BLLs\'>' +
                    '<Eq>' +
                    '<FieldRef Name=\'RefCol\' RefType=\'ID\' />' +
                    '<FieldRef List=\'BLL\' Name=\'ID\' />' +
                    '</Eq>' +
                '</Join>' +
                '<Join Type=\'INNER\' ListAlias=\'LstChangeTypes\'>' +
                    '<Eq>' +
                    '<FieldRef Name=\'RefField\' RefType=\'ID\' />' +
                    '<FieldRef List=\'LstChangeTypes\' Name=\'ID\' />' +
                    '</Eq>' +
                '</Join>' +
            '</Joins>' +
            '<ProjectedFields>' +
                '<Field ShowField=\'ID\' Type=\'Lookup\' Name=\'BLL_ID\' List=\'BLLs\' />' +
                '<Field ShowField=\'ID\' Type=\'Lookup\' Name=\'LstChangeType_ID\' List=\'LstChangeTypes\' />' +
            '</ProjectedFields>' +
            '<QueryOptions />'
    );
    this.collListItem = oList.getItems(camlQuery);

    clientContext.load(this.collListItem);
    clientContext.executeQueryAsync(
        Function.createDelegate(this, this.onQuerySucceeded),
        Function.createDelegate(this, this.onQueryFailed)
    );
}

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') +
            '\nRefCol: ' + oListItem.get_item('RefCol').$1T_1;
        var tst = 1;
    }

    alert(listItemInfo.toString());
}

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

//$(document).ready(function(){
//SP.SOD.executeFunc('sp.js', 'SP.ClientContext', retrieveListItems);
//});

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

Shouldn't it be RefField instead of RefCol?

            <Join Type=\'INNER\' ListAlias=\'BLLs\'>' +
                '<Eq>' +
                '<FieldRef Name=\'RefField\' RefType=\'ID\' />' +
                '<FieldRef List=\'BLL\' Name=\'ID\' />' +
                '</Eq>' +
            '</Join>
  • Hi Deepmala, Sorry my mistake in explanation statement. Have corrected it now. I don't know how to join 3 lists in CAML. Kindly help on this. – shoab Apr 10 '18 at 7:54

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.