1

(Sorry if the title isn't clear I'm not really sure how to phrase it in one question)

Situation: I have a list in sharepoint containing some evaluation of different teams in a company with at least the following fields:

teamname, date of evalution and some fields describing their evaluation

Teams get evaluated multiple times (roughly every 3 months, but this is by no means exact) and as such have many entries in this list

Problem: I want to use CAML to retrieve only the last item corresponding to each of the different teams. is this possible?

Example: given the following table:

+-------+----------+-------+
| team  | created  | score |
+-------+----------+-------+
| red   | 5/7/15   |    10 |
| blue  | 23/6/15  |     9 |
| red   | 26/3/15  |     8 |
| green | 24/3/15  |     8 |
| blue  | 9/1/15   |     6 |
| red   | 10/12/14 |     8 |
| green | 8/12/14  |     9 |
| blue  | 4/12/14  |     5 |
+-------+----------+-------+

I would like te retrieve:

+-------+---------+-------+
| team  | created | score |
+-------+---------+-------+
| red   | 5/7/15  |    10 |
| blue  | 23/6/15 |     9 |
| green | 24/3/15 |     8 |
+-------+---------+-------+

Is this possible using CAML?

0

CAML Query cannot retrieve distinct rows. You can weed out duplicate values after applying the query.

        DataTable dt=new DataTable();
        dt.Columns.Add("team");
        dt.Columns.Add("created");
        dt.Columns.Add("score");
        StringBuilder output = new StringBuilder();
        using (ClientContext ctx = new ClientContext("http://siteUrl"))
        {
            Web web = ctx.Web;
            List list = web.Lists.GetByTitle("ListTitle");
            var q = new CamlQuery() { ViewXml = "<View><Query><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy></Query></View>" };                
            ListItemCollection listOfItems = list.GetItems(q);
            ctx.Load(listOfItems);
            ctx.ExecuteQuery();
            foreach (Microsoft.SharePoint.Client.ListItem l in listOfItems)
            {
                dt.Rows.Add(l["team"].ToString(), l["created"].ToString(), l["score"].ToString());
            }
            var listOfDataRows = dt.AsEnumerable().GroupBy(x => x["team"]).Select(x => x.First()).ToList();
            foreach(DataRow dr in listOfDataRows)
            {
                foreach(string col in dr.ItemArray)
                {
                    output.Append(col);
                    output.Append(";");
                }
                output.Append(Environment.NewLine);
            }
        }


Edit: Edited post based on user comment

Assuming you're using SharePoint 2013, you can use REST api to achieve this.

<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script>
function getDataWithCaml(siteurl,listName, caml) {
var endpoint = siteurl+"/_api/web/lists/GetByTitle('"+listName+"')/GetItems";
var requestData = { "query" :
       {"__metadata": 
          {"type": "SP.CamlQuery" }
          , "ViewXml": caml
       }
    };
return $.ajax({
    url: endpoint,
    method: "POST",
    data: JSON.stringify(requestData),
    headers: {
        "X-RequestDigest": $("#__REQUESTDIGEST").val(),
        "Accept": "application/json; odata=verbose",
        "Content-Type": "application/json; odata=verbose"
    }
});}    
</script>

Usage:

$(document).ready(function(){
var caml = "<View><Query><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy></Query></View>";
getDataWithCaml("http://siteUrl","ListUrl", caml).done(function(data)
{
var data= data.d.results;  
var result = [];  
var lookup = {};
for (var item, i = 0; item = data[i++];) {
  var team= item.team; //the team column value
  if (!(team in lookup)) {
   lookup[team] = team;
   result.push(item);  
}}
var len = result.length;
console.log(len);
for (i=0; i<len; ++i) {
console.log(result[i].team + ";" + result[i].created + ";" + result[i].score);
}
})
.fail(
function(error){
console.log(JSON.stringify(error));
});
});
0

You could set the RowLimit property of your SPQuery object.

The tag is in the schema definition of a view (direct child of ) and therefore cannot be nested inside a tag.

  • 1
    The problem here is that it is not only a RowLimit issue, you also need to get the last entry for each individual team – Robert Lindgren Jul 16 '15 at 10:57
0

You can use CAML Query to a certain extend. You'd have to combine it with LINQ.

First you need to know all of the teamnames

var teamList = SPContext.Current.Web.Lists.TryGetList("TeamList");
var teamItems = teamList.Items;
// Get all the team names in the list, but only 1 of each
var teamnames = teamItems.Cast<SPListItem>().Select(item => (string)item["TeamNameColumn"]).Distinct();

Now that you have the team names, you can now loop through the list of team names and get the items with those names

var results = new Dictionary<string, SPListItemCollection>()
var teamQuery = new SPQuery() 
{
    // This will only return a specific set of fields for each item
    ViewFields = string.Concat("<FieldRef Name='TeamNameColumn'/>",
                               "<FieldRef Name='Created'/>",
                               "<FieldRef Name='ScoreColumn'/>"),
    ViewFieldsOnly = true,
    // We are only finding scores by 1 teamname at a time, 
    // and since we order by descending on Created (see below) we have the
    // latest on top, so we can just take the top row.
    RowLimit = 1
}

foreach (var teamname in teamnames)
{
    teamQuery.Query = string.Format(string.Concat(
                          "<Where>",
                              "<Eq>",
                                  "<FieldRef Name='TeamNameColumn'/>",
                                  "<Value Type='Text'>{0}</Value>",
                              "</Eq>",
                          "</Where>",
                          "<OrderBy>",
                              "<FieldRef Name='Created' Ascending='FALSE'/>",
                          "</OrderBy>"), teamname);
    var latestScore = teamList.GetItems(teamQuery);
    results.Add(teamname, latestScore);
}

Now you should have the latest score for all teams in the list and should be able to write them out

foreach (var result in results)
{
    // We select index 0 because it's a collection
    var spItem = result.Value[0];
    var output = string.Format("{0} {1} {2}", spItem["TeamNameColumn"].ToString(),spItem["TeamScore"].ToString(),spItem["Created"].ToString());
    // Write the output to somewhere...
}
  • It seems I forgot to mention that I need to do this clientside in javascript (because that's all I have available to me). So does that mean I need to query the server holding the list multiple times? isn't that really slow? – user44266 Jul 16 '15 at 12:42

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.