topic Re: Query Task : How to look for NULL? in ArcGIS JavaScript Maps SDK Questions

Query Task : How to look for NULL?

LucieBoucher1 — Mon, 27 Feb 2012 20:31:24 GMT

I feel this question is really stupid, but I'm stuck...

I have adapted the sample "Find Results in Dojo Chart" fom the Sample page : http://help.arcgis.com/en/webapi/javascript/arcgis/help/jssamples/find_map_datagrid.html

Instead of having a text box and a search button, I have three buttons (Positive, Negative, Empty) that display different results in my datagrid. Basically, what I want is three very simple functions : display the elements where fieldA = "Positive", where fieldA = "Negative" or where fieldA contains nothing.

I have managed the first two easily. But now I face a very stupid situation : how do I query for empty elements? I have no idea how to write the findParams.searchText in order to return the empty elements. I have tried with "", with NULL, and I always have an error.

Within my function init(), I have the following code :
[HTML]        //Create the find parameters
findParams = new esri.tasks.FindParameters();
findParams.returnGeometry = true;
findParams.layerIds = [0];
findParams.searchFields = ["FIELDA"];
findParams.outSpatialReference = map.spatialReference; [/HTML]

Then I have the three functions :
[HTML]
function doFindPositives() {
findParams.searchText = "Positive";
findTask.execute(findParams,showResults);
}

function doFindNegatives() {
findParams.searchText = "Negative";
findTask.execute(findParams,showResults);
}

function doFindEmpty() {
findParams.searchText = "";
findTask.execute(findParams,showResults);
}
[/HTML]

And later, in the <body>:
[HTML]
<button data-dojo-type="dijit.form.Button" data-dojo-props='onClick:function(){ doFindPositives();}, value:"Search"'>
Positive
</button>
<button data-dojo-type="dijit.form.Button" data-dojo-props='onClick:function(){ doFindNegatives();}, value:"Search"'>
Negative
</button>
<button data-dojo-type="dijit.form.Button" data-dojo-props='onClick:function(){ doFindEmpty();}, value:"Search"'>
   Empty
</button>
[/HTML]

Thank you very much for your help!

Re: Query Task : How to look for NULL?

RuiShen — Tue, 28 Feb 2012 23:38:21 GMT

Hi,
To conduct a search on a field with NULL value, you need to use the queryTask instead of findTask.
Basically, you need to set something like query.where="OWNERNME2 IS NULL" and then execute the queryTask to get the search result.
You can find more information about queryTask below:
http://help.arcgis.com/en/webapi/javascript/arcgis/help/jsapi_start.htm#jsapi/querytask.htm
query:
http://help.arcgis.com/en/webapi/javascript/arcgis/help/jsapi_start.htm#jsapi/query.htm

In the where property, you can define "A where clause for the query. Any legal SQL where clause operating on the fields in the layer is allowed." That's why you can use
query.where="OWNERNME2 IS NULL"

I have created a sample application for you based on the "Find Results in Dojo Chart" fom the Sample page. When you hit "Search Null" button, it will return all the records with OWNERNME2 as NULL, also the result will be populated to the datagrid.

Hope it helps.

