Solved: Question: Is it possible to do this?

retailpartnerco · 05-17-2020 05:49 AM

Hi! I have a list of store locations in my app. My users often visit these stores and write reports for them. Is it possible in AppSheet to use the user’s current location to find the store that they currently are at (I have an Address column in the table), or is it possible to rank stores based on how close they are to the user (probably not, just want to make sure)?. Thank you!

Steve

Yep, it’s possible, so long as each store has its LatLong location (in column Location, e.g.). The closest store to the user can be found with:

ANY(
  ORDERBY(
    FILTER("Stores", TRUE),
    DISTANCE(HERE(), [Location])
  )
)

See also:

retailpartnerco

How do I convert address to Lat Long using virtual column? Thanks!

Have a Wonderful Day! - Nick Barry RPC

Steve

You cannot, at least not within AppSheet. You’ll have to get the coordinates some other way.

retailpartnerco

Thanks! I found a google add-on that works great. “Geocode by Awesome Table”

retailpartnerco

Also, I have a follow up question. Where do I plug in your formula? Do I make a slice? Also,

ORDERBY(FILTER(“Master Store List”,TRUE),DISTANCE(HERE(),[Latitude, Longitude]))

By removing the ANY part, it should include the full list, right, just ranked instead of 1 value? Thanky ou!

retailpartnerco

My end goal is to have a table view for my user that shows the stores closest to him, ranked.

Steve

Depends on how you want to use it.

If you want to capture the location without the user interaction (and possibly without their knowledge), use the expression with ANY() as the Initial value expression of the column to contain the location.

If you want to give the user the opportunity to choose their location, use the expression without ANY() as the Valid If expression of the column to store the user’s location.

If you set both Initial value and Valid If as described above, the location will be auto-filled so the user doesn’t need to do anything if it’s correct, but will have a sorted dropdown to choose from if the user needs to correct the auto-chosen value.

Correct. You could optionally wrap the ORDERBY() expression with TOP() if you wanted to limit the number of choices to, say, the closest 5, or the closest 10.

See also:

retailpartnerco

Sorry for my many follow up questions, and thankyou for your help, but how would I acheive a table view ranked by how close a user is to them? My goal is to have a table with 5 locations, and the top one is the closest location, the second one is the second closest, and so on. Is this possible? Again, thankyou very much!

retailpartnerco

I figured it out. I made a virtual DISTANCE column using your distance formula, and then I sorted the table by those values ascending, so the ones with the least distance from current location would be at the top. Thank you very much, this is a cool feature I didn’t know existed!

EHong

Hi Nick, I’m trying to do the same thing as you but not sure how to get the table view on my start page to sort by closest distance to the user. Could you explain how you did that? I have each Location LatLong setup and sorted the Table by Location (Ascending/Descending) but can’t get the table view to update properly even though the map shows my current location HERE(). Thanks! @Steve

Steve

The table would need a virtual column with an App formula expression like:

DISTANCE([latlong-column], HERE())

replacing latlong-column with the name of the column containing the location associated with the row. Then sort the view by this virtual column.