4 Replies Latest reply on Oct 16, 2013 1:16 PM by JoRo

    Help with calculations?



      Help with calculations?


      Ok, I'm 16 days into a 30 day trial, although I have used it only 3 days. It seems once you open the program the clock starts ticking.   Anyway I'm new so go easy.


      I have two fields, one called "product", that refers to a value list, and one field called "product type".


      The "product" value list contains these values





      Entry Doors




      What I would like to do is pick one of these products from a "product drop down list field", and have that action populate the "product type field" with a drop down list of possible choices.  The trick is I want the "product type list field" to change according to what's in the product field.


      example: If pick windows from the "product drop down list field", I would like to see in "product type list field" different choices of windows, such as






      But say I pick Insulation from the same "product drop down list field"  I would then want to see the values in that same "product type list field"







      Hope this makes sense. 


        • 1. Re: Help with calculations?



          What you are looking for is a conditional value list. but you will need to setup a table, or tables, that stores the Product and there Product Type


          An example of this can be found on the Database Pros website CONDBASIC.FP7


          So you have your current table


          Create a new table PRODUCTS with the fields





          Relate the two Table Occurances in the Relationship graph


          YOUR TABLE        PRODUCTS

          Product   -- = --   ProductName 


          Now the first value list for products is based on the ProductName field in the  


          • Open your current Products valke list  
          • Check 'Use values from field:' and click Specify field...'
          • Specify the PRODUCTS table and select you ProductName Field.
          • Now make sure to also select 'Include all values '
          • Now OK that and the Value List is setup.


          Now edit your Product Type value list as follows



          • Check 'Use values from field:' and click Specify field...'
          • Specify the PRODUCTS table and select you ProductType Field.
          • Now make sure to also select 'Include only related values starting from:' to be you main PRODUCTS table.
          • Now OK that and the Value List is setup.
          No input the data into the PRODUCTS table with one record for each Product Type and have the Product Name for that type, as follows
          ProductName ProductType
          Windows       6500
          Windows       6500
          Windows       6500
          Insulation      R-19

          Insulation      R-30

          Insulation      R-38

          Insulation      R-49


          Now the value list should change according the the Product Name you choose.


          Let me know if anything is unclear.  


          • 2. Re: Help with calculations?

            Orlando, thank you so much for your reply.  I am off to work, so I will have to try this when I get back.


            I did read the solution and one thing comes to mind.  


            If I relate the product table to my table (which is called Appointments) will I have a "many to many" relationship and if I do, does it matter?  


            Also do I need to set up ProductName as primary key field?


            Anyway a HUGE thanks for taking the time to reply.  


            This is all pretty new to me but, I am excited to try this out. 

            • 3. Re: Help with calculations?

              Hi DeliciousApple


              How did you get on with this?


              With regards to your questions, the relationship would end up being a many to many relationship and effectively the ProductName will be the Primary Key in the new PRODUCTS, but because you are just using thsi table and relationship to generate your value lists you do not need to worry about any of this. 


              Do ask if you need any additional help on this.

              • 4. Re: Help with calculations?

                     I know this post is rather old, but I think it may have solved a problem I was having. I'm wondering if I use this set up, can I then calculate my cost of goods sold based on the PRODUCTS table mentioned?

                     Here is my scenario. We have 22 training manuals. Each manual can be purchased in three formats: print, PDF on CD, or Internet download. The unit cost of each manual in each format is obviously different. So I've been trying to find a way to calculate my cost of goods without forcing my users to find the exact item in the format they need in a list of 66 items or more. So if they can find the item, choose the format from another drop down list AND I can still count items sold and calculate total cost of goods sold that would be great!

                     So in this table occurrence that has the ProductName and ProductType fields, can I simply add unit cost there and pull the data from that table? The table would have to be related to the invoice table.

                     Or, is there a better way to do this now with FM12?