Send email based on a specific field change

[AccessNerd]

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.

[AccessNerd]

In the SQL world, I can Select from a table and then use something like Status IN ( Select Status From tStatus where StatusType = 'PM'). Essentially a subquery contained within the IN. Is that possible here?

[kpcollier]

@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.

[AccessNerd]

@kpcollier Wow, thanks for that detailed explanation. I'll give that a try.

[AccessNerd]

@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!!!

 

[kpcollier]

@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. 

 

 

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.

[IamNatoyThatLovesYou]

Hi @AccessNerd,

It is doable in the trigger.  Here is a screenshot that you can check.

You can check this HowTo for reference regarding triggers.

[AccessNerd]

@kpcollier @IamNatoyThatLovesYou

Thanks, I will take what you have both provided and try to get it to work. Really excited to see that IN statement also. I think that's going to help.