9 Replies Latest reply on Oct 21, 2011 8:21 AM by philmodjunk

# calculation in reports

### 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.

• ###### 1. Re: calculation in reports

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.

• ###### 2. Re: calculation in reports

thanks for this!

I wasn't able to get the code to work; the result would always be "? master box, ? Sub Box, ? Unit

Any idea?

Thanks

• ###### 3. Re: calculation in reports

I tested this in a test file before I posted it so I know that it works.

Sounds like either your MasterBoxCount or SubBoxCount fields are either empty or zero at the time this expression evaluates. (Divide by zero will result in the ? like this.)

• ###### 4. Re: calculation in reports

hmm, I made sure these fields had data but will investigate more fully tonight.  The problem tends to be between the chair and the keyboard (I'm talking about my chair and keyboard).

Thanks again.

JL.

• ###### 5. Re: calculation in reports

Also check that they are defined as number fields (that one has bitten me more than once).

• ###### 6. Re: calculation in reports

I got it to work... It was indeed between the chair and the keyboard.

I had the calculated field in my invoice table, instead of my invoice_line_item table...

Thanks for the help!

• ###### 7. Re: calculation in reports

This isnot about FM. It's about brain surgery. Four years ago I had a serious "TBI". I am lucky in that I have recovered 100% of my cognitive abilities. It took a lot of work. I wish you the same success.

RW

• ###### 8. Re: calculation in reports

thanks for the kind words.  In my bad luck (the tumor is cancerous), I was very lucky as it was in the parietal region, so my only side effects are loss of motor skills on the left side for a few weeks (i'm typing with one hand for now...).  It should all come back.

Phil, you wrote:

---

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.

---

How would you go about doing that?  I've been reading on it and can't find anything.

Thanks

JL.

• ###### 9. Re: calculation in reports

I'll assume that you have the basic/classic table and relationships typical of an invoicing system:

Invoices----<LineItems>-----Products

Where a portal to lineItems is used to list the items purchased. I'm also assuming that three fields in Products provide the box counts for each of the three boxes used.

I'm going to leave LineItems unmodified but use the records in LineItems to generate similar records in a "PackingList" table, but customized to meet the requirements of your packaging system so that single line items generate up to three Packing List Records. I'll related PackingList to Invoices exactly the same as I would LineItems. (And with a few changes, this script can be used to replace the line item record with packaging specific entries, but by keeping two tables, you have your items listed "as ordered" and "as packed"--which may be useful in some situations and in this case makes it easy to compare the original records to those created by this script.)

#Pull up the LineItems record for the current order on a LineItems Layout so we can loop through them, generating PackingList records
If [ LineItems::_fk_InvoiceID //Make sure there are LineItems records for this invoice ]
Go To Related Record [Show only related records; From table: LineItems; Using layout: "LineItems" (LineItems)]
Go To Record/Request/Page [first]
Loop
Set Variable[\$ProductID ; LineItems::_fk_ProductID ]
Set Variable [\$InvoiceID ; LineItems::_fk_InvoiceID ]
#Create MasterBox entry in PackingList if one is required
Set Variable [\$MB ; Div ( LineItems::QuantityOrdered ; Products::MasterBoxCount ) ]
IF [\$MB]
Go To Layout [PackingList ]
New Record/Request
Set Field [PackingList::ContainerType ; "MasterBox" ]
Set Field [PackingList::_fk_InvoiceID ; \$InvoiceID ]
Set Field [PackingList::_fk_ProductID ; \$ProductID ]
Set Field [PackingList::Qty ; \$MB ]
Go To Layout [LineItems]
End If
#Create SubBox entry in PackingList if one is required
Set Variable [\$SB ; Value: Div ( LineItems::QuantityOrdered -  ( \$MB * Products::MasterBoxCount ) ; Products::SubBoxCount ) ]
IF [\$SB]
Go To Layout [PackingList ]
New Record/Request
Set Field [PackingList::ContainerType ; "SubBox" ]
Set Field [PackingList::_fk_InvoiceID ; \$InvoiceID ]
Set Field [PackingList::_fk_ProductID ; \$ProductID ]
Set Field [PackingList::Qty ; \$SB ]
Go To Layout [LineItems]
End If
#Create UnitBox entry in PackingList if one is required
Set Variable [\$Unit ; Value: LineItems::QuantityOrdered - ( \$MB * Products::MasterBoxCount + \$SB* Products::SubBoxCount ) ]
IF [\$Unit ]
Go To Layout [PackingList ]
New Record/Request
Set Field [PackingList::ContainerType ; "Unit" ]
Set Field [PackingList::_fk_InvoiceID ; \$InvoiceID ]
Set Field [PackingList::_fk_ProductID ; \$ProductID ]
Set Field [PackingList::Qty ; \$Unit ]
Go To Layout [LineItems]
End If
Go To Record/Request/Page [Next ; Exit after Last ]
End Loop