Hello,
Being newbie, I am still discovering filemaker and would appreciate your 
hints regarding this issue:

A DB, say DB1, contains people's data, among which country, zip code and 
city.
Another DB, say DB2, contains nothing but known countries and for each 
country involved zip codes and cities.

Now the goal is to automatically complete the third item in DB1 as soon as 
2 of them are entered, and the third field is selected. In other words: if 
I specify country and zip code, I want the city to appear in the city field 
as soon as it is selected. Similarly, I want the zip code to be 
'recalculated' if the zip code field is selected while the country and city 
are known.

I would appreciate your thoughts on how to solve this.
Thank you very much in advance,
Lieven

Lieven Tomme wrote:
> 
> Hello,
> Being newbie, I am still discovering filemaker and would appreciate your
> hints regarding this issue:
> 
> A DB, say DB1, contains people's data, among which country, zip code and
> city.
> Another DB, say DB2, contains nothing but known countries and for each
> country involved zip codes and cities.
> 
> Now the goal is to automatically complete the third item in DB1 as soon as
> 2 of them are entered, and the third field is selected. In other words: if
> I specify country and zip code, I want the city to appear in the city field
> as soon as it is selected. Similarly, I want the zip code to be
> 'recalculated' if the zip code field is selected while the country and city
> are known.
> 
> I would appreciate your thoughts on how to solve this.
> Thank you very much in advance,
> Lieven

Use calculation fields, multiple relationships and lookups.

For example, in both DBs have a calc field that concatenates country &
Zip, and define a relationship based on those two calc fields, then have
City in DB1 defined to be filled with a lookup based on that relationship.

Bill

Hi Bill,

First of all, thank you very much for your help (this is actually the
second time you provide me to the point answers onto my questions). 

The actions for filling City in DB1 work like a charm.
However, if I try to establish a simular lookup based onto a second
relationship, to determine a zip code starting from a city, this results
in the following (hopefully correctly translated) error: 

The field "zip" is being used in the lookup definition. Select another
field. 

Even trying any other field results in the same. I only get this fixed
by removing the previously established lookup for filling the City. So
basically, I am limited to 1 lookup definition. Do you have any idea why
I get this error in the first place, and how I should get rid of it? I
guess it has something to do with the city beling looked up based on the
zip code, and the vice versa. Thanks again, and looking forward for your
advice. 

Lieven



B Collins <bbcollins012@invalid.invalid> wrote in
news:4010584E.A6EBF5B5@invalid.invalid: 

> Lieven Tomme wrote:
>> 
>> Hello,
>> Being newbie, I am still discovering filemaker and would appreciate
>> your hints regarding this issue:
>> 
>> A DB, say DB1, contains people's data, among which country, zip code
>> and city.
>> Another DB, say DB2, contains nothing but known countries and for
>> each country involved zip codes and cities.
>> 
>> Now the goal is to automatically complete the third item in DB1 as
>> soon as 2 of them are entered, and the third field is selected. In
>> other words: if I specify country and zip code, I want the city to
>> appear in the city field as soon as it is selected. Similarly, I want
>> the zip code to be 'recalculated' if the zip code field is selected
>> while the country and city are known.
>> 
>> I would appreciate your thoughts on how to solve this.
>> Thank you very much in advance,
>> Lieven
> 
> Use calculation fields, multiple relationships and lookups.
> 
> For example, in both DBs have a calc field that concatenates country &
> Zip, and define a relationship based on those two calc fields, then
> have City in DB1 defined to be filled with a lookup based on that
> relationship. 
> 
> Bill
> 

Although my problem still persists (which still intrigues me), I though it 
might be wiser to offer search facilities in the country/city/zip database, 
rather than relying on a 1 on 1 relation between zip and city, since search 
features might show alike called cities.
Any idea which approach I should take here, in order to implement this, 
starting from DB1, and including the returning of selected city/zip code?
So basically, the idea would be to open DB2 from within the need to lookup 
data in DB1, search in DB2, have the user select 1 record, and return the 
record data (eventually a unique ID) to DB1.
Thanks in advance for helping me in this matter.

Lieven

