I think that I remember helping you set this up, but I could be remembering a different person's similar post and I might not remember the details of how I set it up.
Exactly how did you get this report to only list the most recent move?
I need to be sure I am correct on how that is set up before suggesting how to include a reference to the previous move location to show the point of origin for each move.
That is right: You saved my life few weeks ago. You helped me to set up a portals with various data, based on my link table (Move).
Thanks to you I have in "Person" layput a portal showing place of presence and history of all moves of this person. I also have a portals on my "Place" layout where I have a list of persons living in the place at this moment and the second portal with the persons which was in the history. This part works great, thank you once again.
Now I'm upgrading my database and it can be useful to have a report showing list of Moves grouped by years. This is not a problem. I made one, but it shows only:
Person Place (where to) Date
I would like to have an additional information to made my report looking like this:
Person Place2 (from where) Place3 (where to) Date
In the portal on person layout this is not a problem, all is clear:
BTW, every Date of Move is a beginning of new chapter but it is also the date of the ending in the last place. For the layout "Person" all is clear, but I have a portal on my layout "Place" with
Again, I would like to upgrade my portal to something like that:
Person1 MoveDate(start) MoveDate(finish)
where MoveDate(finish) is MoveDate (start) for the next move....
Yes, I know it is very complicated, but I don't know how to make it.
I hope You can help me (as always :) )
Going back to this self join relationship:
Move::_fkPersonID = Move|SamePerson::_fkPersonID
We can sort the relationship by double clicking the relationship line, clicking the Sort button on the Move|SamePerson side and specify that the related records be sorted in descending order by the date of the move.
GetNthRecord ( Move|SamePerson::Place ; 2 )
Will return the immediately previous place (From) if such a record exists. To keep a ? from appearing if there is no previous record enclose it in an if function that checks the number of related records:
If ( Count ( Move|SamePerson > 1 ; GetNthRecord ( Move|SamePerson::Place ; 2 ) )
Is it going to work for all Moves? For example, I have person moving every year since 1986.
Is this calculation going to show al Moves "From - To"?
Combined with 600 persons it gives quite big report grouped by YEAR (MoveDate).
Person1 Place1 (where to) Date
Person1 Place1 (from where) Place2 (where to) Date
Person1 Place2 (from where) Place3 (where to) Date
Sorry, but I assumed that you only needed that for their most recent move--which is consistent with the previous thread, not this one.
With the right self join relationship, this method can be adapted to do that.
The following assumes that you have a number field with an auto-entered serial number field in the Move table:
Move::_fkPersonID = Move|Previous::_fkPersonID AND
Move::__pkMoveID > Move|Previous::__pkMoveID
If you sort Move|Previous in this relationship by __pkMoveID in descending order, you can refer to fields from Move|Previous to access data about the preceding move for that person.