I need to sell one product to several customers on one order. What's the best method?

My db has four tables: Customers, Orders, OrderLineItems, and Products. The relationship is built using unique ID key fields for each table. The OrderLineItems table is a join table, and is used as a portal in the Customers layout.

I have 5,000+ customers that will/may receive one product each month. The product will be unique, and only sold (to any customer) once.

I need the "order" so that I can generate a report of the customers (found set, not necessarily all customers in the db) that received "Product1" in January, and "Product2" in February, and so on.

I also need to be able to see which products ("Product1", "Product2", "Product3", etc ...) each customer has purchased in the customer layout.

I just can't seem to get my head around this, and I know the answer will be simple.

Thanks in advance for any help!

May I ask a question? How does it come to be that several customers end up on one order? Are they splitting the expense?

No, each customer pays in advance for their purchase, and may pay a different price.

The "order" I need to create will be used to report on which customers received the product each month. (It will also be used to export customer name, address, etc ... for shipping labels.)

I need to sell one product to several customers on one order.

My db has four tables: Customers, Orders, OrderLineItems, and Products. The relationship is built using unique ID key fields for each table. The OrderLineItems table is a join table, and is used as a portal in the Customers layout.

I have 5,000+ customers that receive one product each month. The product will be unique, and only sold (to any customer) once. (Like a fruit of the month club ... )

I need the "order" so that I can generate a report of the customers (found set, not necessarily all customers in the db) that received "Product1" in January, and "Product2" in February, and so on. (It will also be used to export customer name, address, etc ... for shipping labels.)

I also need to be able to see which products ("Product1", "Product2", "Product3", etc ...) each customer has purchased in the customer layout.

I just can't seem to get my head around this, and I know the answer will be simple. I just don't want to individually create 5,000+ invoices each month, unless necessary.

Thanks in advance for any help!

(This was originally posted in the relationships forum, but I didn't get much help.)

If you're not going to create 5,000 invoices then who does the ONE invoice go to? I'm not quite following your expectations.

I'm not really trying to create an invoice, I need a report that shows which customers were sent the product each month.

Customers have already been invoiced and paid for their "subscription." The invoice covers a period of months. (Example: January through December 2007 = 12 products)

Some customers choose to suspend delivery for a period of time, due to vacation, out of town, etc ... Otherwise I could just "find" all customers where the is "active" and create my report.

My best guess on how to do this is to create an order for one product, that contains all the numbers. (I think then I can use a portal on the customer layout to view the history of each product they have received.)

On another forum I received the suggestion to create an order for each customer, each month.

While this would work, I think it would be very time consuming unless I can create a (global?) field that holds all the numbers for those customers whose is "active" for that month's shipment.

Otherwise, I suppose that I'll start working on a script that creates the 5,000 orders. I'm concerned that creating 60,000 orders per year is going to make the size of db very large, and effect performance.

Please do NOT repost the same question in a new thread. It only adds confusion. If you didn't get a satisfactory answer, post again in the same thread.

http://www.fmforums.com/forum/showtopic.php?tid/190587

I merged your two topics.

Lee