5 Replies Latest reply on Mar 25, 2014 10:37 AM by philmodjunk

    Reporting a portal

    LeszekŁuczkanin

      Title

      Reporting a portal

      Post

           I have table "Person" and "Place"

           I have link table "Move" with fields:

           _pkMove   ::Person     ::Place       MoveDate

            

           Each person was in few places, moving from one to another.

            

            

           I made a report grouped:

           Year (MoveDate)

           ::Person     ::Place      Move Date

            

            

           In this report I have only LAST place, where person is at this moment. But I would like to add a field to report with place from which person move to actual place.

            

           ::Person        ::LastPlace       ::CurrentPlace      MoveDate

            

           I don't know how to made this.

           Simple way to do this is adding a field in table Move and fill it manually, but I have more than 1000 records and I'm looking for a simplier method.

           It could be useful to have a range of time person spent in place. I wish to add a field "DateEnd" and auto fill it with a date of next move minus one day. It is kind difficult to fill it manually.

           Any ideas, please?

        • 1. Re: Reporting a portal
          philmodjunk

               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.

          • 2. Re: Reporting a portal
            LeszekŁuczkanin

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

            Portal problem: filtering recent dates   Report based on portal

                  

                 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:

                  

                 Place1       Date1

                 Place2       Date2

                 Place3       Date3

                 etc.

                  

                 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

                 Person1    MoveDate

                 Person2    MoveDate

                 Person3    MoveDate

                 etc. 

                  

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

                  

            • 3. Re: Reporting a portal
              philmodjunk

                   Going back to this self join relationship:

                   

                        Move-----<Move|SamePerson

                   

                        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.

                   This calculation:

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

              • 4. Re: Reporting a portal
                LeszekŁuczkanin

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

                      

                     Year 1

                Person1                                       Place1 (where to)   Date

                Year 2

                Person1   Place1 (from where)    Place2 (where to)   Date

                Year 3

                Person1   Place2 (from where)    Place3 (where to)   Date

                      

                • 5. Re: Reporting a portal
                  philmodjunk

                       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-----<Move|Previous

                       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.