Jump to content
  • 0

Send email based on a specific field change


AccessNerd
 Share

Question

Hi all,

Is there a way to send an email based on a specific field change only? I know about the on update notifications, but I don't want an email sent for every update.  A user can change as many fields as they want, but I only want an email sent when the Status field (for example) is modified from what it was previously. I'm guessing this will be a trigger of some sort but I thought I'd ask.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

@AccessNerd, you'll want to use an Update Trigger on the table with the Status field. 

Use the Email Block, click the options, and turn the SELECT option on. You'll want to make sure your trigger is set to run on update. Go to the Select Block's options and enable JOIN and WHERE. Set the Select Block to #inserted and the INNER JOIN set as the table with the Status field. Use '#inserted.IDField equal tablewithstatus.IDField' for the join. Then, in the WHERE section, set '#inserted.Status not equal to tablewithstatus.Status'

This should only send an email when the updated record's status does not equal what was in the table before the update.

Link to comment
Share on other sites

  • 0

@kpcollier Thanks for the help. Your explanation worked very well. 

 

Hi again all and anyone,

What I have currently is the Client PM being emailed every time the status is changed. That's great!!! But I'd like to customize (and complicate) it a bit further.

The individual statuses are specific to either the client or the vendor. So if the client PM makes a change to the status, I'd like to email the vendor. If the vendor makes a change to the status, then I'd like to email the Client PM. I could probably do this by user id, but some users hold both a Client PM and Vendor role, so I'd rather do it by status field lists/views if possible. I have two lists/views, one list shows Client statuses, and another shows Vendor statuses. The emails are contained in the tUsers table as seen in my Trigger image.

So what'd I'd like to do is to email one person if the status is in one list/view and email another person if the status is in a different list/view.

And last but added to the above, if the Status = "Some Specific Status" then I'd like to also email a specific user, in addition to the PM or Vendor.

It's a lot to ask but any help is appreciated!!!

 

Trigger1.JPG

Link to comment
Share on other sites

  • 0

@AccessNerd, Have you just created 'Views' for the different status lists? Views cannot be used in triggers.

One thing you might be able to try is to create two different Email blocks in your trigger. Just right click the one you have and duplicate it. Then, on the Logic tab to the left, pull out the block that says 'AND'. You will want two of these for each Email Block. 

In the WHERE section, start with the AND block. Put the '#inserted.ID not equal to table.id' condition inside of it. Then switch the 2nd AND block into an OR block by clicking on the dropdown by AND, and put this inside as well but below the first condition we set. See image for a more clear understanding. Then inside that OR block, put "Status equals 'whatever the status is'" for all of the statuses in the list. 

Do the same thing with the 2nd Email Block, but use the other Statuses in the OR block. 

triggerhelp.JPG.84af3478d988c768259d5ef204dc4987.JPG

 

This should effectively only send the email that is specified by the statuses set in the WHERE section. 

If you want to make sure that a specific user is getting an email for a specific status, use this same workflow. Make sure that inserted.status does not equal table.status, and inserted.status is equal to the specific status you are looking for. Then in the TO section just put their specific email. Or if you want it to go to a group of people, maybe you can set a Group field in your User table and put that condition in the WHERE clause as well.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...