Relationships based on two or more fields

[videotom]

I've been using FMP since the beginning on a Mac, but never dealt much with relationships until now, and this one has me stumped. I'm on FMP6 now. Here's the issue:

I have two travel show databases - The first "Addresses" database contains regular address and contact info (with a "Type" field identifying each record as "Airline" "Tourism Board" or "Hotel" and another identifying the country the contact is located in. The second "Locations" database contains the details of all the countries we'll be traveling to. It is in this database that I'd like to pull-up (i.e., relate) informaton from the address database.

For example, when I select: "Morocco," from the locations database, I want three portals to come up with all the Airlines, Hotels, and Tourism Boards contaned within the Address database that are from Morocco. The problem is, this would invole relating TWO fields: the "Country" field, and the "Type" field. To my knowledge, you cannot relate two fields. I suppose I could use a calculation field to calculate a "value" of the country+type fields in the address database, but I'm not a programmer, and it seems like it would be an enormous amount of work.

Is there a simplier way of doing this?

Thanks!

Tom

[videotom]

I've been using FMP since the beginning on a Mac, but never dealt much with relationships until now, and this one has me stumped. I'm on FMP6 now. Here's the issue:

I have two travel show databases - The first "Addresses" database contains regular address and contact info (with a "Type" field identifying each record as "Airline" "Tourism Board" or "Hotel" and another identifying the country the contact is located in. The second "Locations" database contains the details of all the countries we'll be traveling to. It is in this database that I'd like to pull-up (i.e., relate) informaton from the address database.

For example, when I select: "Morocco," from the locations database, I want three portals to come up with all the Airlines, Hotels, and Tourism Boards contaned within the Address database that are from Morocco. The problem is, this would invole relating TWO fields: the "Country" field, and the "Type" field. To my knowledge, you cannot relate two fields. I suppose I could use a calculation field to calculate a "value" of the country+type fields in the address database, but I'm not a programmer, and it seems like it would be an enormous amount of work.

Is there a simplier way of doing this?

Thanks!

Tom

[videotom]

I've been using FMP since the beginning on a Mac, but never dealt much with relationships until now, and this one has me stumped. I'm on FMP6 now. Here's the issue:

I have two travel show databases - The first "Addresses" database contains regular address and contact info (with a "Type" field identifying each record as "Airline" "Tourism Board" or "Hotel" and another identifying the country the contact is located in. The second "Locations" database contains the details of all the countries we'll be traveling to. It is in this database that I'd like to pull-up (i.e., relate) informaton from the address database.

For example, when I select: "Morocco," from the locations database, I want three portals to come up with all the Airlines, Hotels, and Tourism Boards contaned within the Address database that are from Morocco. The problem is, this would invole relating TWO fields: the "Country" field, and the "Type" field. To my knowledge, you cannot relate two fields. I suppose I could use a calculation field to calculate a "value" of the country+type fields in the address database, but I'm not a programmer, and it seems like it would be an enormous amount of work.

Is there a simplier way of doing this?

Thanks!

Tom

[MoonShadow]

Welcome to FM Forums! Not to worry ... I'll walk you through it, Tom.

In your Addresses file, create a calculation (text) called cCountryTypeKey with the following:

Country & " " & Type ... note there is a space between the two fields on all these keys.

Since you want three portals (I assume one each for Airline, Tourism Board and Hotel), you will need three separate keys in Locations. Create three keys:

cAirLineKey (calculation text)

Country & " " & "Airline"

cTourismKey (calculation text)

Country & " " & "Tourism Board"

cHotelKey (calculation text)

Country & " " & "Hotel"

Create three relationships from Locations - joining each one separately to the same cCountryTypeKey in Addresses. Example:

Open Define Relationships. Create New and select your Addresses file. Join cAirLineKey on the left to cCountryTypeKey on the right. Say okay to save it.

Repeat with each relationship. Name each relationship by it's type name. Place all three portals on your Locations layout. Be SURE to specify the correct relationship when Inserting > Portal and also be SURE to specify the matching fields from the same relationship as each represented portal for placing your fields inside it.

Also, be sure that your Type field in Addresses is only allowed to exactly produce these three results. If someone types in the data, they may make a mistake and it won't display in the portal in Locations. It's wise to establish a Value List in your Addresses file (can be Custom Value List) listing these three SPECIFICALLY WORDED Type names to remove chance of error.

[MoonShadow]

Welcome to FM Forums! Not to worry ... I'll walk you through it, Tom.

In your Addresses file, create a calculation (text) called cCountryTypeKey with the following:

Country & " " & Type ... note there is a space between the two fields on all these keys.

Since you want three portals (I assume one each for Airline, Tourism Board and Hotel), you will need three separate keys in Locations. Create three keys:

cAirLineKey (calculation text)

Country & " " & "Airline"

cTourismKey (calculation text)

Country & " " & "Tourism Board"

cHotelKey (calculation text)

Country & " " & "Hotel"

Create three relationships from Locations - joining each one separately to the same cCountryTypeKey in Addresses. Example:

Open Define Relationships. Create New and select your Addresses file. Join cAirLineKey on the left to cCountryTypeKey on the right. Say okay to save it.

Repeat with each relationship. Name each relationship by it's type name. Place all three portals on your Locations layout. Be SURE to specify the correct relationship when Inserting > Portal and also be SURE to specify the matching fields from the same relationship as each represented portal for placing your fields inside it.

Also, be sure that your Type field in Addresses is only allowed to exactly produce these three results. If someone types in the data, they may make a mistake and it won't display in the portal in Locations. It's wise to establish a Value List in your Addresses file (can be Custom Value List) listing these three SPECIFICALLY WORDED Type names to remove chance of error.

[MoonShadow]

Welcome to FM Forums! Not to worry ... I'll walk you through it, Tom.

In your Addresses file, create a calculation (text) called cCountryTypeKey with the following:

Country & " " & Type ... note there is a space between the two fields on all these keys.

Since you want three portals (I assume one each for Airline, Tourism Board and Hotel), you will need three separate keys in Locations. Create three keys:

cAirLineKey (calculation text)

Country & " " & "Airline"

cTourismKey (calculation text)

Country & " " & "Tourism Board"

cHotelKey (calculation text)

Country & " " & "Hotel"

Create three relationships from Locations - joining each one separately to the same cCountryTypeKey in Addresses. Example:

Open Define Relationships. Create New and select your Addresses file. Join cAirLineKey on the left to cCountryTypeKey on the right. Say okay to save it.

Repeat with each relationship. Name each relationship by it's type name. Place all three portals on your Locations layout. Be SURE to specify the correct relationship when Inserting > Portal and also be SURE to specify the matching fields from the same relationship as each represented portal for placing your fields inside it.

Also, be sure that your Type field in Addresses is only allowed to exactly produce these three results. If someone types in the data, they may make a mistake and it won't display in the portal in Locations. It's wise to establish a Value List in your Addresses file (can be Custom Value List) listing these three SPECIFICALLY WORDED Type names to remove chance of error.

[videotom]

MoonShadow,

Thanks for the info. I'm a little confused, though. Te type field is actually a pull-down field already, so I think that's okay. For the seperate keys, are you saying that I need to use the field country and then the actual words: "Airline" "Tourism Board" and "Hotel" vs. country & " " & Type?

Also, there may be more then one Airline or Hotel that comes-up. Will the portal cover this?

Finally (and this will be the most tricky), the goal of the Locations database is to be able to select from a master list of all countries, then have these relations pull in the portals for that particular country. In your Key sample, would I need to define the relationship for every single country?

Thanks so much!

Tom

[videotom]

MoonShadow,

Thanks for the info. I'm a little confused, though. Te type field is actually a pull-down field already, so I think that's okay. For the seperate keys, are you saying that I need to use the field country and then the actual words: "Airline" "Tourism Board" and "Hotel" vs. country & " " & Type?

Also, there may be more then one Airline or Hotel that comes-up. Will the portal cover this?

Finally (and this will be the most tricky), the goal of the Locations database is to be able to select from a master list of all countries, then have these relations pull in the portals for that particular country. In your Key sample, would I need to define the relationship for every single country?

Thanks so much!

Tom

[videotom]

MoonShadow,

Thanks for the info. I'm a little confused, though. Te type field is actually a pull-down field already, so I think that's okay. For the seperate keys, are you saying that I need to use the field country and then the actual words: "Airline" "Tourism Board" and "Hotel" vs. country & " " & Type?

Also, there may be more then one Airline or Hotel that comes-up. Will the portal cover this?

Finally (and this will be the most tricky), the goal of the Locations database is to be able to select from a master list of all countries, then have these relations pull in the portals for that particular country. In your Key sample, would I need to define the relationship for every single country?

Thanks so much!

Tom

[videotom]

NEVERMIND!!!

I reread MoonShadow's post, experimented some, and it works PERFECTLY!!!

Thanks!!

Tom

[videotom]

NEVERMIND!!!

I reread MoonShadow's post, experimented some, and it works PERFECTLY!!!

Thanks!!

Tom

[videotom]

NEVERMIND!!!

I reread MoonShadow's post, experimented some, and it works PERFECTLY!!!

Thanks!!

Tom

[gradiva]

Hi... moonshadow do you have any suggestions for how to deal with creating new records when using this trick? It works perfectly for displaying existing data, but when the user tries to enter new data (a new record) in the portal, FM is complaining that the relationship is not valid. I'm guessing this is because FM can't autoenter the key value (which is a calculation) in the new record. Oh boy would I be grateful for your help!!

-Gradiva

[Ender]

Gradiva, I answered this in your other post.

[gradiva]

hi Ender, i'm sorry again about the dupes! To anyone else looking for the answer, it is in the thread with the original subject line "adding new records to related files "

-Gradiva

[Ender]

No problem. But please don't change the subject line on replies, as it makes it harder for us to follow the thread in the Active list.