Forum Discussion

MathewCrist's avatar
MathewCrist
Qrew Member
5 years ago

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

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
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago

    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
    ------------------------------

    • MathewCrist's avatar
      MathewCrist
      Qrew Member
      5 years ago
      Thanks Mark! It worked.

      ------------------------------
      Mathew Crist
      ------------------------------
    • HunterB's avatar
      HunterB
      Qrew Cadet
      2 years ago

      Mark,

      I did what you suggested and it did change the State field to the abbreviations but when the user uses the address search function the state field will not populate. Is there anyway around this? I am using the new form style, maybe that has something to do with it.



      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        2 years ago

        Catie, I'm not understanding your question.

        When you use the built in search function for the Address, it should auto complete with the full state name.  The state abbreviation will be a separate formula field.

        can you help me understand your question?  I might need a screen shot to understand the question.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------