Hello,
I hope some of you already done this and you might be able to help me out.

So far I did as folllows:

I created two tables called aCity and dCity with two fields in each table:
fields are City and Country
Third table, called trip distance, have three fields:
- depurture city
- arrival city
- distance
to get cities i created dropdown list using values from aCity and dCity
tables and in the field distance I write distance in kilometers.
With this design I have to input two records for one trip.
Example.
Paris Amsterdam 300 km
Amsterdam Paris 300 km

Is it anyhow possible, or should I say, does anybody knows how to tell
filemaker that
Paris Amsterdam = Amsterdam Paris

This would cut this databese to half of what I have right know.

Of course, this databese will be remotly connected to databese I need
this information for, called Bussines trip plan.

Tomislav

In article <eqf6pg$95q$1 (AT) ss408 (DOT) t-com.hr>, tomislav <tomislav (AT) htnet (DOT) hr>
wrote:

Make one table for City. Make a second table to join city to city. Put
the field for distance in that join table. So you end up with just two
tables, and each city has only one record in the city table. The join
table has the distance between them. BY this means you have a many-to
many relationship between cities, so for example

New York - Paris
Amsterdam - Paris
New Your - Amsterdam and so on.

If you calculate distance by using latitude and longitude, the latitude
and longitude of each city would be in the City record for that city,
and the calculation would be in the join table (Citya_Cityb).

Details:

You don't say what version of FileMaker you are using. This answer
assumes FMP 7 or later. It will work for earlier versions but the
details would be different.

City table has a field kpCityID, which is a computer-generated serial
number that the user can not edit. Also of course has a field for Name
and any other fields you want for the City.

Call the Join table Citya_Cityb. It has a field kpJoinID which is a
computer-generated serial number than can not be edited, and number
fields kfCityaID and kfCitybID. Also has field for distance and any
other desired fields.

In the relationship diagram, define two Table Occurrences of City, name
them Citya and Cityb.

Define relationship between Citya, Cityb and Citya_Cityb as follows:

Citya::kpCityID = Citya_Cityb::kfCityaID
Cityb::kpCityID = Citya_Cityb::kfCitybID

As far as the relationship is concerned, City now looks like two tables,
Citya and Cityb, but it is in reality only one table, with each city
having one record.

There will be one record in Citya_Cityb for each pair of cities, showing
the distance between them. It does not matter which one you call the
departure or arrival city.

You can put portals in layouts of Citya and Cityb showing fields from
the records of Citya_Cityb that relate to the parent city, and showing
fields from the related City of the pair.

In a layout of Citya_Cityb, you can show related names of the two cities
of the pair.

You can put navigation pushbuttons on the layouts and in the portal rows
to go to the related record

And so on.

--
For email, change <fake> to <earthlink>
Bill Collins

Bill,

You mentioned calculating distance using long/lat. Do you have a formula (FM
script) perfected for this? I'm using a zipcode table that contains long/lat
info and want to implement a distance based billing algorithm. Having the
distance calc script woud save me a bunch of time.

Thanks
Steve

"Bill" <bbcollins (AT) fake (DOT) net> wrote

I don't have it worked up as a FileMaker script or filemaker calculation
but that would not be difficult. I would do it as calculation.

I do have suitable equations, as follows, based on sight reduction by
direct calculation given in the Nautical Almanac for year 2000.

The formulas are based on angles represented in degrees.

Notation:

The two locations are at points 1 and 2.

L1 & L2 are that latitudes of the two places, North being positive,
South negative.

Lo1 and Lo2 are the two longitudes, west being positive, east negative

DLo = Lo2 - Lo1

S = sin(L2)
C = cos(L2) * cos(DLo)
Hc = arcsin(S * sin(L1) + C * cos(L1))

Distance in degrees of arc of great circle = 90 - Hc

Distance in nautical miles on great circle = 60 * distance in degrees of
arc. (One nautical mile is one minute of arc along a great circle. You
can of course convert to statute miles or other units of distance once
you know the distance in nautical miles.)

(Comment: Hc is the calculated altitude of a celestial body at L2 and
Lo2 as observed from L1 and Lo1. Altitude is the angle measured
vertically from the observer's horizon, the observer being at L1 and
Lo1. The angular distance is the complement of the altitude, 90 degrees
minus the altitude.)

There is also a calculation for azimuth or direction at the start of the
great circle between 1 and 2. The direction of course changes as one
moves along the great circle. Azimuth calculation is as follows:

X = (S * cos(L1) - C * sin(L1))/cos(Hc)
If X > +1, set X = +1
If X <-1, set X = -1

A = arccos(X)

If DLo > 180 degrees, let Z = A
Otherwise Z = 360 -A

Z is the azimuth or direction measured from north, from 1 to 2.





In article <7c21a$45d322ba$d1a8fa82$5944 (AT) EDELTACOM (DOT) COM>,
"GSP@DCS" <info (AT) dcs (DOT) com> wrote:

--
For email, change <fake> to <earthlink>
Bill Collins

You say it's a "Trip plan" but don't specify how it will be used. You
also don't say what version of Filemaker you're using, but if it's 8.5
you can use the web viewer to plug the cities into Google Maps and get
driving distance or other sevices (like http://www.timeanddate.com/
worldclock/distance.html) to get city to city 'crow flies' distances,
or you can use google to find lat/long distance calculators.

G

On Feb 14, 9:54 am, "GSP@DCS" <i... (AT) dcs (DOT) com> wrote:

Thanks for your response.

I think I tried this formula once before. Problem is FM8.5 does not have an
'arcsin' or 'arcos' function. Not being a math whiz I'm not sure how to get
around that. Any alternatives?

Steve

"Bill" <bbcollins (AT) fake (DOT) net> wrote

There is another formula for the great circle distance that uses the
arctan function. The Wikipedia article on great circle distance gives a
good discussion of the topic and several examples. Do a Google search on
Great Circle Distance and you will find the Wikipedia article and
several others.

In article <b95e4$45d36ca9$d1a8fa82$6344 (AT) EDELTACOM (DOT) COM>,
"GSP@DavClaServ" <info (AT) davclaserv (DOT) com> wrote:

--
For email, change <fake> to <earthlink>
Bill Collins