4 Replies Latest reply on Feb 15, 2013 10:21 AM by lee423

    Value Lists in existing database

    lee423

      Can anyone tell me how I can change 2 exisiting fields from being a gerneral popup to a dynamic values list.

       

      Field 1 = Cruise Comapny

      Field 2 = Cruise Ship

       

      They both have a selection of ships and companies to select from in the popup but I want to relate then, ship to company.

       

      At the moment I have one table with all the data in it.

       

      I need help relating new tables and setting the fields.

       

      I am new to filemaker and would be greatful of any help.

       

      regards

       

      Lee

        • 1. Re: Value Lists in existing database
          Hudi

          Hey Lee,

           

          Welcome to FM.

           

          You need two tables, Company and Ship.

           

          Each needs an id field (call them _K_ID_Company, _K_ID_Ship) which you will use to relate the two. I'm assuming you know how to create auto enter id fields but if not just say so and we can start from there.

          A company can have many ships so in the Ship table create a foreign id field called company_id which is a plain text field. in it you can enter the company_id so that you can identify which ship belongs to which company.

           

           

          In the manage database (command/control shift D) under the relationships tab create a relationship between Company and Ship with

          Company::_K_ID_Company = Ship::company_id.

           

          To create a dynamic value list, once you're done with all that, go to FIle>manage>value lists.

           

          1.Create a new value list.

          2. select the option: 'use values from field' and click specify field

          3. where it says 'Use values from first field' select the ship table and the ship name field(or whatever field you want to show up in the value list).

          4. selecet the option 'include only related values startting from' and select the company table you created that is related to Ship thorugh the company_id relationship.

           

          That should do it. Just make a field on the company layout and choose your value list as the dropdown. It will show a list of all the ships associated with that company.

           

          HTH

          • 2. Re: Value Lists in existing database
            lee423

            Thank you so much for the above information.

             

            I have run through all the above yet still seem to be geting something wrong.

             

            Can you tell me if i have the relationship bit right?

             

            And the other question is do I have to set value lists for both company and ship fields?

             

            Sorry to be a pain but I really want to learn this.

             

            Thank you again for you help

             

            Lee

             

            Screen Shot 2013-02-09 at 15.59.53.png

            • 3. Re: Value Lists in existing database
              Hudi

              Hey Lee,

               

              If you're still stuck...

               

              Yes you have the relationships slightly confused.

               

              In your example it should be _pkCompany_ID = _fkCompanyID

               

              (fk meaning foreign key)

               

              That way, when you are on a layout based on Company you can see all the ships that are related to that company (ie, have a foreign key equal to the company primary key)

               

              Don't be discouraged if this doesn't make sense right away, its the most complicated concept to understand in databasing (but you can do it!)

               

              If I were you, I would get rid of that large table for now and focus on the realtionship between company and ship.

               

              try this and see how it goes. If you don't get it, upload the file and I can build you a small demo of how it should look.

               

              Cheers

              • 4. Re: Value Lists in existing database
                lee423

                How kind of you.

                 

                I have managed to sort it out now with the help of youtube lol

                 

                I am learning every day and like you say, the relationship bit baffals me loads.

                 

                I am doing some online help using Lynda.com which if helpful.

                 

                Thank you again for all you help

                 

                Kind Regards