Email Templates

John_Vance · 02-22-2020 03:21 PM

How do I have a list of all items on an email template. I figured out how to create an email template and some of the formulas (probably a longer formula than needed but none the less it works.) Currently when testing the email, I will get 10 emails with different products on each one instead of getting one email with all products.

I would like to have (1) email with all products instead of (10).

EX. I am ordering products from a vendor. They carry (10) products we need. I would like to have (1) email showing (on a purchase order template(all ready done)) instead of have (10) different emails.

Aleksi

Have you used scheduled report instead of Workflow? If you have used an option “ForEachRowInTable”, yes it will send email for every record. You are probably looking Workflow insted of Report. If you want to add more records from a table, you need to use Start: & End formulas for that purpose. Please check this article.

John_Vance

I am using scheduled report instead of work workflow. I read somewhere on one of the blogs or watched a video saying that was the better way. I still thank you for the suggestion. (I just checked to make to sure I was using reports not workflow.)

First of all, would I need that expression in every column I would want a list in?
Second, I used and expression <<Start:Products[Restock Needed]>> and the log says “should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table.” The only key column I have is ComputedKey and no Ref columns.

Sorry to be a pain but I am learning all this for the first time and I think I have all the simple things down but the really complicated things (i.e. expressions) are “Greek” to me.

Steve

As @Aleksi mentioned, make sure your report is configured ForEntireTable:

No.

You would probably also find these informative:

John_Vance

Throws an invalid Condition Error and the app crashes.

I should have mentioned it was for a slice.

I have “Slices” made up per vendor to only “email” certain vendors for there products.

Steve

Please post a screenshot.

John_Vance

Sorry working with (2) Screens

Steve

What is the Condition expression for the report?

John_Vance

Eventually what i would like to accomplish is to send an email to each of our vendors, from the app, on the products we need to order.

Aleksi

When you want to send an email to your vendors, yes report with the option ForEachRowInTable is the correct answer, but you need to trigger it from the Vendors table. If you don’t have it, you should create it. Then you can send one email with all related products vendor by vendor.

John_Vance

[Current Stock]<=[Restock Level]

John_Vance

@Aleksi is there any way you may be able to help me further on this subject. I am stuck and do not know what I can do. I have been working on this for now for (2) days and can not figure it out.

If at all possible can you look at the app and tell me what I am doing wrong with the email template?

Aleksi

I can take a look. Please share that template with aleksi@appsheet.com.

John_Vance

Deleted post due to: too much information shown.

John_Vance

So this is what I have:

Still having issues with this.

This works great. Gave me just the vendor I want to email and the product I want to order without having every other product show up that does not need to be ordered.

But if I have more than one product to order then I will received multiple emails. Each containing a different product that needs to ordered by that vendor.

How can I get all the products into one email?

This was also made from a “slice”. Which is the only way I was able to get this to function properly. If I change any column valve in the app to “ref” then the values change or come up missing.

@Aleksi

Marc_Dillon

I recommend reading this article, and others associated with it.

Also know that you need to include square brackets in all of your column pointers in the template.
<<[Product]>> instead of

John_Vance

@Marc_Dillon I will re-read the suggested article plus others I have seen.

As of this moment, everything works without the square brackets.

I do understand they will be required if an expression is to be used and that’s where the confusion starts.

Steve

It’s always safe to use the brackets around a column name. I encourage you to always use them.

daw7

Hello @Steve

I have a question regarding with Google Docs email template. When using private tables, AppSheet will create individual related tables for each signed in users and stored on their own Google Drive. What about if there is a Google Docs email template used in the app, does it need to be stored also in individual’s Google Drive? Or is it okay to store the template in the app creator’s Google Drive account.

Thank you!

Aleksi

App creator’s gDrive please.

daw7

Thank you @Aleksi for the reply.

I have a follow-up question though.

I am building an Inventory Management app with multiple users. I want the app to generate weekly Report for inventory updates, the report will be sent to each app user’s email address. I am using Google Docs for the email attachment template which will reflect each user’s related inventory reporting.

My issue now is that I cannot make the attachment template to display only those inventory data which is owned by each users. I want the attachment template to include logo and business name for each users. I did searched a lot in the AppSheet Community page but I cannot find such related topic. I have read about dynamic email template but I was not able to fully understand it.

I will be very glad and thankful if you can help me out with this concern.

Thank you!

