13 Replies Latest reply on May 26, 2011 9:17 AM by philmodjunk

    Conditional Value List Relationships Help

    IgnacioRosenberg

      Title

      Conditional Value List Relationships Help

      Post

      Hello All:

      So, I looked at the example for conditional value lists and followed it through and it worked great.  Now, when I tried to do a 3-level one on my database, I've clearly screwed the pooch.  I'm assuming I've built my relationships wrong, but I'm not sure how to set them up for this.

      Right now I have 4 tables:

      1. Brand (Brand, brandID)
      2. Model (Model, modelID, brandID)
      3. Part (Part #, partID, Description, Picture, modelID)
      4. Final (Brand, Model, Part#, Quantity, partID)

      I've set the relationships linearly, so that:

      • Brand::brandID=Model::modelID
      • Model::modelID=Part::modelID
      • Part::partID=Final::partID

      Eventually I'd like to be able to choose a Brand on one drop down list, then a Model for that Brand and then a list that fits that particular model, and input the number of parts I want to put on the record.  I'll use portals for pictures and descriptions.

      Right now though, I get values on the Brand drop down list and every other one is empty.  I've set the Brand one to "Include All Values" and the other 2 related (Model starting from Brand and Part starting from Model).

      Clearly this linear way of doing it doesn't work, can anyone give me some pointers as to how I could set these relationships/value lists up?

      Thanks!!!

        • 1. Re: Conditional Value List Relationships Help
          philmodjunk

          What you need are these relationships:

          Final::BrandID = Model::BrandID
          Final::ModelID = Part::ModelID
          Final::PartID = Final::PartID // this last relationship is not needed for the conditional value lists, but you probably need it for other uses.

          In each case, the conditional value list filters values based on the value you enter or select in a Record in the Final table, so you need to use relationships that link to fields in the Final table.

          • 2. Re: Conditional Value List Relationships Help
            IgnacioRosenberg

            Thanks for the answer, its beggining to make sense.

            I'm a bit confused though, doing this now leaves the Brand table unrelated to anything and I'm not sure how to do a relationship within the same table (Final::PartID = Final::PartID), any ideas? Sealed

            In any case, I can't even get the parent drop down (Brand) to populate itself with ANY values now.  Do I have to "refresh" the file once I do these relationship changes?

            Thanks.

            • 3. Re: Conditional Value List Relationships Help
              philmodjunk

              The drop down for brand should not be a conditional valuel list as this is the first value list that drives the process for the subsequent conditional value lists. It should just list all Brand IDs from the Brand table.

              You can related two tables in FileMaker with as many different relationships as you need. You can also relate a table to itself in a relationship. The secret is in the term "Table Occurrence". Open Manage | database | relationships. Each "box" in this chart is a table occurrence that points to a data source table. Both the Table Occurrence (TO) and the data source table start out with the same name when you first define tables in your file, but you can actually double click these boxes and rename them any way you want. You have buttons at the bottom where you can add a new table occurrence and then select it's data source table. This table can even be a table in a different FileMaker file. You can also select a TO by clicking it, then click the button with two green plus signs and you'll get a copy of the TO you selected with a number after the name, but referring to the same data source table.

              Thus, you can click Final, click the plus signs button and you'll get Final::PartID = Final 2::PartID. If you look on the Tables Tab, you'll see that you now have two "occurrences" of Final, named Final and Final 2.

              Here's a tutorial on table occurrences you may find helpful:  Tutorial: What are Table Occurrences?

              • 4. Re: Conditional Value List Relationships Help
                mgores

                Good tutorial Phil.  Learned another new trick, have been trying to think of a way to do that with the contactDB tables.  Thanks!

                • 5. Re: Conditional Value List Relationships Help
                  IgnacioRosenberg

                  Oh I see.  Never thought about that.  I will deff look into that tutorial.

                  So, even without relating the tables the conditional ones will "track through" just using the serial numbers?  I've just done that and now my Brand has populated, but it doesn't seem to track through to the other ones.  

                  I've set up the related vales (for Model and Part #) to be related fromt he table above i.e. Model starts from Brand and Part starts from Model, or am I setting that up wrong?

                  Cheers, sorry for the hardhead-eness, I'm learning this as I go along and I've never dealt with databases like this before!

                  • 6. Re: Conditional Value List Relationships Help
                    philmodjunk

                    You'll need the first two relationships to establish the conditional value filtering so that brand limits the list of models (Final::BrandID = Model::BrandID ) and Model limits the list of parts (Final::ModelID = Part::ModelID).

                     

                    • 7. Re: Conditional Value List Relationships Help
                      IgnacioRosenberg

                      But, don't I have to relate the Brand table to any othe table?  In my relationship manager that table is floating without anything attaching itself to any others...

                      I was under the impression all tables have to be related in order for serial numbers to "track through" to other tables.  Right now my Brand drop down menu is set to Include All Values and its populating fine, but the other just give me a blank box.  I'm wondering if either I'm setting my serial number/relationships wrong, or the "related vallues starting from" wrong.

                      Cheers Innocent

                      • 8. Re: Conditional Value List Relationships Help
                        philmodjunk

                        You may indeed need those relationships for other reasons but not for the conditional value lists requested in this thread.

                        A link from Final to Brands, (Final::BrandID = Brands::BrandID, for example), could let you display or look up data from the brands table on your layout, but it's not a part of the process that displays your conditional value lists.

                        • 9. Re: Conditional Value List Relationships Help
                          IgnacioRosenberg

                          I see.  Well its strange that I still can't get any values for the other 2.  No idea where I've gone wrong with this thing!

                          Thanks for all the insight though, much appreciated, I've learnt quite a lot.

                          • 11. Re: Conditional Value List Relationships Help
                            philmodjunk

                            The relationships look correct. If you aren't getting any values in the value lists for Models and Parts, then something is not set up correctly in the set ups for these value lists or in the data types of the fields used to define the relationships.

                            The conditional value list for Models, should list ModelIDs from Model, include only related values starting from Final. Final::BrandID and Model::BrandID should be the same data type (both type text or both type number.)

                            The conditiona valeu list for Parts should list PartID's from Parts, include only related values starting from Final. Final::ModelID and Model::ModelID should be the same data type (both type text or both type number.)

                            • 12. Re: Conditional Value List Relationships Help
                              IgnacioRosenberg

                              Yeah still nothing.  The only thing I have noticed is that some of the ID fields have Indexed in the Database Manager....could that be affecting this somehow?

                              Man how can this little tiny database be such a pain in the @ss??

                              • 13. Re: Conditional Value List Relationships Help
                                philmodjunk

                                Indexing is required on the "many" side in order for relationships to work. Including a field in a relationship will turn on it's indexing if it is not specifically disabled or is a field that cannot be indexed.

                                Thus I'd be concerned if Model::BrandID or Parts::ModelID where NOT indexed.