Please help. Air ambulance quoting database.

May 23, 201114 yr

Newbies

Hi to everyone. I am kind of newbie in FM so a thank you all in advance for your help..

I am going to describe my situation the best way possible.

I work for a Air Ambulance charting company. When a client ask for a quote we do very simple calculations that right now we are using excel but we need to use something better like FM to store quotes and be able to print them or mail them.

In excel I have a datasheet with distances between cities and I use Vlookup to retrieve that distance. Lets put a simple example.. Lets say the distance between Laredo, TX and New Orleans is 600 miles..

A simple trip would be Laredo, tx -- New Orleans (600 miles) and then New Orleans -- Laredo (another 600 miles).

Lets say I have a table in FM that is called Distances where I have 3 columns.

Point A, Point B, distance

I can make that list with the right info and 1 record would be as example: Laredo , New Orleans, 600

The thing is this: In the quote table I try to put, lets say 10 rows with 3 empty cells.. In one you can type: laredo, and the other New Orleans and I need to retrieve the distance (600) and put it in the other cell. Continue in the next row with New Orleans, Laredo (and retrieve again the 600) The idea is to be able to sum the trip, in this case 1200 miles and be able to do some calculations with that info..

Could you please point me in the right direction to do all this in the best way possible? As you can see there are a lot of combinations of origins of Point A and destinations of Point B..

Thank you in advance for your kind comments and help.

May 23, 201114 yr

If given this task, I'd look to a web resource ( http://www.freemaptools.com/how-far-is-it-between.htm), for example, and use the webviewer somehow to return the answer.

May 23, 201114 yr

The idea is to be able to sum the trip

This will become very easy once you put the trips in a related table. What you have is very similar to a classic invoicing model, where trips are the line items and distances play the role of products.

---

P.S. There's another issue here: with your proposed method, you would have two records in Distances for every pair of cities, e.g.

• {Laredo; New Orleans; 600}

• {New Orleans; Laredo; 600}

which is obviously redundant. But the solution for this is more complicated and can wait until you fix the main problem.

May 23, 201114 yr

Author
Newbies

Hi again and thank you for your reply..

One think to mention is that an Airway has specific distances and sometimes that distance is greater than just the distance between 2 points.. Thats why I have to put a List or table with the distances..

As you mentioned, having Laredo, New Orleans, 600 and New Orleans, Laredo, 600 would be redundant.. Is there a way FM could do a "search" in the list and find the correct match in either way and bring back the 600?

Let me try to explain my "logic" how I think this should work (as in Excel)

In the quote form I have 2 empty cells where you put the Point A and point B.. and a "formula" is in the 3rd cell and this formula takes this two values to go lookup these points in the list and bring back the 600.. Is this possible?

Thank you in advance and I send you all a hello from México..

p.s. we use ICAO international airport codes so instead of Laredo, New Orleans, or whatever we use ICAO codes like: MMMY, MMUA, etc (this is just an example).. With these codes is easy and without error to find the value in the distances table.. tnx