8 Replies Latest reply on Jan 7, 2010 1:51 PM by Dracozhu

    How to get data from and unrelate table

    Dracozhu

      Title

      How to get data from and unrelate table

      Post

      I had Inventory Table(include price), and a Product Table

      In Product Table, there is some variable that may change the material will be used and the number been used

      In Inventory Table, there lots differenet material

       

      I want get a cost that, price*quanlity

      However, I cannot think a connect between this two table, which make me confused

      Becaused, there will be logical or if statment to get which one in connect....

       

      How should I do that?

        • 1. Re: How to get data from and unrelate table
          ninja
            

          Howdy Dracozhu,

           

          You have Inventory of Products, right?

          In your inventory table, there are lots of different materials that are Products, correct?

           

          Each product should have a unique ProductID, and each record in Inventory should have that unique ProductID in it so that you know what product you are holding inventory of.

           

          Your tables would then be related from Product:: ProductID = Inventory:: ProductID

          Then you can simply reference the price from the related table.

           

          The solution isn't to reference a value from a non-related table, but is instead to make the tables related.

           

          Edit: removed smileys

          • 2. Re: How to get data from and unrelate table
            Dracozhu
              

            Sorry, I think I expalin unclear

             

            The inventory is for materials not for product

            inside inventory there are the materials used for build different product

             

            If product code changed, the material quality or type will be changed too

             

            and product is different most times.100 different ways

             

            • 3. Re: How to get data from and unrelate table
              ninja
                

              OK, If I now understand correctly, you have Inventory of Raw Materials and you have finished Products made from those raw materials.

               

              If you use one price for your materials, you would need a table in-between those two that has the FormulaLineItems or BillOfMaterialsLineItems.  The Raw Material would have a price, the BillOfMaterialsLine Items would calculate the price of each material used multiplied by the quantity used, and the Finished Product would Sum() the related BillOfMaterialsLineItems.

               

              Table:              InventoryRawMats >--- BOMLineItems >----------Product

              Field:               Material Price                CostOfMaterialUsed        Product Cost

               

              If you track which shipment of raw material went into which lot of product and calculate a different cost for each lot of product, this scheme would get a bit more complex.

              • 4. Re: How to get data from and unrelate table
                Dracozhu
                  

                I get you method, But this will cause over 700 new entries

                I have a logical way that in my head, but not sure if filemaker have code to do so. (anto)

                 

                For Example,

                In inverntory Table, I had an Choose of Type for Product, if It is choose 1, Material 1 will be only 5 used, Material 2 will be 10 used, choose 2, Material 1 will be 10, Material 2 will be 20.

                I also, had another type choose in  Product, choose 1 Material 1 wil cost $1, Material 2 will cost $1.5, choose 2 Material 1 will cost $1.5,Material 2 will cost $2

                 

                Because the double relation I hardly to use the function and relation.

                I tried to find calculation fuction, but dont know which I should use

                 

                Also, If the above is hard to do,

                Is it possible that

                 

                In inverntory Table, have mulit-relation with the material and to get the price

                For example, Material 1 Type A, Material 1 type B, Material 2 type A,Material2 type B

                If and choose happen in Product Table, Choose 1, it will get all Type A, choose 2, it will get all Type B

                • 5. Re: How to get data from and unrelate table
                  ninja
                    

                  Dracozhu wrote:

                  For Example,

                  In inverntory Table, I had an Choose of Type for Product,

                   

                  if It is choose 1, Material 1 will be only 5 used, Material 2 will be 10 used,

                  choose 2, Material 1 will be 10, Material 2 will be 20.


                  Yes Sir.  That's a formula (or Bill Of Materials).

                   


                  Dracozhu wrote:

                  , choose 1 Material 1 wil cost $1, Material 2 will cost $1.5,

                    choose 2 Material 1 will cost $1.5,Material 2 will cost $2

                   


                   

                  Why do your Material Costs change based on what product you are making with them?  I don't understand.

                  • 6. Re: How to get data from and unrelate table
                    Dracozhu
                       Like same kind material that have different colour, that will cause different price=-=
                    • 7. Re: How to get data from and unrelate table
                      ninja
                        

                      Forgive me if I'm being thick-headed...but if the customer cares what color the product is, then green material and blue material are two different materials...they can't both be Material1.  And if this is the case, the BillOfMaterials must specify which is to be used (and reflect the appropriate cost).

                       

                      And if the customer doesn't care what color it is...you would always buy the cheapest one and have only one price.

                       

                      I don't mean to dig into what you're trying to accomplish...but I do want to make sure that what we build together accomplishes what you are trying to do...otherwise it is all for nothing.

                       

                      Could you post an overview of the end goal that you are trying to accomplish?  Then we can make sure that we head in the right direction.

                      • 8. Re: How to get data from and unrelate table
                        Dracozhu
                          

                        Beacuse, the they only can choose one kind colour or one kind style or something

                         

                        I want to find the total costs

                         

                        I trying to find a way that may work, but I dont no if there a code exist

                        which is for some field equals to specific field that unrelate?

                         

                        forexample, field A in table 1= the third line field X in table 2