AnsweredAssumed Answered

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

Question asked by PecCars on Jan 23, 2012
Latest reply on Jan 23, 2012 by philmodjunk

Title

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

Post

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 ? 

Screen_Shot_2012-01-23_at_17.14.45.png

Outcomes