Count by location help

coolgirl · ‎05-24-2016

Hi,

I am working on map pages. I am trying to replicate the code in 'incident count by location' by giving a different table name but it does not seem to work. May i know where am i going wrong.

This is the code

var uri = gs.getProperty("glide.servlet.uri");

var count = new GlideAggregate('u_leads'); //u_leads is the table which has the field location

count.addAggregate('COUNT', 'location');

count.query();

while (count.next()) {

var loc = count.location;

var locCount = count.getAggregate('COUNT', 'location');

if (locCount > 0) {

var item = map.addItem(count);

item.latitude = loc.latitude;

item.longitude = loc.longitude;

item.marker_label = locCount;

var link = 'href=' + uri + 'u_leads_list.do?sysparm_query=location%3D' + loc;

item.html = '<a ' + link + '>' + loc.getDisplayValue() + ' (' + locCount + ') </a>';

item.icon = "/star.png";

item.icon_width = 20;

item.icon_height = 20;

}

Thanks,

Sam

harry_adjevi · ‎05-25-2016

Hi Sam,

Not that used to the Aggregate method but here's what I ended up with:

I used a variable u_status that is true or false, so you can replace the true/false with the value of your Status variable(completed, open, closed....)

var uri = gs.getProperty("glide.servlet.uri");
var count = new GlideAggregate('u_leads');
count.addAggregate('COUNT', 'u_location');
count.query();
while (count.next()) {
var loc = count.u_location;
var locCount = count.getAggregate('COUNT', 'u_location');
var locOpen = 0;
var locClose = 0;
//COUNT THE STATUS = TRUE RECORDS
var counto = new GlideAggregate('u_leads');
counto.addQuery('u_location', loc);
counto.addQuery('u_status','true');
counto.addAggregate('COUNT');
counto.query();
if (counto.next()){
locOpen = counto.getAggregate('COUNT');
}
//COUNT THE STATUS = FALSE RECORDS
var countc = new GlideAggregate('u_leads');
countc.addQuery('u_location', loc);
countc.addQuery('u_status','false');
countc.addAggregate('COUNT');
countc.query();
if (countc.next()){
locClose = countc.getAggregate('COUNT');
}
if (locCount > 0) {
var item = map.addItem(count);
item.latitude = loc.latitude;
item.longitude = loc.longitude;
item.marker_label = (locCount);
item.label_offset_left = -11000;
item.label_offset_top = -2210;
var link = 'href=' + uri + 'u_leads_list.do?sysparm_query=u_location%3D' + loc;
item.html='<a ' + link + '>' + loc.getDisplayValue() + ' (' + locCount + ') - Open = ' + locOpen + ' - Close = ' + locClose + ' </a>';
item.icon = "images/red_marker.png";
item.icon_width = 50;
item.icon_height = 50;
}
}

Thank you for marking the question as resolved

Harry

View solution in original post

coolgirl · ‎05-24-2016

Thank you Harry, by changing it to u_location,i am able to get the markers in the map. But then when i click the link in the map, it shows the entire list of items. not just the one for that particular location.It is unable to filter with the sys id of the location. How can i achieve it

harry_adjevi · ‎05-24-2016

Check if you have "u_location" in the Link as well

var link = 'href=' + uri + 'u_leads_list.do?sysparm_query=u_location%3D' + loc;

There you go

Harry

coolgirl · ‎05-24-2016

Thanks Harry, it worked. Can you tell me how can i get the number of leads in open status,completed status in the marker next to the city name.

harry_adjevi · ‎05-24-2016

Not sure how to but I believe you will need a Count on the Open status (locCount) and a count on the Completed ones (locClose).

Querying the table and doing the Aggregate counts

Then on your map script change the line to :

item.html='<a ' + link + '>' + loc.getDisplayValue() + ' (' + locCount + ') - Open = ' + locOpen + ' - Close = ' + locClose + ' </a>';

This is still one link though that will display all records within the location.

Again I believe ... having like 3 links for All, Open and Close is possible but will require 3 items in the script + 1 item that will display (concatenate) the 3. hope you follow .

Harry

coolgirl · ‎05-24-2016

How did u get the open count and close count in the map, can u share the code.