Reminder email trigger - stuck

[roattw]

I have a form that has an option for Pending Info (checkbox) that users can select if they dont have enough info at the time the form was completed.  

This form is stamped with user level auth so the record is associated with that user, so later they can login and just see thier entries.

I would like a triggered action to EMAIL the user who submitted the form if they used the Pending Info checkbox.  To serve as an email reminder to do back and update the record. 

Their email address is part of their user account info table.  Username and password is used for login, but email is also collected.

I put a virtual hidden field on the form to receive on entry the auth table field email (Virtual1) and also pass that on exit.

I think I have the email set for Pending info checkbox but Im not sure how to have it sent to specific user who submitted the form.

[cheonsa]

Hi @roattw,

You can use #inserted in the Select From block instead of the Table (Patients_Encounters_Pre121219). If you use the Table itself, every time the Triggered Action runs, it will send an email to all records where Pending_Info is equal to "Y".

You can refer to this video for more information:

https://www.caspio.com/webinars/send-sms-emails-using-triggered-actions/

 

Happy Holidays!  

 

-kristina 

[roattw]

Thanks kristina.  Working now.  I was also passing that email field incorrectly.

[kcastagnaro]

Hi, Roattw!

 

Can you share your final solution? I have the same problem that you have.  I have a table with reminders that are "owned" by different users. I would like to create a task that sends a table variable to each user with only their own records. 

User one is emailed the table of her own records, user two is email the table with his own records, and so on.

 

[CoopperBackpack]

Hello @kcastagnaro,

If I got your question correctly, you may test this solution. 

1) Let`s say we have a table with users` info

 

2) The second table stores some data and each record stores User_ID as a secondary key. So, these 2 tables have a relationship based on User_ID.

 

 

3) This is a Task example to send individual emails:

The user with ID=1 receives the following email:

And the user with ID=2 receives the following email:

 

Task design explanation:

• The first table variable is "Variable_For_Message". Here we select the fields from the Assigned_Records table that we want to use in the email body. The key point here is to set any false condition. In this example this is Where 1 = 0. 

               This is needed to set up a table variable structure, but to leave the table variable empty. It is empty thanks to the condition is never met.

• In the second table variable (that`s "List_Of_Emails") we need to select email addresses from the users_emails table. Also, we need to select the field that is used in relationships between our tables, to join the tables on this field, and group by selected fields. 

 

• In the For...Each loop we are going to populate the "Variable_For_Message" table variable -> To send email -> The clear the table variable using the 'Delete From'.  As a result in each iteration the table variable is populated with correct data that is related to the specific user. 

             In the 'Insert Into' block we need to select from the Assigned_Records table and set the condition that compares User_ID in the table with the #record.user_ID(so the User_ID of each iteration). Then we also need to match all the fields:

[CoopperBackpack]

In case you store all data in one table, for example:

Then you may use this Task design:

 

Maybe the Task can be optimized, however these Tasks work from my side.

Hope this helps. 

 

[kcastagnaro]

On 11/26/2021 at 4:16 AM, CoopperBackpack said:

In case you store all data in one table, for example:

Then you may use this Task design:

 

Maybe the Task can be optimized, however these Tasks work from my side.

Hope this helps. 

 

This is absolutely fantastic. It's working beautifully. THANK YOU SO MUCH!

[Katie]

Thank you so much!! I have been trying to figure the same thing out and so glad I found your solution. Thank you for explaining it so thoroughly!!!

[shaferam]

On 11/26/2021 at 6:16 AM, CoopperBackpack said:

In case you store all data in one table, for example:

Then you may use this Task design:

 

Maybe the Task can be optimized, however these Tasks work from my side.

Hope this helps. 

 

@CoopperBackpack This helped me so much! Thank you!  I have a question on an additional WHERE in the INSERT INTO  action. I have added the AND and second WHERE clause directly under the first to filter the data so it only gets sent for records that meet an eligibility criteria (payment eligible = Yes). The problem I'm having now is that if I have someone with records in the table that don't meet the criteria they are still receiving an email but with a blank report. How do I keep them from receiving an email at all?

[DrSimi]

hi @shaferam,

If you are using the variable in the For Each loop, it sounds like you would need to add the condition to not select the email from the users not meeting the condition. You may need to add a JOIN to pull the fields of the eligible table, and DISTINCT to avoid duplicate emails - depending on your data structure.

[CoopperBackpack]

Hello @shaferam,

To prevent sending empty emails you may use the approach that you can see in the screenshot:

 

The idea is to wrap the 'Send email' block into the IF-THEN condition. So, you may just move your current 'Send email' block under 'THEN'.

The condition is to check if the variable generated for the user is not empty.

For that SELECT from the variable used in the email body.  Under SELECT add any field from the variable that cannot be empty in the Table (I used ID in this example).

Feel free to update the post if you have further questions. 

[shaferam]

On 6/8/2023 at 8:56 AM, CoopperBackpack said:

Hello @shaferam,

To prevent sending empty emails you may use the approach that you can see in the screenshot:

 

The idea is to wrap the 'Send email' block into the IF-THEN condition. So, you may just move your current 'Send email' block under 'THEN'.

The condition is to check if the variable generated for the user is not empty.

For that SELECT from the variable used in the email body.  Under SELECT add any field from the variable that cannot be empty in the Table (I used ID in this example).

Feel free to update the post if you have further questions. 

@CoopperBackpack you ROCK! Thank you so much for your help with this!!

[JoeyBrannon]

@CoopperBackpack what is the purpose of the Group By step when compiling the list of emails?