Join table problems

[Johnny777]

Hi all

I have three tables: customers, Sales and Sales Products. It works really well when there is only one sale per product per customer. The sales table holds foreign keys for the customer and sales product tables

The problem is a single sale can have several different customers and one product can be sold to many customers.

Basically I need to set up a sales table/layout which can show, for example, several different customers and one product (several people from same company attending a single event) and then generate one invoice for the whole sale.

Have been tearing what's left of my hair out trying to figure out. If anyone can give me a few pointers would be very grateful

[comment]

Is this correct: a sale has one product and one or more customers?

[Johnny777]

Hello

Thanks for replying

A sale can have one customer and one or more products or

it can have one or more customers and one product, but never a combination of the two ie multi customers with multi products

[comment]

but never a combination of the two ie multi customers with multi products

That makes very little difference, because once your basic data structure enables a sale to have multiple customers or multiple products, it automatically allows it to have both (unless you employ some measures to prevent this situation).

You need a structure with two join tables like:

Customers -< Buyers >- Sales -< LineItems >- Products

Basically, it's a typical invoice solution with a "Buyers" join table added between invoices and customers. This table has foreign key fields for SaleID and CustomerID.

You can add a customer to a sale by creating a new record in a portal to Buyers on a layout of Sales - similar to the way you add a product by creating a new record in a portal to LineItems.

[Johnny777]

Hi

I have made those changes and got everything working, so thanks very much for replying, although I now have a completely new set of problems to solve

For example, On the sales table the first customer on the layout (ie the customer in portal row one) needs to have their address details copied onto the delivery address on the sales table.

With a one to one relationship it is easy as it can all be done on a multi-lookup.

Can anyone advise me on the best way to do this when portals are being used to set up records.

[comment]

Well, you could perhaps script this, but a more "correct" solution would be to lookup the customer address into the 'Buyers" join table, then use the data from the first related Buyer record.

This way, if a mistake has been made and the first buyer needs to be deleted, the sale record will automatically use the second buyer's address, and so on.

Even better option would be to select one of the buyers explicitly as the recipient, by entering their CustomerID into a RecipientID field in Sales. This would trigger a lookup of the selected person's address directly into the sale record. This is IMHO the correct approach, because it keeps the order in which buyers are entered meaningless.