5 Replies Latest reply on Dec 6, 2011 2:37 PM by philmodjunk

    Creating a new record in a join table



      Creating a new record in a join table


      It does seem as if join table are a source of some trouble! I've looked through quite a few threads, but none seem to cover my problem completely. 

      I have two tables Towns and Agencies, with a small join table linking the two. The towns table is populated, as is the agencies table. On the Towns table layout, there is a portal that in theory shows agencies already in the town and that allows me to add new agencies to the list.

      This means that I need it to add a new record in the join table automatically, containing the town key and the agency key. What do I need to do to get this to work?

      I've tried a number of sample solutions (and looked at a demo or two) but I still end up with an empty join table!

      I'll continue to read up about this, but if someone is able to point me in the right direction in the meantime, that'd be very helpful and most welcome!

        • 1. Re: Creating a new record in a join table

          Silly me, I'm trying to do two things in one place - I just realised.

          I need a separate mechanism to create the join table records, using a script.

          I think I'm back on the right road now. (But feel free to confirm!)

          • 2. Re: Creating a new record in a join table

            The easiest way I found is to have a second portal for selecting "agencies" to assign them to Towns.  To do this you need another TO of agencies with a cartesan relationship to Towns.  That portal will then list all Agencies.  Assign a button on the portal row (or make the agency name field a button) to run the following script


            Freeze Window

            Set Variable $town = Town ID

            Set Variable $agency - Agencies 2::agency ID

            Go To Layout  Join Table

            New Record/Request

            Set Field Join Table::town ID to $town

            Set Field Join Table::agency ID to $agency

            Go To Original Layout

            Refresh window


            The agency you selected should then show up in your portal based on the Join Teble relationship.

            • 3. Re: Creating a new record in a join table

              Usually, the method for doing this, as I think you have discovered, is to use a portal to the Join table, not a portal to Towns or Agencies.

              If you enable "Allow creation of records via this relationship" for the join table in the Towns to Join relationship, then you can create a new record in the join portal on your Towns layout simply by entering data in the bottom blank row of the portal. If you then select the ID of the Agency in a drop down list or pop up menu, you can then complete the link from Town to Join to Agency records.

              I know you've looked at a 'demo' or two but here's one I uploaded fairly recently that covers all of this with copious notes and then goes on to explore some alternatives to the simple portal that can be used: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              Feel free to post back here with questions if there is some part of that demo you can't figure out.

              • 4. Re: Creating a new record in a join table

                Thank you both for your very quick answers.

                I tried the second one first as it seemed simpler and after a bit of head scratching I figured out where I'd gone wrong. Because the join table is empty, I couldn't grasp why I would use that to create the dropdown (how can there be a relation when there's a gap?, I thought). Looking at your demo file said that there could, even if it seemed a bit "odd".

                I will also look at Mark's suggestion because it seems to cover an important scripting concept that could be useful to learn and understand.

                As is often the case, it's simple once you learn how. Smile

                • 5. Re: Creating a new record in a join table

                  You'll find a variation of Mark's scripted suggestion in the demo file. Take a look at the "checkboxes" layout.