Ability to auto select nearest site

Hello, I have a quote that calculates pricing based on the depot the item is shipping from. In the past the user selects the depot and inputs a destination zip code and the system runs the mileage based on the distance between zip codes. We’d like the quote to now “automatically” select the NEAREST depot to the destination being entered (vs. selecting the depot) and then proceed with the regular calculations.

  • Calculations are from zip code to zip code so I have a US Zip Codes table already in use and have the geopoints for all the company’s depots in the depot table. (vir_depot_lat_long)
  • When the sales rep inputs the Destination Zip in the quote - I have a virtual field that determines the geopoint for the destination zip (vir_dest_latlong)

Now to determine the nearest Depot (Depot initial value), I created a virtual field in the quote but I’m getting the error below - can someone assist with the right expression?

1 20 993
20 REPLIES 20

MultiTech
Participant V

One thing you might try that might make things a little easier, is to create a virtual column on your depot table that determines the distance between the users location, pulled using HERE(), and the depot geopoint. (I’ll call it - [Live_Distance] for the rest of this example.)

Distance(
     [vir_depot_lat_long], 
     HERE()
)

Then, you can use MINROW() to pull the closest depot:

MINROW(Depots, Live_Distance)


There are other ways, but this seems the simplest.

They’ll be looking for the nearest depot to the destination zip they’ve entered in the quote. So would I replace the HERE() with the Destination Zip Entered? Just seems odd as that virtual field in the depot table would actually not be useful unless they’re inputting a quote.

The problem with DISTANCE() is that you have to use latlongs, you won’t be able to use just a zip code.

Oh yes, sorry I was referring to the geopoint for the destination zip vs. Here(). Does that sound like it would work?

@MultiTech_Visions @Daisy_Ramirez

Please, If you don’t mind I need more details such as App formula, or screen shot for the significant items. I have same idea to apply it.
Thank you very much to all

