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

(Alex Meraru) #1

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) #2

@Aleksi_Alkio …? Translate

@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) #4

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) #5

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) #6

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

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

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

(Alex Meraru) #9

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),

StopNo,

[_THISROW].[JobNo]=[JobNo]

),

IF([StopNo]>1,

StopNo,

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

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

)

),