Date & Time Fields in Calculations

Viewing 2 reply threads
  • Author
  • January 30, 2021 at 7:01 AM #43327

    Rachael Bermingham

    Hello lovely experts. I’ve trawled through the amazing templates but can’t quite find something I’m looking for, which probably means it doesn’t exist. I have a home inventory database and I’d like to record the expiry date for guarantees by adding a duration (eg 1, 2 or 5 years etc) to a guarantee registered field (date) and ideally outputting “expired” or the expiry date. Is this a requirement too far?

    January 30, 2021 at 9:36 AM #43328

    Daniel Leu

    You can use the calculation field for that. Have a look at the dateadd() and now() functions. Then you can check if this expiry date is before the current date using the if() function and return a valid or expired string.

    January 30, 2021 at 9:36 PM #43335

    Sam Moffatt

    Keep in mind that calculation field values are only regenerated when a field they reference within a record changes. You need to explicitly refresh the record or use the “refresh record list” option to refresh all records in a form. This makes it non-trivial to do a field that changes to “expired” once a certain date hits.

    A different approach is to do the date field as suggested by Daniel above and then use a saved search to look for “expired” records after the current date. Use the filter icon next to start a new saved search and after selecting your date field use the “is in the period” selector and then “today or after”.

Viewing 2 reply threads

You must be logged in to reply to this topic.