The CreatorCon Call for Content is officially open! Get started here.

Count by location help

Go to solution
coolgirl
Mega Expert

‎05-24-2016 03:39 AM

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

0 Helpfuls
  • 2,525 Views
1 ACCEPTED SOLUTION

Go to solution
harry_adjevi
Giga Contributor
In response to coolgirl

‎05-25-2016 04:39 AM

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....)



  1. var uri = gs.getProperty("glide.servlet.uri");
  2. var count = new GlideAggregate('u_leads');
  3. count.addAggregate('COUNT', 'u_location');
  4. count.query();
  7. while (count.next()) {
  8.   var loc = count.u_location;
  9.   var locCount = count.getAggregate('COUNT', 'u_location');
  11.   var locOpen = 0;
  12.   var locClose = 0;
  14.   //COUNT THE STATUS = TRUE RECORDS
  15.   var counto = new GlideAggregate('u_leads');
  16.   counto.addQuery('u_location', loc);
  17.   counto.addQuery('u_status','true');
  18.   counto.addAggregate('COUNT');
  19.   counto.query();
  20.   if (counto.next()){
  21.   locOpen = counto.getAggregate('COUNT');
  22.   }
  24.   //COUNT THE STATUS = FALSE RECORDS
  25.   var countc = new GlideAggregate('u_leads');
  26.   countc.addQuery('u_location', loc);
  27.   countc.addQuery('u_status','false');
  28.   countc.addAggregate('COUNT');
  29.   countc.query();
  30.   if (countc.next()){
  31.   locClose = countc.getAggregate('COUNT');
  32.   }
  34.   if (locCount > 0) {
  35.   var item = map.addItem(count);
  36.   item.latitude = loc.latitude;
  37.   item.longitude = loc.longitude;
  38.   item.marker_label = (locCount);
  39.   item.label_offset_left = -11000;
  40.   item.label_offset_top = -2210;
  42.   var link = 'href=' + uri + 'u_leads_list.do?sysparm_query=u_location%3D' + loc;
  43.   item.html='<a ' + link + '>' + loc.getDisplayValue() + ' (' + locCount + ') - Open = ' + locOpen + ' - Close = ' + locClose + ' </a>';
  45.   item.icon = "images/red_marker.png";
  46.   item.icon_width = 50;
  47.   item.icon_height = 50;
  48.   }
  49. }


find_real_file.png find_real_file.png


find_real_file.pngfind_real_file.png



Thank you for marking the question as resolved



Harry


View solution in original post

Preview file
43 KB
Preview file
49 KB
Preview file
20 KB
Preview file
17 KB
0 Helpfuls
11 REPLIES 11

Go to solution
harry_adjevi
Giga Contributor

‎05-24-2016 04:19 AM

Hi Sam,



I'd say check that your "u_leads" table has the same field, active, location as a reference to the Locations table...


I tried the same using the "sys_user" table and it worked fine, I get the user's list on all locations on the map.



Harry


0 Helpfuls

Go to solution
coolgirl
Mega Expert
In response to harry_adjevi

‎05-24-2016 04:25 AM

Hi harry,


              The u_leads table has the location field which is a reference to cmn_location table. i have another field named status which is a string. i dont have the active field..


0 Helpfuls

Go to solution
coolgirl
Mega Expert
In response to harry_adjevi

‎05-24-2016 04:26 AM

find_real_file.png


Preview file
43 KB
0 Helpfuls

Go to solution
harry_adjevi
Giga Contributor
In response to coolgirl

‎05-24-2016 04:41 AM

Ok I tried to create a Leads (u_leads) table and reproduce


You leads is a new table you created so I believe every variable you add should have a "u_" prefix.


Now on your Location reference, you created it so the variable name should be "u_location".


On the mapping script, you are making queries on "location"


Try on the script to replace "location" with "u_location" ... (first of all please check the Location variable name in the "u_leads" table.


That name should be the one to use in your query script.



Test I did:



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


var count = new GlideAggregate('u_leads');


//count.addQuery('active', 'true');


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


count.query();



while (count.next()) {


    var loc = count.u_location;


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


    if (locCount > 0) {


          var item = map.addItem(count);



          item.latitude = loc.latitude;


          item.longitude = loc.longitude;    


         


          item.marker_label = locCount;


          item.label_offset_left = -11;


          item.label_offset_top = -21;


         


          var link = 'href=' + uri + 'sys_user_list.do?sysparm_query=active%3Dtrue^location%3D' + loc;


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


         


          item.icon = "images/red_marker.png";


          item.icon_width = 50;


          item.icon_height = 50;


    }


}



Harry


0 Helpfuls