Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Using Tap Forms › Updating formula – future calculation only
January 12, 2018 at 8:42 AM #26954
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
I’m sorry I missed replying to your post yesterday.
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.
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.
BrendanJanuary 15, 2018 at 2:23 AM #26984
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
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
“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.
BrendanJune 13, 2018 at 12:00 AM #29059
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
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.
You must be logged in to reply to this topic.