2 Replies Latest reply on Dec 20, 2008 3:10 PM by stevestearns_1

    Join to Specific Record for Calculation



      Join to Specific Record for Calculation


      I am trying to create some calculations based upon a join to a specific record in another related table (i.e., spouse or partner). I know how to relate (or join) the two tables, but am not sure how to relate to a specific record within the related table to create the calculation.


      The first table will contain all the contact information for the primary person, so we’ll call this table ‘Contacts’. It will have the following fields:






      The second table will contain information about relationships to the primary person, for instance: spouse, partner, child, friend, assistant, father, mother parent, brother, sister, manager, etc. The fields for the ‘Relations’ table would contain:






      relationship (see list above)





      The two tables would be joined or related by Contacts: contactID = Relations: contactID.


      Now, how to do contact records that match relationship equal to spouse or partner? What I would like to be able to do is:


      Contacts record = John Doe


      Relations record = Jane Doe (spouse) or Jane Smith (partner)


      Calculated result =


      John and Jane Doe (if last names are the same and relationship = spouse)

      John Doe and Jane Smith (if last names are not the same and relationship = spouse or partner)


      I know how to build complex If or Case statements, so all I need help with is how to use a specific record in the calculation, because a primary person can have multiple relationships, spouse, child, etc.

        • 1. Re: Join to Specific Record for Calculation

          Hi there Steve


          With you calculated result do you just want it to show one related record, a Spouse or a Partner, or list them all in the format you describe?


          And if just one, which one if there are multiple?


          Based on the format you describe try this calculation, you could place it in the RELATIONS record and it will calculate for each person and you can use this field to displayed on the CONTACT record depending on how you want to do this.



          If ( RELATIONS::relationship = "Spouse" and CONTACT::lastName = RELATIONS::lastName ; 
          CONTACT::firstName & " and " & RELATIONS::firstName & " " & CONTACT::lastName ; 
          CONTACT::firstName & " " & CONTACT::lastName & " and " & RELATIONS::firstName & " " & RELATIONS::lastName 



          • 2. Re: Join to Specific Record for Calculation

            Hi Orlando,


            This is exactly what I am looking to do. Thanks so much for your help.


            I will rank spouse first and then partner second.