0

Updating calculation - effect on existing records

I have a calculation in my records. Let's say its " 'miles' * 0.50 " so if I travel 50 miles the answer is 25 (£25.00). But I need to increase the rate to 0.60. Now the answer will be £30.00.

If I simply change my existing formula to " 'miles' * 0.60 " will this recalculate my existing records (which should not change)?

What's the best way to deal with 'new' prices and or changes to calculations?

Many thanks.

8 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    How often do the changes happen?

    Do you need to track the changes?

    How often does the changes need to be made for each record in a table?

    1 way of doing it would be to create a new number field called 'multiplier'. Then you can change your other formula field to: 

    miles * multiplier

    Then whatever you type in the multiplier field will be what is calculated. So you can have a different multiplier for each record.

    • Paul_Chappell
    • 2 yrs ago
    • Reported - view

    I would have a 'Settings' table with a 'Pence Per Mile' field, and another 'Pence Per Mile' field in your 'Main' table. When a new record is created in the 'Main' table read the current Settings.'Pence Per Mile' field and set the Main.'Pence Per Mile' field.  That way you can adjust the Settings field if it ever changes moving forward, but still retain the old value in the Main table which doesn't change for old records.  In an accounts system you can use the same method for the VAT rate.  Now it is 20% but if it ever changes clearly you shouldn't alter old records. 

    • Davie
    • 2 yrs ago
    • Reported - view
    Fred said:
    How often do the changes happen?
    Do you need to track the changes?
    How often does the changes need to be made for each record in a table?

    Very rarely. I don't need to track. Once the calculation is made the answer will not change.

    Fred said:
    1 way of doing it would be to create a new number field called 'multiplier'. Then you can change your other formula field to:

     Yes, if the multiplier was a selection list that might work but is another step rather than 'automatic'.

    Paul Chappell said:
    hen a new record is created in the 'Main' table read the current Settings.'Pence Per Mile' field and set the Main.'Pence Per Mile' field. 

     OK, thanks, this sounds like what I need - especially as you use VAT as an example.

    So the 'settings' table says 'pence per mile' = 0.50 so I see that in my 'main' table and that's used to calculate 50 miles * 0.50 = £25.00. But when I update 'settings' to 0.60 the answer in future will be £30.00. So the calculation is only ever done once? When I enter the mileage for the calculation?

    Penny sort of starting to drop...

      • Paul_Chappell
      • 2 yrs ago
      • Reported - view

      Yes. Values like VAT are only correct at a particular point in time when they are used. Once used they don’t change, even if the future VAT value changes. If that makes sense. Tax is another example. Only valid at a single point in time and never updated retrospectively.

    • Fred
    • 2 yrs ago
    • Reported - view
    Davie said:
     Yes, if the multiplier was a selection list that might work but is another step rather than 'automatic'.

     If there is a standard multiplier then you can add it to the default value. Then you can change it to what you need on those special cases.

    Or you can make it a choice field. Or if there are many values that will be entered then you can make it a dynamic choice.

    • Tom_Brooks
    • 2 yrs ago
    • Reported - view

    I have a Company settings table that I put values like this.  Then I check for null before I update the value for the formula.

    if this.'Fuel Surcharge' = null and this.Quotes.'Remove Surcharge' = false then
        let fee := last(select Company_Fees where 'Fee Active Flag' = true);
        this.('Fuel Surcharge' := 'Sub Total' * fee.'Fee Percent')
    end

    • Davie
    • 2 yrs ago
    • Reported - view

    Would this be simpler (for me)?

    Currently I have a formula  

    'Billing Miles' * 0.5

    What if I had something which says:

    If the appointment date is before 30-06-2022, 'Billing Miles' * 0.5 else 'Billing Miles' * 0.6 

    if date(start('Appointment'))<(2022,6,30)'Billing Miles' * 0.5
    else 'Billing Miles' * 0.6
      • Paul_Chappell
      • 2 yrs ago
      • Reported - view

      Davie Not really.  You would have to ensure an Appointment date cannot be changed. Which is not a problem, but what if someone enters the wrong date to begin with? You would need to handle that. Also, the amount may change again from 0.60 and then it starts getting complicated to keep thinking about dates and making code changes. 

Login to reply

Content aside

  • 2 yrs agoLast active
  • 8Replies
  • 121Views
  • 4 Following