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...