1 Reply Latest reply on Jan 23, 2012 8:56 AM by philmodjunk

    How do I create the appropriate relationship in a table that uses keys from two separate tables



      How do I create the appropriate relationship in a table that uses keys from two separate tables


      Consider following situation:

      In order to generate an offer to a guest from a set of several homes I need to pull information from:

      - Contact, the contact details

      - Request, the request details

      … in the appropriate language, as guests come from different countries.


      Each contact is always assigned one single language. When I generate an offer to a customer I need to pull also information from:

      - HomeDescription, several text building blocks that are specific to a particular home and in a particular language.

      - Language, several text building blocks that are language specific.


      When I generate an offer, it is done from the context of Contact. I have tried several join options in order to get the description of the homes in the appropriate language, but so far:

      - either I get the appropriate language in HomeDescription, but it is not the correct home, or

      - or I get the appropriate home in HomeDescription, but not the correct language.


      I tried:

      - creating a Language_ID calculated field in Request (it doesn't work)

      - creating a join from Language to HomeDescription and reference those fields (it doesn't work)

      Which solutions is more appropriate and how do I make it work ? 

      Can you advise what do I need to do to get HomeDescription to point at the right Home in the right Language ? 

      I can upload a sample of the database for your review if necessary.

      The last question is: how would I go about existing records when I apply a new relationship to the tables, provide the ID fields are already filled in ? 


        • 1. Re: How do I create the appropriate relationship in a table that uses keys from two separate tables

          Your layouts look pretty close to what you need. You just need a link from Request to HomeDescriptions that is language specific

          Option 1: Change your relationship to include Language_ID fields.

          Request::HomeID = HomeDescriptions::Home_ID AND
          Request::cLanguageID = HomeDescriptions::Language_ID

          cLanguageID can be an unstored calculation that copies the LanguageID from Contact:

          It should be defined to just list Contact::Language_ID with "Request" specified as the context table.

          You may need to do a commmit records/refresh window in some cases before you will see things on your Request layout (or portal based on Request) update to display the correct data from HomeDescriptions.

          Option 2:

          Use a filtered portal that filters the records from HomeDescriptions by Contact::Lnaguage_ID

          This option works with your current relationships. On your requests layout you can include a portal or portals to HomeDescriptions but use this portal filter expression:

          Contact::Language_ID = HomeDescriptions::Language_ID

          Also in this case, you may find that you have refresh issues with the text thus displayed if you should change the Language specified for a given contact. There are scripting and relationship based methods that can handle this refresh issue without resorting to:

          Refresh WIndow [Flush cached join results] -- which can lead to undesirable delays in updating your layout.