Where expression on Query Task using Action Script

[ionarawilson1]

I am developing a website that has 2 comboboxes and 1 text input. The user can search for a record based on entering the name of a company on the text input, or search based on the name of a company and name of the county (on the dropdown box) or search based on the name of the company, name of the county and type of industry (the other drowpdown box). He can also select one criteria at time or two at a time. So because there 3 elements, there is a possibilty of 7 different types of search. I first had created a query for each (a query usin mxml tags) and it worked fine. But what I want to do is to create just one query and change the where clause according to the search options, but it is not working. I think there is something wrong with my query expression. Does anybody have any ideas on what needs to be changed?  The query occurs after the user selects the options and click a search button. Then the graphic layer is created showing the points relative to the search and a datagrid shows the data


Here is the function for the button click to create the query

  protected function buttonclick():void                            {     var querytask:QueryTask =  new QueryTask();     querytask.url = "http://tfs-24279/ArcGIS/rest/services/ForestProducts/dynamic_layer_forest_products/MapServer/0"          var query:Query = new Query();     query.outSpatialReference = myMap.spatialReference;     query.outFields = ["Company"];     query.returnGeometry = true;     query.where = expr;     var expr:String = new String;     querydg.dataProvider = querytask.executeLastResult.attributes;              querydg.height=querytask.executeLastResult.attributes.length + 150          querydg.visible= querytask.executeLastResult !=null && querytask.executeLastResult.attributes.length>0                if (qText.text != "" && countycb.selectedItem != " " && industrycb.selectedItem != " ")           {      expr="Company Like '%{qText.text}%' AND County = '{countycb.selectedItem}' AND prim_bus = '{industrycb.selectedItem}'"           }          if (qText.text != " "  && countycb.selectedItem == " " && industrycb.selectedItem == " " )           {      expr= "Company Like '%{qText.text}%' "           }                           if (qText.text =="" && countycb.selectedItem != " " && industrycb.selectedItem == " " )           {      expr="County = '{countycb.selectedItem}'"     }                    if (qText.text =="" && countycb.selectedItem == " " && industrycb.selectedItem != " " )           {      expr= "prim_bus = '{industrycb.selectedItem}'"          }          if (qText.text != "" && countycb.selectedItem != " " && industrycb.selectedItem == " ")           {      expr="Company Like '%{qText.text}%' AND County = '{countycb.selectedItem}'"           }                                   if (qText.text != "" && countycb.selectedItem == " " && industrycb.selectedItem != " ")           {      expr = "Company Like '%{qText.text}%' AND prim_bus= '{industrycb.selectedItem}'"           }               if (qText.text =="" && countycb.selectedItem != " " && industrycb.selectedItem != " ")           {      expr = "County = '{countycb.selectedItem}' AND prim_bus= '{industrycb.selectedItem}'"           }                     querytask.execute(query, new AsyncResponder (onResult, onFault));     function onResult(featureSet:FeatureSet, token:Object = null):void     {            if (featureSet.features.length == 0)      {       Alert.show("No matching records found. Please try again.");       resizableDraggableTitleWindow.visible = false;       querydg.visible = false;       info.text = " ";      }             else             {       querydg.visible = true;       resizableDraggableTitleWindow.visible = true;       querydg.rowCount = querydg.dataProvider.length;       myGraphicslayer.visible = true;              import com.esri.ags.utils.GraphicUtil;      }                         if (featureSet.features.length > 1) {       info.text = "There are " + featureSet.features.length + " matching records";             }      if (featureSet.features.length == 1) {       info.text = "There is " + featureSet.features.length + " matching record";      }                 }                    function onFault(info:Object, token:Object = null):void     {      Alert.show(info.toString(), "Query Problem");     }              }       

[ionarawilson1]

Here is a picture of what the website looks like[ATTACH=CONFIG]16032[/ATTACH]

[RobertScheitlin__GISP]

Ironara,

  You have an issue in the sequencing of your code. You are setting the query.where to expr and then in the next line setting expr to a new string and then setting the actual expr string... You need to set query.where to expr after you have done everything you want to do to that string.

Don't forget to click the Mark as answer check on this post and to click the top arrow (promote).
Follow the steps as shown in the below graphic:

[KenBuja]

An easier way would be to build the expression where you add in each component if it's selected. Here's one way (completely untested)

var expr:String = new String

if (qText.text != "") {expr = "Company Like '%{qText.text}%'";}

if (countycb.selectedItem != " ")
  {
    if (expr != "") {expr += expr + " AND County = '{countycb.selectedItem}'";}
    else {expr = "County = '{countycb.selectedItem}'";}
  }

