5 Replies Latest reply on Dec 29, 2010 9:29 AM by philmodjunk

    Script Help

    DanaSadowski7326

      Title

      Script Help

      Post

      Hi, I need some help with a script.  I have a database that has tables for dealers, manufacturers and the mfgs products.  I have my order layout set up with a separate portal for the lineitems on the order.  In my lineitem portal, I have a field for the product id, which shows as a drop-down list from specified fields (it's actually a calculation that shows the mfg code and model #).  When the model number is selected, it automatically fills it in in the model number field.

      Is there a way to write a script so that this drop-down list will only show the items for a certain mfg when that mfg id is put in the order detail part of the layout?  I was thinking something like a find, but I've tried several different things and it doesn't seem to be working.  I figured I could do a script trigger for that field OnObjectEnter once I got the script written correctly.

      Or maybe there's a better way to do what I'm looking for.  The reason being is there will be over 2000 products in our database once I get them all imported, and it will take forever to scroll thru them all.

      If you need additional info about my database, let me know, I'll be happy to provide whatever you need.

      Thank you,

      Dana Sadowski

        • 1. Re: Script Help
          philmodjunk

          A conditional value list is what you are describing here:

          Custom Value List? (Use option 2)

          http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (This is a knowledgebase article on the subject.)

          • 2. Re: Script Help
            EvanScott

            I agree that a conditional value list would be the way to go in order to solve your issue. That way it will selectively show the certain sets of items if the specific conditions exist in order to limit down your product list.

            • 3. Re: Script Help
              DanaSadowski7326

              Thank you for the quick responses.  I will try this and see what happens and let you know if it works.

              • 4. Re: Script Help
                DanaSadowski7326

                Okay, I've looked over the link sent about setting up the conditional value list, Option 2 and looked at my database.  I have a feeling I'm making more of this than I should, but I have not been able to get it to show correctly.  Here is how I have it set up currently:

                Table: Products, Table: Manufacturers, Table: Order, Table: Lineitem

                In my order detail layout, i have a portal set up with a field order_LINEITEM::kf_ProductID.  This is a pop-up menu based on a value list from Products::__kp_ProductID and Products::z_MfgAndModelNum (so that when I click in the ProductID field, it shows a 3 digit manufacturer code and the model number).  Once you select which product you want, it automatically puts in the model number in the next field in the portal.

                In the order detail portion of the layout, I have a field order::_kf_MfgID which is a pop-up menu that shows the mfg ID and Mfg Name.

                I'm confused as to the relationship that I need to set up between which tables to get the order_LINEITEM::_kf_ProductID to only show the products that relate to the mfg that is selected for the order.  I'm very new to this stuff and I have trouble with the many-to-many relationships and the table occurences.

                Also, right now, when you select the product you want for the order, it automatically fills in the model number.  Is there a way to set up the model number field so that if you already know the model number, you can just type it in, basically override selecting the product ID?

                I appreciate any help you can give me.

                • 5. Re: Script Help
                  philmodjunk

                  I think you have these table occurrences in Manage | Database | Relationships:

                  Order---<LineItem>----Products>-----Manufacturers

                  and a relationship that matches LineItems to products like this:
                  LineItem::kf_ProductID = Products::kp_ProductID

                  presumably, you have a field in products that Identifies the Manufacturer (3 digit manufacturer code?)
                  Products::MfgID = Manufacturers::MfgID

                  You can use Products::MfgID in a new relationship to LineItem for your conditional value list:
                  LineItem::MfgID = ProductsByMfg::MgfID

                  Where ProductsByMfg is a new table occurrence of Products. (Click products, then click the button with two plus signs in lower left corner, then rename the new table occurrence box "productsByMfg".)

                  Now you can specify that your value list will use values from ProductsByMfg and select the "Include only related values starting from LineItem" option to limit the values to just those for the selected manufacturer. LineItem::MfgID should be formatted with a value list drawn from Manufacturers that lists the ID and the name of each Manufacturer.

                  If "Table Occurrence" is a new term, you might want to read this thread:  

                  Tutorial: What are Table Occurrences?