I have a database with the following tables:

Customers

Orders

Products

I have done a layout for the order that is linked using a relationship to the customers table (Customers::CustomerID linked to Orders::CustomerIDfk). It works fine

However, I have also done a portal in order to select the different products bought by a customer. I have established a relationship between the order table (Orders::ProductIDfk) and the Product table (Products::ProductID).

I want to be able in my order layout to add records via the portal but it doesn't work.

What am I doing wrong :

Hope you can help...am i missing something ?

Edited December 18, 200619 yr by Guest

In your relationship between the order table (Orders::ProductIDfk) and the Product table (Products::ProductID), do you have the option of allowing creation of records checked?

Yes, I do for Orders::ProductIDfk

Also, in my portal, the fields (from Products Table) are to be displayed:

Product ID

Product Name

Product Price

Can you get the following screen shots:

1. The portal in layout mode

2. The portal in browse mode

3. The Realtionship diagram

4. The relationship dialog box for the relationship in question

You are missing a line items table that should be a join table between your orders and the products. In other words, the Order table will have the CustomerID, and on one of its layouts, a portal to the lineitems table with 'Allow Creation of Records' checked.

Here is an attachment of the JPEG.

Hope this can help you find where the problem is.

Thanks for your help

Yes you need a line items table.

Customers --> Orders <--> LineItems <--> Products

It worked - Thanks John and everyone else for your help.

For future reference,

• 
  

A [color:red]Line_items Table needs to be created (as john just said).

In my case, I included the fields

[color:red]LineItemsID

ProductsfkID

OrdersfkID

• 
  

2 relationships need to be created between:

1/[color:blue]Orders::OrderId --> [color:red]Line_Items::OrdersfkID

'Allow Creation of Records' needs to be checked on OrdersfkID side

and

2/[color:red]Line_Items::ProductsfkID --> [color:yellow]Products::Products

• 
  

In my layout, I have created a portal showing related records from the [color:red]Line_Items table.

Then, include the field [color:red]Line_Items::ProductsfkID and the field you want from the [color:yellow]products table.

You should be able to records new entry in your portal and by typing the product ID in the ProductsIDfk field, you should have the other fields appearing automatically.

Hope this help

Edited December 18, 200619 yr by Guest