Filtering with Multiple lists in one chart

by annlee » Thu Mar 03, 2016 5:49 pm

Hi,

I followed the example here https://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.

Code: Select all
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.
annlee
 
Posts: 35
Joined: Thu Oct 08, 2015 3:25 pm

by Rostislav » Fri Mar 04, 2016 11:14 am

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.
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by annlee » Fri Mar 04, 2016 4:14 pm

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"

Code: Select all
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();
}
annlee
 
Posts: 35
Joined: Thu Oct 08, 2015 3:25 pm

by Dmitry Kozlov » Mon Mar 07, 2016 9:30 am

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.
User avatar
Dmitry Kozlov
 
Posts: 129
Joined: Wed Nov 12, 2014 3:24 pm

by Dmitry Kozlov » Tue Mar 08, 2016 7:41 am

Hi,
Thank you for the list template.
The following clause works properly for me:
Code: Select all
<Where>
  <Eq>
   <FieldRef Name="Municipality" />
   <Value Type="Lookup">Metro</Value>
  </Eq>
</Where>
User avatar
Dmitry Kozlov
 
Posts: 129
Joined: Wed Nov 12, 2014 3:24 pm

by annlee » Tue Mar 08, 2016 10:08 pm

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.
annlee
 
Posts: 35
Joined: Thu Oct 08, 2015 3:25 pm

by Rostislav » Wed Mar 09, 2016 10:52 am

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.
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am


Return to Dashboard Designer for SharePoint 2013

cron