Sending emails using information from 2 different tables automatically

[MN_DDS]

I have been using appsheet for a few months, mainly dabbling and I have been able to figure out most everything with this forum but I am having trouble with something and I was hoping someone could help me out.  I have read a lot of different options on the forum but haven't been able to find the best way to do this successfully. 

My app is for tracking events (workshops and lectures) as well as event sponsorship details.  I am trying to find the best and most effective way to automatically contact around 10 individual companies I work with to request if they will sponsor an event. I need to keep the emails personalized and separate and include information related to both the event as well as the sponsors contact detail.  Here is my table structure

1) Events Table: This table has all the event details (date, location, etc)

2) Sponsors Contact Table: This is a list of different sponsors I work with and their contact details (name, email, company). 

3) Event Sponsorship: This table is intended to have rows with details regarding the sponsorship status for a particular event. Each row is for one sponsor and has a reference column to the Event Table. 

First thing I need help with is to automatically add rows to the Event Sponsorship table so that each sponsor has 1 row PER event to so that the status of the sponsor can be seen as a reference when looking at an event detail.  

My next goal, is to work on automatically sending emails to each sponsor one time per event. I can trigger it with a yes/no column or an action. The email needs to be able to have details from the Sponsors contact table first name, company as well as details from the event (such as location, date, title, etc).  I know I can make 10 individual emails and customize them but I am trying to make it more scalable so that that won't be necessary.

Any help would be appreciated.  

[TeeSee1]

This will probably help your first requirement.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-add-a-set-of-rows/m-p/258033

[MN_DDS]

Awesome, this works great. The only thing is that the parent reference below is based on the last added row. That would work for all new events, but is there a better way to reference the current row triggered from the action? 

• Set these columns:
  • Parent: maxrow("Master Table", "_rownumber")

[TeeSee1]

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-add-row-per-value-in-EnumList/m-p/357133

In that case try this with a little modification, that is the ENUMLIST needs to be replaced with a list of sponsors (can be a vc or an expression like Sponsors Contact[sponsor id]).

Any data related with the sponsor can be obtained via dereference.

Here Control is your events table, Target, Event Sponsorship

[MN_DDS]

Thank you for your help.  I like the first one and I went with it. That works well enough and I can manually adjust rows if ever needed. Thank you. 

Any guidance for the second part now?

[TeeSee1]

One approach is to use a bot. (See this)

You add a status field to Event Sponsorship. You can update this to "SEND" for instance from Events table, which triggers the bot for each of the related Event Sponsorship rows.

You can get relevant Event, Customer info by de-referencing the respective ref fields in the Event Sponsorship record.

[MN_DDS]

Ok I will work on this tonight or tomorrow. By the way, the other solution for adding the sponsors was perfect. Got it working and its the best solution. Thanks again. 

[MN_DDS]

 I got this to work really great and it sent the emails beautifully.  There is one thing I want to change Ideally. 

I added an Email Status Column to the Event Sponsors Table. I also made it so that the original action that adds the rows to the Event Sponsors table would set the Email Status as "SEND". Once the process completes, the status is set as SENT. That is pretty good but there is actually some instances where I have already contacted a sponsor previously and I don't want to email them again. 

I was trying to find a way to create a separate action that would set the Email Status for all blank columns so that I could narrow down which columns would get the "SEND" status and prevent sending emails to already confirmed sponsors.  I wasn't able to find a way to do this as a separate action. Do you have any guidance? I am learning a lot. Thank you.  

[TeeSee1]

Couldn't you use the action's Behavior -> Only if... to set "SEND" to only blank columns?

[MN_DDS]

Right now, the same action that adds the row to the sponsor table, is the one that sets the value of the Email status column. I want to do make that a separate action but i cant figure out how to change the value of the Emak Statue column when I need the action to be for a record of another Table ( Events table).

[TeeSee1 · Accepted solution]

I do not quite follow what you are trying to do but if you want to set values from one table to another you can do so by using two actions.

One attached to the target table that changes the value.

The other that calls the first action. An action of type - Data: execute an action on a set of rows. Here you can specify the rows to execute the first action.

[MN_DDS]

Thank you. Got Everything working with your help.