I have a Sharepoint app and I use a caml query to filter the data I get back:
var caml = "<View><Query><Where><And><Contains><FieldRef Name='PublishingPageLayout' /><Value Type='URL'>" + pageLayoutName + "</Value></Contains><Neq><FieldRef Name='NewsCategory' /><Value Type='Lookup'>Memo mailer</Value></Neq></And></Where><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy></Query></View>";
var url = appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('pages')/GetItems@target='" + hostWebUrl + "'";
var requestData = '{"query": { "__metadata": { "type": "SP.CamlQuery" }, "ViewXml": "' + caml + '"}}';
return $.ajax({
url: url,
type: "POST",
data: requestData,
headers: {
"Accept": "application/json; odata=verbose",
"X-RequestDigest": formDigest,
"Content-Type": "application/json; odata=verbose"
},
dataType: "json"
});
This works and I get data back. Now the issue I have is that I'm not getting any lookup data back, I only get the ID's of the lookups I want. To accomplish this I have tried using oData combined with my caml query:
var url = appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('pages')/GetItems\?$select=*,Author/Title\&$expand=Author\&@target='" + hostWebUrl + "'";
var url = appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('pages')/GetItems?$select=*,Author/Title&$expand=Author&@target='" + hostWebUrl + "'";
This results in a bad request error:
{"error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The $expand query is not valid for field 'Author'."}}}
To overcome this error I removed my caml query and tried using a get request using odata $filter
(I need to filter by the PublishingPageLayout
field) $filter=substringof(PublishingPageLayout, "NewsLayout.aspx")
But again I keep getting an error:
The field 'PublishingPageLayout' of type 'URL' cannot be used in the query filter expression.
I've had a look at oData and I think it only lets you filter on specific fields?
My question is how can I use caml and odata together to get back all of my data including my lookup fields in one call?
If I can't combine them both, is there another way using the rest api to get back all my data and all my lookup data?
I don't want to make other ajax calls to get the lookup data back as I have at least 5-6 lookup fields on each item I'm querying against and I don't want to make 6 more ajax calls just to get all my data back!
Thanks for any input.