# Updating formula – future calculation only

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Using Tap Forms Updating formula – future calculation only

• Author
Posts
• January 12, 2018 at 8:42 AM #26954

David Gordon
Participant

I have a formula used to calculate expenses due for travel. It uses the field “miles travelled” minus 10 as I can’t claim the first 10 miles. So far so good but I need to change the 10 to 15 for future calculations.

If I edit the formula, my whole database of past records change. Can I change the formula to save the past calculations? I guess I want to lock the past records once they’re complete?

Thanks!

January 13, 2018 at 2:38 AM #26972

Brendan
Keymaster

Hi David,

What you can do is use the IF function to return 10 if the Date value is less than a specific date and 15 if it’s not.

For example:

`IF(Travel Date < DATEVALUE("2018-01-01"; "yyyy-MM-dd") ; Miles - 10; Miles - 15)`

So that will check if you have a field called “Travel Date” that is before January 1, 2018. If so, the Miles field will be subtracted by 10. If the Travel Date is equal to or after January 1, 2010, 15 will be subtracted from your Miles field.

Hope that’s what you’re looking for.

Thanks!

Brendan

January 15, 2018 at 2:23 AM #26984

David Gordon
Participant

Thanks Brendan, that’s what I needed.

I should try and remember a database is just like a spreadsheet when it comes to calculations!

Thanks again!

June 12, 2018 at 7:56 AM #29046

David Gordon
Participant

Guess what, I need to update this again. So now I need two IFs? Something like

`IF(Travel Date < DATEVALUE("2018-01-01"; "yyyy-MM-dd") ; Miles - 10; IF (Travel Date < DATEVALUE("2018-06-01"; "yyyy-MM-dd") ; Miles - 15 ; Miles - 20)`

OTOH I now see a checkbox “Calculate one time only”. Would that help me?

Thanks!

June 12, 2018 at 6:19 PM #29055

Brendan
Keymaster

Hi David,

“Calculate one time only” is only useful if you’re using the UUID() function to generate a unique random value in a field.

You’re missing a parenthesis on the right of your formula to close out the first IF statement.

Other than that it looks good to me.

Thanks!

Brendan

June 13, 2018 at 12:00 AM #29059

David Gordon
Participant

Yes, thanks, that works now. I seem to have to re-edit formula for other calculations which use my ‘miles traveled’ result. But I click ‘edit formula’ for those and just save it works perfectly again.

Brilliant!

June 13, 2018 at 1:24 AM #29062

Brendan
Keymaster

You can also just click the Refresh button at the bottom of the records list view and that’ll recalculate all the formulas too without you having to re-edit the formulas.