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

# Calculations in a portal

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

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

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

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

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

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