I’ve tried this way, I created a virtual column DISTANCE for my table POSITIONS that is supposed to calculate the distance between that position and another given position (Distance([ThisRowLatLongField],[TheGivenPosition]) and it works fine, but when I try to extract the lowest distance from the table by using MINROW(Positions, Distance) I continue to get the wrong result: the first row of the table… I tried using MIN(Positions[Distance]) to understand what value was it picking and I got 5167,08 instead of the real min value (in this case 0,18).

Someone has any idea of what’s happening?

The following is the formula that I would use to put things in order:

OrderBy(Positions[PositionID], [Distance])

  • From here, you can easily get the closest and farthest with:
    INDEX(OrderBy(Positions[PositionID], [Distance]), 1)
    INDEX(OrderBy(Positions[PositionID], [Distance]), COUNT(Positions[PositionID]))

  • What’s likely happening here is the LatLong reported by the system is not where you’re thinking.
    • This usually happens inside the editor, it can get off (especially if you’re using a PC, where your position is determined by your IP mostly).

Thanks, I’ll try using these expressions but I’m not sure that would solve the problem because what’s wrong is the value that the formula is reading. For the given position I’m not using a method like HERE() but I’m taking it from another table with a SELECT, and the distance values in the virtual column are correct!

Hmmm… so the data in the table/VCs are correct, but the result of the formula is wrong?

  • It’s extremely rare that the system would get something wrong like that; usually when I’m in this situation, there’s something else that’s influencing the system that I’m not taking into account.

For instance: you say you’re getting the HERE() part from another table; perhaps that’s pulling the wrong info?

MultiTech
Participant V

Difficult. The roadblock here is that you need a way of saying, “ON THE DEPOT TABLE” create some sort of sorting field; and this field needs to be determined by whatever is currently inside the quote.

Because if you don’t have a sorting field on the depot table, how can you say which one should be higher up in a list or lower? You have a lat long, sure - but there is no connection between that one and the quote.

This would work (to give you a list of depots that are not 0 Km away, which would be everything in the table)…
select(Depots[DepotID],
distance([_thisrow].[Dest_LatLong], [DepotLatLong]) <> 0
)
…but how to say, now sort these based on that distance you calculated and give me the closest one? You can’t. The value calculated during the select isn’t sorted anywhere.


Creating distribution type apps, where you have things like this, have always been troublesome to create in appsheet. I’ve done maybe 3 or 4 big projects, and they all never really worked out.


I would create a flag system, to flag out the quote that someone is making; then you can pull the origin point they selected into the depot table to create your distance formula - then give you the auto-selection.

Seems messy…

MultiTech
Participant V

Yeah the only thing I keep coming back to is:

  • Create a way to flag the quote as the “Live” quote, or the “Building” quote - aka, “the one that is currently being built by the user of the app” - using a slice (with criteria something like - isblank([Destination]) or something.
  • Then you can pull the origin point from the slice into your depot table to create a virtual column showing the distance.
    • I would use a formula something like this:

IFS(
	isnotblank(LIVE_Quote[QuoteID]), 
		DISTANCE(
			index(LIVE_Quote[Origin_LatLong], 1),
			[Depot_LatLong]
		)
)

Where [Origin_LatLong] is the origin point’s latlong pulled into the quote (so we can easily retrieve it from the slice like this).
This formula also make use of the fact that the ending condition for IFS() is blank - nothing - so if the LIVE_Quote slice IS blank, then the formula will return nothing, a blank.


  • Then in your quote you’ll be able to pull in a list of the closest locations (and grab the closest automatically using MINROW() or some other varient).
  • Once the destination is selected, the quote moves out of the “Live” slice and the distance formulas clear.

The problem with this flow is that there is a break between selecting the origin and the destination - and unless you can smooth this out somehow, it’s really quite jarring and makes for a poor experience.

Sometimes there are other factors that need to be considered, other datas that need to be entered, that can aid with mitigating the fact that we’re going to one form (to select the origin), then leaving that screen to another (where we can select the destination), but it’s still quite disruptive to the app user.

But by and large, I’ve yet to see an app flow that makes this really smooth and easy.

Steve
Participant V

Try:

ANY(
  ORDERBY(
    FILTER(
      "Depots",
      (
        DISTANCE(
          [_THISROW].[vir_dest_latlong],
          [vir_depot_lat_long]
        )
        <> 0
      )
    ),
    DISTANCE(
      [_THISROW].[vir_dest_latlong],
      [vir_depot_lat_long]
    ),
      FALSE
  )
)
  1. FILTER("Depots", ...) gathers the rows in Depots that match the given criteria (...; see (3)).

  2. DISTANCE([_THISROW].[vir_dest_latlong], [vir_depot_lat_long])) computes the distance (in km) between the depots at the locations indicated by the row’s vir_depot_lat_long column value and the form’s vir_dest_latlong column value.

  3. (... <> 0) matches rows with a non-zero distance from the form’s depot (...; see (2)).

  4. ORDERBY(...1, DISTANCE(...2), FALSE) sorts the resulting rows (...1; from (3)) by distance (...2; see (2)) in ascending order (per FALSE).

  5. ANY(...) gives the first item from the resulting list (...; from (4)), which will be the closest depot (per (4)) that is in a different location (per (3)).

The ORDERBY() expression–without the *ANY() wrapper–can also be used in Valid_If to allow the user to choose from the distance-sorted list of depots.

This expression is expensive; I would recommend against its use in a virtual column or format rule.

See also:



Hi Steve, trying this now…will report back. Thanks so much

IT WORKED!!! I’m doing some more testing now but so far 3-4 entries have worked as expected. I’ll send a note here for the books if anything changes.

A million thanks Steve!!

I’m thinking I should remove the <>0 as I have a scenario where the destination zip is the same as a depot zip. The formula chose the closest depot but not the depot right in the same zip code. Yes?

Anything to consider if we use something like?

ANY(
ORDERBY(
Depot[Depot_ID],
DISTANCE(
[_THISROW].[vir_dest_latlong],
[vir_depot_lat_long]
),
FALSE
)
)

Looks good to me!

Works fine, i will try to add a virtual column to " depot" rows , in order to show each the distance in km

fulippio
Participant II

Update: I tried to use TOP(ORDER BY(Positions[ID],[Distance]),1) and it worked, so I tried again with the MINROW formula but it keeps not working. The position given from the other table seems to be correct.

For my needs I’m done, but this malfunction makes me so curious!

@Daisy_Ramirez @Steve @MultiTech_Visions Thank you for your input in this post. It inspired me

Top Labels in this Space