5 Replies Latest reply on Jan 6, 2013 6:05 AM by robby@brays.net

    Calculations in a portal

    robby@brays.net

      Hello

      Sorry but I am new to FileMaker Pro and am building my first database

      What I am trying to do here is :

      In a portal, calculate the quantity, in my line item table with a field ( number that represents a number of parts for this product) in my products table to give me a total of parts needed for this order. I cannot seem to get it to work for me. It keeps returning 0 for the calculation. The field I am selecting is of the related table order_lineitem_product , so I think the relationship is correct.

      Thanks

        • 1. Re: Calculations in a portal

          Create a summary field in LineItems which would be Total of Quantity.  But I am unsure of your relationship.  It should be similar to:

           

          LineItems::ProductID = Products::ProductID

           

          Products ID should be auto-enter serial.

          • 2. Re: Calculations in a portal
            comment

            robby@brays.net wrote:


            calculate the quantity, in my line item table with a field ( number that represents a number of parts for this product) in my products table to give me a total of parts needed for this order.

             

            I don't think your question is clear enough. Usually, in a portal to line items you would see something like:

             

            ProductID
            Product
            Quantity
            Price
            Extended Price
            101Widget152.3535.25
            354Gadget104.2542.50

             

            Now, what exactly is the "total of parts needed for this order"? It seems like you want the sum of Quantity (15 + 10 = 25 in this example), which is a pretty meaningless number IMHO (unless someone counts the items on exit, or perhaps it has some meaning for shipping costs calculation?).

            • 3. Re: Calculations in a portal
              robby@brays.net

              Thanks for the input. The example you showed would be simulate to quantity x price. To be exact, I manufacture products that use different amounts of parts per item. What I am trying to do is multiply the item by stored component fields in the product item table. For example one part contains 2 of this item and 1 of this item, along with 15 of these items. All the items assembled create the part. What I am trying to do is multiply the number of parts by each of the component numbers to get a material list for the next days production schedule.

              Simply said if I need to build "10" of these items tomorrow I need to multiply 10 by all the individual numbers to each component. 

              • 4. Re: Calculations in a portal
                comment

                I am afraid I don't quite follow. I think you describe a situation where a product is made out of various quantities of various components, say a widget is made out of 5 nuts and 10 bolts, while a gadget requires 7 nuts and 3 bolts. This would be represented by three tables, arranged as:

                 

                Products -< ProductComponents >- Components

                 

                where the ProductComponents table is sort of a "line items" table for the product. The entire structure would then look something like this:

                 

                Orders -< Lineitems >- Products -< ProductComponents >- Components

                 

                 

                 

                robby@brays.net

                 

                What I am trying to do is multiply the number of parts by each of the component numbers to get a material list for the next days production schedule.

                 

                That will become possible if you can find a way to define which orders (or rather line items) are to be included in "the next days production schedule" and make them related to ProductComponents. Note that producing a bill of materials is by no means an easy task, so be prepared to struggle with this for a while.

                • 5. Re: Calculations in a portal
                  robby@brays.net

                  Yes. I think you are on the right track now.

                  Lets say I build "Product A "

                    Product A consists of 2 different parts which total 18 pieces all together. 15 of one part and 3 of the other.

                  My relationship is this right now:

                  Work orders -- line items --products