Bot to send a single email with multiple rows (data change)

[MDGstrategies]

Dear all,

First of, thanks a lot for all the hard work and patience you put into helping people!

Things to consider 1: I am very new to appsheet / coding and I am not very knowledgeable in terms of these kind of things. 
Things to consider 2: The below app is just for myself. I am interested in understanding the processes. Please do let me know if you think the approach is wrong. 

Straight to the question: thanks to this community posts and a bit of help from support, I managed to create an app where I can quote to clients the services they require.

The set up is as follows: table "quote_log" contains all the quotes and are separated by clients (each row contains a single service for a single client).

A column of the "quote_log" table is called "quote_trigger" and an Action is set up to change the value of "quote_trigger" columns to the rows I select to "sending_quote":

I then set up a bot that triggers when there is data change in table "quote_log", column "quote_trigger",  and the condition is [quote_trigger]="sending_quote":

The problem is: although the filtering on the pdf attachment works fine, say that I want to send to a client 2 items (2 rows: same client, different services), the bot would trigger twice, sending two emails (and rightly so!). What I think it is, is that the bot is behaving as if there is a "ForEachRowInTable" (feature available in bots triggering on schedule) enabled.

How do I tell my bot to send a single email with multiple rows if my bot is triggered by data change ( update)? 
Am I approaching the task from the wrong perspective? 

[dbaum]

If you don't already have a dedicated Clients table, consider establishing that and then creating the action there to send an email comprising all quotes for the current client that have a certain status.

[MDGstrategies]

Hi @dbaum!
Thanks a lot for the reply! I actually do have a clients table. May I just ask you to elaborate a bit more how I would do that?
As far as I understand, this would allow me to just send the email through the action, but it is not clear how I would go about filling the attachment of the email (?).  

[dbaum]

As you're doing now, use an action to trigger an automation.

[MDGstrategies]

Update: I did not notice, but there already is a virtual column in my clients table showing the related quotes for the specific client. 

I still am trying to figure out how to have the correct amount of rows showing into the attachment while avoiding the bot to trigger multiple times, thus sending multiple emails.

Thanks to anyone who can help! 

[dbaum]

Use Start expressions in templates - AppSheet Help 

[MDGstrategies · Accepted solution]

Hi [future someone trying to do the same],
Although it has passed a bit of time since achieved, I though that it would be nice to share how I did it. I hope not to confuse people even more. 

Thanks again @dbaum 

1) create 3 tables:
- customers (main table, contains column [trigger])
- services (contains [column name] which is a Ref to table customers)
-quotes (contains [service column] which is a Ref to table services and contains [customer column] which is a Ref to table customers)

THIS IS WHERE MY APPROACH WAS WRONG: I did not know that appsheet would create a ref_rows() expression for all the other tables that would contain the ref column:

Ref_rows:
https://support.google.com/appsheet/answer/10107364?hl=en

1.1)

add to quotes table all the services (Ref to services table) you want to quote to the specific client (Ref to customers table)

2) Create an action on customer table that triggers the process:

3) set up the automation so that it triggers when the data is changed in [trigger] column in customers table: 

Data change -> updates only
table: customers
condition: [_thisrow].[trigger]="sending_quote"

4) set up automation to send an email with pdf attachment (in my case):

Table: customer (as it is the table containing also the customer email, name and etc..)

in the attachment (but it works also on email body for instance) use a start expression to fetch the data, and a select() expression to filter the data according to the condition you need: 

<<start:select([Related quote tables], condition>>

<<[quote table column 1]>>
<<[quote table column 2]>>
<<[quote table column X]>>

<<end>>

5) create an action to clean the trigger in column [trigger] in customer

[dbaum]

I'm glad you got it to work. Mark your explanation as the solution so that others can more readily discover it.

[MDGstrategies]

Hi dbaum, I either don't have the option on the three dots menu besade the question and/or answer,  or I simply do not know how to (even!) do that!

Thanks!

[dbaum]

I see now that you posted in the Tips & Tricks forum. Questions like this should be posted in the AppSheet Q&A board, where they're more likely to be seen by someone who can provide an answer and won't confuse anyone reviewing this Tips & Tricks board for the type of content it's intended for. I suggested to moderators that they move the post.