if (industrycb.selectedItem != " " )
  {
    if (expr != "") {expr += expr + " AND prim_bus = '{industrycb.selectedItem}'";}
    else {expr = "prim_bus = '{industrycb.selectedItem}'";}
  }

[ionarawilson1]

Thank you Ken, I tried your code and it does not work

It seems that if I try anything other than exactly specifying the string, it does not work. For example

if I say

query.where = "Company = 'Terry Manufacturing Company' "

it works

but if I say query.where = "Company Like '%{qText.text}%' "

or

query.where = "County = '{countycb.selectedItem}'"

nothing works! I thing it has to do with expecting a string but I am not sure

Here is the code that works with the expressions I want to include being commented out

 [Bindable]
   private var querytask:QueryTask = new QueryTask();
   [Bindable]
   private var query:Query = new Query();
   // to clear the company layer when searching for counties
   
 
    
   protected function myMap_initializeHandler(event:MapEvent):void
   {
    
    querytask.url = "http://tfs-24279/ArcGIS/rest/services/ForestProducts/dynamic_layer_forest_products/MapServer/0"
    
    querytask.useAMF = false;

    query.outSpatialReference = myMap.spatialReference;
    query.outFields = ["*"];
    query.returnGeometry = true;
    query.where = "Company = 'Terry Manufacturing Company'"
    var expr:String = new String();
  
   /*   
      if (qText.text != "" && countycb.selectedItem != " " && industrycb.selectedItem != " ")
     
     {
     expr="Company Like '%{qText.text}%' AND County = '{countycb.selectedItem}' AND prim_bus = '{industrycb.selectedItem}'"
     
     }
     
     if (qText.text != " "  && countycb.selectedItem == " " && industrycb.selectedItem == " " )
     
     {
      expr= "Company Like '%{qText.text}%' "
     
     }
     
     
     
     
     if (qText.text =="" && countycb.selectedItem != " " && industrycb.selectedItem == " " )
     
     {
      expr="County = '{countycb.selectedItem}'"
     }
     
     
     
     if (qText.text =="" && countycb.selectedItem == " " && industrycb.selectedItem != " " )
     
     {
      expr= "prim_bus = '{industrycb.selectedItem}'"
     
     }
     
     if (qText.text != "" && countycb.selectedItem != " " && industrycb.selectedItem == " ")
     
     {
      expr="Company Like '%{qText.text}%' AND County = '{countycb.selectedItem}'"
     
     }
     
     
     
     
     
     
     if (qText.text != "" && countycb.selectedItem == " " && industrycb.selectedItem != " ")
     
     {
      expr = "Company Like '%{qText.text}%' AND prim_bus= '{industrycb.selectedItem}'"
     
     }
     
     
     if (qText.text =="" && countycb.selectedItem != " " && industrycb.selectedItem != " ")
     
     {
      expr = "County = '{countycb.selectedItem}' AND prim_bus= '{industrycb.selectedItem}'"
     
     }
      */
     
    
     
  
   
   
   }

   
   
   protected function buttonclick():void 
   
    
    
   
    
   {
    
    querytask.execute(query, new AsyncResponder (onResult, onFault));
    function onResult(featureSet:FeatureSet, token:Object = null):void
    {
     
     if (featureSet.features.length == 0)
     {
      Alert.show("No matching records found. Please try again.");
      resizableDraggableTitleWindow.visible = false;
      querydg.visible = false;
      info.text = " ";
      myGraphicslayer.graphicProvider = querytask.executeLastResult.features
     }
      
     else
      
     {
      querydg.visible = true;
      resizableDraggableTitleWindow.visible = true;
      myGraphicslayer.visible = true;
      myGraphicslayer.graphicProvider = querytask.executeLastResult.features
      querydg.height=querytask.executeLastResult.attributes.length + 150
      querydg.dataProvider= querytask.executeLastResult.attributes
      querydg.visible=querytask.executeLastResult != null
     }
     
     

     
     if (featureSet.features.length > 1) {
      info.text = "There are " + featureSet.features.length + " matching records";
      
     }
     if (featureSet.features.length == 1) {
      info.text = "There is " + featureSet.features.length + " matching record";
     }
     
     
    }
    
    
    
    function onFault(info:Object, token:Object = null):void
    {
     Alert.show(info.toString(), "Query Problem");
    }
    
    
   }
    

[ionarawilson1]

But can you please explain your code to me? Thank you!!!

An easier way would be to build the expression where you add in each component if it's selected. Here's one way (completely untested)

