6 Replies Latest reply on Feb 17, 2014 6:16 AM by philmodjunk

    Portal problem: filtering recent dates

    LeszekŁuczkanin

      Title

      Portal problem: filtering recent dates

      Post

           I have a table with "persons" (600) and the second one with "places" (80).

           I have a link table with "move" (900). It is very simple with fields:

           ::Person        ::Place          Move date

           Every person can be in only one place at the moment but there can be a few persons in one place.

            

           On the "person" layout I have a portal with all places this person been moved to and the second portal with the place in which this person is at the moment.

            

           On the "place" layout I have a portal with list of all persons that been there anywhen but i need a portal which shows only persons beeing in this place at the moment.

           I don't know how to made it.

            

           Please, help!!!

            

            

        • 1. Re: Portal problem: filtering recent dates
          philmodjunk

               Add a new table occurrence of Move in Manage | Database | Relationships. Link it to the current occurrence of Move like this:

               Move>------<Move|SamePerson

               Move::_fkPersonID = Move|SamePerson::_fkPersonID   (you probably have a different field name for this)

               Double click the relationship line to open the relationship details dialog box. Select a sort order for Move|SamePerson that sorts the related records by MoveDate in descending order.

               Define a primary key field, __pkMoveID, in Move. For existing records, use Replace Field Contents with the serial number option to put the appropriate value in this field. (Select the option to update auto-enter settings when you do this.)

               Set up your portal to Move on your Place layout, but give it this portal filter:

               Move::__pkMoveID = Move|SamePerson::__pkMoveID

               This will filter out all but the most recent Move record for each person.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Portal problem: filtering recent dates
            LeszekŁuczkanin

                 Amazing! That works! Thanks!

                 I wish I could understand how it works, maybe later on it will become more clear.

                  

                 I have another question:

                 Some of that persons died and others quit job. I note this in  corresponding fields:

                 Quit_Date

                 Death_Date

                  

                 It is good to have them in this historical portal, but they appear in this acctual portal. How can I exclude them?

            • 3. Re: Portal problem: filtering recent dates
              philmodjunk

                   The portal Filter can exclude them. Assuming that the "actual portal" is the portal for which we just set up the above relationships and filter, you can modify the filter expression to be:

                   Move::__pkMoveID = Move|SamePerson::__pkMoveID AND
                   IsEmpty ( Move::death_Date ) AND
                   IsEmpty ( Move::Quit_Date )

              • 4. Re: Portal problem: filtering recent dates
                LeszekŁuczkanin

                     Works again! Thanks so much!

                      

                • 5. Re: Portal problem: filtering recent dates
                  LeszekŁuczkanin

                       It's me again... Still this same case

                       In my table "Places" i have fields: "City" and "Country". I borrow them to my "Move" layout.

                        

                       I want to make a report showing all persons grouped by "Country" and sorted by "City" of their actual presence.

                       I tried to do it based on my portal of actual presence  in "Person" layout, but it doesn't work.

                        
                  • 6. Re: Portal problem: filtering recent dates
                    philmodjunk

                         Use a layout based on the portal's table. Perform a find to find the records you want for your report. Sort by country and then by city to group them accordingly.

                         Use sub summary layout parts "when sorted by" country and "When sorted by" city to show the names of the countries and cities as sub headers.

                    Creating Filemaker Pro summary reports--Tutorial