Hallo,

I have 3 tables, Products, Orders, and OrderProducts.

Products is a list of all possible products, identified by ProductId

A record in Orders is created when someone phones with an order, and is given an OrderId

The Products for an Order are stored in OrderProducts, which contains OrderId, ProductId and Quantity.

On my order form I want to display data from the Order table in the header, then with a list of data from the OrderProducts, but for each OrderProduct I want to display data from the Products table, such as Product Name, Manufacturer etc.

How can I do this? I created a form based on Orders, then a portal for OrderProducts, but I can't work out how to display the Products data in the portal.

I am totally new to FM, so any help much appreciated.

- Frink

Your database architecture is fine. But you cannot nest portals. You could add calculated fields to the join file (OrderProducts) for ProductName, Manufactuer, etc. based on the ProductID (which get the values from Products). Then put those new fields in the portal.

Brilliant. Simple when you know, but impossible when you don't.

Thanks again

- Frink

The other way is instead of creating a form layout on Orders, create a list layout on OrderProducts. Then you make a larger header part with all the related order information, and a trailing grand summary for the order totals etc.

You will need this technique if you want to support orders with arbitrarily large numbers of lines, because you can't make a portal spill on to multiple pages nicely. I started out creating all my order and invoice print layouts as forms, and ended up having to recreate them as list layouts on the related table in order to support multi-page invoices and orders.

AJ