var expr:String = new String

if (qText.text != "") {expr = "Company Like '%{qText.text}%'";}

if (countycb.selectedItem != " ")
  {
    if (expr != "") {expr += expr + " AND County = '{countycb.selectedItem}'";}
    else {expr = "County = '{countycb.selectedItem}'";}
  }

if (industrycb.selectedItem != " " )
  {
    if (expr != "") {expr += expr + " AND prim_bus = '{industrycb.selectedItem}'";}
    else {expr = "prim_bus = '{industrycb.selectedItem}'";}
  }

[RobertScheitlin__GISP]

Ionara,

   You are trying to build a string so it should look like:

query.where = "Company Like '%" + qText.text + "%' "

not

query.where = "Company Like '%{qText.text}%' "

[ionarawilson1]

Awesome Robert! Thanks!!! It worked! And how about for the selected items of the combo boxes?

Here are the comboboxes (I deleted some of the counties so it would not be too long to post here):

<s:ComboBox id="countycb"
      width="100%"
      requireSelection="true"
      selectedItem=" "   >
    <s:ArrayCollection id="countycbarray" >
     
     <fx:String>Anderson</fx:String>
     <fx:String>Andrews</fx:String>
     <fx:String>Yoakum</fx:String>
     <fx:String>Young</fx:String>
     <fx:String>Zapata</fx:String>
     <fx:String>Zavala</fx:String>
     
    </s:ArrayCollection>
   </s:ComboBox>
   
  </s:Panel>
  
  
  
  
  <s:Panel height="60"
     width="230"
     backgroundColor="0xB2BFC6"
     title="Search for Industry Type:">
   
   
   <s:ComboBox id="industrycb"
      width="100%"
      requireSelection="true"
      selectedItem=" "  >
    <s:ArrayCollection>
     
     <fx:String>chemicals, wood</fx:String>
     <fx:String>chip mill</fx:String>
     <fx:String>composite board mill</fx:String>
     <fx:String>container mill</fx:String>
     <fx:String>cooking wood</fx:String>
     <fx:String>dry kiln</fx:String>
     <fx:String>engineered wood</fx:String>
     <fx:String>fencing</fx:String>
     <fx:String>firewood</fx:String>
     <fx:String>gathering yard</fx:String>
     <fx:String>hardwood mat mill</fx:String>
     <fx:String>import/export</fx:String>
     <fx:String>landscape organics</fx:String>
     <fx:String>pallet mill</fx:String>
     <fx:String>paper mill</fx:String>
     <fx:String>planing mill</fx:String>
     <fx:String>plywood plant</fx:String>
     <fx:String>posts/poles/pilings</fx:String>
     <fx:String>preservation, wood</fx:String>
     <fx:String>re-saw mill</fx:String>
     <fx:String>sawmill</fx:String>
     <fx:String>secondary manufacturing</fx:String>
     <fx:String>shaving mill</fx:String>
     <fx:String>timber-frame homes</fx:String>
     <fx:String>veneer</fx:String>
     <fx:String>wood pellet mill</fx:String>
     
     
    </s:ArrayCollection>
   </s:ComboBox>

[ionarawilson1]

Robert, I just noticed it selected all the records. Why would that be?

Here is the code with the expression you provided

protected function myMap_initializeHandler(event:MapEvent):void
   {
   
   
   
  

    querytask.url = "http://tfs-24279/ArcGIS/rest/services/ForestProducts/dynamic_layer_forest_products/MapServer/0"
   
    querytask.useAMF = false;

    query.outSpatialReference = myMap.spatialReference;
    query.outFields = ["Company", "County", "Address", "CITY", "ZIP", "Phone1", "Homepage", "prim_bus"]
    query.returnGeometry = true;
    query.where = "Company Like '%" + qText.text +"%' "

[KenBuja]

But can you please explain your code to me? Thank you!!!

Sure.

In the first If statement, it checks if anything was entered in the qText and if so, it puts that into the Expr variable

The next If statement checks if anything was entered in the countycb combobox.. If so, then if there is something in the Expr variable (meaning the first If statement was true), then it appends "AND County = '{countycb.selectedItem}'" to the variable. If not, it just puts "County = '{countycb.selectedItem}'" into the variable.

Finally, the third If statement check if anything was entered in the industrycb combobox. If so, then if there is something in the Expr variable (meaning either of the first two If statement were true), then it appends "AND prim_bus = '{industrycb.selectedItem}'" to the  variable. If not, it just puts "prim_bus = '{industrycb.selectedItem}'" into  the variable.