Product sheet w/ customer orders displayed

[Brian Heins]

Hello,

I need to create a product sheet / entry form that lists every product from a products table as well as fields that display and allow the user to enter order values for a particular customer.

I have a standard 3-table relationship schema of:

Customer (IDCust_pk)

Customer_Orders (IDCust_fk, IDProd_fk)

Product (IDProd_pk)

On the customer layout, I can get a portal to display just the products that the customer currently has orders for.

What I want to show is a layout where EVERY product is listed, customer information, and data fields for what the current orders are by product.

Any help would be very much appreciated.

-Brian

Edited July 12, 2007 by Guest

[Brett W]

One easy way to show all the products is to add another relationship to the products, using the Cartesian variable, "x". This will give you a result of every record in that related table, independent of how the relationship is set up.

[Brian Heins]

Sorry for the newbie questions. My previous experience with databases has been writing SQL queries from within C and VB apps, not dealing with layouts and portals. I'm having issues getting my head in the right space.

With the 3 table structure I mentioned above, how would I create the relationships so that all of the products showed up in a portal on a layout based on the customers table?

Right now I have:

Customer::IDCust_pk = Customer_Orders::IDCust_fk --> Customer_Orders::IDProd_fk = Product::IDProd_pk

In a layout based on the Customer table, I want to display every record from Product, plus the data from Customer_Orders that matches a product for the current customer, if any.

Again, sorry for the newbie questions and thank you for any help!