4 Replies Latest reply on Mar 6, 2013 8:35 AM by vlight

    Creating most current date field... from earlier post

    vlight

      Title

      Creating most current date field... from earlier post

      Post

           I have an additional question for the person who sent me an answer to an earlier post of mine:

           The title on the former post was:  Creating a separate field to show the most current Date in a table of Notes

      - - Here's my Update Question - -

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

           I now have just the one field "dateCreateOccurd" where it auto-enters the creation date when the Conversation note is created, but i've now made it user-modifiable so that an "actual occurrence date" can be typed in if needed.  

           And in the sorted portal, it does show the most current date at the top, let's say 3/2 (followed below 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.  But instead it is showing the most recent Conversation with ANYONE (in the Notes table, which in this case, is showing 3/4, due to a conversation with a different person on 3/4).

           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.

        • 1. Re: Creating most current date field... from earlier post
          philmodjunk

               You need a relationship that matches to Notes by the ID field that uniquely identifies a record in people. Then whichever method you use for getting the maximum date will return the maximum date for the specified person. You haven't identified that "related table on aother screen" so I am unable to specify in detail how you would set that up.

               From your people table, this relationship:

               People::__pkPeopleID = Notes::_fkPeopleID

               a calculation field defined in People:

               Max ( Notes::cateCreateOccurd )

               will return the date of the most recent notes record for the current People record.

          • 2. Re: Creating most current date field... from earlier post
            vlight

                 Yes, i do have the relationship as you described.  

                 However,  I did NOT have the "Max"  calculation field defined in People (i had one in Notes).  So i went ahead and made it, in People -- i first made it just as you have it in your answer, but that was giving me a 3/4 date (which is wrong for the person i'm testing —  a date of 3/4 was the last FILE NOTE, but 3/2 was the last CONVERSATION NOTE, so it should be 3/2, since i only want to see the the most recent Conversation note.)

                 That led me to doing a different calculation, trying to get the Max date for ONLY Conversations ...  (see screenshot attached) ... but it's still giving me a 3/4 date instead of 3/2.  Any idea what i'm doing wrong??

            • 3. Re: Creating most current date field... from earlier post
              philmodjunk

                   Because this evaluates from People, the reference to Notes::Type refers to the first related notes record and thus you either get a max value or nothing depending on whether the first related notes record is of type "C" or not. And the Max value will be a max of all notes for that person, not just the type "C" notes.

                   YOu'll need a cacluation field in Notes that returns a date only if the record is of type "C":

                   IF ( Type = "C" ; zdateCreateOccurd )

                   Then set up your calculation in People to take the max of this added calculation field.

                   PS. If you are using FileMaker 12, Execute SQL can also be used to selectively return a max value without needing a calculation field in the Notes table.

              • 4. Re: Creating most current date field... from earlier post
                vlight

                     Thank you -- this now works perfectly!