WHERE clause with two field parameters, one being dropdown list selected item. How to do it?

Justin Kraemer

1. März 2022 21:11

I am trying to enable the autocomplete suggestions to include the value in the autocomplete box but limit the results based on the selected value of the dropdown list below it. I have tried it in many unsuccessful ways, but the current format is this:

=`UPPER(CivicAddress) Like UPPER('${$textReplace4.result}%') AND "Municipality = '" +$searchForm.state.dropDownListMunicipalityCA.value` + "'"

The autocomplete succeeds if I leave out the portion starting from "AND" and retain the ending ` symbol. Doing so enables the autocomplete to function. But by setting the query as above, the autocomplete never presents any choices. I do not know what is wrong. Can someone please point me to the solution? The value =$searchForm.state.dropDownListMunicipalityCA.value when placed into an Alert does show the correct value, so I am baffled. Please help.

Thanks,
Justin

0

• 

  Patrick Fingler

  1. März 2022 23:47

  Hi Justin,
  
  I think the issue is due to your syntax.  It looks like you are combining the template literal expression syntax with the concatenation expression syntax.  We have some documentation describing the differences of these expression types here:
  https://docs.geocortex.com/workflow/latest/help/Default.htm#wf5/help/expressions.htm#use-string-expressions?TocPath=Expressions%257C_____2
  
  If using template literals, I think this should do the trick:
  =`UPPER(CivicAddress) Like UPPER('${$textReplace4.result}%') AND Municipality = ' ${$searchForm.state.dropDownListMunicipalityCA.value}'`
  
  This would be an alternative if using concatentation:
  ="UPPER(CivicAddress) LIKE UPPER('" + $textReplace4.result + "%') AND Municipality = '" + $searchForm.state.dropDownListMunicipalityCA.value + "'"
  
  Hope that helps.
  
  -- Patrick

  1
• 

  Justin Kraemer

  2. März 2022 14:13

  Thanks very much Patrick. While the first one using template literals behaved no better than my own attempts, the second one did the trick. Now to handle when the dropdown has the default of Entire County selected, which has a value of '%' I made the autocomplete behave just like in Essentials 4 Workflow by modifying the query to this:

  ="UPPER(CivicAddress) LIKE UPPER('" + $textReplace4.result + "%') AND MUNICIPALITY LIKE '" + $searchForm.state.dropDownListMunicipalityCA.value + "%'"

  Thanks again Patrick,
  Justin

  0
• 

  Justin Kraemer

  2. März 2022 17:23

  I have another challenge with a similar autocomplete box with query

  ="UPPER(LEGAL_TEXT) LIKE UPPER('" + $textReplace8.result + "%') AND MUN LIKE '" + $searchForm.state.dropDownListMunicipalityLegalText.value + "%'"

  Is something wrong in this syntax that I have missed? After typing the first 10 characters into the autocomplete box, it never shows anything besides "Working..."
  
  

  0
• 

  Patrick Fingler

  2. März 2022 22:23

  Hi Justin,
  
  It could be that you may not have properly configured a value for your drop down list.  I would use the console logs to drill into what inputs are being sent to the where clause for your query activity and try to provide that same where clause at your ArcGIS Server Query Rest Endpoint to see if it returns any valid results.
  
  Instructions on how to interpret the console logs can be found here:
  https://docs.geocortex.com/workflow/latest/help/Default.htm#wf5/help/logging.htm#Interpret_a_Verbose_Log?TocPath=Authoring%2520Aids%257CUse%2520the%2520Console%2520to%2520Debug%2520Workflows%257CInterpret%2520a%2520Verbose%2520Log%257C_____0

  Thanks,
  
  -- Patrick

  0
• 

  Justin Kraemer

  3. März 2022 19:33

  Thank you Patrick. I checked the drop down list and it's got the same values as the one that works with my first query. I must set this task aside for next while to work on other things but will return and try the console logs, so thank you for that suggestion

  0
• 

  Ken Lyon

  3. März 2022 23:06

  Hi Justin,

  Just to add to Patrick's suggestion about troubleshooting via the console - I would suggest you construct the WHERE clause via a Create Value activity so that you can see the fully-constructed value before it is sent.

  Also, I think the use of UPPER() might not always be necessary. You'll be able to confirm this via trial and error. I seem to recall that if the collation of the table is case-insensitive, it would work without it. Having said that, I expect you likely added it because you needed to.

   

  1
• 

  Justin Kraemer

  9. März 2022 19:33

  Thank you Ken. Your suggestion of using a Create Value activity was a great one. I insert it and tested the query it returned at the map service REST endpoint and found it failed when putting LEGAL_TEXT into the fields to return parameter in place of *. The LEGAL_TEXT field is from a join to the parcels in that map service, but that same table is published on its own in the map service. So by changing the Url for the Query Layer of the autocomplete from the parcel layer to instead point directly to the table, the autocomplete behaviour succeeds! All set now. 

  0
• 

  Ken Lyon

  9. März 2022 19:43

  That's great new, Justin. I'm glad we got it sorted!

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.