Sorting by distance

[Diarmuid_O_Brie]

in a particular form I want a drop down menu of Airfields, pulled from an ‘Airfields’ table that includes index, name and LatLong columns. This bit is easy, however I want to sort the options in the dropdown menu in order of distance from current position [most likely selection will be first, as that’s where the user will likely be physically located at the time], with the initial value pre-populated with the closest.

So far I have created a [Distance] VC in the Locations table with the following formula:

distance( here(), [_THISROW].AirfieldLatLong] )

I was expecting this to give me the distance from current position for all Locations (Airfields), but when I ‘view data’ I get the below, with distances looking like they’re using my current position as 0.00,0.00

However, when I then view the table data in a UX I get them all listed in the correct order, showing the correct distance

I then tried to use the below formula to lookup the airfield name to set the initial value to the nearest, but it just returns the first in the list [which it thinks is nearly 6000km away, but actually more like 200, and not the closest]

lookup(
min( Airfields[Distance] ), ‘Airfields’, ‘Distance’, ‘AirfieldName’
)

So, my questions:

• how come the UX can correctly show distances when the table view cannot?
• what’s the best way to achieve what I need, in terms of sorting the dropdown list in order of [Distance], using a ref data type, and pre-populating the initial value

Many thanks in advance for any advice

[Steve · Accepted solution]

When you use the Test feature from Expression Assistant, the current location provided by NOW() isn’t entirely reliable. With Test, the expression is actually evaluated by an AppSheet server somewhere else. I don’t know what location the server provides–it looks like it might provide no location.

As for sorting your dropdown, you don’t need the virtual column. Instead, try as your dropdown column’s Valid If expression:

ORDERBY(
  FILTER("Airfields", TRUE),
  DISTANCE(HERE(), [AirfieldLatLong])
)

See also:

help.appsheet.com

ORDERBY()

Sort row references

[Diarmuid_O_Brie]

thanks Steve. That’s worked a treat. Whilst I’m not sure why my method didn’t work, this is much simpler too.
thanks again

[Diarmuid_O_Brie]

Actually, one final question on this, while this method neatly gives me the order for the dropdown option, I also want to pre-populate with the closest record. To do this I can use the same [or similar] expression in the ‘initial value’ field. However, ordering the records [by distance] and then selecting the TOP one, I get a conflict of data type as the expression expects a list. How can I determine the initial value to be the closest record for this Ref data type? thanks

[Steve]

To get the closest one for Initial value, wrap the same expression used for Valid If with ANY():

ANY(
  ORDERBY(
    FILTER("Airfields", TRUE),
    DISTANCE(HERE(), [AirfieldLatLong])
  )
)

[Diarmuid_O_Brie]

outstanding, thanks a lot Steve. I was curious as to why ANY returned the first record, when it’s definition suggests it pulls an ‘arbitrary value’, but the following sterling article was also very useful

ANY() | AppSheet Help Center

thanks again

[Steve]

ANY() always returns the first item in the given list. But, because some methods for generating lists may not produce lists in a predictable order, some app creators might be confused when ANY() doesn’t return the item they expect. So the description is an attempt to avoid confusion from inaccurate expectations.

For instance, SELECT() is not guaranteed to produce its list in any particular order, but an app creator may expect the results to be in row number order. The only way to guarantee the order of the SELECT() results is to wrap it in SORT() or ORDERBY().