Automate Emailing Reports

How do I?
1

Now that I have created my first report and can generate it on-demand, I need to start thinking in terms of how to automate reporting. I’ve done this successfully in my Access application, and want to reproduce it in Five. I don’t necessarily want to do things the the same way I do in Access, but want to take advantage of the Five architecture and do things the “right” way. Here is what I do in Access to email reports to board of directors:

I already have tables that describe what board positions there are, what members are filling those positions, and what reports each position gets. Using those tables, I generate the reports as PDF files, then send emails to the board members with the report(s) attached. Since several board positions may get the same reports, it is inherently inefficient because board members end up getting several different emails, each with a single report attachment. There is a many-to-many relationships between people and reports, and this generates a LOT of emails.

What I’d rather do, if Five supports it, is to send an email to the particular person with a list of their reports in the body of the email, each report name having a hyperlink so they can click on it and download the file directly to their downloads folder. Theoretically, they wouldn’t even have to log in to Five to do this, if a simple link to the generated report is embedded in their email body.

First question:
Can Five generate a PDF report and supply a hyperlink to that report which will cause the report to be downloaded to their computer downloads folder when the hyperlink is clicked even outside of Five itself?

Second question (if yes to first question):
Can you point me to the documentation which shows how to do this, and/or supply instructions if there is no documentation?

Third question (if yes or no to first question):
How long do the reports “last” on the server before they get deleted? Do I need a process to delete them, or can they “time-out” and be deleted automatically?

There are quite a few more questions, but one step at a time.

Thanks…

2

I don’t feel it is reasonable to require all recipients to have an account in my application to build a report for themselves. Preferable would be a way to save the report PDF file in a table with a BLOB field (or whatever can hold a PDF file). That could be done somehow in the On Complete event? Can I send a URL to the recipient which, when clicked, would download the saved report to the user’s download folder, OR at least open it in their browser? Is it possible to run code from the clicked url to do the downloading or rendering in their browser?

Simple requirement, right? Right? Thanks…

UPDATE: I’ve been reading the documentation for Generating Reports and Attaching to a Mail Merge, and that seems promising. Can that technique be expanded to create several reports and attach all of them to an email for mail merge?

3

Great find on Generating Reports and Attaching to a Mail Merge! That approach can be expanded to generate multiple reports and attach them to an email.

Sample below is assigning 2 reports into the SMTPAttachments.

const mailMergeContext = {SMTPToEmail: five.field.EmailAddress, 
SMTPToName: "Staff", 
SMTPAttachments: [reportResult.report, reportResult2.report]};
4

Thanks for the reply Vin. I will need to study the whole mail merge subject. It is not clear to me whether [reportResult.report, reportResult2.report] objects in that array are actual reports, or url pointers to them, since the example in the referenced article only showed one report being added to the email. It doesn’t explain where the generated reports are “kept” on the server, and how long of a lifetime they have between being generated and being emailed. For example, can I generate 20 different PDF reports, somehow save their URL pointer, then email them all at once? When, after emailing, do they get disposed of?

Mainly, I’m trying to understand the architecture and how I can automate different types of reports.

Currently I have one function in my application that sends out board reports to the board members via email. I select the reports by checking off a list, and they get generated and eventually emailed to the proper recipients. I only need to check them off (select the reports) because I was concerned about the application’s ability to send out many reports to many email addresses. So the requirement to pre-select the reports to be emailed from a list may not be necessary.

I also have another functionality to send Study Group Rosters to either the group leaders, or portfolio registrars, or to group members themselves.

These are 2 different forms/menu items in my application, but they share underlying functionalities. so it seems like I should build my Five application to use common code between them, even if I have 2 different menu items or forms to invoke them.

Above all, I do NOT want to be tied to outdated methodology when designing this in Five. I want to do things the proper way, which I need you folks’ guidance on.

Given that there is a many-to-many relationship between the people receiving the emails and the reports that get attached, would it be reasonable to have a server-side function that sends these emails, and client-side functions to build a context and fill it with lists of items like in your example?

If I have a list of board positions, each getting 1 or more reports emailed to them, would Five require that I build the list of email attachments for each position, then loop through the members assigned to that position, and send each email separately but include the list of documents in the context sent to the server function? Or could you make the SMTPToEmail parameter itself an array of email addresses? If possible, this latter method would work well, because as mentioned it is the board position that has the list of reports to be sent, not the member/email address itself. So if the SMTPToEmail parameter can contain an array, this would be very handy.

