Relational quandry?

[AbsoluteVoice4u]

Please relay if there is a place on the web that would help show me how to

accomplish the following?

I have two databases....

1st database has all the zipcodes in MD, DC and VA with the correct

corresponding County for each. ( two fields )

2nd database has multiple addresses with zipcode for each...but county field is

empty...

You guessed it, I want the 1st database to be referenced and have the correct

county show on the 2nd database. Whenever I import new records into database 2,

It would the correct county just show up.....then I could export the records

and have corresponding counties included for each record. This way, the relational database would automatically enter the county for me, directly relational to the zipcode being

supplied in database2.

It [color:"blue"] sounds simple but doggone, it's alot tougher than it looks. I have

read 99% of the help files in FileMaker and for some reason, I don't seenm to

be able to obtain the results I desire.

Any suggestions, please?

[Ender]

Set up a relationship between the address file and zip code file by Zip, then you can access the related State or County in an Auto-Entered Lookup, directly on a layout, or in the Export dialog box.

[AbsoluteVoice4u]

I am sort of new to all this....and uncertain as to the specifics of the procedure you relayed.......please elaborate a bit?....thank you...

I am looking to have the correct county show up in the one database by having the zip code look for the matching zipcode in the reference datatabase and then look at the corresponding county and then place it in the other database....does this make sense?

Thank you.

[Ender]

Sounds like you need an Auto Entered Lookup on the County field or just show the related field in the main database (the file with the address).

To do either, you'll need a relationship set up in the main file between the Zip code field in the main file and the Zip code field in the Zip code file.

This is easily done in the Relationship definition dialog:

1. Create a new relationship

2. Select the file to relate to (your Zip code file)

3. Choose the fields to relate from and to (Zip to Zip)

4. Ignore the other options, click OK and Done to accept the changes

Now for a Lookup on County:

1. In the Field Definition dialog, click the Option button on the County field

2. In the Auto Enter tab of the Options dialog, click the Looked-up value option

3. In the Lookup dialog, select the Zip Code relationship that was created above

4. Select the related County field from the field list

5. Don't worry about the other options, click OK on the dialogs to accept the changes.

This will make all new records in the main file automatically pull in the County data when the Zip code is entered.

If you don't need the county information stored (you probably don't), then it is actually better to skip creating a County field in the main file, and put the related County field on the layout instead.

To do this, create the relationship as described above, then

1. Go to the layout where you want to show the related field

2. In Layout Mode, drag the field tool onto the layout

3. In the Specify Field dialog, choose the Zip code relationship from the pop-up menu at the top

4. Select the related County field and click OK

Now in Browse Mode, whenever the Zip code field is populated with data that has a match in the Zip code file, the relationship will show the corresponding zip code data, including the County field that is on the layout.

So what's the difference between the Lookup and just showing the related field? Using a Lookup field is actually creating a copy of what was in the related County field at the time the record was created. Putting a related field on the layout will show the current related value whenever it is viewed. The Lookup requires extra file space for the field data, but it is faster if you have to do searches on the field (finds on related fields are slow over a WAN when there are many related records.)

[ESpringer]

It sounds like AbsoluteVoice4u also wants to have the address file (with county) serve as a source for data lookups from yet further related files that need to poll it for the county info for each person/address. In that case, a straight text field with the lookup option -- even if it uses more data space -- turns out to be necessary in FM6... (In FM7, one could easily relate to a file several steps of relationship away.)

[AbsoluteVoice4u]

- -- --- ---- ----- ------ -------

I gotta tell ya......you guys ARE Great It's now working like a charm ....so where do I send the checks?

My mother thanks you, My father thanks you, My sister thanks you....and I thank you.

(ok, trivia buffs, where did that "quote"originate?)

[Tripod]

James Cagney, as George Cohan, in 1942's Yankee Doodle Dandy?

Am I right?

[rockman]

You are indeed right!

[rogermax]

We sure have a lot of bananas these days....anybody got ice cream to go with 'em?