14 Replies Latest reply on Aug 5, 2009 6:01 PM by Colinnzz

    Conditional Value Result

    Colinnzz

      Title

      Conditional Value Result

      Post

      Hello,

       

      I am new to Filemaker, having been a spreadsheet man up to this point and after 2 days of frustration, l am hoping someone can help with this (simple?) problem:

       

      I have a table called Stock List containing fields Supplier, Description and Retail ex Tax. Using the advise from the knowledge base (Creating Conditional Value Lists) l have managed to get the Description pop ups to be based on Supplier selection pop up. However, l have been unsuccessful in getting the 'Retail ex price' displaying the value associated with a particular product description once the Supplier and Description have been selected.

       

      My efforts have left me feeling like a monkey infront of a typewriter!

       

      Cheers

       

       

        • 1. Re: Conditional Value Result
          philmodjunk
            

          What are you trying to do with your database? It sounds like you are trying to set up an invoicing system. If so, many other forum participants have worked on that idea. If you click the Advanced search link above and enter "invoice" as your search criteria, you'll find a number of threads that discuss this type of project.

           

          In general, you'll need several tables with relational links between them to manage the data in each.

           

          At a minimum, you'll need an invoice table where one record = 1 invoice, a lineItems table where one record = 1 item listed on an invoice and a price list table that lists each item available, its price and any number of additional fields depending on what you need to set up.

          • 2. Re: Conditional Value Result
            Colinnzz
              

            Thanks for that.

             

            I have searched through the entire database, created numerous files based on examples and although l am sure that somewhere there is an appropriate example, l cannot find it.

             

            Yes, this is for a telephone order invoice and resulting in product price auto entering as the result of the chosen item (Description).

             

            The problem l cannot get my head round is that the table 'Stock items" contains all the data l need to extract. Getting the subset "Description" to occur following "Supplier" selection works fine on the invoice layout, but getting the price associated with the "Description", at best. produces a list of prices associated with the Supplier and not the "Description".

             

            As l mentioned l am am new to this, thankfully Filemaker is on trial, so perhaps l am too thick and should return to the spreadsheets!

             

             

            • 3. Re: Conditional Value Result
              Colinnzz
                

              Further to my last and using your example:

               

               

               

               

              Create a table (which you may already have) INVENTORY with the following fields:

               

              PRODUCT ID (Text)

              Description (Text)

              Price (Number)

               

              Notice that PRODUCT ID is uppercase.  This will be a "key" field when relating to another table.

               

              Enter the following data:

               

              PRODUCT ID - Description - Price

              1 - Product 1 - 1.00

              2 - Product 2 - 2.00

              3 - Product 3 - 3.00

               

              ------

               

              Next, create the table ORDERLINE with the following fields:

               

              ORDER ID (Text) 

              PRODUCT ID (Text)

              Quantity (Number)

               

              ............. I get to the point........."Go to Browse mode, and create a new record.  Enter a name "Kat4", and in the portal, enter "1" into the first field."

               

               

               

               

              ........when l try to enter 1 into the portal field l get "This field cannot be modified until ORDER ID is given a valid value. If l make ORDER ID = 1 then the portal displays Products , 2 and 3 together with price etc.

               

              Even if l could get this to conclude like the example, l am unsure how a single value can be obtained from a portal list!

               

               

              • 4. Re: Conditional Value Result
                Colinnzz
                  

                Perhaps l can clarify what result l am after.

                 

                Suppliers A,B,C each have their own products. If l select Supplier A then l want another field  to display only Suppliers A's selection of products. Once the particular product from the Supplier A is selected, l would like to see the price associated with the selection and displayed in another field.

                 

                 

                 

                • 5. Re: Conditional Value Result
                  comment_1
                    

                  First, you need a Supplier (or, preferably, SupplierID) field, in both INVENTORY (to asociate a product with a supplier) and ORDERLINE (to limit the choices of product) tables. You also need a Price field in ORDERLINE.

                  Next, define two relationships between ORDERLINE and INVENTORY (using two occurrences of INVENTORY in the relationship graph):

                  ORDERLINE:: PRODUCT ID = INVENTORY:: PRODUCT ID


                  ORDERLINE:: SupplierID = INVENTORY 2:: SupplierID


                  Define two value lists, one of SupplierID's, the other showing values from INVENTORY 2:: PRODUCT ID, also displaying INVENTORY 2:: Description, show only related values starting from from ORDERLINE.

                  Place the SupplierID and PRODUCT ID fields (both from ORDERLINE!!) in the portal.

                  Set the SupplierID field to use the SupplierID's value list, and PRODUCT ID field to use the second value list.


                  Finally, define the Price field in ORDERLINE to lookup its value from INVENTORY:: Price.






                  • 6. Re: Conditional Value Result
                    Colinnzz
                      

                    Thank you for your patience.

                     

                    I am still having problems here!.

                     

                    You mention "Define two value lists, one of SupplierID's, the other showing values from INVENTORY 2:: PRODUCT ID, also displaying INVENTORY 2:: Description..."

                     

                    Where does "Description" get referenced from? Just to see l have it right - We had Orderline table containing ProductID, SupplierID and Price and Inventory tables, SupplierID and Product_ID

                    • 7. Re: Conditional Value Result
                      comment_1
                         The Description field is in the INVENTORY table, of course - you said so yourself a few posts ago.
                      • 8. Re: Conditional Value Result
                        Colinnzz
                          

                        Thank you for your reply and sorry to misunderstand you. l was thrown by your first word "First" and assumed that l your guide required me to start from scratch.

                         

                        Does your kind advise suggest l change the field Supplier to SupplierID?

                         

                        Should l have only 2 tables?

                         

                        Should the Table INVENTORY only have fields SupplierID, Description, Price, Product_ID

                         

                        Should the Table ORDERLINE only have fields SupplierID, Product_ID, Price

                         

                        My present imported stock listing has Supplier, Description and Price.

                        • 9. Re: Conditional Value Result
                          comment_1
                            

                          Colinnzz wrote:
                          My present imported stock listing has Supplier, Description and Price.

                          Will you always be using imported data, in this format? Normally, you would have a table of Suppliers, where each supplier has a unique SupplierID. An inventory item needs to "know" only the SupplierID. This way, when a supplier's name needs to be changed, it can be changed in one place only. However, if you are receiving your data from an outside source, you may have no choice but to live with it.


                          • 10. Re: Conditional Value Result
                            Colinnzz
                              

                            I can name the CSV columns that the data is imported from with whatever labels work with Filemaker.

                             

                            Are the Table fields l mentioned in my last email correct though? Is the portal going into ORDERLINE?

                             

                            Cheers

                             

                             

                            • 11. Re: Conditional Value Result
                              comment_1
                                

                              The names of the fields are not important; it's the nature of the data that matters. Consider an Inventory table like:

                               

                              1  Acme      Widget    56.50
                              2  Acme      Trinket   12.99
                              3  Binford   Hammer    21.05
                              4  Bifco     Turbine   246.00
                              ...

                               

                              Now imagine that "Acme" needs to be changed to "Accorn" - you have to do this for every one of Acme's products in the inventory.

                               

                               

                              Compare this to:

                               

                              Suppliers:

                              001  Acme
                              002  Binford

                              003  Bifco

                              ...

                               

                              Inventory:

                              1  001      Widget    56.50
                              2  001      Trinket   12.99
                              3  002      Hammer    21.05
                              4  003      Turbine   246.00
                              ...

                               

                               

                              I will answer your other question after you answer my last one.



                              • 12. Re: Conditional Value Result
                                Colinnzz
                                   The data import is a one off. Once and if ! l can get that single price to display that is the result of the description that is a subset of supplier, then subsequent data can be added as new records - although l had hoped that l could import from csv if and when needed.
                                • 13. Re: Conditional Value Result
                                  comment_1
                                     Ok, then let me summarize the suggested setup, assuming you will be using Supplier (name), not SupplierID.


                                  Orders:
                                  • OrderID (Number, Auto-enter serial number)
                                  ...

                                  OrderLines:
                                  • LineID (Number, Auto-enter serial number)
                                  • OrderID (Number)
                                  • ProductID (Number)
                                  • Supplier (Text)
                                  • Quantity (Number)
                                  • Price (Number, Lookup from Inventory:: Price)
                                  ...

                                  Inventory:
                                  • ProductID (Number, Auto-enter serial number)
                                  • Supplier
                                  • Description (Text)
                                  • Price (Number)


                                  Relationships:

                                  Orders:: OrderID = OrderLines:: OrderID

                                  OrderLines:: ProductID = Inventory:: ProductID

                                  OrderLines:: Supplier = Inventory 2:: Supplier


                                  Value Lists:

                                  Supplier (Show values from field Inventory::Supplier)

                                  Product (Show values from field Inventory 2:: ProductID, also display Inventory 2:: Description, Show only related values starting from OrderLines)



















                                  • 14. Re: Conditional Value Result
                                    Colinnzz
                                      

                                    Praise the Lord!

                                     

                                    You got me there, thanks!