Not sure how to do this...

[Chris Good]

So here's the deal. I have an order entry database. The tables I have are line items, invoices, suppliers, customers. The line items table contains the individual line items for the invoices and is related to the invoices table using a field called invoice number. Each line item can potentially come from a different supplier, so in my line items table I have a supplier # field that the person entering the item needs to populate when entering the item.

On an invoice layout there is a portal that shows the line item records including the supplier # field.

When the invoice is entered and ready to be printed, you only need to print one invoice, ut I need to print a purchase order for each supplier only showing their line items.

Any ideas how the hell to do this? I have racked my brain all day and I can't think through it.

[LaRetta]

You can actually approach this from three sides (Invoice, LineItems or Suppliers). Which direction you come from will produce different outcomes. We need more information. Here's a visualization which might help:

If you print one Purchase Order against one Invoice for each Supplier (at the time of the Invoice), the Supplier will receive a PO with one or two items on it (only theirs) - one for each Invoice. Your postage and paper costs will go up! You can potentially end up with as many Purchase Orders PRINTED as you have LineItems (if each Supplier only has one item per invoice). Is this what you want? Your relationship with your Supplier and the business logic will determine your POV here. If your Purchase Orders instead provide your Suppliers with only a list of their Items, Quantity needed, including a reference to the Invoice Number (and only ONE PO#), that usually makes them happier than 20 Purchase Orders for the same product (because they were for different Customers on YOUR end). Make sense? Do you have a PO created now and how does it accumulate? What are your Supplier needs here? Do they care even who the Customer is? If you wish the second approach, here's a quick way: Find (or GTRR) to isolate a set of LineItems which have not had POs created. Once you have the 'Supplier Set' that you want to create your Purchase Orders from, the rest is easy:

Create a layout (columnar, grouped data with Sub-totals (again in LineItems). Put Supplier in leading part (and Supplier Address, etc. making it look like the upper PO portion) and list the LineItems below. You can add a second part below Supplier to sub-group by Supplier Product if you wish. Ditch the body entirely after you've created your report structure. All the Supplier cares about is THEIR products and the total quantities you wish to order of each. Sort by Supplier and then Product, add a page break on Supplier and THIS produces all of your POs for that timeframe. After printing (in Preview) switch to Form Layout and either loop-set or Replace Contents the PO printed date through the set.

So do you want to print a Supplier PO EACH TIME an Invoice is created? Or do you want to group their products and place your orders daily, weekly etc ? If at the time of Inoivce, you would GTRR to that Invoice's LineItems and produce a report similar to above - it'll just be very short and many more pages and you'll run through PO numbers much more quickly. If you want to select a Supplier and generate a PO from that side, establish a relationship to LIneItems based upon SupplierID and a 'PO not generated criteria'. GTRR to LineItems and produce similar report above.

[Chris Good]

Typically what happends is there are lets say 6 items on an invoice. And 2 of the items come from one supplier, three from another and 1 from another. So I have three suppliers involved with one invoice to a customer. When I print the invoice off I want to be able to print three PO's, one to each supplier.

What I can't figure out is how to grab the couple or line item records that belong to a specific supplier and create a new PO, then do the same for each subsequent set of line items.

[LaRetta]

From Invoices, Go To Related Records (GTRR) [ Show Only Related ; using relationship LineItems ; via layout based upon LineItems. Generate PO in report format as above? A columnar layout in LineItems with appropriate parts will do every Supplier tied to that particular Invoice in one whack! You will have 3 pages print (one for each Supplier) showing the detail of their products.

How are your POs connected in this piece? What layout (table or table occurrence) is your Purchase Orders based upon? Just not enough information here, sorry. But the answer is to GTRR jump to that Invoices LineItems and generate your PO there, I believe.

I use similar technique but we check our reorder status flags (which use 'low-stock' counts and minimum levels and generate our POs by GTRR from Supplier to LineItems and print there when needed to replenish stock only.

Maybe others see a different perspective - that's why these forums are so darned incredible!

LaRetta