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.
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?
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])
INDEX(OrderBy(Positions[PositionID], [Distance]), 1)
INDEX(OrderBy(Positions[PositionID], [Distance]), COUNT(Positions[PositionID]))
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?
For instance: you say you’re getting the HERE() part from another table; perhaps that’s pulling the wrong info?
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…
Yeah the only thing I keep coming back to is:
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.
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.
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
)
)
FILTER("Depots", ...)
gathers the rows in Depots that match the given criteria (...
; see (3)).
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.
(... <> 0)
matches rows with a non-zero distance from the form’s depot (...
; see (2)).
ORDERBY(...1, DISTANCE(...2), FALSE)
sorts the resulting rows (...1
; from (3)) by distance (...2
; see (2)) in ascending order (per FALSE
).
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
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
User | Count |
---|---|
43 | |
27 | |
24 | |
23 | |
13 |