1 2 Previous Next 15 Replies Latest reply on Mar 6, 2014 11:16 AM by LeszekŁuczkanin

    Report based on portal



      Report based on portal


           Please, help!

           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:

           PrimaryKey     ::Person        ::Place          Move date     Info

           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. In this second portal I have a field "Move"Date" and two borrowed from the "Places" table (Country and City).

           I need a report based on the present location of the persons grouped by "Country" (from "Places" table) and sorted by city and I don't know how to make it.

        • 1. Re: Report based on portal

               My best guess as to the relationship details:


               Persons::__pkPersonID = Move::_fkPersonID
               Places::__pkPlaceID = Move::_fkPlaceID

               This is basically a Summary Report that can be based on the Move table so that values from Persons and Places can be included and the records can be grouped by Place under sub summary layout parts.

               But the challenge is in omitting from your found set all previous move records so that you only keep those records that show a person's current location.

               We can add another occurrence of Move and link it to the original Occurrence of Move like this:


               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.

               We can then define cCurrentLocFlag as

               Move::__pkMoveID = Move|SamePerson::__pkMoveID

               this field will return 1 (True) only if this is the most recent Move record for that person.

               Thus, you can perform a Find for 1 in the cCurrentLocFlag field and then sort your records by Place to produce the needed summary report.


          • 2. Re: Report based on portal

                 "We can then define cCurrentLocFlag as

                 Move::__pkMoveID = Move|SamePerson::__pkMoveID"


                 Is this a field? In which table it should be?

            • 3. Re: Report based on portal

                   __pkMoveID would be an auto-entered serial number defined in the Move table. That's why it is prefaced with the "Move::" table occurrence name. For existing records, you'll need to use Replace Field Contents with its serial number option to update them with serial number values unless you already have such a field defined in your Move table.

                   Both Move and Move|SamePerson are the names of two Tutorial: What are Table Occurrences? of the same data source table. That makes this a "self join" relationship.

              • 4. Re: Report based on portal

                "We can then define cCurrentLocFlag..."


                cCurrentLocFlag - what should it be and how can I make it? I'm a beginner in FM Pro 13


                • 5. Re: Report based on portal

                       Sorry for misreading your last post.

                       cCurrentLocFlag would be a field of type calculation with Number selected as the result type.

                       Move::__pkMoveID = Move|SamePerson::__pkMoveID

                       is the expression that you would define as its calculation. If the two fields are of equal value, the calculation returns 1 (True). If they are not equal, it returns 0 (False). This is the key part that allows you to omit previous locations from your found set as this expression will be true only for the most recent Move record for a given person.

                  • 6. Re: Report based on portal

                         And this field would be defined in the Move table.

                    • 7. Re: Report based on portal

                           Thanks a lot, it works!

                           But I would like to know, WHY and HOW it works.

                           Could You explain to me, step by step, how FM sees this MOVE Occurrences? And sorting relationship? One is sorteb by PKey and second by date... Why it is so important and how it is related?


                           I would like to understand the way it works for my future work with FM.


                      • 8. Re: Report based on portal

                             Yes, but how much of this process do you understand? Feel free to post follow up questions for any part of this that you don't understand after reading the following.

                             Any time you have multiple related records and your calculation refers to fields from that same related table, FileMaker references the "first" related record. In an unsorted relationship, this will be the oldest related record. By sorting the relationship on the move date in descending order, we make the "first" related record the newest move.

                             So the expression in the flag field is true only for records for the person's most recent move because the serial number ID's will only match when the record for a person's most recent move is compared to the related record--where the sort makes the most recent move the first related record.

                             It might help to think of it this way:

                             If you were to place an unsorted, unfiltered  portal to Move|SamePerson on your Move layout. The sorted relationship will place the most recent move for that person in the first portal row. The "flag" calculation field evaluates as True (1) when the ID of your current Move record is the same as the ID of the first record listed in that portal.

                        • 9. Re: Report based on portal

                               Thanks a lot! I think I understand now.


                          cCurrentLocFlag - working of this field isn't very clear to me. Primary Keys are equal for every field, aren't they? Please, step by step idea of this field

                          • 10. Re: Report based on portal

                                 PrimaryKeys store a value that uniquely identifies that each record in a table. No two records in the same table have the same value in this field. They are the most often used match field in relationships and the ideal key is unique, never ever changed and free of any "encoded meaning".

                                 cCurrentLocFlag is a calculation field. (I start all calculation fields with a lowercase c so I can see that they are calculation fields when working with lists of fields in other parts of the FileMaker system.)

                                 It returns either a 1 or 0 result as it is what we all a Boolean calculation. Due to using an = sign operator, the calculation returns 1 (True) if the two referenced fields are of equal value and 0 (False) if they are not.

                                 Any calculation that refers to fields from a related table, as does this calculation, refer to the "first" related record. In an unsorted relationship, that "first" related record will be the first record created. In a sorted relationship, the sort order determines which record is the "first" record. The sort sort order for these related records sorts them in descending order by date. That makes the most recent move record, for a given person, the "first" related record. So on each record, the PrimaryKey (unique identifier) field of that record is compared to the PrimaryKey field of the most recent Move record for that person. The values will be equal only when the current record is the record for that person's most recent move and thus only the most recent move records for each person will have a 1 in this field--giving us a field that we can use in our find to omit records for previous moves.

                            • 11. Re: Report based on portal


                                   Now it is clear!

                                   Thanks a lot for this explanation, it's great!!!


                                   I have a lot more questions. Where can I write? I think that starting new post for each question isn't good idea.

                                   Can I ask in this subject? Or is there any e-mail for people in need?

                              • 12. Re: Report based on portal

                                     Actually starting a new post for each question is an excellent idea. You may need to repeat your self a bit by copy and pasting from an earlier thread to set up the context, but by starting a new thread for a new question you are more likely to have it seen and read by more than one person--which allows you to benefit from the experience and expertise of more than just one person.

                                • 13. Re: Report based on portal


                                       Thanks for Your help! I wish I could speak English much better to describe how grateful to You I am :)


                                  • 14. Re: Report based on portal

                                         Your English is quite good. I had not realized that English was not your birth language. (When I do, I often modify my word choices in an attempt to make them easier to understand.)

                                    1 2 Previous Next