[R3T1CAL Contributions: 0Level 1☆]

I’ve created a landed cost search 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.

[Mondray Contributions: 0Level 1☆]

let me break down the formula for you – DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’),’Shipping & Freight Charges Tools’,{amount})/{quantity})

NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’) -> this part of the formula says look for the first occurrence of ‘:’ in the MEMO field.. Once it’s found return the strings before the ‘:’ ; If MEMO is BLANK just use the string ‘Cost’

Whatever value you get from the about it will then check that value is it’s equal to ‘Shipping & Freight Charges Tools’.. if yes then it displays the value of {amount}/{quantity}

if it doesn’t match ‘Shipping & Freight Charges Tools’ then it displays BLANK..

My question now is.. Does you MEMO field contains the phrase ‘Shipping & Freight Charges Tools’ ? (Exact case, upper and lower case.. )

It will help if you can create a Formula TEXt and add this formula NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’) .. You will know what values it’s returning.. My Guess is it’s not returning the exact string ‘Shipping & Freight Charges Tools’..

My 2 cents…

Thanks,

mon

[R3T1CAL Contributions: 0Level 1☆]

NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’) -> this part of the formula says look for the first occurrence of ‘:’ in the MEMO field.. Once it’s found return the strings before the ‘:’ ; If MEMO is BLANK just use the string ‘Cost’

This makes sense, because not all items receipts will have a landed cost. If an item does not have a landed cost it returns the string ‘Cost’ which is always blank. If landed cost is present I get the total landed cost of the item.

My question now is.. Does you MEMO field contains the phrase ‘Shipping & Freight Charges Tools’ ? (Exact case, upper and lower case.. )

Yes the memo field does contain Shipping & Freight Charges Tools. When I tried making a text formula result for NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’) I would see two lines for every item. One would say “Shipping & Freight Charges Tools” the other line would show ‘Cost’.

---

Mondray replied on 03/30/2016, 12:23 PM: did this work? you can try adding another column containing NVL(SUBSTR({memo}, 0, INSTR({memo},’:’)-1), ‘Cost’) so that you can validate the value. Let me know if you still need help on this.

[Author]

Posts