Working with Address data Hi folks, Our con

[David_Ackling-J]

Working with Address data

Hi folks,

Our consultants are on the road, driving from client to client. Each client is in Appsheet with an Adress data type.

I would like to be able to extract the City from an Address field, so staff can group their clients by City and plan their visits.

Is there a function to extract the City property from an Address data object?

e.g. if I could I have a calculated column with the expression

city([Address])

that would be great.

cheers,

David.

[Adam-google]

It would be a good feature, but we don’t have this ability now. You can submit a feature request here:

AppSheet : mobile apps from spreadsheets : Login appsheet.com

[Aleksi_Alkio]

If you have it in your address value and it’s the last value separated with comma, you could split that value. Would that work for you?

[Steven_Coile]

Instead of collecting the address as a single value, you could collect the individual components (street address, city, state, etc.). If you then need a consolidated address (e.g., for mapping), you could assemble it from the components in a virtual column.

[David_Ackling-J]

I love the autocomplete feature of the address input though. It is great.

Can you see a way to add a public method to your address class to allow a function to extract properties from an address object?

[rgckd]

I got around this by writing an appscript function in the underlying google sheet to convert the address field into city:

function ConvertToCity(address) {

  if (!address) { return null };

  var response = Maps.newGeocoder().geocode(address);

  var result = response.results[0];

  var arr = result.formatted_address.split(",");

  var count = arr.length;

  return arr[count - 3];

}

[Phil]

@David_Ackling-Jones

Steve is correct. You should define the address using individual fields such as Street, City, State, ZIP.

AppSheet will automatically recognize that the fields are all part of the same address. It will automatically create an Address field that is populated with the Street, City, State, and ZIP values.

The address will be geo-coded and displayed on Google maps.

See topic “Automatically Computed Address Field” in this article help.appsheet.com - Column Types: Diving Deeper

Column Types: Diving Deeper help.appsheet.com

[Liam_Tennis]

Hi Phil

Is it possible to do this in the opposite direction?

Say we input the full address, can it then automatically split into specific address columns,
Eg Address: 22 Silwood Road, Milnerton, cape town, south africa, 7700
Split to:
Street: 22 Silwood Road
Suburb: Milnerton
City: Cape Town

Thanks!
Liam

[Steve]

Liam_Tennis:

Is it possible to do this in the opposite direction?

There’s no built-in way to do that. You could try to do it with expressions, but I’d expect it to be very error-prone.

[Liam_Tennis]

Hi Steve.

Thanks for the response. I thought that would be the case. The variation in address format is just too much. Will post here if we figure out a solution to extract the specific data we are needing

Thanks again,
Liam

[Adam-google]

@David_Ackling-Jones At the point the autocomplete result comes in, it includes the structured data of which text belongs to which part of the address. But currently we are only keeping the full text of the address as the value of the column. If we only care about getting the city and other fields out at the point you’re selecting the address in the input, it would make things easier because we could have functions that just check those details. But generally functions should work wherever and whenever they run, and to support that we would have to save that structured data somewhere or commit to searching the address again every time it’s needed. Adding @praveen to consider if/how we could try to support this.

[Dan_Oliphant]

Did you ever figure out a solution here that is only somewhat error prone?

[David_Ackling-J]

Thanks Adam!!

[diego_ettore]

hi, i have a similar question and i don’t want to make a new topic (correct me if i’m wrong)
i have a “City” field, and a calculated “state/province” and “region” fields
i do not mind haev a street, it’s not needed.
May i make a calculated address column? the street could be whatever street in that city

[Giampaolo]

Hi everyone,

What if I want to extract the city from the current position?
Thank you

[Steve]

Giampaolo:

What if I want to extract the city from the current position?

Not possible with AppSheet itself.

[Giampaolo]

Thanks Steve,

alternatively is it possible to list all latlon coordinates distant
from a predefined destination?

[Steve]

You can use the DISTANCE() function to calculate the (straight-line) distance (in km) between two LatLong values. Would that help?

[Giampaolo]

I really think so. In the help, I find some material on how to use this function.
Thank you so much