AnsweredAssumed Answered

HELP creating an intelligent drop-down list

Question asked by GiuliaDeNobili on Jan 11, 2012
Latest reply on Jan 11, 2012 by GiuliaDeNobili


HELP creating an intelligent drop-down list


I'm a new user of FM, I just went through some guides but I still don't know how to do this.
I am creating a DB of archaeological sites, it is focused on geographical informations. Most of the data is stored in the main table. It is a very simple DB because I will need to export it into a GIS.
Among my fields I will have:

Country (in which country the site is located)
Region (in which region of that country)
X (longitude info)
Y (latitude info)

I have sites only from 7 countries:

but logically the total number of regions will be much bigger. I want to have drop-down lists to fill these fields so that all the data will be homogeneous. The problem is that if I create a list with all the regions together the list will be very long and uneasy to search.
So I was thinking to create a table with the country and region informations so that every time I specify a country from the list the drop-down list of the regions will list only the regions of that country. Is it possible?????

I also have selected one default coordinate for every region for those sites that I do not have coordinates for (so at least they will be positioned in the correct region on the GIS. So every region will have a X and Y data. I would like that the coordinate field in the main table would auto-fill with these data as I select the region.

So resuming it is something like that:
there are 7 countries
               each country has a variable number of regions
                               each region has one X and one Y field

what I want is that when I select the country from the fixed drop-down list FM will give me only the correct regions to choose among in the drop-down list of the regions and as soon as I chose a region it will fill-in the coordinate fields.

I don't know if I have to create a script or a calculation and how to organize the related table...
Any suggestion?