4 Replies Latest reply on Apr 13, 2015 10:36 AM by WalterWhitmanMoore

    Calculation field is not updating a current date versus a fixed date component

    DavidMenlow

      Summary

      Calculation field is not updating a current date versus a fixed date component

      Product

      FileMaker Pro

      Version

      11.0v4

      Operating system version

      10.6.8

      Description of the issue

      On our financial reports on stocks, we need to age various events that will be visible to the clients who read them.  When pull up a record to print, the aging of the situation is not recalculating.  It is only done when I re-enter the date in the fixed date field.  In the storage options for the calculated field, the box is not checked and indexing is set at none.

      The calculation is this:
      If( Date•PreFull = Date Dummy; " • • No IPOfn Full report was issued for " & ${Symbol}; " The IPOfn Fundamental report for " & ${Symbol} & " was issued " & (Get(CurrentDate) - Date•PreFull) & " days ago, " & Date•IPO Trade - Date•PreFull & " days before " & ${Symbol} & " first traded")

      As you can see, we manually put in the Date•PreFull date field information.  At that time, the calculations are done and the various text segments with number results are displayed correctly.  My problem is that if I use a date, the calculations are computed, but if I come back to that record 2 weeks later, the field is not automatically recalculating with current date data.  The only way to get the calculation field to update is to manually re-enter the date.  There has to be a better way.

      Configuration information

      These are the other calculations on the layout.


      If (Date•IPO Trade = Date Dummy; Text Dummy; ${Symbol} & " came public " & (Get(CurrentDate) - Date•IPO Trade) & " days ago")

      If( Date•PreFull = Date Dummy; " • • No IPOfn Full report was issued for " & ${Symbol}; " The IPOfn Fundamental report for " & ${Symbol} & " was issued " & (Get(CurrentDate) - Date•PreFull) & " days ago, " & Date•IPO Trade - Date•PreFull & " days before " & ${Symbol} & " first traded")

      If( Date•PreOverview = Date Dummy; " • • No IPOfn Overview report was issued for " & ${Symbol}; " The IPOfn Overview report for " & ${Symbol} & " was issued " & (Get(CurrentDate) - Date•PreOverview) & " days ago, " & Date•IPO Trade - Date•PreOverview & " days before " & ${Symbol} & " first traded")

      If( Date•PreSnap = Date Dummy; " • • No IPOfn Snapshot report was issued for " & ${Symbol}; " The IPOfn Snaphot report for " & ${Symbol} & " was issued " & (Get(CurrentDate) - Date•PreSnap) & " days ago, " & Date•IPO Trade - Date•PreSnap & " days before " & ${Symbol} & " first traded")

      Case( Date•Trade Plus 40 = Date Dummy; " • • This stock has not been priced yet";  (Date•Trade Plus 40 - Get(CurrentDate)) > 0; " The quiet period for " & ${Symbol} & " WILL expire in " & Date•Trade Plus 40 - Get(CurrentDate) & " days, on " & Date•Trade Plus 40; (Date•Trade Plus 40 - Get(CurrentDate)) = 0; " The quiet period for " & ${Symbol} & " expires TODAY"; (Date•Trade Plus 40 - Get(CurrentDate)) < 0; " The quiet period for " & ${Symbol} & " EXPIRED " & Get(CurrentDate) - Date•Trade Plus 40 & " days ago, on " & Date•Trade Plus 40; Text Dummy)

      Workaround

      If you look at the screenshot, the client sees the left side of the page.  The right side if for our internal use.  The 5 date fields at the top on the right are the reference fields for the calculations.  The calculated results are from when the stock came public on 11/17 and do not reflect the current days results on 11/23.  Of the 5, the last one does not use the current date function.  It is a fixed date based on 40 day in the future of The 1st Trade Date.

      Screen_shot_2011-11-23_at_11.57.23_AM.png