1 2 Previous Next 21 Replies Latest reply on Jul 18, 2014 8:43 AM by philmodjunk

    Multiple Drop Downs via Same Value List / Related Field

    SeanKooner

      Title

      Multiple Drop Downs via Same Value List / Related Field

      Post

           Hello,

           Somewhat of a beginner to Filemaker but picking it up rather quick. I've finally ran into a wall though. I'm sure I could figure it out with more trial and error but I thought I'd seek help.

      Table One
           - ltype
           - lcost

      Table Two
           - ltype1
           - lcost1
           - ltype2
           - lcost2
           - ltype3
           - cost3


           Basically creating 3 pop-up menus on the Table Two layout (ltype1, ltype2, ltype3) with value lists that refer back to "Table One :: ltype" .

           Now attempting to fill in lcost1, lcost2, lcost3 with the corresponder value from Table One depending on ltype1, ltype2, ltype3.

           I have no problem getting this working if there was just one instance, but there seems to be conflict when doing it three times. Am I missing something? What kind of relationship should I be using?

           Please let me know if I'm missing any further information.

            

           Thanks!

            

        • 1. Re: Multiple Drop Downs via Same Value List / Related Field
          philmodjunk

               With the table design that you have here, you'd need Three different Tutorial: What are Table Occurrences? of Table One all linked to different match fields in table 2.

               A simpler and more flexible structure would be to add a third related table and use a portal to that third table on your table 2 layout:

               Table 2------<Table 3>------Table 1

               Table 2::__pkTable2ID = Table 3::_fkTable2ID
               Table 1::ltype = Table 3::ltype

               In place of three pairs of fields in Table 2, you have 3 related records in Table 3. Not only does this simplify your relationship graph, you are no longer limited to a fixed number of these fields for a given record in Table 2 as you can now record as many such costs as you need without having to do a major redesign of your database if you find that you need to record 4 or more costs instead of just 3.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Multiple Drop Downs via Same Value List / Related Field
            SeanKooner

                 Here's what I did step by step so you can see if I missed any crucial steps:

                 1) Created Table 3, added fields _fkTable2ID and ltype.

                 2) Added fields _pkTable2ID & _pkTable1ID to their correct tables.

                 3) Created the relationship Table2::_fkTable2ID = _Table3::pkTable2ID (Allow / Delete related records checkmarked)

                 4) Created the relationship  Table1::ltype = _Table3::ltype (Allow / Delete related records checkmarked)

                 5) Created a portal of Table3 on Table2 showing field "ltype"

                  

                 Okay now I'm a bit lost, not very familular with portals and what not. So how do I use this in practice? The portal doesnt show anything, do I set it as a pop up menu with value list as I normally would? What about the "lcost" , I've been using scrip triggers on modify to set that field previously. (Although probably the incorrect way to do it, I've just kinda been learning as I go and using what works)

            • 3. Re: Multiple Drop Downs via Same Value List / Related Field
              philmodjunk

                   2) Make sure that these auto-enter serial numbers and any records that already exist before these fields are added will need to be updated. Replace Field Contents has a serial numbers option that can do that for you.

                   3) Be careful when selecting the Delete option or you'll get large numbers of related records disappearing from your database when you didn't expect it.

                   Ltype would be set up with a drop down list or pop up menu of Ltype values.

                   For the cost, you have two choices and I cannot tell you which is best for your project from the info provided thus far:

                   Option 1) You can put the Cost field from Table 1 inside the portal row and when you select a value in LType, the current cost value will appear.

                   Optoin 2) You can define a cost field in Table 3 and use auto-enter field options to look up (copy) the cost from the selected LType record.

                   With Option 1, any time that you edit a cost in Table 1, the cost shown for that Ltype will automatically update for every Table 3 record that specifies that Ltype. With Option 2, no such automatic update occurs--which can be either a good or bad thing depending on how you need to use this data. (Price changes in an invoicing system, for example shouldn't affect the costs on past invoices, only those on new invoices.)

              • 4. Re: Multiple Drop Downs via Same Value List / Related Field
                SeanKooner

                     Alright awesome, portal is working perfectly fine. I went with option 2 since the data could change from time to time / client to client. Now I need to figure out how to implement it correctly for what I need. I've attached a picture for reference so you can have a better idea.

                     Shall I follow the same process for the additional table (products) and create another portal? How do I reference specific portal records (to make final calculation for cost) ? Is there anything I could do in terms of laying out everything similar to my picture? or would I have to adapt to using something else?

                     Summarized:

                     Need to pull Leather Type (ltype) & Leather Cost (lcost) from ltype table.
                     Need to pull Product Type (pname) & Size (psize) from products table.
                     Then I'll use a formula to fill in the Cost per Piece using those pulled values.

                     And this has to happen at least 3 times.

                     I appreciate the help a lot, thanks!

                • 5. Re: Multiple Drop Downs via Same Value List / Related Field
                  philmodjunk

                       Hmmm, I'm not sure that I understand the intended function to each table (occurrence) shown in your screen shot.

                       Let's back up a bit and get the big picture.

                       Correct me if I'm wrong, but this looks like a "manufacturing" database for the manufacture of leather based products--specifically, documenting the particular materials used to construct each product and compute the manufacturing cost for creating one such unit of product.

                       If that is correct, consider this data model:

                       Products-----<BOM>-----Materials

                       Products::__pkProductID = BOM::_fkProductID
                       Materials::__pkMaterialID = BOM::_fkMaterialID

                       BOM stands for "bill of materials" and is a standard industry term for the list of materials and quantities needed to create one unit of product. ("one unit of product" can be a single car, a single leather purse, A pair of shoes of a specified size, a gallon of fruit punch, a ton of concrete mix, etc.) You can think of it as the "list of ingredients" for a recipe to make one of your leather products.

                       Any given product will be made up of multiple materials and the same material can be used in the production of many different products so this is also an example of a many to many relationship with the BOM table serving as the "join" table between two different parent tables (Products and Materials).

                       The unit cost for any given Material would be recorded as a field in Materials (or in more sophisticated systems, possibly yet another table linked to Materials). The materials cost for a given product would be computed as a field in the BOM table: Qty * Materials::UnitCost. To show a total cost of all materials listed in the BOM, a calculation field can be defined in Products as: Sum ( BOM::MaterialsCost ), where "MaterialsCost" is the name of the calculation field that multiplies unitCost by Qty.

                       So imagine that you have a product where 3 square inches of Leather A at a unit cost of $0.05 per square inch and 10 Square inches of Leather B at a unit cost of $0.07 per square inch is used to produce one unit of product. The unit costs of $0.05 and $0.07 will be the same for every product that lists these materials in their BOM and thus these will be cost fields in two records (one for each leather type) in your Materials table. You'll need two BOM records, one for each of these leather types with quanties of 3 and 10 with a materialCost field computing costs of $0.15 and $0.70 in these two records. Then the sum calculation defined in Products sums these to produce a unit material cost of $0.85.

                        

                  • 6. Re: Multiple Drop Downs via Same Value List / Related Field
                    SeanKooner

                         You're pretty correct with what I'm attempting to do. I'm going to start reworking all my tables and see how it goes.

                         Essentially the layout would be more of "choose what you need to add" instead of "choose from these static options". Which is cleaner.

                         One question I do have though is: Can I place two portals of the same table on the same layout? (ie. One filtering to show "Leather" materials, and the other showing "Non-Leather" materials) 

                         Also, the BOM table would be used as a portal on a separate layout? Correct? Just confirming.

                          

                         And a break down of my goal, so we're clear on everything and dont miss anything:

                         I am creating a costing sheet for "Super 2.0 Product A"

                         - This uses "Product A" as a base (Each product ALWAYS uses ONE base), this base requires to be paired with a selected leather of choice. The cost is calculated using the dimensions of the base and cost of leather.

                         - This uses "Addon_B" as a component. (Products can use MULTIPLE components)  this also requires to be paired with a selected leather of choice. The cost is calculated using the dimensions of the base and cost of leather.

                         - This uses "Bead_A" as a material. It uses 5 of them. The cost is calculated by multiplying the QTY by cost.


                         This is the intended function.
                          

                         Materials Table:
                          

                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                        _pkMaterials                     mType                     mName                     mCost
                                        1                     Leather                     Leather A                     2
                                        2                     Leather                     Leather B                     3
                                        3                     String                     String A                     5
                                        4                     Bead                     Bead A                     2

                          

                         Products Table:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                        _pkProducts                     pType                     pName                     pDimensions
                                        1                     Base                     Product_A                     15
                                        2                     Base                     Product_B                     10
                                        3                     Component                     Addon_A                     3
                                        4                     Component                     Addon_B                     2

                          

                          

                         Edit:

                          

                         The more I stare at this and plan things out, it seems "Products" are more like "Materials". So wouldn't it be more like this?

                          

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                        _pkMaterials                     mType                     mName                     mCost                     pDimensions
                                        1                     Leather                     Leather A                     2                      
                                        2                     Leather                     Leather B                     3                      
                                        3                     String                     String A                     5                      
                                        4                     Bead                     Bead A                     2
                                         
                                         
                                        

                                             5

                                   
                                        Base                     Product_A                                           15
                                        6                     Base                     Product_B                                           10
                                        7                     Component                     Addon_A                                           3
                                        8                     Component                     Addon_B                                           2

                          

                         Products Table:

                                                                                                                                                                                                                                                       
                                        _pkProducts                     pName
                                        1                     Super 2.0 Product A
                                                               
                                                               

                          

                    • 7. Re: Multiple Drop Downs via Same Value List / Related Field
                      philmodjunk
                           

                                One question I do have though is: Can I place two portals of the same table on the same layout? (ie. One filtering to show "Leather" materials, and the other showing "Non-Leather" materials)

                           Yes. and the "filter" can be an expression in portal setup, or you can use two Tutorial: What are Table Occurrences? of BOM and use a second pair of match fields to filter between leather and non-leather materials. This typically uses a pair of calculation fields in the Product table that always return a specific value such as "leather" used as match fields to match to a "type" field (such as your mType field) in materials. The filtered portal makes for a simpler relationship chart in Manage | Database, but certain applications of this concept update much more quickly when the match field based "filter" is used instead.

                           

                                Also, the BOM table would be used as a portal on a separate layout? Correct? Just confirming.

                           I think so, but I'm not sure that I understand what you have in mind for that "separate layout". As long as the layout is based on a table occurrence that is linked in a valid relationship to the portal's table occurrence of the BOM, this will work.

                           I see that you have  requirements that your DB support two additional issues common to the manufacturing process:

                           1) A "general spec" (product A) that is then combined with additional details to produce a "specific spec" ("Super 2.0 Product A")

                           2) Component Assembly or "kit" inventory management where a given item listed in the BOM has it's own BOM in turn.

                           Support for both of these features can require some pretty sophisticated database design and scripting to pull off.

                           A "general spec" usually requires a related table of general specs linked to the specific products that are based on that common general spec: Gen Specs-----<Products. This can include a BOM of materials that are exactly the same for all products based on that general specification. A supplementary BOM can then be linked to Products to list materials that are specific to that one variation of the general specification. And there are multiple approaches to how you might set that up.

                           Component Assemblies are usually handled by using one data source table for both Products and Materials--which means that Products and Materials in our original design become two table occurrences of the same data source table. That way, a component can be both a Product with its own BOM records and yet also be a material listed in the BOM's of other products. (and yes, there can be some brain stretching uses of recursive scripting to get full parts lists that drill down through listed components to the basic materials even when components are set up with BOMs that list other components...)

                      • 8. Re: Multiple Drop Downs via Same Value List / Related Field
                        SeanKooner

                             I think the route im going to take is the edit I made on the last post (I think it was after your reply). So combining the Products / Materials table, and using scripting. Do you see any issues I would run into?

                        • 9. Re: Multiple Drop Downs via Same Value List / Related Field
                          philmodjunk

                               My post agrees with yours that materials and products should be combined such that your relationship graph still has a materials and a products table occurrence, but that both use the same data source table.

                               

                                    "See any issue"?

                               I can imagine, many, many issues, but they are manageable and which you might encounter will depend on how your solution develops as you move forward to turn your ideas into reality.

                          • 10. Re: Multiple Drop Downs via Same Value List / Related Field
                            SeanKooner

                                 Okay so I had a little time to work on it today and as I understand I create a portal to BOM. I'm having a little trouble with dynamic drop downs, pulling the correct information and what not. I'll break down each field in the portal I'm having trouble with and what function I require. I put the portal on the left just for reference... it seems like _fkMaterialsID isn't updating at all... I'm not sure what I'm missing, I know it's probably a small step.

                                  

                                 How should I be generating value lists? 

                            • 11. Re: Multiple Drop Downs via Same Value List / Related Field
                              philmodjunk

                                   Typically, you'd build the BOM for a given product like this:

                                   I am assuming that you have Products and Materials defined as two occurrences of the same data source table. You'd start with your defined tables/relationships:

                                   Products----<BOM>-----Materials

                                   Products::__pkProductID = BOM::_fkProductID
                              Materials::__pkProductID = BOM::_fkMaterialID

                                   In the products to BOM relationship, double click the relationship line and select "allow creation of records via this relationship for the BOM table. This isn't strictly necessary, but it makes it possible to add BOM entries in a portal to the BOM without any need for a script.

                                   Now put a portal to the BOM on your Products layout. Put the BOM::_fkMaterialID field into this portal row and format it as a drop down list or pop up menu. Define its value list to list the __pkProductID value of Products as field 1 and a name or description field as field 2 in this value list. This allows you to select by name or description, but the value list enters the ID field. This is the __pkProductID field, not __pkMaterialID because Products and Material are now the same table and you only need one primary key field in your table.

                                   With "allow creation.." correctly enabled, you can select a material from the drop down and this action creates a new record in the portal and automatically links it to the current Product record by copying the value of Products::__pkProductID into the BOM::_fkProductID field.

                              • 12. Re: Multiple Drop Downs via Same Value List / Related Field
                                SeanKooner

                                     Alright, I've got that working but I'm having trouble with making it "dynamic" filtering the results based on other drop downs.

                                     1) Select "Type" (Base, Component, Bead, String) (It can be a static value list, or actually pull unique value list from Materials::pType)
                                     2) Select "Name" (The MaterialsID field showing names, as you explained above) Select Materials::pName WHERE Materials::pType = BOM::bomType
                                     3) Select "Leather" (Only show field if "Base or Component" type was chosen, select from Materials::pName WHERE pType = Leather.)
                                     4) Auto Fill "Dimensions, Cost" (Haven't gotten that far, but im assuming its just a calculated lookup or script)

                                • 13. Re: Multiple Drop Downs via Same Value List / Related Field
                                  philmodjunk

                                       1) Select "type" and what is supposed to happen next? Do you want that to filter the values in a value list to only show items of that type, or to update a filtered portal to only show BOM records of that type? both could be set up from what you describe.

                                       2) Do you mean to select a material by name in order to add that material to the BOM. I've described that method in detail via one method (value list where ID is field 1 and name is field 2) and alluded to a more sophisticated method that can be used once you are able to get the first method working (which verifies that you have the needed basic relationship set up.) Where are you exactly in this process?

                                       3) see this thread: Auto Fill

                                  • 14. Re: Multiple Drop Downs via Same Value List / Related Field
                                    SeanKooner

                                         The process is step by step in my previous post.

                                         Yes, I'd like to filter the values of items/names after selecting type.

                                         2) Yes, sorry if I wasn't clear, I've got this working already... was just reitterating to explain the entire process im trying to accomplish. (Just need to work on filtering / conditional lists)

                                    1 2 Previous Next