Blog

Data - How to create a drop down list of US States

This post provides the formula to create a dropdown list of US States, including a walkthrough of how to setup an edit form to accept data entry through the dropdown.

When building apps for address entry, there's a frequent requirement to allow users to enter a US State through a dropdown.

This post describes how to carry out this task, and also demonstrates the technique to configure a dropdown control with key-value pairs. This setup stores the key value to a data source but displays the associated description in the dropdown.

Setting up a data source

This post is based on an Excel spreadsheet of addresses. In practice, we can use a SharePoint list or any other data source instead.


The pre-requisite is to create an auto-generated app based on this data source, or to add an edit form to an existing app.

Formula to create a collection of US States

A typical app will reference the state codes and descriptions in multiple places. Therefore, a good practice is to store this data in a collection. We can add the formula shown beneath to the OnStart property of the app, in order to create the collection on startup.

In design mode, note that it's necessary to select the right-click, 'Run OnStart menu item' to create the collection (shown in the image beneath).


The formula to create the collection of states follows beneath:

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

How to setup a dropdown list of US States on an edit form

To modify the state field on an edit form to display a drop-down list of US states, we click the 'edit fields' link and set the control type to Allowed Values, as shown beneath.


Next, we unlock the state card (right-click the card and select unlock) and set the Items property to the collection of US states - colStates in this example.


The Update property of the card specifies the data value to save to the data source. To save the 2 character state code, we would set the Update property to reference the DropDownControl.Selected.Code value.


To configure the dropdown control to display the state name, we set the Value property of the dropdown control to Desc.

To display the correct value when the form loads an existing record, we set the Default property to look up the description based on the code value, using the formula beneath.
LookUp(colStates, 
       Code=Parent.Default).Desc


Conclusion

On address entry screens, a common requirement is to enable the entry of US States through a dropdown. This post summarised how to carry out this task, and demonstrated how to configure a dropdown control to work with name-value pairs.
Previous
Next
Related posts