12 Replies Latest reply on May 5, 2015 6:16 AM by RuthCopping

    Linking records

    RuthCopping

      Title

      Linking records

      Post

      We are an Arts Charity and use Filemaker Pro V10 as our database where we hold details of people who we send our brochure out to as well as the details of events and workshops we run. 

      We have it set out in 4 tabs, show, workshops, venues and contacts with records in each. I am trying to find out if we are able to link several records in one tab (contacts) to 1 record in another tab (workshops) so we can use it as a booking system to record who has booked on to each workshop and be able to access that information in the future. 

      At the moment we are using Excel spreadsheets to record the delegates and in putting the details on to Filemaker afterwards but are not able to link the 2 sets of records 

      Any help or ideas would be great as we can not afford to buy a brand new system at the moment. 

        • 1. Re: Linking records
          philmodjunk

          Hopefully "multiple records on each tab" means that you either have portals on each tab or you really have a multiple layouts that simulate a tab control but the tabs are really buttons that change layouts. If you have multiple fields in the same record to record multiple contacts, for example, you have significant problem with the design of your database.

          What you are describing is a many to many relationship. A given contact can sign up for many workshops and yet each workshop will be linked to many different contacts. This is called a many to many relationship. Before seeing if this can be done with your current layout, we need to start with a simpler approach in order to describe the relationships that need to be set up.

          Start with these relationships:

          contacts-----<contact_workshop>-----workshops

          contacts::__pkcontactID = contact_workshop::_fkcontactID
          workshops::__pkworkshopID = contact_workshop::_fkworkshopID

          You can place a portal to contact_workshop on the contacts layout to list and select  workshops records for each given contacts record. Fields from workshops can be included in the Portal to show additional info about each selected workshops record and the _fkworkshopID field can be set up with a value list for selecting workshops records by their ID field.

          • 2. Re: Linking records
            RuthCopping

            Thanks Phil, 

            I have uploaded an image of the 2 tabs for you to look at to see if the layout is correct. I will try the relationships and let you know 

            • 3. Re: Linking records
              philmodjunk

              I don't see any portals. I suspect that your "tabs" are really buttons to different layouts. If it really is a tab control, then your database design has a significant problem.

              • 4. Re: Linking records
                RuthCopping

                What would I need to do to change the layout to make this work? 

                • 5. Re: Linking records
                  philmodjunk

                  To repeat from my first post,

                  Before seeing if this can be done with your current layout, we need to start with a simpler approach in order to describe the relationships that need to be set up.

                  I can't tell the underlying design of your database from a screen shot of your layout. When you open Manage | Database | Tables, you should find different tables defined for show, workshops, venues and contacts. If you find only one table, then you are using the same record to record data on 1 show, 1 workshop, 1 venue and 1 contact which is a recipe for series problems when working with your database. So you need to take a look at what you have first.

                  Assuming that you have the needed tables, I suggest to create a new layout for contacts and a new layout for workshops to use as a "test bed" for setting up your needed relationships and getting them to work without the added complications of your current design. Please note that my suggested solution includes adding a new table to serve as the "join table" between contacts and workshops.

                  And I forget a link in my first comment: For an explanation of the notation that I am using to describe the tables and relationships, see the first post of: Common Forum Relationship and Field Notations Explained

                  • 6. Re: Linking records
                    RuthCopping

                    Hello, 

                    Here is what I find when I open Manage | Database | Tables I think it means we have the right set up. Am currently working on the test bed and layout so will probably have lots more questions. 

                    Thanks

                     

                    • 7. Re: Linking records
                      philmodjunk

                      I would need to see the relationships used to link these tables with enough info to know which serve as the basis for your layouts.

                      • 8. Re: Linking records
                        RuthCopping

                        Here is the view of the relationships set up already. I am a new user to Filemaker and know the basics but know very little about the "backstage" work that needs to happen to make it work. So any help is greatly appreciated. 

                         

                        • 9. Re: Linking records
                          philmodjunk

                          ..with enough info to know which serve as the basis for your layouts.

                          My crystal ball is still in the shop. wink

                          You need to enter layout mode and explore your layouts. By selecting different layouts from the layout dropdown or by clicking the pages of your "book" control in the upper left corner (while in layout mode), you should be able to find out if you have a set of similar layouts or just one. By entering layout setup, you can check to see what is specified in the "show records from" dialog. The name selected there will correspond to one of the Tutorial: What are Table Occurrences? "boxes" in your latest screen shot. I need to know which of the above occurrences is the basis for your layout. My guess is that you may have several layouts designed to look like the same layout but each based on a different table occurrence.

                          • 10. Re: Linking records
                            RuthCopping

                            The Workshop tab is linked to the Shows table and the Contact tab is linked to the Contacts table. 

                            • 11. Re: Linking records
                              philmodjunk

                              Which means that they are not tabs but are layouts--just as I have predicted.

                              Decribe "Events" in your system. It would seem that an "event" is a particular workshop taking place at a specific venue--which implies that it also taking place at a specific data/time.

                              If i am right, signing up a contact for a workshop would really be signing them up for a particular event, the presentation of a particular workshop at a particular date and time.

                              • 12. Re: Linking records
                                RuthCopping

                                That is correct, we run workshops (events) that happen at a certain place at a certain time.