Grouping Line Items within a Portal

[Jeff Simon]

Hello. Please forgive me if the solution to this is obvious. I have looked through many threads and have not come across anything exactly like my problem.

I have an Inventory database that needs each bottle of (vintage) wine to have a unique entry (meaning, no quantity field because the quantity is always "1"). But when it comes time to invoice, using an Order database, many bottles and many varieties may be included in the portal. Right now, they are showing up as individual line items, related by the PO Number. The subtotal function Count is working, as is the subtotal of Sale price and appears like this:

Qty Wine Each Amount

3 2002 ABC Merlot 1.10 3.30

3 2002 ABC Merlot 1.10 3.30

3 2002 ABC Merlot 1.10 3.30

2 2002 ABC Cab 1.50 3.00

2 2002 ABC Cab 1.50 3.00

What I would like to appear on the invoice is:

3 2002 ABC Merlot 1.10 3.30

2 2002 ABC Cab 1.50 3.00

I imagine I could come up with some scripting solution, but I was hoping for something more elegant. Any suggestions?

Jeff

[bruceR]

Hello. Please forgive me if the solution to this is obvious. I have looked through many threads and have not come across anything exactly like my problem.

I have an Inventory database that needs each bottle of (vintage) wine to have a unique entry (meaning, no quantity field because the quantity is always "1"). But when it comes time to invoice, using an Order database, many bottles and many varieties may be included in the portal. Right now, they are showing up as individual line items, related by the PO Number. The subtotal function Count is working, as is the subtotal of Sale price and appears like this:

Qty Wine Each Amount

3 2002 ABC Merlot 1.10 3.30

3 2002 ABC Merlot 1.10 3.30

3 2002 ABC Merlot 1.10 3.30

2 2002 ABC Cab 1.50 3.00

2 2002 ABC Cab 1.50 3.00

What I would like to appear on the invoice is:

3 2002 ABC Merlot 1.10 3.30

2 2002 ABC Cab 1.50 3.00

I imagine I could come up with some scripting solution, but I was hoping for something more elegant. Any suggestions?

Jeff

Those totals don't look right. Don't you mean you want:

9 2002 ABC Merlot 1.10 9.90

4 2002 ABC Cab 1.50 6.00

[comment]

An easy way to achieve this is to print the invoice as a report of LineItems, sub-summarized by Product. To get a similar result in Browse mode, you would need to use either the so-called "Ugo method" or a recursive custom function.

Those totals don't look right.

the quantity is always "1"

[bruceR]

An easy way to achieve this is to print the invoice as a report of LineItems, sub-summarized by Product. To get a similar result in Browse mode, you would need to use either the so-called "Ugo method" or a recursive custom function.

I think we'll have to get further explanation from the OP because he has presented conflicting statements.

This is the result he said he wanted:

What I would like to appear on the invoice is:

3 2002 ABC Merlot 1.10 3.30

2 2002 ABC Cab 1.50 3.00

[comment]

It's the display that's conflicting, not the statements. The portal, as it is now, is repeating a summary result. He has 3 items of 1 Merlot and 2 items of 1 Cab, showing in 5 rows instead of the desired 2.

[Jeff Simon]

Yes, there should be only two line items, as Comment pointed out. I went with a workaround: I created a new table with a PO, item, quantity, and item cost, and this portal works. Not elegant, but functional. I have read and reread Ugo's method, but couldn't get my head around it. Thank you for your assistance.

[comment]

I created a new table with a PO, item, quantity, and item cost, and this portal works.

OK, now you have lost me. Isn't a "table with a PO, item, quantity, and item cost" the LineItems table?

[Jeff Simon]

Since I couldn't make the Inventory table work, because the grouping of like line items wasn't working, I created a intermediate table that can keep track of quantity/item and used that table in my invoicing layout. It isn't elegant, but it gets the job done.

[comment]

I still don't understand what you mean (you said the same thing as before). Can you post your file?

I couldn't make the Inventory table work, because the grouping of like line items wasn't working

See if this helps.

ConsolidateLI.fp7.zip

[Jeff Simon]

This is quite nice and I thank you. However, it breaks down when ABC Merlot has two product IDs. See attached. Any suggestions?

ConsolidateLIjs.fp7.zip

[comment]

Well, then use whatever they have in common (product category?) instead of the ID.

[Jeff Simon]

Using "Product" worked! Yeah! Problem solved. If you are ever in Berkeley, I'll buy you a beer at the Albatross...