Assign, and Dictionary Question key values

I have an email automation where i get a city and state from an email. The state data is apprveiated, California = CA, Montana = MT and so on. Once I retrieve the CA for the state of California I have a drop list where i need to pick the state. So for CA I would have to choose California from the drop down list. I used an assign activity to get the appreviation for the state =System.Text.RegularExpressions.Regex.Match(strBody, " (AL|AK|AZ|AR|CA|CO|CT|DE|DC|FL|GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|TX|UT|VT|VA|WA|WV|WI|WY)\s\d{5}").value

I’m trying to figure out how I would choose California from the drop down list once i retrieved the state abbreviation… My thinking is I would create a data table that has the states and there abbreviations? Any suggestions or workflows?

Hello!

For clarity, I am going to assume that the state abbreviation is reliably pulled and is assigned to a variable.

str_State = “CA”

Your idea would be a sound approach in this instance. You could have an excel sheet that is read into memory as a DataTable object (let’s call it dt_States) and every time in your workflow when you retrieve/process this “state” that is retrieved then you would:

Let’s assume your column at the 0 index = “Abbreviations” as the header and column at index 1 = “State”

Iterate through each row of the data table

For Each (row) in (dt_States)

Iterate through (for each row of) the data table

if str_State.ToLower().Equals(row(“Abbreviations”).ToLower().ToString)

(I convert both strings to lower to ensure equal comparison, may not be needed in your use case)

then you can just grab the string value from the row at the 2nd column index/column in the iteration and break the for each once you find the value you’re looking for.


Alternatively, as your title suggests you could instantiate a Dictionary<String, String> with the abbreviations being the keys so then you retrieve a value as such:

dict_States(str_Abbreviation).ToString

The value associated with each abbreviation would be the full state name.


If you need more ideas or a workflow example, please let me know!

Garett,
Sorry it took so long to respond was out on vacation. Thank you for the response and walk through i will try it out today and let you know if i need anymore help if. Thanks Again brotha!


us_states.xlsx (9.7 KB)
Garett,
Im getting closer to solving this, i have attached a screen shot of my automation with some explanations. I have also attached the state excel file i am trying to pull from. Could you look it over and let me know if you have any suggestions?

Hello!

My first suggestion is to instead use a Matches activity rather than using an Assign.

I noticed the regex in your screenshot only accounted for a space at the end so I tried it with a \s character prefixing the regex expression:

Then I worked out what you’re doing in my own pseudo workflow so you can see what I did and I added annotations on the activities to help explain. Please let me know if you have any further questions, concerns or just want to discuss it. :slight_smile:

Here’s a GIF of the workflow I’ve attached below and how it works:

XYKaf2ftsj

Example workflow:
StateAbbrQuestion.xaml (13.2 KB)

Edit: Forgot to add, you would just use the str_FullState variable as shown in my workflow and put that into your “Select Item” activity to make it dynamic.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.