0 Replies Latest reply on Apr 27, 2016 2:16 PM by synergy46

    FM 14 adv - field reference in related file

    synergy46

      I have a membership application.

      It has Members::=>>Drama

      It works.

       

      The narrative is:

      "The 'club' has members.  Members act as participants in other members 'Drama'.

      I produced a report based on Drama that needs the (Drama)Participant's ::Phone when someone is a 'participant'.  As it is,  I can only get the Members::Phone which is the phone for the Candidate. (They are both members and reside in the Member's table) , but the Drama::Name is the name of the participant that is chosen from a Dropdown list of Members::Name; Michael Davis - below)

       

      For example, for the Drama of William Hall (Phone Number 208 555-6666), Michael Davis is a participant (Phone Number 360-222-2222) .

      But, on the report layout I want to include Davis's phone number; not Hall's.

      Drama1.png

      The problem  is:

      In the portal above, Davis is a 'Participant' for Hall's Drama.   But, when I put Members::Phone on the layout/Report

      it shows the phone number for Hall 555-6666 ; not Davis.   And, if I add a new 'Drama::Phone' field, it is empty.

      (Plz see "Active Member Drama Participants" window above)

       

      DramaTbl.png

      DramaDavis1.png

      So, "How do I get Davis phone number to show?  (360-222-2222 instead of Hall's number - 208 555-6666)

       

      I have tried creating a Drama::Phone field with a lookup but that won't work.

      I have tried creating a new relationship .... Drama::Phone << Members::Phone  that won't work.

       

      In the abstract I am thinking that I need to write a script that will replace the Drama::Phone field with the Member::Phone field, but this seems really inefficient and unnecessarily complicated.  After all, Davis's phone number IS in the Members table.  I just can't reference it.

       

      Thanks for thinking about this....

       

      Message was edited by: Ron Gordon.  Trying to be more clear.