cancel
Showing results for 
Search instead for 
Did you mean: 

Automatically Prefill City and State using Zip Codes in your App

Obtain the free file from the US postal service. It is available at http://federalgovernmentzipcodes.us/  and consists of a single .cvs file for more than 42,000 cities and states by their zip codes.  It also contains several other columns but once in Excel, I just delete these. Open it with Excel and format it as a Table. Then use PowerQuery to separate the table into 3 tables of no more than 15000 rows each.  Save each table with a different name in the same Excel file.  I made mine Table1, Table2, and Table3. The reason for splitting the large table into three smaller ones is because PowerApps has an import limit of 15000 items for Static files.

 

 

zip2.PNGOpen one of your apps and add the 3 tables using the add Static files from Excel connector. This takes seconds to do. 

For this example, I used a file of free sample data for testing from the internet, so the names and addresses are bogus. https://www.briandunning.com/sample-data/ 

 

Put a button on the screen and set the OnSelect property to Collect(zips, Table1); Collect(zips, Table2); Collect(zips, Table3). (If you use this formula in the OnStart property of the App, it will always be available and you don't need the button.) Moreover,creating this collection from Static tables only takes a few seconds and the user won't notice.zip1.PNG

 

As you can see, the resulting collection consists of 42,522 rows and is called zips. Now where ever a zip code is found in your own dataset, you can use the LookUp() function to display the city and state. So, in the example, the Zip Code is abbreviated zip in the bogus datasource. In the gallery, I placed a City and State label, with the Text property as follows. 

LookUp(zips, Zipcode=ThisItem.zip, City) & ", " & LookUp(zips, Zipcode=ThisItem.zip, State)

To maintain the 5 digit format with leading zeros, wrap ThisItem.zip as follows

Text(ThisItem.zip, "00000")

This will preven PowerApps from stripping the 0 as shown in the example for Venere, Art.

Once you have the static data in your app you will never need to enter City and State again as you can look it up from the Collection. 

 

Comments

When the zip code is populated in PowerApps it strips the leading 0. Is there a way to get it to show the leading zero? When importing the tables in I made sure the Excel file had the full zip code listed.

Good point. Text(zip,"00000") should do the trick. 

I don't a Text property box. Is it possible to add it to this formula under default?

(LookUp(zips, (State=ComboBox2_2.Selected.Result &&
City=ComboBox1_2.Selected.Result), Zipcode))

(LookUp(zips, (State=ComboBox2_2.Selected.Result &&
City=ComboBox1_2.Selected.Result), Text(Zipcode,"00000")))

Having a bit of a disconnect here.  What are each of the variables and fields being passed.  Do the tables have to be loaded as part of the starup or just a data source.  

 

I have three tables called Zip1, Zip2, and Zip three with each of these fields City, State, and Zip.  

 

My PA Text Input fields are called Value23_2 (City), DropDown2 (State), and Value23_$ (Zip)

 

I have tried this numerous ways and each time I get the "Red Circle of Death".  

 

Any further assistance is appreciated.  

 

NOOB

Meet Our Blog Authors
Labels