Give user state names, but filter in query on state abbreviations

I need the user to select a state, then I'll query a table using that state. However, users aren't so great with the two-letter state abbreviations, sometimes. So it'd be swell to offer them the full state name.

What's the best way to give the state name in a select component, but use the state abbreviation in a WHERE statement in a query?

Note: I don't have a table with states and abbreviations. I can use any kind of key/value system to create that, so whatever works best, I'll create it (and share it here for the next guy that fights with this situation).

Hi @mathfour ! :wave:

The select component could lend itself nicely to this use case since it has labels & values.

One option could be to hardcode a JS query with each state like this [{abbreviation: 'WI', name: 'Wisconsin'}, {abbreviation: 'CA', name: 'California'}...]

Then, your select can be set up like this where the selected value property is only the abbreviation (but the user only sees the full name):

You could also grab the array of arrays from this library that has all the states & abbreviations:

Thanks, @Tess!

Here's the rub - I am not using ALL 50 states (or at least not right now). So I have a query for just the states that need to be used:


And then I use that query in the select component:


I was thinking I could use something like you show in "Data source" in your screenshots in a transformer and then use that in the label part. Like in the very bottom of this:


But I don't know what that transformer would look like. Or if it's even a transformer.

  1. Am I on the right track?
  2. If so, what would some_transformer_that_gives_full_state_names look like?


Ah, I see! I'd probably put a JS transformer on the query to add the property to the query that is populating the select.

For example, I have a JS query that returns some abbreviations:

Then, I have a transformer that uses an array of arrays for all states' names & abbreviations & finds the corresponding name for each of my abbreviations:

Could that work for your use case?

Thanks, @Tess!

I'm a bit confused - your first screenshot is a Run JS Code query called stateQuery, but your second screenshot is also called stateQuery. How are you using the first screenshot query together with the thing in the second screenshot?

And what kind of query/resource is in the second screenshot?

Also, in lines 55 and 56 in the second screenshot, you use the word name three times. Must all those be the same? Or can they be state_name, st_name and name_of_state? What connects each of the three of those, if anything?

And you are using data (line 53) - I'm guessing you're assigning data above? Instead of using data in this, can you use my_data or something to make it clear that it's a human assigned term and not a language (sql or JS) specific word, please?


Hi @mathfour

It's a JS transformer on the stateQuery (so only 1 query total)! Apologies, it was hard to capture in a single screenshot

No, it doesn't need to be name. It could be any variable, but they should all be the same, except for the x['name'] part--you can make 'name' whatever you want the property to be called. You could also consolidate this to: x['property_for_state_name'] = states.find(y=>y.includes(x.abbr))[0]

Since this is a transformer on a query, data is already pre-defined as the response from the query.

Hope that helps! Let me know