App formula not updating value as expected

[Lindsey_Lindow]

I am having difficulty with a column in the "Punches" table which uses an App Formula to select the record from "Locations" table that is the closest distance to the LatLong value of HERE().

When navigating to the Form view, the App formula initially returns the correct location, but when I manually change the LatLong coordinates, the App Formula does not recalculate to reflect the other location.

Example: Upon opening the form view, the App Formula initially returns value "Naperville Recon" which is closest to me, but when I try to emulate a different location by manually changing the initial value of HERE() to coordinates close to our Chicago location, "Naperville Recon" is still displayed rather than dynamically changing to "Chicago Recon" as expected.

I have tried several different App Formulas and they all seem to have the same behavior: Displaying the correct value upon opening the form, but do not change dynamically.  App Formulas I have tried include:

• ANY(SELECT(LOCATIONS[LOCATION ID], DISTANCE([LATLONG], ANY(SELECT(LOCATIONS[LATLONG], TRUE))) <= 1))

• ANY(SELECT(LOCATIONS[LOCATION ID], DISTANCE(HERE(), [LATLONG]) <= 1))

• ANY(ORDERBY(SELECT(LOCATIONS[LOCATION ID], TRUE),DISTANCE([LATLONG], HERE()),TRUE))

• IF(ISNOTBLANK([LATLONG]),MINROW("LOCATIONS", "DISTANCE_FROM_USER"),"")

[Steve]

For a list of locations sorted by distance from HERE():

ORDERBY(
  LOCATIONS[LOCATION ID],
  DISTANCE([LATLONG], HERE()),
    FALSE
)

To get the closest, wrap the above in ANY().

[Lindsey_Lindow]

Thank you for replying Steve, I was hoping you'd see my post. I tried your expression wrapped in ANY(), but the column still won't recalculate upon manually changing the coordinates of the LatLong column.

ANY(ORDERBY(
LOCATIONS[LOCATION ID],
DISTANCE([LATLONG], HERE()),
FALSE
))

Do you have any other thoughts on what the issue could be that's causing this behavior?

[Steve]

Please post a screenshot of the configuration of the column to receive the closest location.

[Lindsey_Lindow]

I wondered if it had anything to do with the column type being Ref, so I added a text column to the table and tried the App expression there too, but it behaves the same. I also tried it in a virtual column.

[Steve]

---

@Lindsey_Lindow wrote:

when I manually change the LatLong coordinates, the App Formula does not recalculate to reflect the other location.

---

How, exactly, are you doing this?

[Lindsey_Lindow]

In the form I replace the initial value of HERE() with another row's coordinates that I copy/paste from the Locations table. Upon changing coordinates, I can see the mini map change in the form view, so I can't make sense of why the app formula isn't recalculating. I added a virtual column using expression DISTANCE([LATLONG], ANY(SELECT(LOCATIONS[LATLONG], TRUE))) to see if Appsheet was recognizing the change in distance to the nearest record of the locations table when I modify coordinates, and it does.

[Steve · Accepted solution]

Try this:

ANY(ORDERBY(
LOCATIONS[LOCATION ID],
DISTANCE([LATLONG], [_THISROW].[LATLONG]),
FALSE
))

[Lindsey_Lindow]

It works, you're a rockstar!!  Now I think I understand the issue: It was the presence of the HERE() function, wasn't it?

[Steve]

You weren't comparing the other locations' latlong to your current row's latlong.