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) endwhich gets turned into function aRound(value : number,dp : number) do round(value + 1e-13, dp) endwhen 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
- 9 mths agoLast active
- 13Replies
- 42Views
- 
    3
    Following
    
