1 2 Previous Next 20 Replies Latest reply on Dec 20, 2012 2:56 PM by philmodjunk

    how to have a conditional value list make look up to related fields

    hanstrager

      Title

      how to have a conditional value list make look up to related fields

      Post

           I basicly have what seems to be a very easy problem, never the less it's not really working out for me...

            

           I have a small database where i have 3 tabels:

           1) product category (Fabric, leather, trims etc.)

           2) product information (name of product, size, supplier, price etc)

           3) technical overview of product

            

           so far i have made a conditional value list that filters my product based on the product category. I want choose a product based on the category and then in seperate fields show price, supplier etc. I want to do without using a portal, since im making it for a print layout...

           So far im testing with a price field, i've assigned prices to all my products, but the price only changes when i change the category it dosn't change when i chose the different products which is in the same category... what can i do..?

        • 1. Re: how to have a conditional value list make look up to related fields
          davidanders

               If you have a Contacts database, most people will have one phone number and one email address.

               But you design for worst case, so a Phone Table and Email Table allows unlimited phone numbers and email addresses.

               It seems to me, the Category would be a field in Products, size and colors would be Tables related to Products, as would Tech Overview.

               There are several online sources that may be useful to read.


          http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
          Home > Designing and creating databases > Creating a database > About planning a database
               A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.

               Follow these general steps to plan a database:
          <SNIP>

          http://help.filemaker.com/app/answers/detail/a_id/3248/related/1
          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database


          http://help.filemaker.com/app/answers/detail/a_id/3247/related/1
          Relational Database Design 101 (part 2 of 3)

          http://help.filemaker.com/app/answers/detail/a_id/3246/related/1
          Relational Database Design 101 (part 3 of 3)

          The White Paper for FMP Novices
               http://foundationdbs.com/downloads.html
               
               Key Concepts in Filemaker 7 (PDF)
               http://www.ddbainbridgeassoc.com/upload/images/fm7_key_concepts.pdf

          • 2. Re: how to have a conditional value list make look up to related fields
            philmodjunk

                 This thread describes/discusses two methods for getting data from a related table to appear: Auto Fill

            • 3. Re: how to have a conditional value list make look up to related fields
              hanstrager

                   I've tried now for a few days without luck.

                   Im basicly trying to make a small database for making technical descriptions for garments.

                    I have a table for different materials: fabric, trims, leather etc. Each record contains data about the different category of material.

                   fx.

                   ZIP, 65cm, color, 54$, ordered the 5th Oct...

                   on another table i have the technical print file, where I with a conditional value lists wants to first choose category, which filters everything down to fx a specific zip, a fabric etc.

                   so far I have succeeded...

                   My problem is, when i press on fx "ZIP" and then specify the zip i want filemaker to look up the price and supplier for that specific zip. and if i change the price of the zip in my category table, the price should also change in the technical description table.

                   i've tried to look on the internet, and related my tabels in every possible way, but nothing seems to work.

                   Help please...

                    

              • 4. Re: how to have a conditional value list make look up to related fields
                philmodjunk

                     This thread describes/discusses two methods for getting data from a related table to appear: Auto Fill

                     The option in this thread that describes a "dynamic" link to the related data will do what you describe here.

                • 5. Re: how to have a conditional value list make look up to related fields
                  hanstrager

                       It's still not really working.

                       I've added all the relevant fields from the categories table to my technical description table. The problem appear when i first select a category (from a drop down list) fx. fabric, then I select the name of the fabric (from a conditional drop down list based on category) then the autofill kicks ind, but if I choose a different fabric name the data dosen't change, what am I doing wrong..?

                  • 6. Re: how to have a conditional value list make look up to related fields
                    philmodjunk

                         You'll need to describe what you have done in much more detail. We need to see what you have set up for the relationship--what match fields. And how you have set up your layout. The fields that "autofill" should be fields from the related table, placed on this layout.

                    • 7. Re: how to have a conditional value list make look up to related fields
                      hanstrager

                           I've uploaded some images of my table graph and my technical description layout in both layout and browse mode, and as you can see when I change the name of the leather in my condiotional value list the rest of the info dosen't change...

                           I have 6 differet conditional value lists on my layout to be able to fill in 6 different materials. But I've only tried to set it up for "material 1"

                           My conditional value list consists of the Category and Name field. The fabric name should be the one that the other fields should be related to, im just not sure how to do that with my condiotional value lists allready set up.

                           sorry if my question is a bit newbie, but Im quite new to Filemaker...

                      • 8. Re: how to have a conditional value list make look up to related fields
                        philmodjunk

                             And is this layout based on "technical sheet 2"?

                             To which table occurrences do the Composition, Width, Price and Supplier fields refer?

                             (A "table occurrence" is one of the "boxes" found in your relationship graph.)

                             Does "fabric category" indentify a specific record in the fabrics table or a group of records in the fabrics table? I'm guessing that you have one relationship for the conditional value list (for pulling up a list of all fabrics in a category), but no relationship for matching a meterial to a specific fabric in that category. (You need one relationship for the conditional value list that matches to a group of records and a different relationship that matches to only one specific record for the "lookup".)

                             You also have some significant structural issues with Technical Sheet 2. The fact that I see multiple fields named: Material1, Material2, ... strongly suggest that you need a related table for listing these materials in separate records, but all related back to a specific record in Technical Sheet 2.

                             With the individual fields like this, you end up with a very complicated relationship graph as you need to define the same set of relationships for each and every material field.

                              

                        • 9. Re: how to have a conditional value list make look up to related fields
                          hanstrager

                               yes the technical print file is based on the "technical sheet 2"

                               Im not sure what table occourence composition width and and prices are coming from, but guess they're coming from "fabrics". Material1, Material2, Material3, Material4, Material5, Material6, are all occurences from the "fabric" table.

                               Fabric_caegory identifies groups of fabrics. Silk, Leather, cotton etc...

                               the multiply fieldnames with material1, material2...material6 are basicly so I can choose different materials to go into a garment... they should function all the same way and take info from Fabrics... as described before. first you choose from a drop down list the fabric category fx. Leather, then from a conditional value list you'll choose the name of the leather fx. brown suede. and then it should look up the rest of the info from that particular record automatically.I tried different solutions, but this was the only one that i could get working. so the conditional valuelists didn't get mixed up. Im not really sure how I relate a table for the listing of materials in seperate records, which relates back to a specific record in technical sheet2...?

                          • 10. Re: how to have a conditional value list make look up to related fields
                            philmodjunk
                                 

                                      the multiply fieldnames with material1, material2...material6 are basicly so I can choose different materials to go into a garment... they should function all the same way and take info from Fabrics... as described before.

                                 Yes, but if you use a portal to a related table of materials, you get this same result, but with much, much simpler relationships.

                                 

                                      I tried different solutions, but this was the only one that i could get working.

                                 But it isn't working for you is it?

                                 What data in your fabrics table uniquely identifies one fabric? What field or group of fields in techincal data sheet 2 store values that match to that one unique record in fabrics?

                                 You can't use the same relationship that you are using for your conditional value list as this matches to a group of records. You need a second occurrence for each material field that matches to one and only one Fabric record.

                                 I'd set up the needed relationships for the first material field like this:

                                 FabricsByFabricID-------<TechSheet2------<FabricsByCategory

                                 FabricsByFabricID::__pkFabricID = Techsheet2::_fkFabricID
                                 Techsheet2::Category = FabricsByCategory::Category.

                                 Use the relationship to FabricsByCategory for your conditional value list of fabrics for a specific category. Format Techsheet2::_fkFabricID as the drop down list with this conditional value list such that selecting a fabric by name from the list enters an ID number for that one specific fabric into the field. Then add fields from FabricsByFabricID to show information about that selected Fabric.

                                 The downside to this is that you need a pair of these relationships for each and every material field in TechSheet2. This is why I have recommended this structure instead:

                                 TechSheet2----<Materials>----FabricsByFabricID
                                                              |
                                                              ^
                                                       FabricsByCategory

                                 By using a portal to Materials on your techsheet layout, you then only need one conditional value list and just one pair of relationships for the value list and the lookup.

                                 Such a materials table is often referred to as a BOM, a Bill Of Materials and is a very common whay to list the "parts" that make up one product to be produced, whether it be a garment or an automobile.

                            • 11. Re: how to have a conditional value list make look up to related fields
                              hanstrager

                                   Think I've realised that a portal i so much easier... Though i it possible to move around on the fields in the portal, so it dosen't look like a list view so it looks more like the picture above...?

                                   I've tried now a doesn't of time with the conditional value list and the portal, and embarrased to say there's a small thing somewhere that Im not getting right - since it seems so straight forward especially when you follow tutorials on the internet like this one: http://www.youtube.com/watch?v=bQr1E5fJGAI

                                   it seems I get stuck when they have 3 tables, where I only have 2.

                                   I treat my fabric table as what he in the tutorial call "line items" and my technical sheet as a form of "invoice". My problem comes to how to set up my conditional value list. My category is just a straight forward valuelist in the fabrics table. but when I have to make a conditional value list based on the category, im not really sure what to relate the new tabel occourence of "Fabric" to, since im using a portal not using material1/material2... fields in my techncial sheet table anymore... it seems I'm missing a table, or is it just me who's a "complete filemaker retard"..?

                              • 12. Re: how to have a conditional value list make look up to related fields
                                philmodjunk

                                     It seems to me that you should have 3 tables for this, not two.

                                     TechSheet2.

                                     Materials (what they called "line items" in the tutorial)

                                     Fabrics.

                                     Materials is used to list one selected fabric for a given record in TechSheet2. Fabrics has one record for each type of fabric.

                                     Using a way over simplified example:

                                     Record 1 in Techsheet2 can link to two Materials records. One Materials record links to a Fabric record for "silk" and another to a fabric record for "nylon".

                                     Record 2 in TechSheet2 might link to 3 materials records, one that links to "rayon", another to "Calf Leather" and a third to "nylon"--the same fabric record selected in one of the materials records linked to Record 1.

                                      

                                • 13. Re: how to have a conditional value list make look up to related fields
                                  hanstrager

                                       I think thats what confuses me since I thought line items are my fabrics..?

                                       So let me get it completely straight:

                                       Technical sheets is where my portal is which comes from line Items

                                       the thing which is confusing me is what do I put in Line item/materials and fabric/categories..?

                                       I understand that line items is what i have as my "fabric table" and my "material type" (see pic) is a value list based on "fabric/categories table". But in the tutorial he makes an table occourence of the "fabric/categories table" which to me seems i need more fields on my fabric/categories table, then just an ID and a category name to be able to sort with the conditional value list.

                                       I think I basicly don't understand the difference between the line item/materials and fabric categories..? it seems to me in the tutorial that they're bit of the same..?

                                       it's so annoying it seems so easy and i just can't get my head around it - sorry to be such a pest...

                                  • 14. Re: how to have a conditional value list make look up to related fields
                                    philmodjunk
                                         

                                              I think thats what confuses me since I thought line items are my fabrics..?

                                         It is not.

                                         Line Items are used to list the specific fabrics for a given record in TechnicalSheet2.

                                         Fabrics records are a table of records that lists all of your different fabrics that might be linked via a line item record to a given technicalSheet record.

                                         Think of it like an invoice where your techsheet record is an invoice, the lineitems list the items purchased on that invoice and your fabrics represent the list of different products that a customer might buy. The same product might be purchased on any number of different invoices. Each invoice has it's own lineItem record to document that sale, but they all link back to the same product record inorder to access information, such as a price and description about that product.

                                         BTW, this LineItems or Materials table (what I called it earlier) is often referred to as a "BillOfMaterials" or BOM in the manufacturing industry.

                                    1 2 Previous Next