6 Replies Latest reply on Feb 20, 2014 8:08 AM by philmodjunk

    Relational Database question (FileMaker starter)


      Relational Database question (FileMaker starter)


           Hi, i'm not new to databases but instead new to FMP.

           I'm setting up a database with a structure like a Cars database. See picture for relations defined.

           When i add a new car i want to choose a brand (all brands are shown) and then a type (only types from that chosen brand must be shown in drop-down). But wat is the brand field on my Cars form? I don't need a foreign key for the brand i think, because when i have a foreign key for the type i know the brand via the relation...or do i some thing wrong?





        • 1. Re: Relational Database question (FileMaker starter)

               Is this what you mean?

               You want to select a Brand in field 1, then the list of types in the value list for field 2 are only the types appropriate for the selected Brand?

               This is called a conditional value list and yes, you'll need a Brand field in Cars table to use in a relationship that "filters" the possible values you might select for the type. But if you want, this can be a field with global storage specified.

               BTW, Your _kp_type_id field should also be defined as either an auto-entered serial number field or an auto-entered UUID, with the unique values validation specified. I can see that this is not the case by the crows feet in the relationship connector between cars and Type.

               Here are some links on conditional value lists.

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Relational Database question (FileMaker starter)

                 Thanks PhilModJunk for your quick and clear and helpful answer. 

                 I've got it working now....never had found it with out you, thanks




            • 3. Re: Relational Database question (FileMaker starter)

                   Sorry PhilModJunk, celebrated to soon..... I had tried the Menu example from the knowledge base article and even added another layer Flavours and it worked, so I thought i can apply this technique no to my cars example...to bad I didn't manage it.

                   I added a foreign key brand_id to my car table and made a conditional value list "Types of Brand"

                   Value List: "Types of Brand"

                   Use values from first field Type::_kp_type_id

                   Also display values from second field: Type::type

                   Include only related values starting from Cars


                   I can't see why this isn't working. Please help...;)


                   FileMakerPro Car example database:  https://copy.com/S2QS2rVsjSGhXYgm




              • 4. Re: Relational Database question (FileMaker starter)

                     Don't you want to select a Brand first and then select a type compatible with the brand selected?

                     You don't have a relationship in place that filters by brand.

                     You need this relationship in place:

                     cars::_kf_BrandID = Types|ByBrand::_kf_BrandID

                     Then your value list should list values from Types|ByBrand, include only related values starting from Cars.

                • 5. Re: Relational Database question (FileMaker starter)

                       Thanks again PhilModJunk for your quick answer! 

                       Yes, exactly. I want to select the Brand first and then select a type compatible with the brand.

                       I've got it working now. Did you mean to add another Type table instance with the name "Types|ByBrand" and related the tables Cars and Types|ByBrandID with the fields cars::_kf_BrandID to Types|ByBrand::_kf_BrandID?

                       The MergeField <<Brand::brand>> is only updated after the type is chosen. I understand because the relation is build via a Type to a Brand. That's why i must show the brand via the second field. Correct?

                       It's a nice feeling when I start to understand it cool, thanks!



                  • 6. Re: Relational Database question (FileMaker starter)

                         Yes. We call that "instance" a Table occurrence. See here to learn more about them as they are a key aspect of how FileMaker works: Tutorial: What are Table Occurrences?