Sorry to ramble. If you understand what I am asking, could you suggest some code, even pseudo-code that would accomplish this?

And could you speak to the efficiency of sending many emails at the same time, or somehow segmenting them into separate transactions? Will things “blow up” if I try to send too many emails at the same time?

Thanks so much for reading this long rant…

5

Sorry to bother.

I’ve attempted to copy the example from the documentation to generate a report and email it to a recipient. I used my Roster report for testing. I added a menu item to email the report.

So in the Reports menu of the running application, clicking Roster will generate the report on-screen. Clicking Generate Reports will open that form and allow you to change parameters, then generate the roster on-screen from the action button. Clicking Email Reports on the menu “should” cause the example code to run and generate an email with the report(s) attached.

I get an email with the designated body text, but the attached content is empty except for Group By Final rqStudyGroupRoster, which is the literal text in the template for that part of the report. But the report itself is not rendering.

So even though I’m closer, the functionality is not working. So here are my questions:

1: I’ve uploaded the FDF to OneDrive. Can you please take a look and see what I may have done wrong?

2: Do you recommend the mail-merge technique as the best way to create and email multiple reports, or is there a better way to accomplish this? There may be several reports, being sent to many recipients.

3: In the context for executing my mail merge, is it possible to send an array of email addresses rather than a single address?

4: If answer 3 is Yes, then could I also send an array for the SMTPToName property as well? Or is it possible to send instead an array of verbose email addresses like “Ron Mittelman (rmittelman@gmail.com)” instead of using both email and name properties?

5: Depending on above answers, would it be more efficient to iterate through the individual email addresses and send one email to each recipient containing all of the reports that recipient gets, or is it better to iterate through the reports and send an email to each person who gets the report, then rinse and repeat? This seems inefficient, since each person will get multiple emails.

6: Not clear how I generate multiple reports and get a reportResult for each. I’m thinking of taking this snippet of the EmailReport function

    const reportResult = five.executeAction('StaffPhoneExtensions', {});
    if (reportResult.isOk() === false)   {
        return five.createError(reportResult);
    }

and just repeating it for every report that is to be printed, with a different name for the reportResult variable? THEN using those variables add them to the attachment names array in the context? Except I’d need to not try adding if there is an error on a particular report, right? so instead of doing a return, maybe create a collection/dictionary/array of the successful ones, so if any are successful, I can email those?

Finally, is it possible to explain what is going on in the example? I was (almost) able to make it work by following the instructions, but don’t really understand what is going on with the mail merge object, using Processes, etc.

As you can see, this is quite a complex requirement. It may take a long time to design this. Any help is MUCH appreciated!

6

I’ll take a look and get back to you.

7

I may have found the problem. Email Reports is a process which runs the EmailReports function. This is server-side as it’s triggered by the Do Run event. I changed the function code to only display a message containing five.variable.UserKey, as that is needed for processing, but the showMessage command doesn’t even run.

What I need is some way to run an already existing function which loads the proper filtering tables so the query works. What about changing the event to On Run instead, so I can run code to load the tables properly first? If I do that, can I use the exact same code that was in the example for Do Run?

When I run the report from the Generate Reports form, it causes the GenerateReport client-side function to run the report.

If I use On Run, I can load the proper tables, but not sure if I can then run the server-side process so the mail merge works.

Thanks…

8

I tried changing things so the process causes my tables to be loaded properly prior to running the report, by using the on selection event. then moved the call to the mail merge back to the Do Run event. Still, not working. All I get is a single group footer text as before.

Also, is it normal for a single report to take 20 seconds or more before I get the message that the process has been completed? This is concerning in case I need to automate a process which emails several reports. Thanks…

9

I know you guys are very busy, but it’s been over 3 weeks. Is there anything that can be done with this? Thanks…

10

Hello,

Yes you should be able to move your function from DoRun to OnRun, however modifications will need to be made. If you are accessing server side objects within your function (such as sql queries), you will need to create a client function and a server function.

You will then need to attach the client function to the OnRun event, and execute the server function via:

five.executeFunction(functionName, variables, null, null, null, function (result){})

You will also need to parse any required variables from the front end to the back end through the ‘variables’ object within the executeFunction parameters.

Within the back end function, you will then be able to complete any required sql queries or database activities needed.

Thanks,
Riley.

11

Thanks for the reply Riley. I was able to finally export my FDF, so it is up on OneDrive.

Not sure I understand your reply. It’s true my function is being called by DoRun. Here is the function code, which you folks supplied to me:

