Need assist creating lookup or calculation

[Croe]

I have a table of country, province, district and village, with corresponding latitude and longitude coordinates.

My database contains records of known outbreak locations for infectious diseases. The diseases often break out in the same locations--I would like to create an automated lookup feature so I don't have to look up the lat/long for each new record and use the table of known coordinates.

Can anyone help me set this up in FM 9?

Thanks in advance,

Claudinne

[Quartekoen]

Relate your Diseases table to your location table based on the Name of your location. Then, on your Diseases table, make a new field for the lat and/or long, then edit the options for that field. In the Auto-Enter options for that field, check the "Looked-up value" option, and specify that it refers to the lat/long that's in your location table. Now, whenever you create a new record and enter the name of where the disease broke out, the lookup field should automatically update with the lat and long of that location.

Hopefully this answers your question!

[Croe]

Thanks for the instructions, I think I must have missed a step somewhere. I'll provide some detail on my data. What I have are db fields for:

Country, Province, District, Village, lat, long,

In my table of known locations:

Country, country_lat, country_long, District, dist_lat, dist_long and so on...

When I enter a new outbreak location, say

Country =Vietnam

Province=Kien Giang

District=An Bien

Village=unknown

FM would do a lookup on the most specific location I have, district in this case and pull up a known district lat/long from the table.

dist_lat 31.36 dist_long 31.71

I have created db fields for a lat/long coordinate in each: country, prov, dist, village

In relationships I linked dist to dist, dist_lat to dist_lat and so on.

In manage db I selected district and specified a lookup value to my table, same for dist_lat and dist_long.

Where did I mess this up? When I enter a new record and a district I know is in the table I am not getting a looked up lat/long.

Thanks so much again,

Claudinne

[Croe]

Thanks LaRetta, it works!!

Claudinne

[LaRetta]

Hi Claudinne,

Ummm, errr ... I had deleted my post because I hadn't fully read the thread and thought my answer was too spontaneous to be helpful. Hmmm, maybe my posts are best when I DON'T think very much.

I'm glad it was helpful, whatever it was I said ...

LaRetta

[comment]

I'm curious.

[LaRetta]

Sure. Catch me late with eyes crossing and ask me to repeat what I deleted because I thought I hadn't even read the thread properly which I was told was 'spot on' that I can't even remember. Sure.

I think I just said that lat and lon shouldn't be included in the reltionship if they were set to look up. Nothing major nor even necessarily right. No, nothing earth-shaking nor novel and not even particularly witty. :king:

[comment]

Well, it doesn't have to be today - or is it the end of the world tomorrow? It's just that it seems to me there are some tough issues here, so either I misunderstood the problem or you have come up with something I'd like to know about.

[LaRetta]

I very-much doubt that I came up with something you'd like to know about, Michael. Oh, it is quite rare for me but I hadn't read the entire thread. All I read was this portion:

I have created db fields for a lat/long coordinate in each: country, prov, dist, village

In relationships I linked dist to dist, dist_lat to dist_lat and so on.

In manage db I selected district and specified a lookup value to my table, same for dist_lat and dist_long.

Where did I mess this up?

All I thought (and said in my post) was that, if one wants to lookup the latitude then the latitude can't be part of the relationship itself. Same with longitude. I didn't see any tough issues because I didn't read it all. Maybe I didn't even help although Claudinne thinks so. Maybe I just said something which clicked the RIGHT answer in her mind. I have no idea if/how that helped because I didn't read the entire thread and still haven't (although I WILL read it later when I have time).

Vaughan

:king:

[LaRetta]

Okay I read it.

When I enter a new record and a district I know is in the table I am not getting a looked up lat/long.

And I think my answer stands. But since you saw more in the post now I'm unsure and think there must be more in the post than I see; it happens all the time to me. Some say I over-complicate but more often I miss complexities. :blush2:

[Croe]

The lookup is working perfectly. I created two additional tables of lat/long data for province and village. Linked the relationships and lookup options. Now when I enter a new province, district or village if it is known to the tables the correct lat/long is entered. Ya-hoo!

Now my next question for all of you FM experts is how do I go about updating the tables with new data every once in a while? Occasionally I get a new location--Do I look up the lat/long and enter into my Excel file I use to create my tables?

Why am I doing this you ask? Part of my job is to track emerging infectious diseases around the world. Avian Influenza is my primary focus, but I track lots of bugs that kill people. From the FM database I create a kml file to use with Google Earth, it's very cool.

Thanks again,

Claudinne

[comment]

OK, thanks. The thing that caught my eye was:

FM would do a lookup on the most specific location I have

That's not an easy thing to do.