Issue with true value stored in number field
I am having a bit of trouble with a selling price field and its associated (extenedSell - field which is a formula field
in my image below I can iner my cost price then either key in the sell price (which stores that exact amount) and then goes back to calculate the markup field value (this works as expected - 100%)
ie I have key in £9.41 (and this exact value has been stored)
OR i can calculate the markup field (mkup) and this will then go onto calculate the sell price - then work 95% time until you hit a value that is greater then 2 decimal points
as you can the rue price is £9.405 but Ninox will display £9.41
on the quote grid as you can sell the extended sell the formula field (ext Sell) returns the actual stored value
whereas I want it to be £18.82
this only accurs when there is a rounding issue
I have tried many combinations of setting round seeting on both the sell price and on the formula but nothing doing - because all these do is set a display (what you see) as opposed that what is store. Thus what I need to do is calculate from the margin - but instead of storing a 3 decimal place - I nned to store the valus as if I had keyed it in as a rounded figure (ie 2 decimal places)
I did find this set about 5 years ago and tried to use this in my Mkup trigger
if 'Cost Price' > 0 then
'Sell Price' := 'Cost Price' * Mkup / 100 + 'Cost Price';
let n := 'Sell Price' % 1;
if n = 0 then
'Sell Price'
else
if n < 0.5 then
'Sell Price' - n + 0.5
else
if n > 0.5 then 'Sell Price' - n + 1 else 'Sell Price' end
end
end
end
but the value stored in Sell price seems to ignore it
Any suggestions
13 replies
-
Have you tried the round() command? So if you use the Mkup field in a formula you would do something like:
'Cost Price' * round(Mkup,2) or round('Cost Price' * Mkup, 2)
-
said:
if Qty > 0 then Qty * round ('Sell Price',2) endround() does not only affect what is displayed. If you use round() then that is the data that is stored. It all depends on where you use the round() command.
said:
in my image above the formula field even the script there has "round" onLooking at the code you originally posted, I don't see any round() commands.
-
just noticed that round() has a bug and that it is confirmed by Ninox in their seminar. If you code
round(9.405,2)
it rounds to 9.4 not 9.41.
-
sorry Fred I though I had posted the original script in trgger after update Mkup. the above was what i finally attempted today having found some other script bits on forum.
if 'Cost Price' > 0 then
'Sell Price' := 'Cost Price' * Mkup / 100 + 'Cost Price';
'Sell Price' := round('Sell Price', 2)
endBut it still store the £9.405 instead of £9.41
- i know i am missing something silly but .....
cost price is £6.07 thus 50 % is £3.13 which added comes to £9.405
followng accepted commercial practice this should round up to £9.41 which is the value i want to store not £9.405
-
The only hope is
round(9.405 + 0.0000000000001,2)
You might need a global function
function aRound(value:number,dp:number) do round(value+0.0000000000001,dp) end
which gets turned into
function aRound(value : number,dp : number) do round(value + 1e-13, dp) end
when saved.
Regards John
-
From my perspective I fixed my initial error.
I probably wanted too much from the system
insofar if i keyed in a cost price and the markup% I wanted the sell value to compute
if i keyed in the cost price and the selling price i wanted the margin% to compute
or if i keyed in the markup% and sell price l wanted the cost price to compute
thus each field has an appropriate trigger in it. except one of them I had the round code out of place - thsu on occasions with would not do the job properly!
cheers @john I will add in the global function as well.
Content aside
- Status Answered
- 6 days agoLast active
- 13Replies
- 37Views
-
3
Following