1 2 Previous Next 17 Replies Latest reply on Mar 27, 2013 5:25 PM by ShaoLiang

    Conditional Value List

    ShaoLiang

      Summary

      Conditional Value List

      Product

      FileMaker Pro

      Version

      12

      Operating system version

      Windows

      Description of the issue

      In the relation (Customer <-> SalesOrder <-> SalesOrderLineItem <-> QuoteLineItem), I would like to call the QuoteLineItem from SalesOrderLineItem by using Pop-up Menu. The Pop-up Menu gives all the QuoteLineItems and I onlt want to show the QuoteLineItems related to that Customer. I tried many ways to do it. Nothing works right for me.

      Steps to reproduce the problem

      [Customer] and [SalesOrder] are related by CustomerID;
      [SalesOrder] and [SalesOrderLineItem] are related by SalesOrderID;
      [SalesOrderLineItem] and [QuoteLineItem]are related by QuoteLineItemID and CustomerID;

      Expected result

      In the Pop-up Menu, I choose the "Include only related values...". I should be able to load all the item info into the SalesOrderLineItem.

      Actual result

      In the Pop-up Menu, I choose the "Include only related values...". I couldn't load all the item info into the SalesOrderLineItem. I guess that is due to the double relation of QuoteLineItemID and CustomerID. Any better way to do it?
      Thanks,

      Exact text of any error message(s) that appear

      no error message

      Relation-2013-03-26.jpg

        • 1. Re: Conditional Value List
          Fred(CH)
          Hi Shao, could you please post one more screenshot : one of the manage Value Lists dialog, please ? It will be perfect. See you soon. Fred. PS : what lovely graph : cleaner than i never saw !
          • 2. Re: Conditional Value List
            ShaoLiang
            /files/1a6feb2cf0/Value_list_UQLI.jpg 466x530
            • 3. Re: Conditional Value List
              Fred(CH)
               Hi again. I am not shure to understand correctly your structure. But assuming that : **************** • The CUSTOMER ID in SOrdrer LineItems is known and STORED (no unstored calculations). • We want, with an dynamic menu, choose one of the multiple Quote Line concerning this client, and like this, Enter the Unique Quoted Item result on SOrdrer LineItems Record. • The final goal is to establish relations between SOrdrer LineItems and Quotes LineItems For SOrder. **************** To do that : • Create a third Table Occurrence of Quotes LineItems "Quotes LineItems by Customer". • Connect this new TO at SOrdrer LineItems with the simple relation of Customer ID. • Modifiy your Value Lists to assign this new TO instead the older. Be carefull with the fields that are using. First is naturally Unique Quoted Item but second, if you want it, must be relevant. Company Name is not because as assumed at startup the customer is known. For example you can choose the Concerning or Subject or Reference (We don't see all the fields). • On your layout, define the Field SOrdrer LineItems::Unique Quoted Item as a menu and choose the value list we built. **************** If i misunderstood the structure, it won't work and you need to correct my first paragraph. (If the paragraph is visible because i encounter problems with that and i don't use a iPad but a iMac).
              • 4. Re: Conditional Value List
                ShaoLiang

                     Thanks fred,

                     I am sorry that I made a mistake when I took the image by click the "Customer Company". It should be the "Customer ID".

                     Please find the relation image (Figure 1) below as I posted again with all the fields. Same color of the TO means from the same table.

                      

                • 5. Re: Conditional Value List
                  ShaoLiang

                       I would like to make the problem more clear by add more images here.

                       The first problem:

                       When I enter a new record in the SalesOrder Line Item section. The relation field did come up itself.

                  • 6. Re: Conditional Value List
                    ShaoLiang

                         Since the "Customer ID" is not entered in the SaleOrder Line Item, the Pop-up Menu ("Unique Quote ID" with option of display the "Customer ID") doesn't work and nothing can be entered.

                         When I delete the SalesOrder Line Item that I just Entered (Item 001) and try to re-enter it again. The relation field "Customer ID" is entered aytomatically. and Pop-up Manu still doesn't work (Maybe due to double relation of "Unique Quote Item" and "Customer ID" and only "Customer ID" is entered not the " "Unique Quote Item")

                    • 7. Re: Conditional Value List
                      ShaoLiang

                           In the Pop-up Menu, I don't choose the "Include only related values...". I could load all the item info into the SalesOrderLineItem but all the Quote items are in the Pop-up Menu list even quoted to other customers as shown in the above picture.

                           I would to see only the first item in the Pop-up Menu list.

                      • 8. Re: Conditional Value List
                        Fred(CH)
                        Dear Shao,  I need to admit that my english is pretty bad. Moreover, your structure is already very advanced. So i think a better strategy is to reverse the roles. I will send you an example with a really simple database that shows how to define dynamic menus. Three tables and four occurrences are needed (because one specific for the MAGIC menu) . I don't know if it is the best way but let see... Fred
                        • 9. Re: Conditional Value List
                          Fred(CH)
                           The Schema
                          • 10. Re: Conditional Value List
                            Fred(CH)
                             Define the Magical Value List
                            • 11. Re: Conditional Value List
                              Fred(CH)
                               Configuring your layout
                              • 12. Re: Conditional Value List
                                Fred(CH)
                                 Define your categories and sub categories...
                                • 13. Re: Conditional Value List
                                  Fred(CH)
                                   Example #1 : choosing the "Companies" Primary Category
                                  • 14. Re: Conditional Value List
                                    Fred(CH)
                                     Example #2 : Choosing the "Privates" Primary Category
                                    1 2 Previous Next