[R3T1CAL Contributions: 0Level 1☆]

I’ve created a landed cost report that works almost perfectly for my purchasing department. The employee types in a Item Receipt number or the item number and see’s the:

Item Receipt Number

Vendor

Item

Item Name

Quantity Ordered

Purchase Price on IR

Last Purchase Price on Item Record

Item Weight (Each)

Total Item Item Weight (Weight * Quantity Ordered)

Tools Landed Cost (Total)

Additional Landed Cost column based of cost category – see second screenshot

This report was created with the help of the suiteAnswers:

37754

35633

My purchases department needs two more columns added:

Tools Landed Cost (Each): (Tools Landed Cost (Total) / Quantity Ordered)

The problem is I can’t get the formula that gives me the total tools landed cost to be divided by the quantity ordered. My purchaser doesn’t care that 50 items cost 10.00 to get here, he wants the column to take 10 and divided it by 50.

I’ve attached a screenshot of the results setup. I want to take the formula: DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’),’Shipping & Freight Charges Tools’,{amount}) and have it divided by the quantity ordered. I got this formula from SuiteAnswer: 35663

I’ve tried the following to no avail:

DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’),’Shipping & Freight Charges Tools’,{amount})/{quantity}

DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’),’Shipping & Freight Charges Tools’,{amount})/({quantity})

What call do I add to this to get the total landed cost to be divided by the quantity ordered?

The other column I need is:

Item Landed Cost

Which would do:

(Tools Landed Cost (Total) / Quantity Ordered) + last purchase price = item landed cost

I’m hoping this is a simple fix in the formula. While we can achieve this very quickly in excel, my boss does not want to add an extra step when he’s checking multiple IR/items in a row.

Thank You in advanced!
This is a cached copy. Click here to see the original post.

[Author]

Posts