7 Replies Latest reply on Aug 22, 2016 2:02 AM by deedeetee

    Drop Down list populating from another field without duplicating records

    deedeetee

      Hello, this is my first post, so I'm sorry if I'm missing some protocols, or repeating a question.

       

      I'm trying to teach myself databases and now reached a point where I need help. I'm essentially databasing a TV show, with stories / episodes / cast / crew etc. It felt right (especially once I'd discovered Portals) to have the main data in separate databases (stories in one, cast/crew details in another, characters in another, etc) and I was far too excited when I discovered that I could populate the drop-down menus from linked databases, so that I didn't have to learn all the id numbers for all the fields.

       

      For example, I have the Story database, a crew database and then a production list database that will link the two databases under a production credit. (e.g. story id 3 with crew id 4 under the credit writer.) But whenever I use the drop-down menu to select the writer's name, to populate this in the portal against the story, it creates another duplicate record for the crew member in the crew member database, with a brand new id number. However, when I take the tick out of the 'create new record' box in relationships, I can no longer access the drop-down menu at all. It's very frustrating.

       

      I'm also convinced I'm not explaining myself very well, so I would appreciate so more informed help, and questions around what I'm doing, in the hope we can all understand what I'm doing and how to fix it.


      Thank you!

        • 1. Re: Drop Down list populating from another field without duplicating records
          carlosilvia0

          HI,

          can you add a screenshot with database relationship and drop down list details?

          1 of 1 people found this helpful
          • 2. Re: Drop Down list populating from another field without duplicating records
            jbrown

            Hello and welcome to the forum. This is a good place to get your questions answered. There aren't too many protocols, I think. Just the normal ones: Be Nice.

             

            I'm glad you're learning FileMaker. It can be challenging but very rewarding as you are able to automate workflows and store, manipulate, and report on the data.

             

            You've got a good start, it seems. Portals are useful; they allow you to see related data from another table (by the way, where you use "databases", you mean "table" probably.

             

            Dropdowns are also extremely useful, so you've discovered some good things about FileMaker.

             

            A couple of points about what you're asking:

            1. a portal, as i said above, shows related data from another table. You can add records to that table when you have the "Create Records Via this Relationship" checkbox checked. So the table on which the portal is based should be the one where you want to create a new record. (I'm assuming the layout containing the portal is based on the Story Table). In your case, I think, you want to create a new Production List  record, so the table in the portal should be that. In that table you've got an ID_Crew field, something that will hold the name or ID of the crew member.

            2. The dropdown can come from any table, so you've got it from the crew. That works just fine.

            3. Check out all the resources you can on FileMaker. The FileMaker Training Series | FileMaker   is a great place to start. Other sources include Lynda.com. And of course this forum.

            1 of 1 people found this helpful
            • 3. Re: Drop Down list populating from another field without duplicating records
              deedeetee

              This is the relationship that links the Production Credits sheets to the Crew sheets. If I take out the allow creation of records tick, then I'm not able to use the Full Name (one of the fields in the Crew DB), as a drop down option. But If I click to allow, then I get duplicate records in the Crew DB, rather than the Production Credits just taking the information. (When I selected from the Drop Down box, it auto-filled the Crew ID field with a serial number...)

              Screen Shot 2016-08-15 at 19.05.52.png

              And this is the relationship that links the story to the production credit record.

              Screen Shot 2016-08-15 at 19.13.41.png

              Finally, a couple of shots showing the field (Full Name) that I wanted to use with the drop down box.

              Screen Shot 2016-08-15 at 19.07.13.pngScreen Shot 2016-08-15 at 19.07.37.png

              And this one shows the settings for the drop down option...

              • 4. Re: Drop Down list populating from another field without duplicating records
                deedeetee

                Thanks Jeremy,

                 

                A couple of points about what you're asking:

                1. a portal, as i said above, shows related data from another table. You can add records to that table when you have the "Create Records Via this Relationship" checkbox checked. So the table on which the portal is based should be the one where you want to create a new record. (I'm assuming the layout containing the portal is based on the Story Table). In your case, I think, you want to create a new Production List  record, so the table in the portal should be that. In that table you've got an ID_Crew field, something that will hold the name or ID of the crew member.

                 

                That's exactly what I'm trying to do, create the new Production List record, taking details from the Crew DB. If I use the ID of the crew record, then it works fine, but if I try and use the drop down box, then it creates a duplicate record.

                 

                2. The dropdown can come from any table, so you've got it from the crew. That works just fine.

                 

                As I type this reply, I've just realised that maybe what I've done wrong is select the wrong relationship link, and if I used the link via the Full Name link, it may work... Or am I wrong again?

                • 5. Re: Drop Down list populating from another field without duplicating records
                  deedeetee

                  Just had chance to try it and changing the relationship to match full name didn't work. So I still can't use the drop down from the crew DB to select records, without creating a new record in the Crew DB.

                   

                  *sigh*

                  • 6. Re: Drop Down list populating from another field without duplicating records
                    jbrown

                    Howdy.

                    If you want to send me a copy of your file, or a sample that is set up like yours, I'd be glad to look at it and figure it out.

                    • 7. Re: Drop Down list populating from another field without duplicating records
                      deedeetee

                      Thank you very much! What's the best way of managing this? I've got a back up of the whole series of databases on Dropbox, I could share with you...