[/HR]

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=7, IE=9" />
    
    <meta name="viewport" content="initial-scale=1, maximum-scale=1,user-scalable=no"/>
    <title>Display Find Task results in Dojo DataGrid</title>
    <link rel="stylesheet" type="text/css" href="http://serverapi.arcgisonline.com/jsapi/arcgis/2.7/js/dojo/dijit/themes/claro/claro.css">
    <link rel="stylesheet" type="text/css" href="http://serverapi.arcgisonline.com/jsapi/arcgis/2.7/js/dojo/dojox/grid/resources/Grid.css">
    <link rel="stylesheet" type="text/css" href="http://serverapi.arcgisonline.com/jsapi/arcgis/2.7/js/dojo/dojox/grid/resources/claroGrid.css">
    <script type="text/javascript">
      dojoConfig = {
        parseOnLoad:true
      }
    </script>
    <style>
      html, body { height: 100%; width: 100%; margin: 0; padding: 0; }
    </style>
    <script type="text/javascript" src="http://serverapi.arcgisonline.com/jsapi/arcgis/?v=2.7"></script>

    <script type="text/javascript">
      dojo.require("esri.map");
      dojo.require("dojox.grid.DataGrid");
      dojo.require("dojo.data.ItemFileReadStore");
      dojo.require("esri.tasks.find");
      dojo.require("dijit.layout.BorderContainer");
      dojo.require("dijit.layout.ContentPane");
      dojo.require("dijit.form.Button");

     var queryTask, query;
      var findTask, findParams;
      var map, startExtent;
      var grid, store;

      function init() {
        dojo.connect(grid, "onRowClick", onRowClickHandler);

        //Create map and add the ArcGIS Online imagery layer
        startExtent = new esri.geometry.Extent({"xmin":-9267533.641224435,"ymin":5225262.235659762,"xmax":-9243914.599484349,"ymax":5232026.912662991,"spatialReference":{"wkid":102100}});
        map = new esri.Map("map", { extent: startExtent});

        var streetMapLayer = new esri.layers.ArcGISTiledMapServiceLayer("http://server.arcgisonline.com/ArcGIS/rest/services/World_Street_Map/MapServer");
        map.addLayer(streetMapLayer);

//querTask
queryTask = new esri.tasks.QueryTask("http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/TaxParcel/TaxParcelQuery/MapServer/0");



        //build query filter
        query = new esri.tasks.Query();
query.outSpatialReference=map.spatialReference;
        query.returnGeometry = true;
        query.outFields = ["*"];



        findTask = new esri.tasks.FindTask("http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/TaxParcel/TaxParcelQuery/MapServer/");

        //Create the find parameters
        findParams = new esri.tasks.FindParameters();
        findParams.returnGeometry = true;
        findParams.layerIds = [0];
        findParams.searchFields = ["OWNERNME1","OWNERNME2"];
        findParams.outSpatialReference = map.spatialReference;

        dojo.connect(map, 'onLoad', function(theMap) {
           //resize the map when the browser resizes
           dojo.connect(dijit.byId('map'), 'resize', map,map.resize);
        });
      }

      function doFind() {
        //Set the search text to the value in the box
        findParams.searchText = dojo.byId("ownerName").value;
        findTask.execute(findParams,showResults);
      }
      function doFindNull() {
        //Set the query text
        query.where="OWNERNME2 IS NULL";

        //Execute task and call showResultsNull on completion
        queryTask.execute(query, showResultsNull);
      }

      function showResults(results) {
        //This function works with an array of FindResult that the task returns
        map.graphics.clear();
        var symbol = new esri.symbol.SimpleFillSymbol(esri.symbol.SimpleFillSymbol.STYLE_SOLID, new esri.symbol.SimpleLineSymbol(esri.symbol.SimpleLineSymbol.STYLE_SOLID, new dojo.Color([98,194,204]), 2), new dojo.Color([98,194,204,0.5]));

        //create array of attributes
        var items = dojo.map(results,function(result){
          var graphic = result.feature;
          graphic.setSymbol(symbol);
          map.graphics.add(graphic);
          return result.feature.attributes;
        });


        //Create data object to be used in store
        var data = {
          identifier: "PARCELID", //This field needs to have unique values
          label: "PARCELID", //Name field for display. Not pertinent to a grid but may be used elsewhere.
          items: items
        };

         //Create data store and bind to grid.
        store = new dojo.data.ItemFileReadStore({ data:data });
        var grid = dijit.byId('grid');
        grid.setStore(store);

        //Zoom back to the initial map extent
        map.setExtent(startExtent);

      }

     function showResultsNull(featureSet){
   //remove all graphics on the maps graphics layer
   map.graphics.clear();
   var symbol = new esri.symbol.SimpleFillSymbol(esri.symbol.SimpleFillSymbol.STYLE_SOLID, new esri.symbol.SimpleLineSymbol(esri.symbol.SimpleLineSymbol.STYLE_SOLID, new dojo.Color([98, 194, 204]), 2), new dojo.Color([98, 194, 204, 0.5]));
   //QueryTask returns a featureSet. Loop through features in the featureSet and add them to the map.

   var items = dojo.map(featureSet.features, function(feature){
    var graphic = feature;
    graphic.setSymbol(symbol);
    map.graphics.add(graphic);
    return graphic.attributes;
   });


   //Create data object to be used in store
   var data = {
    identifier: "PARCELID", //This field needs to have unique values
    label: "PARCELID", //Name field for display. Not pertinent to a grid but may be used elsewhere.
    items: items
   };

   //Create data store and bind to grid.
   store = new dojo.data.ItemFileReadStore({
    data: data
   });
   var grid = dijit.byId('grid');
   grid.setStore(store);

   //Zoom back to the initial map extent
   map.setExtent(startExtent);
}

      //Zoom to the parcel when the user clicks a row
      function onRowClickHandler(evt){
        var clickedTaxLotId = grid.getItem(evt.rowIndex).PARCELID;
        var selectedTaxLot;

        dojo.forEach(map.graphics.graphics,function(graphic){
          if((graphic.attributes) && graphic.attributes.PARCELID === clickedTaxLotId){
            selectedTaxLot = graphic;
            return;
          }
        });
        var taxLotExtent = selectedTaxLot.geometry.getExtent();
        map.setExtent(taxLotExtent);
      }

      dojo.addOnLoad(init);
    </script>
