Auto-Populate Addresses using list of all addresses in our City?

NeilKY

We have a list of EVERY address in our city. It contains 455,598 rows of addresses (Woohoo!) That's a LOT of addresses, I know. It could be trimmed down to about half that if needed.

We'd like to auto-populate fields in Smartsheet using those addresses as a reference point as the user types the address.

How do we auto-populate using this data? I can't find any how-to posts about it.

NeilKY

I was contemplating the idea of using Datatables and some queries of some sort, but that train of thought crashed as it departed the station.

Paul.Woodward

@NeilKY

That is a great question!

I personally am not aware of the ability to do that, unless you create a drop-down menu which would be so tough with as much Data as you have. Unless there is an ability to cell link your column to another MASTER sheet of addresses?

If someone knows a way around this I would LOVE to hear about it!

Lindsay Whitbread

Hi @NeilKY, I agree with @Paul.Woodward that putting 455K options in a dropdown list wouldn't work.

The only way that I can think of this being possible would be to use something like Google AppSheets to create a custom app that does that lookup, so that you would use the third party app to add and update your Smartsheet data. That way, you'd have the ability to do the address lookup the way that you want, without being constrained by Smartsheet's limits. The app would then make sure that the Smartsheet sheet was synced with the information that was added in.

If you're interested in something like this, InfoSpark can do that kind of work. You can contact them via email at solutions@infospark.com.au.

Cheers,
Lindsay

NeilKY

I was contemplating the idea of using Datatables and some queries of some sort, but that train of thought crashed as it departed the station.

Lindsay Whitbread

Yeah that's not going to work easily @NeilKY - have you managed to find a solution?

NeilKY

Well, no I haven't. Smartsheet is no Excel or Google sheets because it doesn't update realtime without a refresh usually. Even if it did, it would need to be fast enough to keep up with users typing in an address and help them select it before they finish the whole thing. So that idea is a no-go.

But, I did do something that helped me look things up slooooowwlyyyy:

1. Linked a sheet to Datatable in "Lookup Mode". The Data table contains all addresses and a bunch of other useful data about those addresses.
2. Sheet Unique identifier (Street Address) links to the datatable.
3. I type in the address in the "Type Full Address" column.
4. In 1-10 minutes, the sheet populates the city, Zipcode, ParcelID, Zoning, District, Cencust Tract, Latitude, Longitude from the linked Datatable.
5. Sheet formulas give me links directly to the Lat/long coords for accurate pinpointing.

Yeah, it's not even close to my original request but it's pretty neat :)

NeilKY

Ps. Unfortunately, sometimes my results are blank. I haven't figured out why but I know the addresses exist in the datatable. I'll update if i ever figure it out. For now, we're not using this in production but will want something reliable in the future.