2 Replies Latest reply on Nov 25, 2013 4:59 AM by philmodjunk

    Creating a new record via relationship with a join table



      Creating a new record via relationship with a join table


           I'm very new at FM, and struggling with something which I imagine is easy.

           I have two tables, A & B, with many to many relationships.  I'd like to be able to enter data in A, including data that would use information from Table B, or create a new record in B.  For instance, if B is a list of first names, if I entered "George" in the first name field in A, and "George" already exists in B, it would populate the field in A; if George does not exist in B, it would create a new record for "George".

           I've created a join table, J, with its own ID, and fields for IDs from tables A & B.  I've linked aID in A to aID in J, and bID in J to bID in B.  Between A & J, I've selected Allow Creation ... (in J), and between J & B, I've selected Allow Creation ... in B.

           If I enter records in B, I can see them in A, but I can't add records in B from A.  

           When I search for this issue, the solutions are way more complex, involving scripts.  I'm looking for the simple FM setup that allows this.  Is there documentation somewhere that sets forth the proper setup for this requirement?

           Thanks for any help.


        • 1. Re: Creating a new record via relationship with a join table

               FileMaker Training Series12 is pretty good. I think it's free from the FileMaker website.

               I'm not sure what you are trying to accomplish. If you create a record in A and always want a related record created in B, you might not need B at all. But maybe you do so I might script it Go To Layout, Create New Record, Set Field, etc and then write the two keys into the join table. FTS 12 shows all of this and is a good introduction to simple portals as well scripting these sorts of things. As a bonus you'll learn local and global variables which are handy little devils to have in your toolbox. FMPA 12 is quite diverse on the techniques available for this.

               I find myself scripting portals and relationships all the time. Scripts also allow for the use of script triggers so that a lot of stuff can automated and hidden making for the simplest interfaces.

               When I first learned FMP I had trouble getting my head around portals. Once I got some good information on learning them I discovered that they are really pretty simple. They look complicated at first because they are extremely flexible in their usages.

          • 2. Re: Creating a new record via relationship with a join table

                 Normally, linking a record in Table A to Table B requires creating a new record in the Join table. You would select George in a new record of the join table in order to link the record in Table B to the current record in Table A on your Table A layout. Since Table A could be linked to many records in Table B, this is done with a portal to the join table where (in many cases) you select from a drop down list or pop up menu in order to select a record from Table B to complete the new portal record and establish the needed link.

                 This is what takes place in the Invoices Join table when you select a product in the portal in the invoices layout. You are creating a new record in Invoice Data--the join table--that links the current Invoice to a specific record in Products.