8 Replies Latest reply on Jul 8, 2015 1:32 PM by Atcha

    Developing form for many-to-many

    Atcha

      Title

      Developing form for many-to-many

      Post

      Hi,

      I am still finding my way around Filemaker and need to set up a form with details from two tables A and B in which the link table (lets call it AB) is automatically updated when adding a cross reference to either A or B. Setting up the tables and their relationships was straightforward, but how do I implement this at the form level? More specifically, how can I set up the form so it will allow me to use a lookup linked to say table B and search for a value in that table and then automatically enter the corresponding B_ID as well as the A_ID into the linking AB table? Any pointers would be much appreciated.

        • 1. Re: Developing form for many-to-many
          philmodjunk

          There are multiple options. The most basic is to use a portal to AB on either your A or B layout. If you put a portal to AB on your A layout, the B_ID field from AB can be defined with a value list of B_ID and some sort of name or description from B to help select the correct ID value. If "allow creation of records via this relationship..." is enabled for AB in the A to AB relationship, selecting an ID for B_ID creates a new record in AB and automatically links the current record shown on your A layout to the selected B record.

          • 2. Re: Developing form for many-to-many
            Atcha

            Thanks, that is helpful. I have done as you suggested and placed a portal to AB on the form and populated it with sensible fields from B as well as the B-ID field from AB. Everything looks good and the correct data is shown and "allow creation of records via this relationship..." has been enabled for AB in both the A to AB and B to AB relationship. Now how do I "define the B_ID field from AB ... with a value list of B_ID"?

            • 3. Re: Developing form for many-to-many
              philmodjunk

              Open Manage | Value List and create one with the "use values from field" option. Select B_ID values from the B table as field 1. Use a different name or description field as field 2. You can choose to hide the B_ID data and only show the name or description field data. Either way, the value list enters B_ID data.

              It's also possible to include additional fields from B inside the portal row of your AB portal if you find that helpful.

              • 4. Re: Developing form for many-to-many
                Atcha

                Thanks for walking me through this (you may have guessed this is my first Filemaker database). I created a Value List with the settings you suggested. Now how do I use it? In your first mail you said: "If "allow creation of records via this relationship..." is enabled for AB in the A to AB relationship, selecting an ID for B_ID creates a new record in AB and automatically links the current record shown on your A layout to the selected B record." The phrase "selecting an ID for B_ID" throws me. Of course that is what I want to do and I now also have a Value List I could use, but but how do I link the Value List to a field in the AB portal (or anywhere else on the form for that matter) and what do I do to call up the Value List so I can choose the appropriate value?

                • 5. Re: Developing form for many-to-many
                  philmodjunk

                  Enter layout mode.

                  Select the field.

                  On the Inspector's data tab, near the top, you'll find controls for changing the field's format from an Edit Box to a value list format such as a drop down list or pop up menu. There are also controls there for creating or editing the set up for a selected value list as a shortcut for getting to Manage | Value list.

                  • 6. Re: Developing form for many-to-many
                    Atcha

                    Thank you so much! You have helped me enormously not to mention saved me hours of frustrating work. Why they don't put such elementary info in the manual, is beyond me. All they would have to do is walk the reader through a few practical examples (flat database, relational) and that would be enough to get one going. I feel quite embarrassed having to bug you with such elementary things.

                    • 7. Re: Developing form for many-to-many
                      GuyStevens

                      Hi Atcha, if you want to learn more beginner tips and tricks you can always check my youtube page. I have a video showing exactly the thing you have just been building: Check it out here. And there are more video's on my channel.

                      Greetings Guy

                      • 8. Re: Developing form for many-to-many
                        Atcha

                        Guy, I checked out your video and found it very informative. Bedankt.