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 Like

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.

1 Like

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.

1 Like

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

1 Like

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

1 Like

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… :thinking:

1 Like

@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

2 Likes

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.

2 Likes

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:



5 Likes

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

1 Like

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!!

4 Likes

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?

1 Like

Anything to consider if we use something like?

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

1 Like

Looks good to me!

2 Likes