4 Replies Latest reply on Feb 9, 2017 9:21 AM by siplus

    Max Note Date Based on Note Type

    danjamins

      Hi all,

       

      I have a little problem I'm trying to find a solution for. I'm playing around with the Last() and Max() functions on a related table called Notes which stores notes for each customer.

       

      The problem is, the Notes table contains a field for "Note Type" which can be system generated (emails sent, status changes, etc. ) which I don't want these functions to look at.

       

      Not sure how to go about this. I would like to basically exclude any note type that is not a user generated note so that I can see when the last time a user actually manually entered a note on a customer.

       

      I've been trying to google search this and see if it exists and found a few Max() and Last() functions but they're as helpful on this as the FileMaker functions reference is - which doesn't give an example of how I can come up with a solution for this.

       

      I'm hoping one of you clever experienced developers can help point me in the right direction so I can have this valuable data in my solution.

       

      Thank you very much for any input!

       

      Regards,

      Dan.

        • 1. Re: Max Note Date Based on Note Type
          BruceRobertson
          I would like to basically exclude any note type that is not a user generated note...

          Your google search seems premature.

          What are you looking for?

          What does the Note Type field contain in the case where it IS a user generated note?

          Are you looking only for entries where this field is empty?

          • 2. Re: Max Note Date Based on Note Type
            siplus

            Create a new calculated field, If (Note Type ="User Generated"; Note; "")

             

            OR

             

             

            Create a flag field, Flag = If (Note Type ="User Generated"; 1; 0)

            Create a Global calc gOne = 1 in Customers

             

            and make a relationship , based upon CusomerID = fk_CustomerID  AND gOne = Flag

             

            put a portal in Clients, sorted by NoteDate descending, based upon this rel.

            • 3. Re: Max Note Date Based on Note Type
              danjamins

              I don't want the note, just the last user entered data to perform a script that notifies the assigned user they need to follow up.

               

              For simplicity let's just say note types 1-5 are to be excluded from this calculation. They have specific names in our solution but for this example I'll generalize.

               

              I want note types:

              1

              2

              3

              4

              5

              To not show up as last note date. Every other note type should show the most recent date the related note was entered for a customer. So I can say in my script hey user you haven't updated this customer in X amount of days, you should follow up and see where they're at. I basically want to exclude any of my auto generated notes like changes to fields such as status updates or changing a name field to a new name, etc. From showing as the most recent note and only have manual user notes show up as the most recently dated note.

               

              You kind of gave me the right direction I think I can figure it out from here but if you have any other input I would greatly appreciate it.

               

              Get Outlook for Android<https://aka.ms/ghei36>

              • 4. Re: Max Note Date Based on Note Type
                siplus

                once you got the relationship set, just look at max(relationship::NoteDate)