PS: I intend to use Private Tables for the inventory table security settings.

Aleksi

The best way is if you create an “Users” table (if you don’t have it already). The minimum request is one column (Email address). Then you can trigger the Scheduled report from that table as “ForEachRowInTable”. You can think the Report/Template like this… it’s triggered first for the Row#1 and when that’s done, it’s triggered for the Row#2, etc. Because it’s reading the first row, it knows the email address. If you have a template with a Start: expression, you can filter the data like <<Start: SELECT(Table[KeyColumn],[EmailColumn]=[_THISROW].[Email])>> Now you have a dynamic report containing data just for that one user.

daw7

Thank you for this @Aleksi!

What about the dynamic template with images? Say for example I have already the USERS table with [LOGO] column. I want to include the logo for each email attachment template per user.

I used the expression <<USERS[LOGO]>> in an attempt to display the logo on the email attachment (in PDF format) but I only got the result of “USERS: logo_business name.png” in text format and not in image format. Obviously that text was the directory file name of the logo which is stored in my Google Drive.

John_Vance

@Steve I will put them in, you guys definitely know better than I do.

But for know, that’s the least of my troubles.

Marc_Dillon

Can you provide screenshots of your tables?

John_Vance

@Marc_Dillon

Aleksi

@John_Vance Please check this sample app called “Report for all users” from www.appsheet.com/portfolio/531778

John_Vance

@Steve @Marc_Dillon @Aleksi

First I would like to say thank you to all of you who have tried to help. I really do appreciate it.

Either I don’t understand what your trying to teach me (which I am positive is 99% of the issue) or I have something wrong with the whole app to email template structure.

I will just have to find other means to integrate all the emails into one so it looks more professional.

Thank you all again.

Marc_Dillon

I had meant for you to post more than that one screenshot, show us the column structure of your tables in question, describe how they connect, even some example records.

If you want to display more than one record in a workflow, you need to use a START expression, as the help file that I linked describes.

Let’s say you want to send the info for all Products in one mail, you’d add something like this to your template:

Judging by your list of tables, it is possible that you’re missing a linking table. I’m guessing you’re building either a Quote or Purchase record out of some number of Products, let’s go with Quote for now. This is a many-to-many relationship, as you can use the same products for many different Quote records. Setup a table called Quote Items, with at least 3 columns, a key, a ref to a quote (with isPartOf), and a ref to a product. Probably columns to copy over the price & item# data from the Product record as well (google “Appsheet dereference”). Then you’ll have a virtual column in your Quote table called “Related Quote Items”. Replace the START expression from the above screenshot with "<<START:[Related Quote Items]>>

Don’t take any of this code as literal, it is just guidelines since I don’t know your exact data structure and column names and whatnot.

John_Vance

Thank you @Marc_Dillon. I have been working on this for 4 hours a day for over a month and I know I am missing something really simple and it drives me. I am the type where I WILL find the solution, it takes alot of time for me to find the right one.

Is it possible for you or anyone to look at my app? I don’t want you to fix it but simply tell me what i am doing wrong?

Marc_Dillon

I’ll DM you.

John_Vance

@Marc_Dillon This would also be for ordering side of things. I have everything listed and everything works as it should when it come to the app. All the credit goes to the sample apps. I am really good at reverse engineering app/programs to do what I would like them to do. This isn’t the problem.

When it comes to emailing a Purchase Order. What I am trying to accomplish is to email each vendor a list of their items on (1) email per vendor which needs to be ordered. Currently, I am getting multiple emails for (1) vendor (which is step 1 in my book). I would like to receive (1) email per vendor with a list of items which need to ordered.

So all of this comes from a email template I am trying to create and obviously missing a step somewhere.

Hopefully I am explaining that correctly…

Currently, I am just learning all this for the first time. I have no experience in coding or low-coding anything. I have been out of school way to long to remember these things. I first learned how to code in DOS and binary. Things have changed (just a little) since then.

I don’t even remember the first computer I worked on. It consisted of a T.V. (black and white), a keyboard, no mouse and a portable tape deck. That’s old!!!

Aleksi

Users[Logo] will generate a list of values, not an image. If that logo is saved in Users table, you can read it just like <<[LOGO]>>.

daw7

Thank you very much @Aleksi

Aleksi

You’re welcome

daw7

what if the logo is saved in another table?

Aleksi

Then you can read it with a LOOKUP().