using relationships/portals for auto enter

[mike72]

Hi all - I've been searching around this and a few other sites and although there are some good tips and techniques here - I am still having a problem creating the following:

I have a main database that has blank fields: town, county, state, country. In a second database I have filled in fields: town, county, state, country. In this second db there is only one instance of each town, but many towns in counties, etc. I want to type the name of the town in the main database and have the county, state, country automatically pop up in the corresponding fields. I know this is possible but I have been trying with no success...the closest I got was returned with an error message "The field cannot be modified until....". (p.s. if it isn't blindingly obvious already that I am a complete novice - please reply with step by step instructions!) thanks in advance,

mike

[John Caballero]

Hi Mike,

Many states have towns with the same name. How do you forsee FileMaker knowing which county, state, and country to pick? A better solution would be to enter the zip code, which no two towns share, and have that be the lookup key. You'll need a zip code database that contains all the cities, states, towns, counties that correspond to each zip. Have a look here: http://www.zipwise.com/

[mike72]

Hi John

In my database I am lucky enough to not have duplicate towns. Each record in my "second" database has a unique town name. Since the names of the towns are unique (each town name is not duplicated and has its own record with corresponding county, state, etc) - I am hoping that the unique town name will act as a serial (or zip code) being the unique identifier for the record. A user enters the town name in the "main" databases town field, that is related to the unique town field in the "second" database, and the fields related to that database should fill in....? Because some of the towns are in international locations and ones that clearly do not have a post code, I won't be able to use that option. But thanks for your suggestion! Any more ideas/help?

-mike

[John Caballero]

Hi Mike,

OK, so what you want is a relationship from your main table to your "towns" table based on each table's "town" field. The other relevant fields in your main table get their data from a lookup based on that relationship. You set that up in the Options part of Define Fields for each field you want to auto-enter into. When a user enters a town name (and spelling will obviously be critical here) and then exists the town field, the relationship is "activated" and the lookup occurs. You might consider making a drop-down menu for your users to select from instead of having the user type in the town name.

[Ugo DI LUCA]

Notwithstanding John's suggestions, make sure the field you're trying to enter is not a relating field coming from your relationship Town::Town.

It should be a field from the current record.

If the relationship is established correctly, the other fields would come naturally as lookup fields, provided you define them to behave as such.

To make sure the field on layout isn't a related field, just switch to Layout mode and checks for any semicolons appearing before the FieldName.

[mike72]

With John's suggestions - the lookup worked - and everything is working as it should now...I was barking up the right tree before, but was having issues with the "The field cannot be modified until 'blank' (field) is given a valid value." error message - probably, as Ugo stated, I had irrelevant relationships for lookup fields (e.g. "Country::Country") that should have only been related to "Town::Town". No idea why it took me so long to figure out something so easy - thanks!

mike