Relationship and calculation field problem

[FMtech79]

Hello, could anyone help me with the following please:

I have two tables related one to many, each table has their corresponding children tables (line items).

Table 1: Orders

Table 2: Invoices

Table 3: OrderDetails

Table 4: InvoiceDetails

The relationships are as follows:

Orders."_kp_Order_id" = Invoices."_kf_Order_id"

Orders."_kp_Order_id" = OrderDetails."_kf_Order_id"

Invoices."_kp_Invoice_id" = InvoiceDetails."_kf_Invoice_id"

Both detail tables has a field named "ProductCode" and "Quantity".

I need a calculated field to know how many quantities have been invoiced from a particular ProductCode in a particular Order.

I though that it could be acomplish by creating a calculated field in the invoice detail to get the OrderDetails."_kf_Order_id" in that table, then to create a table ocurrence "ProductsInvoiced" to relate it to order Details with two fields, like that:

OrderDetails."ProductCode" = InvoiceDetails."ProductCode" and OrderDetails."_kf_Order_id" = InvoiceDetails."_kf_Order_id"

with previous relationship, i created a calculation field in the order details table to get the sum(ProductsInvoiced."Quantity"). This calculation field is named InvoicedQty.

Unfortunately the invoicedQty comes out with nothing.

Do you know what to do to relate both details tables directly or what solutions is apropiate to acomplish the needed field.

Thank you in advance.

[RalphL]

Since you are using FMP 12, you can use the ExecuteSQL function to get the quantity invoiced. Make an un-stored number calculation = ExecuteSQL(" SELECT SUM (c.Quantity) FROM InvoiceDetails c INNER JOIN Invoices b ON c."_kf_Invoice_ID" = b."_kp_Invoice_ID" INNER JOIN OrderDetails a ON b."_kf_Order_ID" = a."_kf_Order_ID" WHERE c.ProductCode = a.ProductCode AND a."_kp_OrderDetail_ID" = ? " ; ""; ""; OrderDetails::_kp_OrderDetail_ID ) in the OrderDetails table.

The "'s are necessary because you have field names that do not start with an alpha character. I made a sample file and tried to use your nameing convention. You should be able to just copy and paste into your database.

[FMtech79]

Thank you very much RalphL, it works just perfect!