4 Replies Latest reply on Nov 10, 2010 12:03 PM by JamesRoland

    [SOLVED] How does one insert a new record at a given location in a related table

    JamesRoland

      Title

      [SOLVED] How does one insert a new record at a given location in a related table

      Post

      Howdy.

      As the title suggests, what I would like to accomplish is to insert a new record at a given location in a related table. It would be "identical" to inserting a new row at a given spot in an Excel worksheet; new record is where it belongs, and all below have shuffled down one spot. Have tried a few things, but to no avail. Sorting (at least at this point) does not appear to be an option. Any suggestions will be greatly appreciated :)

      Cheers!
      Lyle

        • 1. Re: [SOLVED] How does one insert a new record at a given location in a related table
          philmodjunk

          Sorting should be the option here.

          The order in which you see the records in a portal will be the creation order unless you specify a sort order for either the portal or the portal's relationship.

          Here's one possible way to do this.

          1. Define an auto-entered serial number field in your portal table. Specify that your portal records be sorted in ascending order by this field.
          2. To insert a record after the third portal record, add the new record, but assign a number to this field that is the serial number of row 3 plus the serial number of row 4 then divide by 2 (take the mid point, in otherwords).
          3. This will insert the new record into the desired position in the portal.
          • 2. Re: [SOLVED] How does one insert a new record at a given location in a related table
            JamesRoland

            Thanks for the quick reply :)

            This is what I had also considered and attempted to do; renumber the auto-increment primary key field to have the "new" record where it should go; but to no avail ... still shows up at the end of the file.

            Perhaps a bit more of an explanation of the app involved.

            The application is used to create Task Cards for B737 maintenance items. The primary table contains the Task description and other data and there are 3 related tables: first is for Initial Intsrtuctions, second is for Task Detail instructions and the third is for Task images (diagrams, etc.). Each of these related tables contains one almost full page field which represents one printed page for the complete Task Card (the firtst 2 are text fields, the third is a container field). The issue arrises when there is a requirement to add additional Detail Page(s). For example, if there are already 16 Detail pages followed by 21 Image pages, and now there is a requirement to add a new 17th Detail page, how does one get that one to "stay there" and not at the end of the 21st image page :) ... hope this makes sense :)

            Then again, it could all be how I have it set up, but up until this popped up, the system was working just fine :)

            • 3. Re: [SOLVED] How does one insert a new record at a given location in a related table
              philmodjunk

              I use this trick in an invoicing system here where I work, to handle a special case where the line items sometimes need to be in a specific order.

              In order for this to work, you must define a sort order for your portal records. Without a sort order you get the records displayed in creation order and this is what you describe.

              1. You can open portal setup and define a sort order for you portal that sorts in ascending order on your serial number field.
              2. You could also go to Manage | Database | Relationships, click on the line linking the two table occurrences and specify a sort order there. Since this locks all references that use this relationship to a specified sort order, the first option is generally a more flexible way to go.
              • 4. Re: [SOLVED] How does one insert a new record at a given location in a related table
                JamesRoland

                Merci beaucoup! :) The portal sort did the trick!

                Thanks again! :)