Find Value from Previous Row

I am new here but have used appsheet before.
I have a table that stores Full Trip data and a related table that stores journeys in this trip.
Full Trip - Place A - D
Journeys in the trip -
Place A - B
Place C-D
I am able to create a form to enter the related data.
I want to find the distance between Place B to Place C.
Since these are in different rows - I an unable to do this.
Can anyone help ?

This is possible, but I have a question.

Can it be assumed that there could be several journeys in a trip?

For example, A->B, C->D, E->F

If that was the case, does that mean that you’d want to calculate the distance from both B->C and D->E?

And if so, how would you determine the order of the journeys? Would you rely on the order that they are entered, or would there be some other way?

Thanks @Darren_Murphy
Yes there would be several journeys in a trip.
Yes i need to calculate both B-C & D-E in your example and if there are more then others too.
Yes they are always entered in order.
Thanks a ton.

So I assume you store each place’s address/coordinates?

What you can do is this:

  • Create an index for each row, as described below

Please note that the index should be related to each “trip” only, since you want to index each row based only on their respective list. You should have a “Trip ID” that is unique to each trip.

  • Create a Math column, name it “Previous Index”, take the index from the 1st step, minus 1.
  • Create a Template column, name it “Previous Index Temp”, the template should join the “Previous index” and the “Trip ID”. E.g: 1 - abcdef.
  • Create another Template column, name it “Current Index Temp”, joining the “Current Index” (step 1) and the “Trip ID”.
  • Create a relation from the “Previous Index Temp” column to the “Current Index Temp”.
  • Return the “Destination address/coordinates” from the relation. It should give you the address/coordinates of place B, D, F etc in Darren’s example.

Thanks a ton got it !

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.