Updating formula – future calculation only

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

Viewing 6 reply threads
  • 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,

    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.

    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.

Viewing 6 reply threads

You must be logged in to reply to this topic.