Need to transform data into a new field: State name = State abbreviation

Question

When a new 'Lead' entry is created in my app, the field for Address: State/Region is fully spelled out (i.e. Alabama). We've set it up this way and this part can't be changed.

What I want to happen is this:
Anytime a new 'Lead' entry is created, I want to create an action or formula that creates a separate field for the State abbreviation.

For instance:
A new lead comes in. The state address is California.
Need to create new field entry of CA for this lead.

How can I do this?

FYI: There's another post that is somewhat related, but I couldn't figure out how to execute based on these instructions:
Reference: https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MID=30396&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

{{ "Alabama"|replace("Alabama", "AL") }}

Thanks,

Matt

------------------------------
Mathew Crist
------------------------------

MarkShnierYou · Answer

The answer was in that other post but just create a new text formula field

var text RawState = [Address: State/Region]; // your current address state field goes here. Note that address fields
// are different in that they have 6 subfields. Look at the field properties to see the 6 sub field components.

Case($RawState,
"Alabama", "AL",
"Alaska", "AK",
"Arizona", "AZ",
"Arkansas", "AR",
"California", "CA",
"Colorado", "CO",
"Connecticut", "CT",
"Delaware", "DE",
"Florida", "FL",
"Georgia", "GA",
"Hawaii", "HI",
"Idaho", "ID",
"Illinois", "IL",
"Indiana", "IN",
"Iowa", "IA",
"Kansas", "KS",
"Kentucky", "KY",
"Louisiana", "LA",
"Maine", "ME",
"Maryland", "MD",
"Massachusetts", "MA",
"Michigan", "MI",
"Minnesota", "MN",
"Mississippi", "MS",
"Missouri", "MO",
"Montana", "MT",
"Nebraska", "NE",
"Nevada", "NV",
"New Hampshire", "NH",
"New Jersey", "NJ",
"New Mexico", "NM",
"New York", "NY",
"North Carolina", "NC",
"North Dakota", "ND",
"Ohio", "OH",
"Oklahoma", "OK",
"Oregon", "OR",
"Pennsylvania", "PA",
"Rhode Island", "RI",
"South Carolina", "SC",
"South Dakota", "SD",
"Tennessee", "TN",
"Texas", "TX",
"Utah", "UT",
"Vermont", "VT",
"Virginia", "VA",
"Washington", "WA",
"West Virginia", "WV",
"Wisconsin", "WI",
"Wyoming", "WY", $RawState)

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community