7 Replies Latest reply on Jul 17, 2010 9:39 PM by crtopher

    Dynamic filtred list

    Sibban

      Title

      Dynamic filtred list

      Post

      Hello

       

      I have a problem of connecting two tables. Table one is called Car_parts and has 3 fields, car_parts_type, car_part and price. Here I have a long list of different brands of carparts that is sorted by types of carparts.

       

      The other table is a quotationtool and has the fields car_part_type, car_part , price and sum. The purpose of the second table is that you should have several options that you can check and get the total sum of it. 

      1) is to mark or choose what kind of type of parts, i.e. an engine, wheels or something else. 2) is to choose from a list of parts that is only has the same type of parts that has been chosen from the first option, i.e. different types of brand on wheels. 3) the last is to get the price from the second option and calculated the total sum of every chosen part.

       

      I have tried to have car_parts_types as a relationship with these two tables and make 2 drop list of car_parts_types and car_part where I marked in the specify value list the "Include only related values starting from quotationtool table" option.

      I got the list to function but I don't get the right price. I only get the first price on every type of parts instead of the correct carparts.

       

      Anyone has any good idea or better to solve this dynamic filtered list?

       

      /Thanks

        • 1. Re: Dynamic filtred list
          TSGal

          Sibban:

           

          Thank you for your post.

           

          Your relationship for the price is built upon the "car_part_type" AND "car_part" fields.  However, to get the filtered dynamic list, you only need the "car_part_type" field.  Therefore, you will set up two different relationships into the same file.

           

          Pull down the File menu and select "Manage -> Database...".  Click on the Relationships tab, and you will see a graphical representation of your tables.  Connect the car_parts_type in "Car_parts" table to the car_parts_type in the "Quotation" table.

           

          In the bottom left corner, click on the "New" icon and include another table occurrence of the "Car_parts" table.  You will now see a "Car_parts 2" table listed.  Connect both the "car_part_type" field and "car_part" field to the "Quotation" table.

           

          In the layout of your quotation, go into Layout Mode (from the View menu) and double-click on "car_part_type".  A "Field/Control Setup" dialog box appears.  Change the "Display as:" pop-up from "Edit Box" to "Pop-up Menu".  Under "Display values from:", select "Manage Value Lists...".  Create a new value list, name it "Car Part Type", and select "Use values from field:" and specify the table "Car_part" and the field "car_part_type".

           

          Next, double-click on the "car_part" field.  Also set this to a "Pop-up Menu", select "Manage Value Lists...", create a new value list, name it "Car Part", select "use values from field:" and specify the table "Car_part" and the field "car_part".  At the bottom, select "Include only related values starting from" and select "Quotations".  This is what will limit you to the filtered list.

           

          Now, create a portal into the "Car_part 2" table, and only include the Price field.  This won't get populated until both the "car_part_type" and "car_part" are selected. 

           

          In your "Car_parts" table, enter the following information:

           

          car_parts_type - car_part - price

          Wheel - Michelin - 60

          Wheel - Goodyear - 50

          Wheel - B.F. Goodrich - 40

          Body - front fender - 100

          Body - back bumper - 90

          Body - rear tail light - 25

           

          ------------

           

          Now, go to the Quotation layout.  Click on the "car_part_type" field, and the only option is "Wheel" and "Body".  Select "Body".

           

          Next, click on the "car_part" field, and only the values "front fender", "back bumper", and "rear tail light" are displayed.  That is, it is filtered based upon the "car_part_type" field.  Select "back bumper".

           

          Now, in your portal, the price of "90" is displayed.

           

          This will give you a good starting point.

           

          Please let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Dynamic filtred list
            Sibban
              

            It worked perfectly. I have been seeking for this solution for several days now.

             

            Thanks alot

            • 3. Re: Dynamic filtred list
              MarkyMark

              TSGal:

               

              I used the method you specified to create my own table. I am a beginner in FileMaker and would appreciate it if you could help me out with his issue I'm having:

               

              I have a "Market Profiles" database which contains two tables: Markets and Market Profiles. Market is a breakdown of the governmental departments of a given City or Town while Market Profiles would be used to store demographic information about that City or Town.

               

              In the Market Profiles layout, the user can select Country, then Province, then Region which are all filtering properly. Once I get to the Market (the City or Town), I get the entire list instead of a filtered one.  Please see the link below for an image of the Relationship Graph and please give me some insight. Maybe I missed something.

               

              Mark

               

              http://www.flickr.com/photos/49112342@N08/

              • 4. Re: Dynamic filtred list
                philmodjunk

                Additional information would help:

                 

                Exactly how did you set up the value list?

                Which field from which table occurrence is the value source?

                Which table occurrence did you select from the "starting from" value list.

                On which table occurrence did you base your layout?

                • 5. Re: Dynamic filtred list
                  Shaunnx

                  Hi Phil,

                  Can you please explain clearer how to setup and config the value list?

                   

                  My Table_Color has 2 fields:

                  - the 1st field is "Group", list value contains: Dark Color and Light Color

                  - the 2nd field is "SubGroup", list value contains:  Black, Brown, White, Beige

                   

                  I want to select Dark or Light in 1st field , and then the 2nd field will only show drop list black/brown corresponding to Dark

                  or White/Beige corresponding to Light.

                   

                  I did self-join relation between 2 field, Group in Table_Color Connects to SubGroup in Table_Color 2 (the occurence table)

                  I played around but couldnt get what I want.

                  Please help

                  Thanks

                  • 6. Re: Dynamic filtred list
                    philmodjunk

                    You need two value lists for this, not one.

                     

                    Your first value list would just display your two values: Dark Color; Light Color

                     

                    Your second value list would reference the field that stores your entire color range from Table_Color and you select the "Include only related values..." option specifying Table_Color 2 as the "starting from" table occurrence.

                     

                    Note that this assumes Table_Color is the specified table occurrence for your layout. I suspect that this is not the case and if so, you will likely need to modify your relationships in order for this to work.

                    • 7. Re: Dynamic filtred list
                      crtopher

                      Thankyou TSGal. I too, like sibban, have been searching for this answer and have just implemented it exactly step by step as described and it works perfectly! though I'm using FMPro11 so some of the dialog options were different. One of the reasons i could'nt get it to work initially was that my field value lists were "drop-downs" not "Pop-ups" - why would this make a difference?