SharePoint Programming

An example of how to use Joins and Projected fields in a Spquery in Client Object model. The code example is in ECMAScript


<span style="font-weight:bold;">Country </span>Lookup list - Title, CountryName, CountryContinent

<span style="font-weight:bold;">Test1 </span>Destination list - Title,City (Title being a lookup column)


<script type="text/javascript">

ExecuteOrDelayUntilScriptLoaded(getProducts, "sp.js");

var productcollection;
function getProducts() {

try {
var context = new SP.ClientContext.get_current();
var web = context.get_web();
var list = web.get_lists().getByTitle('Test1');
var query = '<View>' +
'<Joins>' +
'<Join Type=\'INNER\' ListAlias=\'Country\'>' +
'<Eq><FieldRef Name=\'City\' RefType=\'Id\' /><FieldRef List=\'Country\' Name=\'ID\'/></Eq>' +
'</Join>' +
'</Joins>'+
'<ProjectedFields>' +
'<Field Name=\'CountryName\' Type=\'Lookup\' List=\'Country\' ShowField=\'CountryName\' />' +
'<Field Name=\'CountryContinent\' Type=\'Lookup\' List=\'Country\' ShowField=\'Country_x0020_Continent\' />' +
'</ProjectedFields>' +
'</View>';
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml(query);
camlQuery
this.productcollection = list.getItems(camlQuery);
context.load(this.productcollection, 'Include(Title,City,CountryName,CountryContinent)');
context.executeQueryAsync(Function.createDelegate(this, this.productsReceived), Function.createDelegate(this, this.failed));
}
catch (e) {
alert(e);
}
}
function productsReceived() {
alert('Success');
var item = this.productcollection.itemAt(0);
var itemValues = this.productcollection.itemAt(0).get_fieldValues();
alert(itemValues.City.$b_1);
alert(itemValues.CountryName.$b_1);
alert(itemValues.CountryContinent.$b_1);
}

function failed(sender, args) {
alert('failed. Message:' + args.get_message());
}
</script>