Filtering with Multiple lists in one chart

Hi,

I followed the example here plumsail.com/blog/2015/08/using … -designer/ to have multiple lists in 1 chart.

I can get the data, and have a chart, but I also trying to have a where clause in the caml query to filter some data, but it seems to have no effect.

var query = new SP.CamlQuery('<View><ViewFields><FieldRef Name="Municipality"/><FieldRef Name="Population"/><FieldRef Name="Date"/> </ViewFields><Query><Where><Eq><FieldRef Name ="Municipality" /><Value Type="text">Metro</Value></Eq></Where></Query></View>');

Could you give me an example where I can filter with where clause when using multiple lists?

Thanks.

The way you are doing it is correct. What contitutes as “having no effect” - the data that’s being returned has all Municipality field values? That’s wierd. Please show us all of your code.

Hi, the below is my code.

Instead of For loop for projectList, I just called them separately as my lists have different names. They both have the same column names.

Yes, all the municipality names are being returned, not just the one I specified. In this case “Surrey”

var handlers = {};
handlers.init = function(data, logger) {
  return true;
}

handlers.requestInit = function(query, logger) {
  return true;
}

handlers.requestSuccess = function(data, logger) {
  var dfd = $.Deferred();
  var projectList = data.items;
  data.items = [];
  var deferreds = [];
  
  var query = new SP.CamlQuery('<View><ViewFields><FieldRef Name="Area"/><FieldRef Name="Municipality"/><FieldRef Name="Population"/> <FieldRef Name="Date"/> </ViewFields><Query><Where><Eq><FieldRef Name ="Municipality" /><Value Type="text">Surrey</Value></Eq></Where></Query></View>');

  var d = request(query, projectList[0], 'Municipal Urban Development', ['Municipality', 'Area', 'Population', 'Date'], data, logger);
  deferreds.push(d);
  
  var e = request(query, projectList[1], 'Urban Centres Development', ['Municipality','Area', 'Population', 'Date'], data, logger);
  deferreds.push(e);
  
  /*for (var project in projectList){
    //logger.debug("lists", projectList[project]);
    var d = request(query, projectList[project], 'Urban Centres Development', ['Population', 'Year'], data);
    deferreds.push(d);
  }*/

  logger.debug('All requests fired.');
  $.when.apply(null, deferreds).done(function() {
    console.log('All requests have been resolved.');
    dfd.resolve(); 
  });
  return dfd.promise();
}

handlers.requestError = function(error, logger) {
  return $.Deferred().reject(error);
}

handlers.aggregationSuccess = function(data, logger) {
  return true;
}

handlers.aggregationError = function(error, logger) {
  return $.Deferred().reject(error);
}

handlers.finish = function(data, logger) {
  logger.debug('Data is processed: ', data);
  return true;
}

function request(query, project, listName, fields, data, logger) {
  
    var dfd = $.Deferred();
    var ctx = new SP.ClientContext(project['URL']);
    var list = ctx.get_web().get_lists().getByTitle(listName);

    var itemCollection = list.getItems(query);
  
  
    ctx.load(itemCollection, 'Include(' + fields.join(',') + ')');
 
    ctx.executeQueryAsync(function () {
        var itemCollectionEnumerator = itemCollection.getEnumerator();
        while (itemCollectionEnumerator.moveNext()) {
            var listItem = itemCollectionEnumerator.get_current();
 
            var item = {};
            $.each(fields, function () {
                try {
                
                     var value = listItem.get_item(this);
                      item[this] = value; 

                }
                catch (e) { }
            });
            item['Project'] = project['Title'];
            data.items.push(item); 
          	
          }
              
           
           
        }
        console.log('Request to ' + project['URL'] + ' has been resolved.');
        dfd.resolve();
    }, function(caller, args){
        console.log('Error: ' + args.get_message());
    });
 
    return dfd.promise();
}

Hi,
Could you send a template of the list to support@spchart.com? To save the list as a template go to the List Settings -> Save list as template -> pick Include Content.

Hi,
Thank you for the list template.
The following clause works properly for me:

<Where> <Eq> <FieldRef Name="Municipality" /> <Value Type="Lookup">Metro</Value> </Eq> </Where>

Hi Dmitry,

Unfortunately it didn’t work for me. But I have a work around in the request function to push items that only has “Metro” as municipality. Everything is working as expected this way.

On the same topic, I want to change the value field dynamically via a dropdown. For example, change the measure from Population vs Date to Employment vs Date.

How can I do that here?

Thank you.

Hi Ann,

Regarding your first point, did you replace ‘text’ with ‘Lookup’ as the value type of the field?

Regarding the second point, check this demo http://www.spchart.com/demo/helpdesk-dashboard. It has a dropdown (a regular html element you’ll have to add as a web part). Check the Configuration section. The Demo is about changing the CAMLQuery used to retrieve data items, but you can use the same idea to alter the chart’s configuration object, precisely, to change the object’s property that is used to specify the field that the chart uses as a category or a value field.