</head>
<body class="claro">
<div data-dojo-type="dijit.layout.BorderContainer" data-dojo-props="design:'headline'" style="width:100%;height:100%;margin:0;">
    <div data-dojo-type="dijit.layout.ContentPane" data-dojo-props="region:'top'" style="height:40px;">
      Owner name: <input type="text" id="ownerName" size="60" value="Katz" />
      <button data-dojo-type="dijit.form.Button" data-dojo-props='onClick:function(){ doFind();}, value:"Search"'>
        Search
      </button>
   <button data-dojo-type="dijit.form.Button" data-dojo-props='onClick:function(){ doFindNull();}, value:"Search"'>
        Search Null
      </button>
    </div>
    <div id="map" data-dojo-props="region:'center'" data-dojo-type="dijit.layout.ContentPane" style="border:1px solid #000;"></div>
    <div data-dojo-type="dijit.layout.ContentPane" data-dojo-props="region:'bottom'" style="height:150px;">
     <table data-dojo-type="dojox.grid.DataGrid" data-dojo-id="grid" id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px'">
      <thead>
        <tr>
          <th field="PARCELID">Parcel ID</th>
          <th field="OWNERNME1" >Owner 1</th>
          <th field="OWNERNME2">Owner 2</th>
          <th field="RESYRBLT ">Year Built</th>
          <th field="SITEADDRESS" width="100%">Address</th>
        </tr>
      </thead>
    </table>
    </div>
</div>
</body>
</html>

Re: Query Task : How to look for NULL?

LucieBoucher1 — Wed, 29 Feb 2012 13:15:12 GMT

It works! Thank you so much!

Re: Query Task : How to look for NULL?

LucieBoucher1 — Thu, 01 Mar 2012 17:14:45 GMT

Using the code you provided, I see a difference in the way the dates are written in the grid, if I use the find task or the query. In the <th field...> section from the body, I have a field named Date_Creation, of type date. When I display results with the find task, the date format is yyyy-mm-dd. But when I display results using the query task, the date seems to be converted to an integer, such as : 13304736000. Is there any way in the grid definition where I can specify the date format? Something like :
[HTML]<th field="Date_Creation" format="yyyy-mm-dd">Date Creation</th>[/HTML]

Or maybe kind of conversion could be written in the query function? I do not want to change the data : both task are applied on the same dataset, one works correctly and the other don't.

Both functions :

[HTML]
//When Result=Positive or Result=Negative
function showResults(results) {
map.graphics.clear();
//create array of attributes
var items = dojo.map(results,function(result){
    var graphic = result.feature;
    map.graphics.add(graphic);
    return result.feature.attributes;
});
//Create data object to be used in store
var data = {
    identifier: "IDFICHE", //This field needs to have unique values
    label: "IDFICHE", //Name field for display. Not pertinent to a grid but may be used elsewhere.
    items: items
};
//Create data store and bind to grid.
store = new dojo.data.ItemFileReadStore({data:data});
var grid = dijit.byId('grid');
grid.setStore(store);
}
[/HTML]

[HTML]
//When Result is NULL
function showResultsNull(featureSet){
map.graphics.clear();
//QueryTask returns a featureSet. Loop through features in the featureSet and add them to the map.
var items = dojo.map(featureSet.features, function(feature){
    var graphic = feature;
    map.graphics.add(graphic);
    return graphic.attributes;
});
//Create data object to be used in store
var data = {
    identifier: "IDFICHE", //This field needs to have unique values
    label: "IDFICHE", //Name field for display. Not pertinent to a grid but may be used elsewhere.
    items: items
};
//Create data store and bind to grid.
store = new dojo.data.ItemFileReadStore({data: data});
var grid = dijit.byId('grid');
grid.setStore(store);
}
[/HTML]

