2 Replies Latest reply on Oct 19, 2013 5:10 PM by kaiviti

    Separating One Record into Two

    kaiviti

      I have a "Ship Movement" table that has individual records for each ship. There are multiple ships and they arrive at different times; some on the same day and other a day or two later. Some ships have two people departing at a certain location at exactly the same time, and others have just one. These persons are captured in the same record as fields "person#1" and "person#2". The ship is identified as "ship_name" and there is a primary key called "__kp_booking".

       

      I need to separate these persons into individual records and each having the same "ship_name", "arrival_date", "arrival_time", "arrival_location", "departure_date", "departure_time", "departure_location". I am not sure how to do this.

       

      I am assuming that I will need to have a separate table where the "separated" records are kept. I need some help in writing a script that will do this. It will need to create a new record IF a second person is getting off the same ship or just copy the original record IF it is only one person. In either case, the "person#1" "person#2" fields will have to be converted into a singular "person" field.

       

      From these results I will then need to create a report that shows whether these people will be in for breakfast, lunch or dinner. It will depend on the arrival and departure times of each individual person. More on that later....

       

      Any help would be appreciated.

       

      Ivan.

        • 1. Re: Separating One Record into Two
          erolst

          For the beginning, you only need one additional table (let's call it Shoreleave), with its own primary key, a foreign key of _fk_booking and a name field; since all data of the ship movement are identical for both persons, they can be referenced by the Booking foreign key and needn't be copied. btw, I'd recommend using a Persons/People table and a foreign personID instead of names, and the same goes for the ships whose movements you record in bookings. Anyway …

           

          To convert your existing person entries into new child records, you don't actually need a script; if you had person #1 to, say, #20, writing a script would be worth your while, but with only two fields …


          In Bookings, find all records with a person #1; go to Shoreleave and import the records from Bookings, where _kp_booking goes into _fk_booking, and person #1 name goes into name (consider using an ID!). Search all bookings with person #2 and repeat.

           

           

          Define a relationship between Bookings and Shoreleave as _pk_booking = _fk_booking, allow creation of related records, and put a portal into the new table. You will see the respective child record(s) youjuzst created, and you can add as many crew members as you like.

           

          Now Shoreleave will be (as you have seen correctly) the right place to create a report layout. If you're clear about the other data you need to record, this structure can quite easily be extended, e.g. by adding fields for arrival and departure times (of the individuals), and/or by adding another table with the breakfast etc. events for each person on leave. You'd then have several tables to use for reporting, depending on the entity you want to report on (People or Events) and the desired level of detail.

          • 2. Re: Separating One Record into Two
            kaiviti

            Thanks erolst,  This certainly works and it was staring me in the face.  I was trying to make a mountain out of a mole hill.  However, I think I still need a script.  The reason for this is that I need the process automated and I need it to be done when new bookings have been made.  I think the best way to automate this would be to have a script trigger for whenever the "Meals" layout is opened.

             

            You have got me on the right track and I now just need to perfect it.  Thanks again.