do I need a multi-criteria relationship?

[jbenner]

I just upgraded to FM 7 and it's like I'm in a foreign land...

I've got 2 tables:

Fossils- fossil id; fossil name; location id

Locations- location id; city; county; state; country

I have a relationship (=) between Fossils::location id and Locations::location id

Fossils::location id is a lookup to Locations::location id

On my fossils layout I have the fields from the Fossils table and a portal containing the city, county, state and country fields from the Locations table.

I want to be able to type the city, state, etc. into the portal, and then have the lookup copy the correct id into the Fossils::location id field. I want the lookup to be looked up when all location fields match (city, county, state, country)a location in the Locations table, and I want a new Locations record to be created if they do not match.

Do I need to create another TO of the Locations table in the relationships graph , then do a multi-criteria match using those fields? This seems like an exceedingly simple task, but everything I've tried seems to fail in one way or another...

Thanks in advance for the help!

Jake

[Aussie John]

You dont need to do the lookup. In the relationship dialog check the box that says "allow creation of records in this table via the relationship". You can then add new records directly into your portal.

I assume you are trying to verify that no two identical regions are created so I would suggest creating a Calculation field which is a concatenation of the various locations and set to validate as unique.

Also, I dont know what you are trying to show but I would reverse your relationship and have the location as the main layout and the fossils as the portal records. Feel free to disregard this bit of advice!!

Regardless make sure the parent of the relationship creates the ID autmatically (serial number) and the portal ID will be created automatically.

[jbenner]

Thanks for the reply, John.

I will elaborate a bit on the failure of one of my prior attempts that I thought should work:

Each fossil comes from only one location. One location could have produced many fossils. The point of view is that of the fossil, which is why I would like to have the location data on the fossil layout.

When I place a portal containing the location data on my fossil layout, I expected to be able to permanently record the location ID (serial looked up from locations table) in the location id field in the fossil table. The failure came when typing into the portal a location that already existed in the locations table: A new record would be created, with a new serial number in the locations table--a duplicate! This new location serial number would then be looked up and copied by my lookup in the fossil table, and I was well on my way to having one unique location per fossil record...

In the relationship dialog check the box that says "allow creation of records in this table via the relationship". You can then add new records directly into your portal.

make sure the parent of the relationship creates the ID autmatically (serial number) and the portal ID will be created automatically

I have done these. I can enter data directly into the portal, and the ID is created automatically.

I assume you are trying to verify that no two identical regions are created so I would suggest creating a Calculation field which is a concatenation of the various locations and set to validate as unique.

Yes! Although a concatenation (&) would put each of my fields into one field(?) Can you send me to an example of this technique?

I would reverse your relationship and have the location as the main layout and the fossils as the portal records. Feel free to disregard this bit of advice!!

I definitely see the logic behind this suggestion, but due to my POV interest, I think I'd better stick with the current setup.

Thanks again for your help.

Jake

[jbenner]

I've attached the little file I've been using to test the concept

locationtestCopy.zip

[Aussie John]

Hi jbenner

To make a concatenated field;

Make a separate calculation field and put an ampersand symbol (&) between any fields you wish to join together. Something like this, "location:state&location:city&location:county" (no inverted commas) Add whatever extra fields you wish. You will end up with a string containing all the location info in one field.

To make that record easier to read you can add "text" for example;

"location:state&"_"&location:city&" ("&location:county&")"

A result like Arizona_Tampa (maddison) might be a result for the geographically challenged or someone not from the States

<<<Each fossil comes from only one location. One location could have produced many fossils. The point of view is that of the fossil, which is why I would like to have the location data on the fossil layout.>>

This implies that your relationship parent to child should be Location to Fossil. Otherwise the fossil as parent will result in only one location to put into the portal. By using my approach, an additional layout will be able to represent the fossil and its location as the point of interest.(by using "view as list" in the "view" menu and sliding the body part up tight to the fields)

If you decide to change the relationship and you have already input a bit of data into your database you can import the data into the new field from within the same file. The import process recognises the tables rather than the whole record.