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.
__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.
"We can then define cCurrentLocFlag..."
cCurrentLocFlag - what should it be and how can I make it? I'm a beginner in FM Pro 13
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.
And this field would be defined in the Move table.
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.
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.
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
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.
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?
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.
Thanks for Your help! I wish I could speak English much better to describe how grateful to You I am :)
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.)