8 Replies Latest reply on Dec 5, 2013 2:27 PM by SkippDink

    Matching fields in a portal with multiple values... I think

    SkippDink

      Title

      Matching fields in a portal with multiple values... I think

      Post

           Ok I think I will have it all together after this. I'm sure Phil (most likely to answer and super helpful through my whole process) is aware of what I'm doing by now but for the sake of explanation here we go...

           I have a price list which has fields : Item, Specification, Vendor and price among others. Let's say I a section of my records looks like this:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                          

      Item

                     
                          

      Specification

                     
                          

      Vendor

                     
                          

      Price

                     
                          

      Chicken

                     
                          

      Organic boneless breast

                     
                          

      Delmonte

                     
                          

      $$$

                     
                          

      Chicken

                     
                          

      Organic boneless breast

                     
                          

      sysco

                     
                          

      $$$

                     
                          

      Chicken

                     
                          

      Organic boneless breast

                     
                          

      something farm

                     
                          

      $$$

                     
                          

      Chicken

                     
                          

      Whole 1-2#

                     
                          

      Delmonte

                     
                          

      $$$

                     
                          

      Chicken

                     
                          

      Stock bones

                     
                          

      sysco

                     
                          

      $$$

                     
                          

      Noodles

                     
                          Rotini                     

                                

                     
                          

      palo alto

                     
                          

      $$$

                     

           Where the dollar signs are equal actual dollar amounts that I didn't feel like faking up.

           In a separate layout I use a recipe recipe database which holds basic information about a recipe and a related ingredients table in a portal hold all the ingredients. so far so good. this is the functionality I am looking for:

           upon data entry I would like to enter an ingredient in the portal lets say chicken! then I would like the specification field to be a drop down list that displays all of the the available specifications for chicken but none of specifications used to describe say, noodles. furthermore I would like the vendor field to act similarly only limiting vendors by data in both of the previous fields. So, I choose "chicken" and then "Organic boneless breast" I would like the vendor field to display only "delmonte", "sysco" and "something farm" in its drop-down list. Likewise if i choose "chicken" then " stock bones" then only "sysco" should show in the vendor drop down list. and finally, the price field (and or others) should display the price for the ingredient that matches completely.

           I am also looking into a way to allow complete recipes to show up on as ingredients available for selection in recipe entry accompanied by pricing information. for instance, if I have a recipe for Chocolate ganache and a recipe for chocolate genoise in the database I would like to be able to create a new recipe for chocolate ganache cake by choosing ganache and genoise as ingredients and accompanying them with the procedure etc already built into my recipe entry system. 

           Strictly idealistic here, it would be awesome if in the print view of a recipe that utilizes recipes as ingredients, one could click on the name of that recipe to be taken to a print view of that recipe.  

           is any of this possible?

           Thanks

        • 1. Re: Matching fields in a portal with multiple values... I think
          philmodjunk

               What you are describing is a conditional value list. Here are some links on the topic:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Matching fields in a portal with multiple values... I think
            SkippDink

                 Thanks Phil! 

                 just know what that functionality is called is a huge leg up on unearthing more info

            • 3. Re: Matching fields in a portal with multiple values... I think
              SkippDink

                   I have the relationships defined as shown in the picture.  I have defined value lists to show values from related records starting at price list but the lists are not populating. there must be more.

              • 4. Re: Matching fields in a portal with multiple values... I think
                philmodjunk

                     If the value list lists values form PriceList, then the starting from table occurrence will be Recipe Ingredients.

                     It's by selecting a pair of table occurrences, one in the "Use Values from first field" drop down and the other in the "Starting from" drop down that identifies the relationship that will control what values appear in the value list.

                • 5. Re: Matching fields in a portal with multiple values... I think
                  SkippDink

                       Perfect! so simple.

                  • 6. Re: Matching fields in a portal with multiple values... I think
                    SkippDink

                         OK so I have come up with a problem that I didn't even notice at first.

                         i have my layers of conditional value lists which selectively whittle down my options like so

                                                                                                                                                                                                                                           
                                        

                    Item

                                   
                                        

                    spec

                                   
                                        

                    vendor

                                   
                                        

                    unit

                                   
                                        

                    Unit cost

                                   
                                        

                    Shows all items in my price list

                                   
                                        

                    shows only specs available for the item I selected

                                   
                                        

                    shows only vendors for the spec I have chosen

                                   
                                        

                    shows a predefined value list of all units of measurement available

                                   
                                        

                    unexpectedly show first result for whatever item i chose regardless of spec and vendor choices

                                   

                         now of course I do not have a conditional value list for the price because it should just show the cost of the single item that matches all my options chosen before that. the unit cost field itself contains a calculation that relies on three fields in the price list as follows: #, Fl Oz, and Ea. the unit cost field is a long string of if/then calculations that do the math based on the unit chosen. So, the problem lies in the three fields in the pricelist. how can I look them up where all three of the first fields must match...

                    • 7. Re: Matching fields in a portal with multiple values... I think
                      philmodjunk

                           Set up a relationship to a table occurrence of the table that stores the unit prices where all three fields are used as match fields to match to a record in that table occurrence. Set up your unit cost field to look up (auto-enter) the unit cost from this new table occurrence.