Loop through List and send attachments

Griff
Participant V

I have a table of materials, each having fields named Attachment 1, Attachment 2 and Attachment 3. The fields point to the location of PDF files with information about the materials.

I would like to create an ENUMLIST field in my order form that allows the user to select a range of products. Then I want to send Attachments 1-3, if they exist, for the chosen items. I know how to create a Behaviour that will send an email when the value of my ENUMLIST field changes, but how would I get it to effectively loop through my list of products in the ENUMLIST, find the attachments, and attach them all to a single email?

0 10 761
10 REPLIES 10

LeventK
Participant V

@Griff
Is your Materials table refโ€™d to your Orders table? I believe you can solve the problem by referencing materials to orders table so that you can de-reference your Attachment columns.

Hi,

Not sure that I understand, or more to the point, I am not sure that I have explained my need.

My Orders table has all the header information about the order. When an order is placed we need to determine which materials our team will use on the job and send the supervisor the Material Safety and Technical Data Sheets for those products. The customer doesnโ€™t actually order those materials, they order services that are completed by our team using required materials.

I could create a child table Materials Used table, then cycle through the child fields sending out the required data sheets but I was wondering if thereโ€™s a way to do it using a single ENUMLIST field in the Orders table instead of creating a new Child table.

Letโ€™s say I create my ENUMLIST field and fill it with a list of 3 materials, e.g. A,D,H. I am trying to work out if thereโ€™s a way I can look up each of those 3 products in the materials table, find the relevant data sheets and attach them all to a single email. This involves working through a loop - i.e. look up the info for material A, find the data sheets, attach them to my email, then look up the info for material D, find the data sheets, attach them to the same email and do the same for material H. The number of materials will vary.

May I suggest rethinking your approach, and make it two part instead of looping:

  • gather all necessary data-sheets by using a virtual column or SELECT statement
  • an action using the result that attaches data-sheets generated in the gathering/collation step above

Iโ€™m new to AppSheets and I struggle with methods to complete looping functions, but I found a solution to one of my early problems using virtual columns to parse apart data in order to auto-fill rows and save loads of time from the data-entry steps.

Thanks for the suggestions. As it turned out, the easiest solution I could find was to use a Word template for email body in conjunction with an EnumList. The user selects the products they want then the Word document displays hyperlinks to the required data sheets. Not ideal because the user needs to download from hyperlinks instead of having the required sheets attached to the email but it was the best I could do.

Thanks again to everyone for your suggestions

hodgson_doug
Participant III

If the hyperlinks are static (not changing, or rarely changed), you could add them to another Sheet, and present the hyperlinks via the key to the link stored in the โ€˜Data Sheet Hyperlinksโ€™ sheet, which could look something like,

'MSDS Code'          'Hyperlink'
 ESM113               https://www.msdsonline.com/sds-search/blah%20blah%20blah
 7504                 https://www.msdsonline.com/sds-search/7504%20real%20link

You can find methods to display only what you want, depending on what user selects with โ€˜reference listsโ€™ and go from there. It may be the right direction thatโ€™ll get you closer to your ideal scenario. Let us know.

By the by, coming from a programming background that I havenโ€™t seriously used in years, I now realize that if I need to store a value to use elsewhere (programmatically referred to as a โ€˜variableโ€™), I need to make use of virtual columns if it relates to data stored in rows. I wish that were readily apparent in the docs, so I can follow the โ€œknowโ€-code methodology and get up to speed as quickly as possible.
-Functions (like loops, conditional logic, etc) go in columns themselves or in actions;
-Variables (that relate to row data) can be stored in a virtual column, and possibly the upcoming UserSettings functionality.

Griff
Participant V

Thanks for the suggestion. I contemplated doing it the way you suggest but I really needed the data in an EnumList so the user could quickly select the products needed from a list.

In the end I decided the easiest way to manage this would be using a Word document as a template for the email, a solution to a related problem suggested by Aleksi. The Word document has sections set up as follows. I also add the same โ€œkeysโ€ to my EnumList field. That way the user can select from the list presented and the workflow builds the MSDS and TDS into the email as required. Very un-glamorous, high maintenance, and error prone I know but it works, and saves time in the field.

<<IF:In(โ€General Purpose Grey Cementโ€ ,[Specs Required]) >>
General Purpose Grey Cement
MSDS:http://cockburncement.com.au/wp-content/uploads/2017/04/Portland-Cement.pdf
TDS: http://cockburncement.com.au/wp-content/uploads/2017/04/Portland-Cement.pdf

<>

Where did you find that solution Rob?

Griff
Participant V

Full credit to Aleksi who helped me develop that solution to another, bigger file. After looking at alternatives, and since I already had it working in that other file I decided to apply it here too

I meant where on the forums here could we find details on the solution. Do you have a link to that thread in order to help others? It sounds interesting and like something I may implement in the near future.

Griff
Participant V

Hi Doug,

Because it contained private company data, Aleksi and I took it off the public site. However I am happy to share the technique - I have no doubt that Aleksi wonโ€™t mind. In the end I used the technique and its working a treat, even with a 60+ page document.

The way I did it was to create a Google Doc template with multiple sections (see sample below). You can hide the desired sections using the << IF: >> & << EndIf >> expression. The template below, for example, contains two sections. The section will only appear in the final Email or PDF generated by AppSheet if the conditional clause is satisfied, i.e. if the value of the field [Template] equals the nominated value.

<<IF:[Template]=โ€Template Aโ€>>

Your Company

123 Your Street

Your City, ST 12345

(123) 456-7890

Project Name

4th September 20XX

OVERVIEW

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper.

GOALS

  1. Lorem ipsum dolor sit amet, consectetuer adipiscing elit
  2. Sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.

SPECIFICATIONS

Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Investigationes demonstraverunt lectores legere me lius quod ii legunt saepius.

MILESTONES

Lorem Ipsum

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.

Dolor Sit Amet

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.

<>

<<IF:[Template]=โ€Template Bโ€>>

Your Company

123 Your Street

Your City, ST 12345

(123) 456-7890

Project Name

4th September 20XX

OVERVIEW

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper.

GOALS

  1. Lorem ipsum dolor sit amet, consectetuer adipiscing elit
  2. Sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.

SPECIFICATIONS

Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Investigationes demonstraverunt lectores legere me lius quod ii legunt saepius.

MILESTONES

Lorem Ipsum

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.

Dolor Sit Amet

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.

<>

Top Labels in this Space