Calculate actual elapsed days in filemaker?

Question

How do you make Filemaker always separately display the actual elapsed days from a given date stored in a field in a layout?

Example

I enter today's date into the database. When I look at my view today there should be a separate text field saying 0 days. In one week this text field should say 7 days.

Answer 1

Field StoredDate

Separate field ElapsedDays

(text? why do you want a number to be text? Conversion is left as an exercise for the student, if the student actually wants that. It's easy to find...)

ElapsedDays, type is calculation, Button [Options] Get(CurrentDate) - StoredDate

Button [Storage Options] Check Do not store calculation results - recalculate as needed

If you don't change the storage options, it gets run when created, and does not change. This is the key setting to get it to change when the current date changes.

If, for some bizzare reason, you really want to store the unchanging text " days" in the field as well as the number, that can also be done in the calculation, it just makes it more tedious.

GetAsText ( Get(CurrentDate) - StoredDate ) & " days"

So, 480 days later, not even an upvote? I know it's 480 days since I answered, because it works and I still have the test DB I tossed together to test it...

Answer 2

Of course, there are many ways to accomplish this. It would depend on your solution and how you want the calculation to appear on your layout.

Using FileMaker 14 this can be done without using an Unstored Calculation in your data table. You can take advantage of Filemaker's calculation engine directly on your layout using the Hide When calculation on a simple text object. A button could be used for this too.

Assuming you have a stored date in a field, let's call it Table::myDate in this example where Table is your table name and myDate is your date field with the date stored.

Layout

• On your desired layout go into Layout Mode. ( ⌘+L OR control+L on Windows)
• Insert a new Text object and type in a Merge Variable (Merge variables can be either Local or Global in scope. You can use a local variable in this case that will be discarded when the layout is left or refreshed.) You can use whatever you wish for a variable name, we'll use $daysElapsed in this example. So your text object will have <<$daysElapsed>> entered as text.
• Go to the Data Tab in the Inspector Pane
• Go to the Hide When calculation
• Using Let() we can take advantage of Filemaker's calculation engine without having to add a field to the Table. Enter the a let statement like so:
  Let ( $daysElapsed = Get ( CurrentDate ) - Table::myDate ; False )
  This sets the $daysElapsed variable automatically on entry of the Layout. Thus displaying the days elapsed. Returning False as a result in the Let Statement allows the object to always be shown. See screenshots below.

To add text to this, simply add it in the calculation or it can be added directly in the text object before the Merge variable declaration. Like so:
Days elapsed: <<$daysElapsed>>