How can I use the inputs from 2 checkgroups in a query and then display results?
I'm building a form for a user to query a data layer of available services. I have a form with 2 different checkgroups - one selects the city (or cities), and one selects the service type(s). I have each of them going to 2 separate arrays (cities goes to value7, services goes to value5). The query works for both individually as WHERE =$value7.result.join(" or ") or WHERE =$value5.result.join(" or ") but I can't figure out how to combine them so the query uses both arrays. What would the proper syntax be for this?
The other thing I tried was to query the layer WHERE=$value7.result.join(" or "), and then do a For Each using IF =$value5.result.join(" or "), then if True, add those results to a new array and display that, but it's not working, and I'm guessing the issue is in the If statement.
Thanks very much for the help!
-
This might be a bit easier to write if you use Template literals.
There are a few things you need to combine for it to work. I think the syntax of the WHERE clause that you should be aiming for is something like:
CITY IN ('a', 'b', 'c') AND SERVICE IN ('d', 'e', 'f')- I'm using "CITY" and "SERVICE" as the column names, but they are probably something different.
- Note that each value must be wrapped in single quotes.
- Note that each set of values must be comma-separated.
When we combine all of that into an expression, it looks something like this:
=`CITY IN ('${$value7.result.join("','")}') AND SERVICE IN ('${$value5.result.join("','")}')`I'll try to break this down as best as I can.
- The whole string is surrounded in backticks characters. (`) This is a special syntax allowing you to include javascript expressions inside the string. You could do the same with strings and expressions separated with plus (+) characters, but it's a little easier to use.
- Every expression is wrapped in ${ }.
- Whenever we join the array values, we include a quote, comma and another quote. The first quote is for the end of the previous value in the array and the second quote is for the start of the next value in the array.
- We always need a quote before and after the "join" for before the first value and after the last value.
0 -
PS: I'd also encourage you to rename "value7" and "value5" to make them easier to remember when you come back to it in future.
0 -
Thank you so much @Ken Lyon?! This works great. Also I really appreciate your breaking down the expression, that's such a huge help for future reference, and makes sense regarding renaming the values.
0 -
You're welcome. Glad it helped!
0 -
@Jessica Dell? I just thought of an important caveat with this approach. As we are concatenating the parts of the WHERE clause together, there are a few things that could break it.
- If any of the cities or service types contains the apostrophe (i.e. single-quote) character, there will be a syntax error in the expression because it will be interpreted as the end of the string. To solve this problem, you need to escape them to tell SQL that it's meant as an actual apostrophe, not the end of the text. To do this, every apostrophe in a city or service type must be replaced by two. Example: "Martha's Vineyard" becomes "Martha''s Vineyard". This could be done to your array prior to constructing the WHERE clause.
- If any of the values for cities or service types are entered by the end user, there's a risk of SQL injection. Generally, the same approach as above will work for this too. Make sure every apostrophe is escaped.
0 -
@Ken Lyon? Thank you very much for following up on this; we actually do have some services with apostrophes. Would I just use the Text Replace or Regular Expression Replace tool before the query runs?
0 -
@Jessica Dell? You could use a For Each activity that contains a Text Replace (to replace ' with '') followed by Evaluate Expression (to update $forEach1.item).
NOTE: This will only work if the Items input of the For Each activity is a collection of objects, not strings. The reason being that you cannot replace $forEach1.item, you can only change its properties.
For example, this input would not work:
= [ "abc", "def", "ghi", "Martha's Vineyard" ]This input would work:
= [ {name: "abc"}, {name: "def"}, {name: "ghi"}, {name: "Martha's Vineyard"} ]0 -
@Ken Lyon? Okay, I think I got it (sort of). So would I then need to put the results of the Evaluate Expression into a new array? (Also I've determined that I can easily change the data containing apostrophes with no consequence, so I may just end up going that route. The user wont be entering any of this data in, just selecting from a drop down or check box, so that part will be easy to manage.)
0 -
If you only have an array of strings, then yes your loop could push the new values into an array that was created before your loop.
I have an example of Escaping Apostrophes that uses an array of objects.
0
Du måste logga in om du vill lämna en kommentar.
Kommentarer
9 kommentarer