Solved: Re: Bot: Send an e-mail with a table at 0900hrs Mo...

dhdevans · 01-30-2023 09:16 AM

Hi folks,

I've got a table that has been constructed via Google Sheets to act as a summary data table. It has two columns: Size and Number. Looks something like this:

Size	Number
18	2
17	45
21	4

The data is dynamic, peeling out from Google Sheets.

This sheet is pulled directly into my app and i'd like to send something very much like the layout above in an e-mail saying:

"Hi Warehouse team,

Here is a report on the sizes and numbers required:

Size	Number
18	2
17	45
21	4

Bw,

Me"

This is how i've started to set it up:

In 'E-mail Body' i'm entirely unsure how to construct the HTML/variables required to report all the data. in the table.

Can someone help me with this? Seems a simple enough thing to do but i'm getting very frustrated!

dhdevans

Found a workaround, but not happy with how convoluted it is.

Created a helper column in the data set combining [Size/Gender] and [No.] ([Size/Gender]&" - "&[No.]) which presents the data as a single string (e.g. 10in - Female - 2). I hate it taking away the dynamics of calculating a value but... whatever. It stinks and I can't see another solution.

Using this new string (named [Combo]) I've put together a template that allows me to call each row of the report individually:

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),1)>>

This is absolutely MacGyvered bollocks, but it works. The SELECT statement grabs all the values from [Combo] as a single string separated by commas, the SPLIT statement snips them into individual values, the INDEX command grabs each row according to the number (in bold and underlined above) at the end of the command.

The issue with this is that the above command has to be copy pasted as many times as there are rows. Now, as my report is dynamic (e.g. each week there might not be 20 rows in the report) I have copy pasted it 40 times to be safe. So the template looks like this:

Report<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),1)>><<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),2)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),3)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),4)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),5)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),6)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),7)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),8)>>

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),9)>>

etc. which results in blank rows in the e-mail.

It does what it needs to do but it's absolutely barbaric in its conduct and in no way neat or tidy.

Answers on a postcard about how to crib the above to only grab the rows in the report...

View solution in original post

Aurelien

Hi @dhdevans

Did you try generating a template documents with AppSheet? That may help you a lot without having to any HTML template.

For reference:

Sample email templates - AppSheet Help

dhdevans

Well, there's a feature i didn't know existed!

I've tried setting up a template like below to just test getting a table into the e-mail:

I save this and the app but the preview e-mail is blank. Is my syntax bad here? For reference, the [Size/Gender] column is text type and [No.] column is, surprisingly, a number.

Aurelien

I'm glad you learnt something new then 🙂

@dhdevans wrote:

I save this and the app but the preview e-mail is blank. Is my syntax bad here?

Preview email is not fully accurate, I don't use it a lot.

I prefer clicking on "run" or "test" button attached to the top bar of the bot 🙂

Please note it may have run empty is there is no data to write for the specific record you are evaluating.

dhdevans

OK, so yet another new feature for me! I appreciate that, thank you.

Using the template above does not generate an e-mail at all. I tested with it, then without it (stuck a 'HELLO WORLD' in as the body) - e-mail sends me HELLO WORLD which is reassuring, but leads me to think something is broken in my syntax for the variables.

Took a different tack by clearing out the template and plugging the following in the 'Email Body' field:

<<Start: [Size/Gender]>>
<<End>>

That... was a mistake. You know that programmers song 99 bugs in the code? Yeah. I get it.

OK, so what in the fresh hell is this? As mentioned before the column type for 'Size/Gender' is text, and it's just... a column of different text categories.

Any ideas?

Aurelien

@dhdevans wrote:

That... was a mistake. You know that programmers song 99 bugs in the code? Yeah. I get it

🤣

Start is expecting a list of reference values of another table. For example, let's say you have an order (main table) and order items (child table).

Most of time, it's like this:

<<Start:[Related ChildTableNames]>> your expression <<End>>

For reference:

Use Start expressions in templates - AppSheet Help

dhdevans

So, this is just one table with no relationships to any other table at all - seems like i might be overcomplicating it.

Is there a way that you know to simply reproduce the table in the e-mail?

Aurelien

it all depends on your table structure.

It may be a very simple table, with no start nor end syntax if [Size/Gender] and [No.] are one of the columns of your table.

I think this is the case?

dhdevans

That's what i was thinking, i should be able to slap down a table with <<[Size/Gender]>> in one cell and <<[No.>> in another but it only produces one of the records in the e-mail. And then it sends 65 individual record e-mails...

Aurelien

Just to see more clearly, would you mind sharing a screenshot of your tables' structure? (from your app editor)

dhdevans

No worries!

dhdevans

To add more fuel to the frustrating fire I've managed to wangle an expression that produces the first record in the table:

<<[_THISROW].[Size/Gender]&" - "&SUM(SELECT(Picklist Summary Table[No.],([Size/Gender]=[_THISROW].[Size/Gender]),FALSE))>>

This throw back the first row only. So, getting there i guess?

Aurelien

Hi @dhdevans

@dhdevans wrote:

Hi Warehouse team,

Here is a report on the sizes and numbers required:

From your first post, I deducted the email is supposed to be sent to a warehouse.

Would you mind adding a "warehouse" table, and a "warehouse" column in your current Sheets that will be of type Ref, source table "Warehouse"?

The idea is to establish a correct parent/child relationship and to make your email to fire properly.

This is my suggestion, for instance:

References between tables - AppSheet Help

dhdevans

I think I see what you're getting at, but want to point out that this table is standalone with zero relationships to any other table in the app. All the calculation is being performed in Google Sheets behind the scenes.

I may be wrong, but to me it seems that creating an extra table to establish a parent/child relationship seems like creating more work and relationships that don't exist?

Aurelien

@dhdevans wrote:

I've tried setting up a template like below to just test getting a table into the e-mail:

I save this and the app but the preview e-mail is blank. Is my syntax bad here? For reference, the [Size/Gender] column is text type and [No.] column is, surprisingly, a number.

I juste realized something:could you try this expression?

size	num
<<Start:YourTableName[Size/Gender]>><<[Size/Gender]>>	<<[No.]>><<End>>

(with no line break)

dhdevans

Honestly, i figured the above would be a solution but also considered my syntax was crap. Tried several iterations of the above previously but it shows a blank e-mail in preview and when i fire the bot as a test it doesn't send anything.

I'm absolutely perplexed!

dhdevans