1 2 Previous Next 29 Replies Latest reply on Nov 19, 2014 7:48 AM by philmodjunk

    Inventory drop-down list based on the validity of two fields in separate tables

    ChadBarnard

      Title

      Inventory drop-down list based on the validity of two fields in separate tables

      Post

      Maybe I'm going about this the wrong way, but what I'm trying to do is create a drop-down list that only has values whose records have two matching values from different tables.

      On the Orders table, I have a field that designates what "Image" a project is going to be.  I have a portal on the Orders table that pulls information from a standard type of "Order Line Item" table.  When a person selects which Vendor provides the material on the portal, I want the next field to be a drop-down list that shows only products from that Vendor and that match the specified "Image".

      I have image fields on the Product table as well as the Orders table.

      Do I need to create a separate table that pulls the information into the same table and then reference that table?

      Thanks for any help.

        • 1. Re: Inventory drop-down list based on the validity of two fields in separate tables
          FentonJones

          OK, I'm going to guess on this. I am not very good at reading what other's write (not my own either :-|  I think what you're looking at is the "can't look back" problem. That is, you are in Order Line, you have the Image (id?) in the Order, and you now want to look at the Products, using those of a Vendor (which each Product has). But you can't look back (relationally) to the Order table at the same time as looking out at Products.

          An easy way to get past this is to (recreate) the Order::Image(id) as a calculation field in the Order Line table. The calculation would be just: Order::Image(id), as you already have that relationship; lets call the field "_cOrder_Image". You could then use _cOrder_Image as part of a relationship to Products, and see only the records matching:
          VendorID = VendorID
          _cOrder_Image = Image(id)

          _cOrder_Image is Unstored. But this is OK, as it is on the "beginning" (or "from") of the relationship.

          P.S. This could likely be done via SQL, with less pieces (but more "work", to me, or likley you).

          • 2. Re: Inventory drop-down list based on the validity of two fields in separate tables
            philmodjunk

            On the other hand, the Order:ImageID and Order::VendorID fields might both work as match fields in the same relationship to an occurrence of the vendors table.

            You may find the conditional value list examples found in Adventures in FileMaking #1 a useful source of ideas as this one file illustrates 8 different ways to set up a conditional value list.

            Caulkins Consulting, Home of Adventures In FileMaking

            • 3. Re: Inventory drop-down list based on the validity of two fields in separate tables
              ChadBarnard

              Fenton, thanks for your post.  I think Phil's link to AiF#1 - and it's "Hierarchical Conditional Value Lists" section - is just what I needed.

              Phil, I do have some questions in setting this up.  Looking at my Relationships image, in relation to the HCVL, would my OrderLineItem table = their MainHL; would my Orders table = their Region; would my Vendors table = their County; and would my Products table = their City?

              I think I have this right, but I just wanted to get this straight to make sure that I'm approaching the problem correctly.

              Thanks for your help, guys.

              • 4. Re: Inventory drop-down list based on the validity of two fields in separate tables
                philmodjunk

                I can't tell as I don't see where "image" comes into play here.

                And won't you have just one vendor for a given order? I can't imagine that you purchase product from more than one vendor on the same order....

                If so, it would seem that your starting point here would be the Orders table occurrence, not OrderLineItems.

                • 5. Re: Inventory drop-down list based on the validity of two fields in separate tables
                  ChadBarnard

                  Phil,
                  Hopefully this image is clearer.

                  In our line of work, the order from a customer would be filled with material from several different vendors.  Of course, there are separate invoices to each vendor, but for our customer's order, several vendors have to be ordered from.  The order line item acts as a "load sheet" where we put in what's been ordered, how much was ordered, when's it expected to arrive, it's arrival date, and storage location.

                  I now have a portal pointing to OrderLineItems in the Orders table.  So, would Orders be the equivalent of their MainHL?

                  • 6. Re: Inventory drop-down list based on the validity of two fields in separate tables
                    philmodjunk

                    Thanks for clarifying, but all is not clear yet.

                    Please explain how "image" affects what appears in your value list. What kind of data is stored in this field? How is it used?

                    And please explain the connection between products and order line items. (Usually, a table like products links to the order line items table rather than orders as each record in line items of a typical customer order might refer to a different product.)

                    • 7. Re: Inventory drop-down list based on the validity of two fields in separate tables
                      ChadBarnard

                      I work for a company that images convenience stores (Citgo, Exxon, BP, etc.).  The "image" is the specifications of that oil company's "look".

                      Let's say that an order is going Citgo.  In this case, I need a drop-down menu that only shows products that have the same vendor and "image".  So, when the vendor is chosen from the left (from a value list based off of the Vendors table), I need the next drop-down menu to only give me Citgo materials from that vendor.

                      Our Products table will have hundreds of items, that are image and vendor specific.  For any given image, only approved vendors are allowed to furnish the material.  I don't want the users to have to "know" every detail about who's allowed to provide what, so I only want to give them the allowed options.

                       

                      Now concerning the connection between products and order line items, the relationship from orders to products is many-to-many, and the relationship between products to orders is many-to-many; so I thought that an "OrderLineItem" would be needed to break both sides down to a one-to-many relationship.  Just like you said above, each order will have several line items relating to different products.

                      • 8. Re: Inventory drop-down list based on the validity of two fields in separate tables
                        philmodjunk

                        Now concerning the connection between products and order line items, the relationship from orders to products is many-to-many, and the relationship between products to orders is many-to-many; so I thought that an "OrderLineItem" would be needed to break both sides down to a one-to-many relationship.  Just like you said above, each order will have several line items relating to different products.

                        But that's not what you have set up in your relationships. In a many to many relationship between orders and products, you would use Order LineItems as the "join" table linking orders to products. You do not have that relationship shown here as Order LineItems does not have a relationship linking each line item record to a record in products.

                        And while you have provided a great deal more detail, this one picky item is still not clear:

                        What kind of data is stored in this (the image) field?

                        Is this text, an ID number or is this a container field with an actual image in it?

                        To some up what I am distilling from your posts:

                        The "image" is determined at the "order level". All products listed in line items must be from products with the specified "image".

                        But "Vendor" is determined at the Order LineItems level, each product might be selected from a different vendor--so long as the "image" is correct.

                        • 9. Re: Inventory drop-down list based on the validity of two fields in separate tables
                          ChadBarnard

                          I guess I'm confused about the construction of a join table.  I thought that to make a join table you related the primary key of the many tables (Orders and Products in this case) to the relevant foreign keys in the join table (OrderLineItem in this instance).  How should I have set up the relationships?

                          Image equals a text field (i.e. Citgo, Exxon, BP, etc.)

                          Yes, your distillations are correct.

                          • 10. Re: Inventory drop-down list based on the validity of two fields in separate tables
                            philmodjunk

                            Apologies, I had a brain fart here and misinterpreted your relationship map. All's good for the many to many from orders to products.

                            Is this what you have in mind?

                            You select an image to control what vendors appear in a vendor list. You then select a vendor to control what products appear in a products list?

                            If so, then Orders would appear to be the equivalent starting occurrence here as the first hierarchical value list would be based on a relationship between Orders and Vendors with Image serving as the match field.

                            • 11. Re: Inventory drop-down list based on the validity of two fields in separate tables
                              ChadBarnard

                              In playing around with this, I've become hopelessly lost.

                              Comparing my case to the AiF#1, wouldn't Orders = Regions, Vendors = Counties, and Products = Cities with OrderLineItem acting as the MainHL (The MainHL being the table where all of the values are displayed)?

                              I'm pretty sure that I understand the concept (maybe not) and could make it work if I were constructing it the exact same way as the example.  I think where the problem that I'm running into is that the Orders (Regions) is selected on a different table (Orders vs. OrderLineItem).  Since it's already on a previous tab, I don't want to take up space with displaying the Image field again.

                              Is this making any sense?  I'm a little mired in the muck at the moment and think that I'm focusing on the wrong things.

                              • 12. Re: Inventory drop-down list based on the validity of two fields in separate tables
                                ChadBarnard

                                Ok, I've went back and used Fenton's suggestion, and I have it partly working.  I can pick a vendor and it will only show me products from that vendor, associated w/ the proper image.  Now, I just have to get it, so that it only shows me Vendor that manufacture material for that Image.

                                • 13. Re: Inventory drop-down list based on the validity of two fields in separate tables
                                  philmodjunk

                                  You never answered my last question. I needed that answer in order to answer your question.

                                  • 14. Re: Inventory drop-down list based on the validity of two fields in separate tables
                                    ChadBarnard

                                    You select an image to control what vendors appear in a vendor list. You then select a vendor to control what products appear in a products list?

                                    Sorry about that.  Yes, this is what I'm trying to do.

                                    1 2 Previous Next