hello you all! I have a table with [JobNo] = ...

[Alex_Meraru]

hello you all! I have a table with [JobNo] = text [StopNo] = number (1,2,3,4…) (one job has multiple stops) [Coordinates] = LatLong [Previous coordinates]=LatLong [Distance]=decimal

How it should work: When a new stop is added for a job, [Previous coordinates] will prefill with [Coordinates] from the previous stop.

This way, I can calculate the distance between stops and ultimately the distance for the route by adding the distances between all stops for that job.

How do I get [Previous coordinates] prefilled?

Thank you!

[Alex_Meraru]

@Aleksi_Alkio …? Translate

[pravse]

@Alex_Meraru, you need an expression in the App Formula for ‘Previous Coordinate’.

First create a virtual column with the MAXROW() function. See help.appsheet.com - List Expressions and Aggregates

Something like this: MAXROW(TableName, StopNo, [_THISROW].[JobNo] = [JobNo])

Then de-reference that virtual column to get the coordinates. This will be the AppFormula for Previous Coordinate [VirtualColumnName].[Coordinates] List Expressions and Aggregates help.appsheet.com

[Alex_Meraru]

works ! But it gives me the max, so if I want to change an existing stop or add a new one in between (5 stops and I want to add another one as 3’rd), previous location will be filled with the last location not previous.

[Alex_Meraru]

Also when editing a stop will change the previous coordinates (which originally were ok) with the coordinates from the last one.

So MAXROW is not the right one.

I need something like

[_THISROW].[StopNo]-1=[StopNo]

But don’t know exactly how to use it

[Bellave_Jayaram]

You will need to create separate way points or stops as in Stop1-From, Stop1-To etc. and adjust them according to your need.

[pravse]

the third parameter of MAXROW() is a filter function. You can make that an AND() and include the condition over the StopNo as well.

[pravse]

Or you can write your own ANY(SELECT(…)) expression. MAXROW() is just one such expression in a stylized form.

[Alex_Meraru]

All 3 situations work: 1. First Stop (no previous location) 2.New Stop for an existing Job 3. New Job+ new Stop (linked to the last stop of previous job)

=IF(AND([JobNo]=1,[StopNo]=1),

MINROW(Addresses,

StopNo,

[_THISROW].[JobNo]=[JobNo]

),

IF([StopNo]>1,

MAXROW(Addresses,

StopNo,

AND([_THISROW].[JobNo] = [JobNo],

[_THISROW].[StopNo]-1=[StopNo]

)

),

MAXROW(Addresses,

StopNo,

[_THISROW].[JobNo]-1= [JobNo]

)

)

)

Next step is adding the distance per JobNo and Route calculation done! Thank you for the support! I’ll be fine from here. I was an important chapter of my app.