Using FM 16, I have a table called salaries . Each salary record has a year field associated with it (e.g. 2018) and a person field. Right now while I'm testing the set up, I have two records for 2018, two records for 2017, the same for 2016 and 2015 (eight records total).
I anticipate many more records for each of these years. On any year 2018 record, for example, I want a user to be able to see all of the previous year's records (2017 in this case) in a portal. To do this I set up a calculation field (call it Year-1) to be used in a self join relationship.
The stored calculation field is the year field (a number) - 1. The calculation is evaluated from the context of the original salaries table (not the self join table). In the layout based on the salaries table I can see the year field with an entry of 2018. I also have Year_1 on the layout and it says 2017.
Then I duplicated the salaries table and self joined it using Year ( salaries table) to Year_1 ( self join copy of salaries table). The records are sorted in the relationship by year (descending). Because this is an information relationship, no permissions are granted for creating or deleting records in the relationship.
I then placed a portal in the salaries table layout based on the self join table expecting to see in the portal on a 2018 record 2017 records only. Instead, I see all eight records (that is every year's salary records). What have I missed?
Thanks for any help.