Triggered action to send a meeting agenda recap

[JoeyBrannon]

I have the following tables

• MEETINGS
• USERS
• USERASSIGNMENTS

The tables are related as follows

• MEETINGS.teamID < USERASSIGNMENTS.teamID
• USERS.userID < USERASSIGNMENTS.userID 

where < represents a one-to-many relationship

I have a field MEETINGS.recapStatus that I would like to use to trigger an action to send an email to everyone assigned to the team.  The idea in my head is that when a record has an update and the MEETINGS.recapstatus field = "Ready to send" the trigger will run and send an email to all the users with a URL related to that meeting recap (a url on my website with an embedded Caspio Datapage using an external parameter in the url to show the correct record in the Datapage. Things are complicated by the fact that the email addresses I need are in the USERS table, two tables removed from the MEETINGS table where this triggered action originates.

I know this can be done, but it is my first task/triggered action and I could use some help figuring it out.

 

[Tubby]

You mentioned that the trigger should send the email to the users with the same URL related to the meeting that was updated. Do all of the tables have that meeting URL or atleast does that Meeting and User table contains that URL?

[JoeyBrannon]

the url is http://myappdomain.com/meetingrecap?mid=..... where the mid parameter is the external parameter tied to the MEETINGS.meetingID field.

In other words there is a web page on my website at myappdomain.com/meetingrecap with the datapage embedded in it. This datapage is a single record details report filtered for a specific MEETINGS.meetingID value. It gets that value using an external parameter from the URL. This way I can insert the url link in the body of the email by pulling the MEETINGS.meetingID field from the record.

[CoopperBackpack]

Hello @JoeyBrannon,

I hope I understood your table design correctly.

For example, there are these tables with some test data and the user enters the 'Ready to send' value in the 'recapStatus' field:

It is possible to join tables in the Trigger to get the data you need. The main point here is to have relationships between tables.

This is the Trigger example:

It runs on an update in the 'MEETINGS' table and sends an email.

The important thing to understand is that the virtual #inserted table represents the data that is inserted into the table or the data that is updated.

But as for the update it actually represents all the data from the record that is updated: 

- if in the updated record the value in some field was not changed (updated) it is stored in the #inserted table as-is;

- if in the updated record the value in some field was changed (updated) this new value is stored in the #inserted table.

[CoopperBackpack]

1) When the first 2 tables (#inserted and USERASSIGNMENTS) are joined in the Trigger on the 'teamID' fields (highlighted in red)  this results in the following data set:

2) The next join with the USERS table on 'userID' fields (highlighted in yellow) returns the following data set:

As you can see now we have access to the 'email' field.

3) In order to send an email when the value in the 'recapStatus' was changed, we need to check the value that was there before the update and after the update.

For that, we can join the #inserted table and the MEETINGS table on the unique field (highlighted in blue):

In the WHERE clause, there is a condition. We are interested in the updated records where the  'recapStatus' wasn`t equal to 'Ready to send' before the update and is equal to 'Ready to send' when it is updated.

Feel free to update the thread if you have questions. And please always test the Triggers on some test data first. 

[JoeyBrannon]

@CoopperBackpack This is incredibly helpful! I can't thank you enough for going the extra mile to explain how the inner joins continue to expand the dataset. I get it now.

I have decided to move from a triggered action to a task that runs periodically (daily or hourly) so that I can check the recapStatus field for a "Ready to send" value and then update it to "Sent" after the email action.

Another question I have is what is the difference in the various dropdowns in the task between fields in the #record group that shows up first and those same fields in the "Meetings" group below (see screenshot)?

[Tubby]

13 hours ago, JoeyBrannon said:

@CoopperBackpack This is incredibly helpful! I can't thank you enough for going the extra mile to explain how the inner joins continue to expand the dataset. I get it now.

I have decided to move from a triggered action to a task that runs periodically (daily or hourly) so that I can check the recapStatus field for a "Ready to send" value and then update it to "Sent" after the email action.

Another question I have is what is the difference in the various dropdowns in the task between fields in the #record group that shows up first and those same fields in the "Meetings" group below (see screenshot)?

#record appears if you are using loops such as FOR EACH. #record represents the current record that the loops is checking (so #record contains 1 record at a time). While Meetings on the other hand is your actual table and contains multiple records based on the condition set.

For example:

You have 5 records in the table containing an email address that you are using FOR EACH loop and you want to send 3 emails for each of the email address in the table (total of 15 emails).
Instead of selecting the field from the actual table which will include all the 5 emails in the TO address of the email, use #record so it only gets the current record in the loop.

Hope this explanation helps

[CoopperBackpack]

Hello @JoeyBrannon,

I would like to add that in many cases the loops are not needed in Tasks.

If you need to send emails to the users based on a condition, you just need the outer SELECT block to select these users. 

For example, this Task sends individual emails to all the users who have an appointment in 1 day.

Let`s say 3 users met this condition. So, 3 emails will be sent.

[Flowers4Algernon]

Hello! You may also check out this link for more triggered action samples: https://www.youtube.com/watch?v=w6T1cqg0gnk

[JoeyBrannon]

I started testing today under the following scenario....

scheduled task that will run every day (during testing I am just running it on demand)

The task essentially looks for a True value in the MEETINGS.sendRecap field

It uses two inner joins

1. The first joins the user ID's associated with the team having the meeting using USERASSIGNMENTS.teamID and MEETINGS.teamID
2. The second joins the email addresses associated with the userID using USERASSINGMENTS.userID and USERS.userID

I have tested this with teams that have 1 user, 2 users and 3 users and every time it says it sends two emails but only one is ever received by any of the users and it is always the same user.