5 Replies Latest reply on Oct 15, 2011 3:09 AM by amandawrigley

    I know what I want, but I don't know what it's called!



      I know what I want, but I don't know what it's called!


      I am new to FileMaker Pro 11, with some experience (years ago) of building a similar database to the one I’m building now but in Access. Then I had a lot of tech support, so I just had to have a clear idea of what I wanted the system to do. Now I have no tech support and only dim memories of the terminology and functionality of things!

      I have worked through the User Guide and the Tutorial, and neither seem to help me with the next stage of what I need to do. Look-up tables and portals looked promising initially, but I don’t think they are what I need.

      I am building a database in which to record a particular type of television programme.

      I have a bunch of tables with fields. So far so good. I’ve even built relationships between them in the Relationships tab in Manage Database. (Although some look one-to-many and I can’t find where to change that.)

      The central form in the database is Productions, and this holds records on individual television programmes. In this form I need to have a field for Network, to indicate on which network the television programme was broadcast. I have a table for Network (fields: network_ID and network_name).

      What I would like to happen is, when entering data in the network field in the Productions table, for a drop-down list to appear showing me the contents of the Network table, so that I can make a selection without typing out the whole entry. And if the network I want isn’t listed, I’d like a prompt to appear to ask me if I want to add a new entry into the Network table. This is what happened in the database I worked on before in Access: I’d select ‘Yes, I’d like to make a new entry in the Network table’ and I’d get a data entry form for Network; I’d fill it in and on hitting carriage return there would be a new entry in the Network table and this would appear in the field in the network field in Productions and I could move on to the next field.

      Productions and Network should be many-to-many, so I’d also like to have the facility to record more than one network in Productions. I think this is called a combo box in Access. Also, if I delete a record in Network it should disappear in all instances where it occurs in Productions.

      Perhaps this is a more complicated thing than I remember, but I’d be very grateful for at least some pointers as to what the terminology is for this kind of thing in FileMaker Pro 11!

      Thank you for any help offered!

        • 1. Re: I know what I want, but I don't know what it's called!

          Productions and Network should be many-to-many

          In both Access and FileMaker, many to many relationships are implemented with a third table, a join table where one record in the join table establishes a link between on Productions record and one Network table.


          I think you want to type in part of the Network name in a drop down list (closest FileMaker can come to a combo box) that auto-completes. And if you type in a network name that does not exist in the Network table, it should ask if you want to add a new Network record and this record should automatically link to the current Productions record.

          Does that sound like what you had in mind?

          Just to get you started, here's a demo file I recently released for how to implement a Many to Many relationship in FileMaker. It has lengthy notes explaining how it works and also deomnstrates two alternative interfaces for establishing links between the two "many" tables:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          I realize that this demo won't get you all the way to where you want to go as it doesn't have an example of the auto-complete drop down list that I am describing, but it's a starting point. The auto-complete drop down, with scripted support, can be created in FileMaker, but I'll save that for a follow on post after I hear back from you.

          • 2. Re: I know what I want, but I don't know what it's called!

            Thank you! Sounds like you know what I'm after. I'm really looking forward to looking at this in detail tomorrow and will report back once I've tried to implement it! Thanks so much.

            • 3. Re: I know what I want, but I don't know what it's called!

              Hello there, and thanks again for this help and advice!

              Yes, what you describe about drop down lists, and then being able to create a new record sounds perfect.

              You're tutorial was really useful, but I only got so far: I've created the necessary join table, and I've created a portal in the Productions table. This portal displays information added in (what I would call) table view of productions, but I can't get the portal to function as a data entry. The cursor just won't go into the portal at all. In the portal I am pulling the network_ID from the productions_join_network join table, and the network_name from the network table. In Edit Layout I have played around with the settings in Inspector > Data, but to be honest it's all guesswork on my part and it's clearly not working!

              I'm sure there are some basic steps here that I've missed out and it's a simple case of learning what I need to do. Are you aware of a tutorial online which takes new users through thsi process baby-step by baby-step?

              Thanks for your help, PhilModJunk!

              • 4. Re: I know what I want, but I don't know what it's called!

                Go to Manage | Database | Relationships.

                Doubleclick the line linking your layout's table occurrence to the table occurrence box for the join table.

                Enable "Allow Creation of records Via this Relationship".

                • 5. Re: I know what I want, but I don't know what it's called!

                  Excellent, that works! Thank you - you've been a great help.