2 Replies Latest reply on Mar 5, 2013 12:17 PM by vlight

    Creating a separate field to show the most current Date in a table of Notes

    vlight

      Title

      Creating a separate field to show the most current Date in a table of Notes

      Post

           In FM Pro 11, I'm trying to find the right "calculation" to use on a field "MostCurrentAccurateDate."

           I have a table of Notes.  It includes 3 date fields:  

           1) "DateNoteCreated,"  an auto-entered field;  

           2) "DateActuallyOccurred" field -- where the user can enter a more appropriate date if needed  (e.g., notes about a conversation that was actually held on May 3rd, as opposed to the date in #1 above,  which would be the date the user actually gets around to creating that note in the database, say May 15th);  and

           3) "DateMostAccurate" -- a calculated field (result=Date) that gives me the value of #2 unless it's empty, in which case it defaults to give me #1.  

           I want to create one more field -- that could be shown on other layouts (in related tables) -- that will show me the date of  "the most recent" note, based on #3 above (the "dateMostAccurate").  How do I accomplish this??

        • 1. Re: Creating a separate field to show the most current Date in a table of Notes
          philmodjunk

               Do you really need two date fields? Since DateNoteCreated is an auto-entered date field, you could allow the user to directly edit this field whenever the date created is not the correct date for your note.

               A summary field set to report the Maximum of DateMostAccurrate (If you use two date fields) could be used to show the date of the most recent date.

               You may also find it useful to use a portal to Notes set to sort records in descending order by date so that the most recently dated note appears as the first note in the portal.

          • 2. Re: Creating a separate field to show the most current Date in a table of Notes
            vlight

                  

                 Thank you, Phil -- the summary field works perfectly!  

                 And thanks too for questioning my needing the two date fields.  I see that i CAN allow the user to edit that field, and that would simplify things.  So perhaps i'll do just that. Meanwhile i do have a "timestamp-creation" field.  I may just keep that one "in the background" so to speak, in case i find a future need for it.

                 And yes, on another layout I am using a portal just as you described.

            - - - Update Question - - -

                 On my original post, you'll notice the last ⁋.  That part is still not working correctly, and i request any help you can give me.  

                 I now have just the one field "dateCreatedOccurred" where it auto-enters the creation date when the Conversation note is created.  And in the sorted portal, it does show the most current date at the top, let's say 3/2/13 (followed of course by the earlier dates).  However, i still need to "capture" the fact (on another screen, in a related table) that the most current Conversation with THIS person was on 3/2/13.  But instead it is showing the most recent Conversation with ANYONE.

                 The 2 tables are People, and Notes (and Notes has 3 types: C/conversations, F/file note, N/next step)

                 In Notes, i'm using 3 fields for this:

                 1.  dateCreateOccurd (auto-enter creation date, but user-modifiable)

                 2.  c_MaxDate_IfConvers (calc =  If (type="c" ; Max(dateCreateOccurd) ;  " )

                 3.  s_MaxConversDate  (summary  = Max of  c_MaxDate_IfConvers  )

                 Any suggestions as to how i can get it to show me the correct date for the most recent conversation note with a particular person, NOT the most recent conversation note across ALL people.   Thanks.