function EmailReport(five, context, result)  {



    ////////////////////////////////////////////////////////////////////////////////////////////////
    // Execute the report action
    ////////////////////////////////////////////////////////////////////////////////////////////////
    const reportResult = five.executeAction('Roster', {});
    if (reportResult.isOk() === false)   {
        return five.createError(reportResult);
    }

    ////////////////////////////////////////////////////////////////////////////////////////////////
    // The reportResult contains a parameter called report, which is the report result in the format
    // of a pdf (encoded as data url application/pdf mimetype)
    ////////////////////////////////////////////////////////////////////////////////////////////////
    const mailMergeContext = {SMTPToEmail: five.field.EmailAddress, SMTPToName: "Staff", SMTPAttachments: [reportResult.report]};
    const mailResult = five.executeAction('ReportEmail', mailMergeContext);
    if (mailResult.isOk() === false) {
        return five.createError(mailResult);
    }

    return five.success('Report has been sent');
}

It’s not clear what won’t work in a server-side function. Is it the executeAction to run the Roster report? Or is it the next code block which should be doing a mail merge? Do either of these code blocks require they be run client-side?

On the process, I have the InitReportSubforms function running from the OnSelection event. The help for that event says it fires when you select the process from a menu. At this point, that will not work, because one of the things being used in that function is five.field.ReportsKey, which is available when I call this function from the GenerateReports form. But is not available in the process run from a menu item.

Also, the code called to do the mail merge (currently from DoRun event in the process) has the Roster report hard-coded in it. So therefore, none of this will run properly if not run from the GenerateReports form.

I think what I need is a form similar to the GenerateReports form, but from which I would have a list of all the reports with a check-box for each. Then I could check the ones I wanted to run, and somehow when I hit the button to actually run and email them, it could iterate through the list of checked reports and for each one, first run the InitReportSubforms function to load the “selected…” tables, then run the mail merge part.

This is the part I’m having trouble conceptualizing.

To simplify things, what if I had a table containing the ReportsKey of each report I want to generate. Can you suggest how to best iterate this table and generate each report? Theoretically, I can generate the report server side, which will return a result variable containing the report PDF, right? So I could generate each report, keeping track of the results in a list or dictionary, then use that list to do the mail merge part, right? I’ve already been advised how you can attach multiple files to an email.

Sorry to be so verbose. I just need to get an idea of how Five could handle this for mail-merging. Some pseudo-code of how to work this would be SO helpful.

I already can generate a particular report client-side from the GenerateReports form.

Thanks…

12

Hello,

You could potentially create a join table with the reports that can be selected. You could then present this in a process, when the process is ran, you could then execute a back end function in which queries the join table to determine which reports have been switched ‘on’. You could then add these reports to an array, and then iterate through them using a for loop, running any required functions for each report within each iteration.

Thanks,
Riley.

13

Thanks for the reply Riley. I appreciate the suggestion. I will need to think about this.

However, I can’t even get the email for 1 person for 1 report. It gives me an error saying:

image
image2392×470 43.3 KB

Not sure what I did wrong. Can you help me get the report emailed?

Thanks…

14

I re-watched the YouTube video Jo recorded showing how to do the mail merge to email a report. My code was the same as in the tutorial, except for the name of the report itself. When I run the application and click the menu item to email the report, I get the following error message:

MailMerge finished with errors : [ Could process attachments, reason - ****Code: ErrScriptError, TypeError: attachment.toDataURL is not a function
at DoAddAttachments (DoAddAttachments.fs.js:5:43) => TypeError: attachment.toDataURL is not a function > DoAddAttachments.fs.js:5:43 <TypeError: attachment.toDataURL is not a function>**** ]

The code I used, and the events used to run the code are correct according to the tutorial.

Could you please take a look at this issue? it’s been 2 weeks since the last time I replied to your prior message. Thanks…

15

Hi @RMittelman,

It looks like the issue is with the attachment.toDataURL line. In Five, you should use five.toDataURL instead.

Please refer toDataURL() | Five | Low-Code For Real Developers for the detail.

Hope that helps!

16

Thanks for catching that, Vin. However, even though there is no hard error, the report is still not working.

Here is the last page of the report generated client-side:

image
image1920×824 73 KB

Note the last 2 lines, which are part of the template, and haven’t yet been cleaned up in the template design. Good thing…

Otherwise, the entire report is printed with the study groups I’ve selected for default.

When I run the mail merge process, here is what arrives in my email inbox:

image
image1564×1302 86.5 KB

That line looks familiar, but where is the rest of the report?

Thanks…