7 Replies Latest reply on Aug 16, 2014 7:26 AM by taylorsharpe

    Complicated Layout Help Please


      I am trying to develop a solution that displays on one layout the movements of pilots. We have several bases where pilots are accomodated and fed. I need to display a list of pilots that are in each of these locations. Tabs can be used to flick between the locations but, the layouts should be the same. I want to set up the display so that it is like a daily diary where down the left side is the name of the pilot, the ship he arrives on, the landing departure location, the date and time he arrives in the house. On the right side, the ship he is departing on, the boarding location and the date and time of boarding. On many ocassions, the departure details are not known for some time so these details will remain blank for awhile. The other complicated part is that the arrival and departure locations could be one of three locations at each area. Considering that we have multiple houses, the arrival and departure locations for each are different and again, there can be several for each house.


      The raw information I have is that a pilot boards a ship at either A,B,C,D,E or F location on a certain date and time; he is scheduled to get off the ship in either U, V, W, X, Y or Z location at a certain date and time.


      Assuming A, B and C are boarding / departure locations for the house in area 1

      D, E and F are for the house in area 2

      U, V and W are for the house in area 3, and

      X, Y and Z are for the house in area 4


      I have tried a couple of options:


      1) I set up a layout with two portals. One is for the arrival details and the other is for the departure details. But, they are not directly linked. Therefore, sometimes (and most of the time) there are more on the arrivals side than on the departures side so, the records do not line up and this looks like a dogs breakfast. Portals does have a lot of apealto me though because I would very much like to add another portal on the side that shows additional informarion for the house location.


      2) Using a list layout I can display all the arrival details in chronological order. Displaying the departure details on the same line is the problem. The work around I have is to have some additional fields for manual entry of the departure details. This detracts from the prime objective of making this as automated as possible. The less we require the user to put input in the better the solution will work.


      Unfortunately, the FM12 file is too large to attach. I have attached screen shots of the two methods as described above.


      Any help on this would be appreciated.


      Thanks in advance,



        • 1. Re: Complicated Layout Help Please

          Hi Ivan,


          I'll take a stab at this ... however, I don't know much about your current table structure and the finer points of your need. So, with the limited info you've given here hopefully my input will at least get you thinking.


          It seems you might have over-complicated this solution. You can do this with 1 table where each record consists of fields for both the arrival and the departure. This will allow your fields to always line up. Your groupings that you've described in the middle of your post would be calc fields in the solution. Your interface can be built from relationships (or even finds) which can display active records (where pilots are now) or historical based upon the selections a user makes at the top of the screen. I don't think you'd even need tabs. With the selections fields up top being (in no particular order) Movement, House, Pilot, Ship/Place & Location with all the possible values in a value list for each. As the user makes the various selections the relationship displays the matches. If you feel the relationship would be too complicated, you can trigger a find as each field is exited by the user.


          Just some thoughts.



          • 2. Re: Complicated Layout Help Please



            Thanks for your reply.   Your suggestion of getting the arrival and departure fields into one record is exactly what I am trying to do. This would then line everything up and make all my other calculation a breeze.


            I think a little more explanation is required.


            Currently, each movement of a ship is a record on its own in a "movements" table. The fields are: name_ship, name_pilot, location_boarding, location_departure, boarding_date, boarding_time, landing_date, landing_time.  Each time a ship is added to the record all the fields are assigned except the pilot.  sometimes this is not assigned until the last moment so this adds another little complication.  I would like to have these unassigned ships show up in my table at the bottom of the list and in chronological order.


            The problem is getting these unrelated movements from the "movements" table into one record.  This one record must be for the same pilot and must be the next assigned ship for that pilot.  Take an example of Pilot A.  He arrived on a ship at a certain location on a certain date.  There are no ships assigned to him for several days.  There are several ships listed at the bottom that do not have a pilot assigned to them yet.  When he is assigned to one ogf these ships, the ship should be placed on his record and removed from the un-assigned list at the bottom.


            In between time, several other pilots arrive and depart by ship or airplane.  They can in ain a similar situation to Pilot A or they can already have a departure ship assigned to them.  I need to have a layout that dispays a record for each pilot by name, and sorted by arrival date.  This record should have all the other fields related to his arrival plus his departure.  The tabs at the top of my layouts are presently simple filters so that I can see what movements are taking place at each location.


            I have a bit of an idea for how to write a script but it seems rather complicated.  I think I have to first create a joining table that will match up the arrival and departures for each pilot.  Taking into consideration that the name of a pilot for a ship can, and regularly does, change means that the record can change repeatedly until the pilot has actually boarded the ship.  This record can only be locked down once the boarding time is in the past (less than the current time).  I have a feeling that for this join table to work, each time the table is accessed, or a pilots name is changed, all the records that have not been locked down must be deleted and rebuilt.  Scripting this is going to be a bit of a challenge.


            The other part of the script, for matching up the pilot to the arrival and departure ships is also difficult.  I think it should involve looping by first filtering all the records by a pilots name; then further filtering them into the arrival and departure location (which could be any of the three or so for a particular site); then trying to match up the arrival and departure movements in chronological order.   The location filtering would have to be done according to what location tab has been selected.  This looping would have to be done for each and every pilot and then the records would need to be sorted into chronological order based on the arrival date and time.  This script would have to be assigned to each location tab at the top and the script would have to be modified to become specific for that location.  Could probably use local variables to simplify this part of the operation.



            I was hoping that someone might come up with a simple way of doing this.

            • 3. Re: Complicated Layout Help Please

              I think you should make your table narrower (less fields), but use more records; i.e. a transactions table where each record has fields for pilotID, shipMovementID, movementType(ID) (arrival, stay, departure), dateBegin and dateEnd (or use timestamps).


              Then create records for a given pilot in sets of three: arrival at location on date as pilot for ShipMovement B, stay in location from dateBegin A to dateEnd C, and departure from location on date D as pilot for ShipMovement E.


              Give all three records the same groupSet ID to tie them together (incrementing per pilot). Fill in whatever information you have. When you fill in the departure date, automatically create a new pilot groupSet for the next location etc.


              For your portals, either use three relationships, or three portals filtered by movementType; sort them by groupSet. (Sorting by date would be correct for one pilot's records across the three portals, but not necessarily when looking at set records across multiple pilots.)

              • 4. Re: Complicated Layout Help Please

                Thanks Guys.


                I have actually managed to sort it out myself.  Just placing the post made me think of how I should go about it and then it was just a case of putting the ideas into practice.  It wasn't really that complicated and I am very pleased that it works well.

                • 5. Re: Complicated Layout Help Please

                  What resolution did you come to?

                  • 6. Re: Complicated Layout Help Please

                    I knew someone would ask.  Explaining is harder than actually doing it.  But here goes:


                    Firstly, I set global vaiables for each location and called them $$A, $$B etc.  Then I do a find to list records specific to that location based upon the arrival and departure locations.  Next was to set a timestamp for each record which was for an arrival, the departure time and for a departing vessel, the expected boarding time.  These were then sorted according to the timestamp.  Some of these departure vessels had pilots already assigned to them, others had the pilot field blank.


                    I then had to sort the records according to the assigned pilot and in chronological order.  This is because there can only be one instance of a particular pilot at a location at any one time.  He may or may not have an assigned departure vessel at that time.  An if statement will determine this because if there are two  records with the same pilot then one must be the arrival and the other must be the departure vessels.  It is then a simple case of copying the departure details of the departure vessel into new fields of the arrival vessel.  These new fields can then be diplayed on the same line as the arrival vessel.


                    Finally, we need to filter out all the departure vessels that have an assigned pilot.  We are left with a list of pilots (and there can be only one of each), that have details for both the arrival and departure vessels; or, the arrival vessel only with the departure fields still blank.  There is also a series of records showing that do not have an assigned pilot to them at this stage.


                    These un-assigned ships will eventually be given a pilot and the list will be updated accordingly.


                    I hope that explains it a bit.

                    • 7. Re: Complicated Layout Help Please

                      Something like this report makes me want to create a virtual report with SQL and Perform on Server for fast performance.  Oh well, so many different ways to solve a problem in FileMaker.