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");
});