n00bie coder question

Greetings all!

I have a really lame n00bie coder question… I need/want to add a new field to the Item Ledger Entry table which calculates the Cost Amount (Actual)/Quantity – I’m going to call it “Cost per Unit”, the same as is extant in the Value Entry table (however that’s real data in the table).

Can someone help me go through creating a calculated column in a NAV table?

Hi M.Berk,

I’m not sure if a “field” is actually what you are looking for, I think your looking for a calculation.

In Form 38 you could enter a new column with the following source expression:

“Cost Amount (Actual)”/Quantity

That would give you a quick result, but there is a downside to this. You can’t filter on this column! To prevent a devision by zero, you could also put this calculation in a function of the Item Ledger Entry table that returns a decimal.

IF Quantity <> 0 THEN
EXIT(“Cost Amount (Actual)”/Quantity)
ELSE
EXIT(0);

Yep, that gets me part of the way there… I wish there was a way to do it on the table itself though, so it could be filtered or sorted – the purpose of the filed being there is to facilitate finding that occasional bad purchase posting for an item, so if it’s usual CpU was say $10, the user could filter the field for <5|>15, for instance.

I’ll go with the form option for now, it’s far better than nothing, and for that I thank you soooo much! If you think up a way to do it on the table (short of editing the posting routine to calculate and populate a new actual field), let me know :wink:

Thanks!!!

Hi M.,

Well as you said, I think the only other way is to change the posting routine. The difficulty here is that you’re calculation is partly based on a flowfield.

i wish this is your test case only - not a real life situation ; so i can say that you can make a new field in the table. in the properties, set this new field as flowfield, and write your formula in it.

if you are on a live system, you will need a joint discussion session with your senior consultant and client representative.

I would use “Invoiced Quantity” as this is is the base for value; Quantity is pimarely a quantity only.

Joe – My whole question was HOW to get a flowfield to have a formula… I appreciate your input, but it doesn’t answer the question itself.

Anfinnur **-- “**Quantity is pimarely a quantity only.” – If Quantity wasn’t primarily a quantity, then what else would it be? Is this a riddle?

It is possible to have a Quantity and an “Invoiced Quantity” which are not the same. If you in such a case want to get Unit Cost pr. unit, you have to calculate “Cost Amount (Actual)” / “Invoiced Quantity”.

If you use “Expected Cost Posting” (sat in “Inventory Setup”) you probably need “Cost Amount (Expected)” / Quantity as well.

Object Designer - Table. Select on, e.g. table 32. Design. go to most bottom, add new line.
e.g. field no. = 50001, field name = new calc , data type = decimal

in the Properties: field class = flowfield, calcformula = here where you write your formula.

tell me if you need a screen capture.

sigh

Maybe this is a language barrier issue.

I understand WHERE to put the formula; I don’t understand HOW TO WRITE THE APPROPRIATE FORMULA.

I understand the difference between the “Quantity” and the “Invoiced Quantity” fields as well, and I appreciate you input on using one instead of the other and why one should do so, but my fundamental question remains unanswered…

The sort of answer I am looking for is pretty straight-forward as I’m using basic NAV fields in basic NAV tables which are common to every NAV installation. I’d think that if it was truly as easy as you’re making it sound, someone would be able to say something along the lines of; "oh sure, here’s the formula you can use: " followed by … THE FORMULA ITSELF.