Please show the match fields used in your relationships. There are several different ways that this might fail depending on those match fields and whether a record exists in Group that links the current record in Income to the desired record in Members.
You may need ExecuteSQL to access the data in ways different form your established relationships, or you may need to modify those relationships in order for your calculation to work.
Thank you for your reply. Hopefully this sheds some light on my question:
1. I have a layout based on the Income table. When a new record is generated in the Income table, one of the fields is populated via drop-down list. This drop-down allows for the selection of a specific member (from Members table) along with information in the Income table fields. Selecting the member is accomplished by populating the fkMemberID field (in the Income table) via value list that looks back at the _pkMembersID field in the Members table.
2. At this point we have the pk of the member from the Members table stored in the Income table as fk_Members.
3. There is a field in the Members table that contains the year a member retires (MemberRetireYear).
4. Now I want to perform a calculation in a field in the Income table. The calculation needs to find the year that the member retires related to the pk_MembersID and compare it to the current date. The calculation in the Income table field is basically as follows:
Case(Members::MemberRetireYear ≤ Year(Get(CurrentDate));0
What the calculation really needs to express is to find the related record in Members table (via fk_MembersID & PK_MembersID) and return the related value from the field MemberRetireYear to the calculation.
INCOME contains a field _fkMemberID. If you want to access fields of this member, you need an additional table occurance (those boxes in graph) based on MEMBERS (just duplicate Members TO and rename it Income_Member) with relationship:
Income::_fkMemberDI = Income_Member::__pkMemberID
1 of 1 people found this helpful
Thanks. I had a gut feeling about the TO but thought that there may be some magic in the calculation that I could use.
I appreciate your input.
There is indeed a second way. Using ExecuteSQL the new TO and relationship are not required. But this relationship seems to be so basic that the classical FileMaker approach seems appropriate.