And the grid definition :
[HTML]
<table data-dojo-type="dojox.grid.DataGrid" data-dojo-id="grid" id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px'">
<thead>
    <tr>
      <th field="IDFICHE">IDFiche</th>
      <th field="DISTRICT">District</th>
      <th field="DATE_CREATION">Date creation</th>
      <th field="RESULT">Resultat</th>
    </tr>
</thead>
</table>
[/HTML]

Thanks, once again

Re: Query Task : How to look for NULL?

RuiShen — Wed, 07 Mar 2012 14:28:39 GMT

Hi Lucie,
Can you try below:
Js:

function showResults(results) {
map.graphics.clear();
//create array of attributes
var items = dojo.map(results,function(result){
    var graphic = result.feature;
    map.graphics.add(graphic);
   var Testdate=dojo.date.locale.format(new Date(result.feature.attributes.DATE_CREATION);
var item =dojo.mixin({ DATE_CREATION2: Testdate }, result.feature.attributes);
return item;
});
//Create data object to be used in store
var data = {
    identifier: "IDFICHE", //This field needs to have unique values
    label: "IDFICHE", //Name field for display. Not pertinent to a grid but may be used elsewhere.
    items: items
};
//Create data store and bind to grid.
store = new dojo.data.ItemFileReadStore({data:data});
var grid = dijit.byId('grid');
grid.setStore(store);
}

html:
<table data-dojo-type="dojox.grid.DataGrid" data-dojo-id="grid" id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px'">
<thead>
    <tr>
      <th field="IDFICHE">IDFiche</th>
      <th field="DISTRICT">District</th>
      <th field="DATE_CREATION2">Date creation</th>
      <th field="RESULT">Resultat</th>
    </tr>
</thead>
</table>

be sure to add       dojo.require("dojo.date.locale"); in your code.

Let me know if it works.

Re: Query Task : How to look for NULL?

LucieBoucher1 — Sun, 12 Dec 2021 07:46:13 GMT

Hi Rui,

I tried using your code. Now, instead of large numbers (or dates), I see "..." in my grid.

I modified both my showResults and my showResultsNull functions, and my html table with the following code :

      function showResults(results) {
        map.graphics.clear();
        //create array of attributes
        var items = dojo.map(results,function(result){
          var graphic = result.feature;
          map.graphics.add(graphic);
          return result.feature.attributes;
          var Testdate=dojo.date.locale.format(new Date(result.feature.attributes.DATEFICHE));
          var item =dojo.mixin({ DATEFICHE2: Testdate }, result.feature.attributes);

        });
        //Create data object to be used in store
        var data = {
          identifier: "IDFICHE", //This field needs to have unique values
          label: "IDFICHE", //Name field for display. Not pertinent to a grid but may be used elsewhere.
          items: items
        };
        //Create data store and bind to grid.
        store = new dojo.data.ItemFileReadStore({data:data});
        var grid = dijit.byId('grid');
        grid.setStore(store);
      }

      function showResultsNull(featureSet){
        map.graphics.clear();
        //create array of attributes
        var items = dojo.map(featureSet.features, function(feature){
          var graphic = feature;
          map.graphics.add(graphic);
          return graphic.attributes;
          var Testdate=dojo.date.locale.format(new Date(featureSet.feature.attributes.DATEFICHE));
          var item =dojo.mixin({ DATEFICHE2: Testdate }, featureSet.feature.attributes);
        });
        //Create data object to be used in store
        var data = {
        identifier: "IDFICHE", //This field needs to have unique values
        label: "IDFICHE", //Name field for display. Not pertinent to a grid but may be used elsewhere.
        items: items
        };
        //Create data store and bind to grid.
        store = new dojo.data.ItemFileReadStore({data:data});
        var grid = dijit.byId('grid');
        grid.setStore(store);
      }

[HTML]        <table data-dojo-type="dojox.grid.DataGrid" data-dojo-id="grid" id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px'">
          <thead>
            <tr>
              <th field="IDFICHE">IDFICHE</th>
              <th field="DISTRICT">District</th>
              <th field="DATEFICHE2">Date fiche</th>
              <th field="RESULT">Resultat</th>
            </tr>
          </thead>
        </table>
[/HTML]

Thanks for your help...