0

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

null
    • Fred
    • 7 days ago
    • Reported - view

    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)
    
      • Mel_Charles
      • 7 days ago
      • Reported - view

       Yes Fred many times and this is set in several of the other fields too to check

      as said above I have used round in various formats but this only effects the displayed value not the actual stored value). in my image above the formula field even the script there has "round" on  

      if Qty > 0 then Qty * round ('Sell Price',2) end

      after the trigger on Mkup has done is job if i over key the result on sell price from 9.405 to 9.41 then obviously the problem goes away but - I don't what to do this on every price just in case as can be doing up to 100 quotes a day with up to 36 lines on quotes. and then i would have to set the dipslay to show 3 decimals etc etc.

      • Mel_Charles
      • 7 days ago
      • Reported - view

       ps Fred - i know its late but !!! happy new year bye the way -

      • Mel_Charles
      • 7 days ago
      • Reported - view

       and with this if Qty > 0 then Qty * round ('Sell Price',2) end it makes it worse as i am 2p out with round in the script as sellprice stores 9.405 not 9.41 even though it displays 9.41 thus 20 x sleep price is 18.21 where i neeed it to match the display sell price of 2 x 9.41 

      • Fred
      • 7 days ago
      • Reported - view

      Happy New Years to you too.

    • Fred
    • 7 days ago
    • Reported - view
     said:
    if Qty > 0 then Qty * round ('Sell Price',2) end

     round() 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" on  

     Looking at the code you originally posted, I don't see any round() commands.

    • Fred
    • 7 days ago
    • Reported - view

    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.

      • John_Halls
      • 6 days ago
      • Reported - view

       Yes, you are right

      round(9.404,2) = 9.40 Correct

      round(9.405,2) = 9.40 Incorrect

      round(9.406,2) = 9.41 Correct

       because round(940.5,0) = 941

      I would multiply by 100, round to 0dp, and then divide by 100

      round(9.405*100,0)/100 = 9.41

      regards John

      • John_Halls
      • 6 days ago
      • Reported - view

       Except it doesn't! Aagh,it still equals 9.4

    • Mel_Charles
    • 7 days ago
    • Reported - view

    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)
    end

    But 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

      • Mel_Charles
      • 7 days ago
      • Reported - view

       ERR HANG ON - I THINK I MIGHT OF CRACKED IT

      Will get back after much testing tonight !

    • John_Halls
    • 6 days ago
    • Reported - view

     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

    • Mel_Charles
    • 6 days ago
    • Reported - view

    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.