10 Replies Latest reply on Feb 11, 2015 3:43 PM by JamesPeragine

    Add data from value in portal record field

    JamesPeragine

      Title

      Add data from value in portal record field

      Post

      I'm trying to add the distance between two airports.

      I have a table called Flights, a table called Airports, and I have a join table called Flight Legs.

      I have a PK for flights and a PK for Airports and a corosponding FK for each in my FlightLegs table

      I put a portal on my Flights layout.  I have a function that calculated the distance between airports based on LAT LONG cordinates stored in the Airports table.  

      With this setup, how can I get the LAT LONG data from the data in the previous portal row?  

       

       

      portal_row.png

        • 1. Re: Add data from value in portal record field
          philmodjunk

           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.

          • 2. Re: Add data from value in portal record field
            JamesPeragine

            Yes my calculation field is in the FlightLegs table, however, the latitude and logitude fields in the airport table.

             

             

            • 3. Re: Add data from value in portal record field
              JamesPeragine

              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.

              • 4. Re: Add data from value in portal record field
                philmodjunk

                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.

                • 5. Re: Add data from value in portal record field
                  JamesPeragine

                  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.

                   

                  • 6. Re: Add data from value in portal record field
                    philmodjunk

                    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.

                    • 7. Re: Add data from value in portal record field
                      JamesPeragine

                      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?

                      GetNthRecord("MyField"; (Get(CurrentRecord)-1))

                      • 8. Re: Add data from value in portal record field
                        JamesPeragine

                        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?

                        • 9. Re: Add data from value in portal record field
                          philmodjunk

                          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---<FlightLeg-----<Airports|Departures
                                                \-------<Airports|Arrivals

                          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.

                          • 10. Re: Add data from value in portal record field
                            JamesPeragine

                            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!