Lieven Tomme <NOSPAM.1m92@gmx.net> wrote in 
news:Xns947AAC9EEE6CB1m92gmxnet@195.130.132.70:

> Hi Bill,
> 
> First of all, thank you very much for your help (this is actually the
> second time you provide me to the point answers onto my questions). 
> 
> The actions for filling City in DB1 work like a charm.
> However, if I try to establish a simular lookup based onto a second
> relationship, to determine a zip code starting from a city, this results
> in the following (hopefully correctly translated) error: 
> 
> The field "zip" is being used in the lookup definition. Select another
> field. 
> 
> Even trying any other field results in the same. I only get this fixed
> by removing the previously established lookup for filling the City. So
> basically, I am limited to 1 lookup definition. Do you have any idea why
> I get this error in the first place, and how I should get rid of it? I
> guess it has something to do with the city beling looked up based on the
> zip code, and the vice versa. Thanks again, and looking forward for your
> advice. 

....snip...

>>> Lieven
>> 
>> Use calculation fields, multiple relationships and lookups.
>> 
>> For example, in both DBs have a calc field that concatenates country &
>> Zip, and define a relationship based on those two calc fields, then
>> have City in DB1 defined to be filled with a lookup based on that
>> relationship. 
>> 
>> Bill
>> 
> 

One approach would be to format the Zip code in DB1 as a pop up list, 
then define the value list based on fields in DB2.

In DB2 define a calc field that concatenates contry & city. Define the 
value list for DB1 to use the Zip code as the primary field, with 
<Country City> as the second field, and sort by <Country City>. Now the 
list will show zip codes, countries & cities from DB1. The user picks 
from the list.

Define a relationship from DB1 to DB2 based on <country Zip>. that 
should be unique in DB2.

Now you can use either related country & city fields from DB2 in DB1, or 
define the Country & City fields in DB1 to be populated by calculation 
from the related DB 2 record.

I played with the other approach and saw immediately the multiple zip 
codes of one city would lead to trouble, etc. Also, it only allows the 
query one way; trying to do it two ways, as you discovered, results in a 
circular definition, which is not allowed. There is probably a way 
around that.

Bill

Lieven Tomme wrote:
> Although my problem still persists (which still intrigues me), I though it 
> might be wiser to offer search facilities in the country/city/zip database, 
> rather than relying on a 1 on 1 relation between zip and city, since search 
> features might show alike called cities.
> Any idea which approach I should take here, in order to implement this, 
> starting from DB1, and including the returning of selected city/zip code?
> So basically, the idea would be to open DB2 from within the need to lookup 
> data in DB1, search in DB2, have the user select 1 record, and return the 
> record data (eventually a unique ID) to DB1.
> Thanks in advance for helping me in this matter.
> 
> Lieven
> 
> Lieven Tomme <NOSPAM.1m92@gmx.net> wrote in 
> news:Xns947AAC9EEE6CB1m92gmxnet@195.130.132.70:
> 
> 
>>Hi Bill,
>>
>>First of all, thank you very much for your help (this is actually the
>>second time you provide me to the point answers onto my questions). 
>>
>>The actions for filling City in DB1 work like a charm.
>>However, if I try to establish a simular lookup based onto a second
>>relationship, to determine a zip code starting from a city, this results
>>in the following (hopefully correctly translated) error: 
>>
>>The field "zip" is being used in the lookup definition. Select another
>>field. 
>>
>>Even trying any other field results in the same. I only get this fixed
>>by removing the previously established lookup for filling the City. So
>>basically, I am limited to 1 lookup definition. Do you have any idea why
>>I get this error in the first place, and how I should get rid of it? I
>>guess it has something to do with the city beling looked up based on the
>>zip code, and the vice versa. Thanks again, and looking forward for your
>>advice. 
> 
> 
> ...snip...
> 
> 
>>>>Lieven
>>>
>>>Use calculation fields, multiple relationships and lookups.
>>>
>>>For example, in both DBs have a calc field that concatenates country &
>>>Zip, and define a relationship based on those two calc fields, then
>>>have City in DB1 defined to be filled with a lookup based on that
>>>relationship. 
>>>
>>>Bill
>>>
>>
>