5 Replies Latest reply on Jul 9, 2017 8:54 PM by JensT

    Finding Related Value in a Calculation

    cmj

      I'm certain I've accomplished this before but can't recall how it's done. Perhaps an additional table occurrence?

       

      Tables are related as follows: MEMBERS <---- GROUP ----> INCOME

       

      This calculation is taking place in a field in INCOME TABLE

      Case(Members::MemberRetireYear ≤ Year(Get(CurrentDate));0)

       

      The MemberRetireYear is stored in MEMBERS Table

      The INCOME TABLE does store fkMembers (it's populated via drop-down list during record creation).

       

      How do I express in the calculation to find the record (MemberRetireYear) that relates to the fkMembers value?

       

      Thanks

        • 1. Re: Finding Related Value in a Calculation
          philmodjunk

          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.

          • 2. Re: Finding Related Value in a Calculation
            cmj

            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.

             

             

            Tables.JPG

            • 3. Re: Finding Related Value in a Calculation
              JensT

              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

              • 4. Re: Finding Related Value in a Calculation
                cmj

                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.

                1 of 1 people found this helpful
                • 5. Re: Finding Related Value in a Calculation
                  JensT

                  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.