Total qty of each product in a purchase order

[Fernando_Martin]

Because we control serial numbers for much of our products, I have one separate line for each purchased item. This means for the majority of items I can’t have qty>1. But I want to generate a Purchase Order pdf showing the sum of each product, to send to my provider. I can’t figure out how to show this sum for the items in a Purchase Order. Does anyone know how could I do it?

These are the basic connections I have between some of my tables.

[Kamille_Parks]

Try adding a Rollup field to your Purchase Order table that does SUM(values) for the Order Items {Order qty} field.

[Fernando_Martin]

I did it, but I guess it doesn’t work because the Order Item field in the Purchase Order has multiple Order Items (lines).

[Kamille_Parks]

How are you creating the PDF? If you’re using the Page Designer app, you could set it up to make one page per Purchase Order record, and on each page have a table of linked Order Items records rendered as a table. In that table, you’d be showing the {Order qty} field which should give you a list of each linked Product's individual quantities.

[Fernando_Martin]

I’m trying to do it in Page Designer. But it only shows the lines for each order item. It doesn’t sum them.

You can see it in the example below. It’s the same product, but it shows each purchase order line. I need it to sum all the quantities for the same products in the same purchase order.

[Kamille_Parks]

I see. I misunderstood your data structure. Have you considered making one Order Item record per product, and linking that record to one or more Serial # records in a 4th table? That way you’d be able to use Count-type field for {Order qty} to give you the number of linked {Serial #s}.

[Fernando_Martin]

It makes sense. I’ll try in a test database, but it will result in more work or I’ll have to find a way to create the Serial Numbers lines automatically.