### Title

calculation in reports

### Post

Hi,

I have something somewhat trivial to do but I had brain surgery 2 weeks ago to remove a tumor (seriously) and I know it'll give me headaches to try to do/troubleshoot.

So I was wondering if someone could help me.

I have products that can be sold individually, in sub-boxes or in master boxes. For example, product X can be sold by the unit, in subs of 6 or in master of 36 (which contains 6 subs, each with 6 units).

I need to send a packing slip to my warehouse and in order for them to be effective and cost me less in handling, I need to provide them with the simplest answer.

I would like to only have one sku per product, so having 3 sku (one for single unit, one for subs and one for master) won't work.

Instead, I would like a formula that looks at the number of products ordered and produce the simplest answer. (with the assumption that the sub and masters size are already defined in the DB).

So, if we take the case of product X, (which can be sold by the unit, in subs of 6 or in master of 36 (which contains 6 subs, each with 6 units).) and that a client buys 6, I would like a calculated field/fornula that would return "1 sub-box" as a result (instead of 6 units).

-If someone buys 7, I would be looking for "1 sub box and one single unit" as a result

-If someone buys 36, I would be looking for "1 master box" as a result

-if someone buys 42, then "1 master box and 1 sub box" as a result

-if someone buys 43, then "1 master box, 1 sub box and 1 single unit" as a result

Would anyone have suggestions?

Thanks

JL.

Let ( [q = QuantityOrdered;

Mb = Div ( q ; MasterBoxCount ) ;

Sb = Div ( q - ( Mb * MasterBoxCount ) ; SubBoxCount ) ;

Unit = q - ( Mb * MasterBoxCount + Sb * SubBoxCount )

];

If ( Mb ; Mb & " master box " ) & If ( Sb ; Sb & " Sub Box " ) & If ( unit ; Unit & " Unit" )

)

Note that with scripting, the same basic algorithm could be used to rewrite a single line item into up to three line items with each entry a different packaging configuration and quantity. I would think that a better approach here as this would enable better reporting of packaging material consumption and cost for each order.