8 Replies Latest reply on Nov 23, 2011 10:51 AM by ShaunHamid

    Creating New Records in Parent Table

    ShaunHamid

      Title

      Creating New Records in Parent Table

      Post

      I have built a database to track information on television shows.  The tables are set up as Show (for the show overall) which relates via a PK/FK to Episode (for each individual episode) which in turn related the same way to Media (which tracks the inventory of media like tapes or DVD's for each episode).  Each of these tables has its own layout which holds data pertinent to each table.  I have portals set up so you can create new episodes via the Show layout, and likewise you can do the same with Media via a portal on the Episode layout.  I am able to create new episodes (though not entires shows) via the Episode layouts.  My question is, would it be possible to add records to the Show table from the Media table layout?  Right now I cannot get anything to work as far as getting the interface to create anything on the Media layout.  I can relate the episode key to the media layout and create episodes, but is it possible to create a level higher?  Am I making sense?  I have an additional question, but it is somewhat dependent on this one so I will stop there for now.  Thanks a bunch!

       

      Shaun

        • 1. Re: Creating New Records in Parent Table
          philmodjunk

          I'm guessing you have this relationship:

          Shows::ShowID = Media::ShowID

          if so, then this script would create a new show record and a related new Media record when performed from a Layout based on Media:

          Freeze Window
          Go To Layout [Shows]
          New Record/Request
          Set variable [$ShowID ; Shows::ShowID]
          Go To layout [original layout]
          New Record/Request
          Set Field [ Media::ShowID ;$ShowID ]

          Keep in mind that the only fields in the new Shows record to get any data when this script is performed are any that are defined to auto-enter data. Shows::ShowID would be such a field--auto-entering a unique serial number. Other fields, such as the title of the show would still need to be filled in by the user. If you put these fields on you Media layout, you can perform the above script and then fill in the empty fields. If you do not put the fields on the media layout, you'll need to use other means to fill them in.

          • 2. Re: Creating New Records in Parent Table
            ShaunHamid

            Actually the relationship is like this Show::ShowID = Episode::ShowID to Episode::EpisodeID = Media::EpisodeID.   Can I relate Media to Show thusly or do I need to create a new table occurence?  I am a bit of a journeyman at best, so I'm sorry if it is a bit inelegant.

            • 3. Re: Creating New Records in Parent Table
              philmodjunk

              In that case you need to also create a new Episode record. Sure this is a good idea? Wouldn't it make more sense to create new Show and Episode reocords on layouts based on those table and then start creating related Media records?

              Would it work to use a portal to Media on an Episode layout to create new Media records? (With such a layout, use the above script, but change references to the Media table into references to your Episodes table.)

              Answering the original question:

              Freeze Window
              Go To Layout [Shows]
              New Record/Request
              Set variable [$ShowID ; Shows::ShowID]
              Go To layout [Episodes]
              New Record/Request
              Set Field [ Episodes::ShowID ;$ShowID ]
              Set Variable [$EpisodeID ; Episodes::EpisodesID]
              Go To Layout [original Layout]
              New Record/Request
              Set Field [Media::EpisodeID ; $EpisodeID]

              • 4. Re: Creating New Records in Parent Table
                ShaunHamid

                I agree, the best case here is to only create media records from the Episode layout via portal, I was just curious if it was possible.  Thanks PMJ.

                 

                My second question is this.  So some of these media elements are housed to a storage facility offsite.  I want to create a table where I can create a box and populate these boxes with the assorted media from different episodes.  So say I sent box 112 to the offsite place and put in a DVD from Show A Ep 1 and a Tape from Show B Ep 3 they would list in this box.  I would be able to assign the media to this box.  I am assming it would be a SHipment Table, but how would I relate it?  Any help would be useful help.

                • 5. Re: Creating New Records in Parent Table
                  philmodjunk

                  Presumably, you'd create a table of box records with a related LineItems table to list the items packed into that box... This is much like an invoices type setup, but instead of listing each item purchased in a sales transaction, you are listing each item packed in a specific box.

                  • 6. Re: Creating New Records in Parent Table
                    ShaunHamid

                    The problem with that solution is that the items are universal.  So it is effectively possible for a user to populate multiple boxes with the same media piece (like in an invoice solution).  The Media needs to be unique and singularly assigned to a box.  Thus I suppose I would need to be able to assign the media piece to a box, and then see those boxes with all related media that assigned to that box on a separate layout.  Is this just a filtered relationship?  Am I missing something with using a join table? 

                    • 7. Re: Creating New Records in Parent Table
                      philmodjunk

                      I'm not sure I understand your description of the problem. The "items" table that would list the box contents is indeed a join table linking specific media records to a specific box record. If every media record is unique, you do not need the items table, you can add a Box ID field to the media table and use it to link each media reocord to the box in which it is stored. A layout based on this "boxes" table would look much the same, but in one case, you have a portal to an Items table serving as your "join" table and in the other you have a portal directly to the Media table.

                      It's function, however would be different. With an Items table, you just select items from a drop down in your portal. If it's a portal directly to media, you have to use controls on your layout for selecting media to put in the box that are external to the portal. A global field with drop down list and script trigger controlled script is one method. A filtered "selection" portal where you enter text in a search field and then click a listed media record in the portal to assign it to a box is another option.

                      • 8. Re: Creating New Records in Parent Table
                        ShaunHamid

                        Thanks so much.  I was able to relate it as you said!  I have a diffrent unrelated question so I will post a new topic.  Thanks again.