I need two fields for country. One is the standard country name (Unites States) and the other is the ISO 2-character country code (US). My plan was to have the 200 and however many country codes that we use live in a separate database table. From that table we would be able to keep the relationship of the country names and the ISO codes pretty clean. Here's where my issues come up.

I've managed to get it work going one way, in which the country name can be entered and the ISO code for that country appears in another field. However, I need this to instead work both ways. Entering "US" in the country code field would put "United States" in the country name and vice versa. Is this possible?

I have looked into the EventScript plugin and thought that might be an easier way to trigger the lookup. When the country name field changes, perform a lookup for the appropriate country code, etc.

Is this possible to do with relationships?

A simple auto-entered calculation will work if the "do not replace" checkbox is checked. The downside is that you have to clear the country field if you want to put in a different code or v. versa.

Seems to me there was a technique to make that work but I can't remember where I saw it.

What I ended up trying out is a set of scripts that run when the country code or country name is changed. Basically, if you change country code, the script does a lookup and overwrites the country name. It works well, but has some issues whenever I try to do an import. I've got to find a workaround for that, but for now I'm just handling the imports without Country info...

Hi

In this way whichever field you edit you'll have what you wanted.

In the Main table there are two fields:

Country code text field with auto-enter option ( alway evaluate ) of this calc:

Case(

Get ( ActiveFieldName ) = "country name"; lookup ( Countries by name::country code );

Upper ( country code )

)

Country name : text field with auto-enter option ( alway evaluate ) of this calc:

Case(

Get ( ActiveFieldName ) = "country code"; lookup ( Countries by code::country name );

Proper ( country name )

)

"Countries by name" and "Countries by code" are two occurrence of the same table ( Countries )

Try this file.

BTW: if you can't download the attached zip file, write me and I'll send it directly to you.

[email protected]

[color:red]EDIT: Only now I saw that you still are on 5 !

Sorry, but no way to use what I suggest you.

Main.zip

Edited September 29, 200817 yr by Guest
Added file for Daniele

Both of these suggestions will not work since you are on FM5.

Try this file.

I can't edit this post anymore, so here is the file ...

Main.zip

I have added the file for you on your old post.

BTW for anyone else who has added a file to a thread during the time of the forums issue. PM myself or one of the other moderators that are online with your file and thread and we can reattach your file for you.

Edited September 29, 200817 yr by Guest

Thanks, John !