I have a function that calculated the distance between airports based on LAT LONG cordinates stored in the Airports table.
That sounds like something that should be calculated in FlightLegs rather than Airports as this is the distance between two airports and will vary with each possible pair of airports that makes up the start and stop of a given "leg" of your flight.
Yes my calculation field is in the FlightLegs table, however, the latitude and logitude fields in the airport table.
I got this done with a script trigger instead. I have it go through each portal record and calculate the distance.
On a side note, if I want to be able to add new portal records, is there anyway to have it NOT display an empty portal row at the end? I'd ideally like to have the empty portal row appear and be added by the user clicking an "Add Airport" button to add a record to the portal.
Shouldn't be necessary. If the distances are calculated in the FlightLegs table you can use either a summary field defined in FlightLegs or a Sum function in a calculation field defined in Flights that computes the total trip distance.
No script for that should be needed.
Note that a running total summary field totaling the distance could be put in the portal rows if such a value is useful.
I'm just trying to calculate the distance between the airport in the current row and the one before it. My script is working great.
Also if I want to be able to add new portal records, is there anyway to have it NOT display an empty portal row at the end? I'd ideally like to have the empty portal row appear and be added by the user clicking an "Add Airport" button to add a record to the portal.
Yes, but your flight legs table has one record for each such distance. If you use a match field for the Departure and arrival airport records in each such record, a calculation field in this table can compute the distance. Copying the previous record's arrival ID into a new FlightLeg record as its departureID can be done with an auto-enter calculation using GetNthRecord.
To eliminate the blank "add row", just clear the "allow creation of records via this relationship" option specified for the portal's relationship.
I see what you're saying. How would I use GetNthRecord in my auto-enter calculation? How do I reference the previous record, like this?
I'm really not following this but I'd like to understand this method you're talking about.
Right now I just have one airport in each FlightLeg record so that I can use the relationship to show all related data for that airport. I then have a script that cycles through each record and calculates the distances between each airport using a script trigger anytime an airport is changed.
You're saying that I should have a departure airport and an arrival airport in each FlightLegs record and a caculation field to calculate the distance. If there are two airports in each FlightLeg record what do I use for a match field?
By the name, FlightLeg, represents the trip from Airport A to Airport B. If you fly from A to B to C, you have a trip of two legs, AB and BC.
So you might set up your relationships like this:
Flight::__pkFlightID = FlightLeg::_fkFlightID
Airports|Departures::__pkAirportID = FlightLeg::_fkDepAirportID
Airports|Arrivals::__pkAirportID = FlightLeg::_fkArrAirPortID
Airports|Departures and Airports|Arrivals are two different Tutorial: What are Table Occurrences? of your Airports table.
You'd use a portal to FlightLeg to record each leg of your journey by selecting the departure and arrival airports in each record. Once you have specified the two airports for the first leg, subsequent legs, can copy the preceding record's _fkArrAirportID into its _fkDepAirportID field to save selecting the same airport twice. Each FlightLeg table can compute the distance of that leg once departure and arrival airports have been identified.
Ahhhh very clever. I will attempt to implement this. This might actually a better solution for Multi-Leg flights as well.
Thanks for taking the time to outline this for me!