topic Re: Where expression on Query Task using Action Script in ArcGIS API for Flex Questions

Where expression on Query Task using Action Script

ionarawilson1 — Thu, 12 Jul 2012 18:05:50 GMT

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");     }              }

Re: Where expression on Query Task using Action Script

ionarawilson1 — Thu, 12 Jul 2012 18:22:05 GMT

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

Re: Where expression on Query Task using Action Script

RobertScheitlin__GISP — Thu, 12 Jul 2012 18:25:33 GMT

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:

Re: Where expression on Query Task using Action Script

KenBuja — Sat, 11 Dec 2021 14:19:31 GMT

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}'";}
  }

Re: Where expression on Query Task using Action Script

ionarawilson1 — Sat, 11 Dec 2021 14:19:34 GMT

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");
    }
    
    
   }

Re: Where expression on Query Task using Action Script

ionarawilson1 — Sat, 11 Dec 2021 14:19:37 GMT

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}'";}
  }

Re: Where expression on Query Task using Action Script

RobertScheitlin__GISP — Thu, 12 Jul 2012 20:33:58 GMT

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}%' "

Re: Where expression on Query Task using Action Script

ionarawilson1 — Sat, 11 Dec 2021 14:19:39 GMT

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>

Re: Where expression on Query Task using Action Script

ionarawilson1 — Thu, 12 Jul 2012 20:46:57 GMT

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 +"%' "

Re: Where expression on Query Task using Action Script

KenBuja — Thu, 12 Jul 2012 20:52:16 GMT

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.

Re: Where expression on Query Task using Action Script

RobertScheitlin__GISP — Thu, 12 Jul 2012 20:58:54 GMT

Ionara,

Are you sure there is anything in qText.text when myMap_initializeHandler is called?

Re: Where expression on Query Task using Action Script

ionarawilson1 — Thu, 12 Jul 2012 21:14:52 GMT

Robert, I think it is blank when it is initialized, then the user will enter something, then do the search after clicking on the search button.

I am attaching the code. Thank you so much for your help!!!

Re: Where expression on Query Task using Action Script

ionarawilson1 — Thu, 12 Jul 2012 21:16:47 GMT

Thank you for the explanation Ken! I am printing the code and your explanation and will analyze tonight !!! Thanks!!!

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.

Re: Where expression on Query Task using Action Script

RobertScheitlin__GISP — Fri, 13 Jul 2012 02:55:09 GMT

Ionara,

Here is your code fixed and cleaned. There is a great command in Flash Builder source menu called "organize imports" you need to use it. Also notice the coding convention, Clean and free of excess white space (much easier to read). The main part of the fix was to build the expression in the buttonclick function.

Make sure you promote Kens posts as well.

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:

Re: Where expression on Query Task using Action Script

ionarawilson1 — Fri, 13 Jul 2012 13:31:41 GMT

Thank you Robert ! You are my hero!!! I know I need to clean up the data better and I will be more careful with that!
The only thing I added is the else condition in case the user selects nothing and all data is retrieved:

else if (qText.text == "" && industrycb.selectedItem == " " && countycb.selectedItem == " ")
    {

     expr = "1 = '1'"

    }

Thank you Ken for the help with the code explanation. You guys are so helpful. I can't thank you enough!