3 Replies Latest reply on Jan 23, 2016 8:24 AM by DHunt

    Insert next appointment date/time in email template?

    MRitte Apprentice

      Is this possible?


      I want to insert the latest Appointment date and time into the body of an email template.


      Running latest version of GMPE.



        • 1. Re: Insert next appointment date/time in email template?
          John Neighbors SSMMVPGroup

          Possible?  Yes!

          Easy?  No!


          I've done this two distinct ways in days past for clients.  The easier of the two is more prone to user error and thus less reliable.  The more challenging of the two simply requires a lot more setup.


          Easier:  (more room for user error)


          With this approach, a requirement is that the GM user goes to the Pending tab on the record in question (thus you cannot do these in mass either) and "selects" the NEXT scheduled item (for which you wish to make reference).  In a few cases through the years with different versions of GM, we've found the user had to effectively open or double-click the item and then cancel out of the edit to make sure the "focus" was correct (vs. simply highlighting the item on the Pending tab).


          Then, within the email template, just use traditional merge fields and refer to the CAL table fields, as needed.  The currently focused CAL record should be the one in question.


          Simple to design, but lots of room for error.


          More challenging:  (more reliable)


          With this approach, the idea is that you setup distinct custom fields in CONTACT2 for each CAL table data element that you want to "know" about and reference in your email template.


          Then, you write a SQL TRIGGER that effectively monitors changes to the CAL table, and UPDATES these CONTACT2 custom fields accordingly.


          Then, when the email template needs to refer to these fields, they are simply CONTACT2 fields which can be easily referred to via email merge fields.  The expectation is that with a properly designed SQL trigger, whenever the email template is used, the respective CONTACT2 fields are accurate and up-to-date.


          Other checks-n-balances can also be put into place (e.g. SQL stored procedure that runs every 5 minutes, etc.) to make sure the custom fields in the CONTACT2 table are truly kept accurate and up-to-date.


          Hope this feedback helps.

          • 2. Re: Insert next appointment date/time in email template?
            MRitte Apprentice

            Thank you.  That was almost too easy.  I tested using your simple method and it seems to work quite well.  I only needed to have the activity active under the pending tab by clicking on it and then opening the template.  It inserted the correct date and time. 

            But you right that the user will have to make sure that they click on the correct activity.


            I really appreciate your help.

            • 3. Re: Insert next appointment date/time in email template?
              DHunt Specialist

              I'm working remotely so I'm not seeing everything, but I have done this for years using an HTML Template.  In that Template I have Fields like:






              You get the gist I'm sure.  So I can have any Calendar Field that I want in the Template even an Expression Field against a calendar field.


              Create a new GoldMine E-mail Template and in the Body have a pointer to that HTML Template.




              1.  Create or Edit a Calendar Activity for the Client.


              2.  Immediately a Creating or Editing that Calendar Activity click on the E-Mail label in GoldMine for that Client, and select the GoldMine E-Mail Template.


              3.  Verify that the information in the E-Mail is correct, and Send.


              If you pulled up the wrong information simply Edit the Calendar Activity ( I usually put 3 spaces at the end of the Reference field ), and close the Activity.  Now repeat steps 2 & 3 above.


              I have been including Calendar Activities in my E-Mails this way for years now.