1 2 3 Previous Next 31 Replies Latest reply on May 5, 2015 3:12 PM by burghfan

    conditional value list help

    burghfan

      The Solution has the following Tables


      "Products Table"

      "Manufacturers Table"

      "Estimates Table"

      "Estimate Items Table"

       

      The "Products Table" has a field labeled "Item ID" based on a calculation "GetAsNumber(Manufacturer ID) & "-" & Item #"

       

      In the "Estimates Table" I would like to use a Pop Up Menu where only the "Items" from the selected "Manufacturer ID" can be used.

       

      The field will be inside a "Estimate Items Portal"

       

      I have created a values list for the "Item #" which will show all of the "Items" associated with that field from the "Products Table" what I am having trouble with is how to create a "Conditional Value List" that only shows "Items" from the "Products Table" that are associated with a specific "Manufacturer ID"

        • 1. Re: conditional value list help
          erolst

          burghfan wrote:

          In the "Estimates Table" I would like to use a Pop Up Menu where only the "Items" from the selected "Manufacturer ID" can be used.

          Where exactly do you (want to) select the manufacturer – in the estimate, or in a line item?

           

          Does an estimate only comprise items of a single manufacturer, or across the board?

          burghfan wrote:

          The "Products Table" has a field labeled "Item ID" based on a calculation "GetAsNumber(Manufacturer ID) & "-" & Item #"

          Is that pertinent here? And is "Item ID" the primary key of that table?


          If so, it would be better to a) give it the same name of the table and/or add a suffix like “PK” (otherwise this nomenclature will cause confusion), and b) to use a meaningless, auto-entered productID to use in relationships; you can still use a calculated serial for display purposes / business requirements.

           

          btw, the result of this calculation must be text (or the "-" wouldn't make sense) – but then there is no need to use GetAsNumber().

          • 2. Re: conditional value list help
            burghfan

            Thanks for the response,

             

            I had been selecting the manufacturer in a drop down list in the body of the estimate, not in a line item.

             

            An estimate will only be from one manufacturer

             

            “Item ID” is not the pk, I was using it as a way to distinguish products from different manufacturers

             

            The result of the calculation is something like the following “101-30004-1206” where 101 id the manufacture id and (30004-1206) is the item #

             

            or "139-4” Enviromag 2000” where 139 is the manufacturer id and (4” Enviromag 2000) is the item #

             

            Hope this helps you understand what I’m attempting, thanks again for the help.

             

             

            Regards

             

            Dan Nelson

            dan@dasgroupinc.com

            • 3. Re: conditional value list help

              Filemaker 12's help file:

              Example of a value list that includes only related field values

               

              Hierarchical Value List.png

               

              After you have selected the table to use to determine the contents of this value list, it will show only the selected item. This requires a parent file and a related child file.

               

              Manufacturer<>Parts List

               

              After a manufacturer is selected (popup list or field) then the popup for Parts will only show the matching items.

               

              This works just like a portal would if change the contents of the match field or move to another record.

               

              Note: you could place a portal on the list record instead of a popup.

               

              To make this work, your table needs the mfg id to link to the items table.

               

              A value list created from the mfg table can be assigned to the field in your table. Then the field for part number can be assigned to the value list you created above to show only the related items. When done properly this popup will show only the related parts for the mfg. Or use a portal. Both will show the same parts.

               

              Zip Codes.png

              In the above I have created 5 related popups using this technique. If I enter a Zip Code, the popups change to show the information from the Zip Code table using value lists created as related.

               

              If I select from one of the popups the lower popups are set to empty and the upper one will show only a list of the related values.

               

              This form view could be set to show as a list and work as want.

              • 4. Re: conditional value list help
                erolst

                burghfan wrote:

                I had been selecting the manufacturer in a drop down list in the body of the estimate, not in a line item.

                An estimate will only be from one manufacturer

                 

                In that case, add a new TO of the Product table as , say, Product__byManufacturer, and define a new relationship

                 

                Estimate::id_manufacturer = Product__byManufacturer::id_manufacturer

                 

                then define your value list as using fields from Product__byManufacturer; instead of Include all values, select Include only related values starting from Estimate.

                 

                then format the field in the portal to use that VL.

                 

                Note that using the same relationship with a selection portal may be a bit more effort to set up (ad requires a bit of scripting), but lets you do things like type-ahead, multi-selection and other nifty things; if you have FM13, you can even stash that portal away in a popover, only visible when needed.

                • 5. Re: conditional value list help
                  burghfan

                  Thank you for the response, I’ll give that a go.

                   

                  Regards

                   

                  Dan Nelson

                  Email: dan@dasgroupinc.com

                  • 6. Re: conditional value list help
                    burghfan

                    Thank you

                     

                    Regards

                     

                    Dan Nelson

                    Email: dan@dasgroupinc.com

                    • 7. Re: conditional value list help
                      burghfan

                      That worked great thank you.

                       

                      — —

                       

                      Regards

                       

                      Dan Nelson

                       

                      Sent from my iPhone

                      • 8. Re: conditional value list help
                        burghfan

                        Hi

                         

                        This worked for the the two existing estimates I had already generated, but what would cause it to not work when I create a new estimate?

                         

                        When I select the “Item #” in a new order the Pop Menu doesn’t pop and the field shows

                         

                        I’m assuming I did something wrong, I just can’t figure it out.

                         

                        Thanks for any help in advanced.

                         

                        Regards

                         

                        Dan Nelson

                        Email: dan@dasgroupinc.com

                        • 9. Re: conditional value list help
                          erolst

                          burghfan wrote:

                          I’m assuming I did something wrong

                          Hard to tell since we don't know what exactly you did.

                           

                          Most obvious reasons would be that a) you didn't specify a manufacturerID in the estimate, or b) there are no products for the specified manufacturer; in either case, the value list would be empty and display something to the effect of “missing values”.

                          • 10. Re: conditional value list help
                            burghfan

                            No I have a manufacturer id selected. I must have done something else wrong. The other thing that I would think should happen is that if I were to change the manufacturer id from one manufacturer to another the product list should change, and that doesn’t happen.

                             

                             

                            Regards

                             

                            Dan Nelson

                            • 11. Re: conditional value list help
                              erolst

                              burghfan wrote:

                              I must have done something else wrong.

                               

                              Stands to reason, but as long as you don't “Tell or Show”, we have to resort to Guessworks, Ltd., which is a bit tedious …

                               

                              OTOH, have a look into the attached file that not only shows how this works, but also the aforementioned better approach.

                               

                              What did you say your FM version was again …?

                              • 12. Re: conditional value list help
                                burghfan

                                Please see the attached screen shots;

                                 

                                One showing a estimate I had already entered prior to creating the conditional value list, where the cv list works.

                                 

                                The other screen shot is a new estimate that gives me the “no values defined” in the conditional value list.

                                 

                                 

                                Regards

                                 

                                Dan Nelson

                                 

                                DAS Group, Inc.

                                “The Environmental Solutions Provider”

                                3251-C Old Frankstown Road

                                Pittsburgh, PA 15239

                                 

                                Email: dan@dasgroupinc.com

                                Mobile: 412-559-8646

                                Direct & Fax: 724-972-4014

                                 

                                Web: www.dasgroupinc.com

                                Facebook: www.facebook.com/dasgroupinc

                                • 13. Re: conditional value list help
                                  erolst

                                  Did you look at the sample file, its behaviour and the way the relationships are set up? Find the difference between the pertinent relationships there and in your file.

                                   

                                  Did you make sure that 101 actually has products? And (“never start a sentence with and …”) what happens if you switch 101 to 103 (Krohne), of which you know that it does have related records and yields a non-empty VL?

                                   

                                  You can also check whether the VL works by putting a portal onto the layout that shows records from the TO your VL fields come from. No RR and empty VL – correct; RR and empty VL – problem (no RR and non-empty VL - a miracle!).

                                   

                                  You could also try to

                                  • create a calculation field in the LineItems table as Estimates::id_manufacturer

                                  • add a relationship between LineItems::cIDManufacturer = AnotherTOOfProducts::id_manufacturer

                                  • re-define your value list (or create another one) as using fields from AnotherTOOfProducts, “starting at” LineItems.

                                   

                                  This must work (provided the id_manufacturer is set in the parent record), regardless of the way the other relationships are defined.

                                  • 14. Re: conditional value list help
                                    burghfan

                                    Switching the manufacturer does not switch the value list, see the attached.

                                     

                                    Mfr ID change from 103 to 101 and the value list remains with Mfr ID 103

                                     

                                    I’ll try checking using your following suggestion: You can also check whether the VL works by putting a portal onto the layout that shows records from the TO your VL fields come from. No RR and empty VL – correct; RR and empty VL – problem (no RR and non-empty VL - a miracle!).

                                     

                                     

                                    Regards

                                     

                                    Dan Nelson

                                    1 2 3 Previous Next