Email Multiple Reports to Multiple Recipients

How do I?
1

I’ve finally gotten my report to run both client-side and via mail merge process.

Now I need to automate multiple reports and multiple recipients. This snippet of code, which Vin gave me some time ago may be pertinent:

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

This appears to let me attach multiple reports to a single email. I presume I would first generate each report on the server, and keep track of the various reportResult objects.

My particular application knows, via various tables, which people get which reports. I was hoping to be able to attach multiple email addresses to a single email with the pertinent report attached. The other thread you pointed me to only showed how to attach a To, CC and BCC email address to an email, but not how to attach multiple emails to the TO address. However, the snippet below from your examples page calls the context item SMTPAddresses and looks like the value is an array because of the [ and ]. If that is true, I could still send a report to multiple people on one email, right?

const mailMergeContext = {};
mailMergeContext['SMTPAddresses'] = [five.emailAddress('john@example.co', 'John Citizen')];
mailMergeContext['SMTPCcAddresses'] = [five.emailAddress('kirk@example.co', 'Finance'), five.emailAddress('belinda@example.co', 'Orders')];
mailMergeContext['SMTPBccAddresses'] = [five.emailAddress('management@example.co', 'Management')];
const mailResult = five.executeAction('SubmissionReceived', mailMergeContext);

In my current Access application, we do this. Create an email, attach the document, then add all the required email addresses, then send the email. Unless you tell me there is no way to do this (multiple TO addresses on a single email), I will need to switch perspective and send a single email to each person with all of the documents they would receive.

So please verify that the first code snippet above works for adding multiple documents to a single email, and also advise if I am correct that I can add multiple TO addresses to a single email (not cc and bcc, but multiple TO addresses). This will tell me how to build my logic for generating the reports. Thanks…

2

Hi Ron,

We definitely support sending an email to multiple recipients.

Your interpretation is correct. By using one of the properties ‘SMTPAddresses‘, ’SMTPCcAddresses’, or ‘SMTPBccAddresses‘, you add multiple email addresses to the array.

Here is the link with an example of how you can achieve it:
emailAddress() | Five | Low-Code For Real Developers

Please let me know if you have any questions.
Regards,

Elton S

3

thanks for the link. Not exactly answering the issue. Example 1 shows executeAction, with only a single address and name.

Example 2 looks promising, and seems to be the same exact example I listed above. But it is working on different objects than example 1 and doesn’t show multiple TO addresses, but a single TO address, a BCC address and BCC address. however, can the code be expanded as such:

mailMergeContext['SMTPAddresses'] = [five.emailAddress('john@example.co', 'John Citizen'), five.emailAddress('j.doe@xxx.com', 'John Doe'), etc... ]

If this is possible, then it answers my question. Thanks again…

4

Hi Ron,

Yes, you can add multiple emails through the array, for example:

mailMergeContext[‘SMTPAddresses’] = [five.emailAddress(‘kirk@example.co’, ‘Finance’), five.emailAddress(‘belinda@example.co’, ‘Orders’)];

regards,
Elton S

5

Thanks for all the help so far, Elton.

I think I need help figuring out how to email reports to various people. Currently, I’m using a process, but the report is hard-coded and the email address needs to be typed in. Now I’m trying to make a general purpose form to do this.

In my Access application, I have a form to send desired reports to board members. The form includes a checkbox list of reports, so I can choose at run-time which reports to process. Here is a screen shot:

image
image2716×1070 266 KB

When I choose reports on the left-hand list by checking the checkboxes, the reports get generated automatically. For now, ignore the other lists, whose contents change when I select different reports.

I don’t think there is a way to do this in Five. Up to now, I’ve been able to simulate this by having a form page powered by a DataView. When I click the name in the DataView, it causes the SelectedReports table to be updated with IsSelected either true or false. Then the DataView’s live query is refreshed and the result shows immediately. I need to know if this can be easily done using a data view. I’d also need a list of Send To destinations, which will contain things like “Members”, “Board Members”, “GroupLeaders”, etc. I believe this could be on the General page. I tried to use a DataView on the General page, but that doesn’t seem to work well.

So the first question is: Is there a way to have a multi-select list similar to the one I have in Access? It does not need checkboxes next to each record, but I do need to be able to select 1 or more records to process. This can be a lookup field based on Reports table, or a LookupQuery. Is this possible? This would eliminate the need for a dedicated DataView control, just to choose which reports I need to send out.

Thanks…

6

Hi Ron,

What we usually do is to query all the emails which is already saved in the database and send an email for each email address.

The example previously provided shows how to send an email to multiple recipients.

(The array needs to be populated with all the email addresses.).

If I understood your scenario (Please, let me know if I missed something), you want to select a report and the members to whom the email will be sent. It can be achieved, but it depends on how your database was structured and the approach you are using.

In the example you sent, I believe you have a database designer similar to:
Report name-> Board Position → Board Position Member List → Email.
Therefore, you can navigate between all pages and make a selection.

In five, if you have a similar structure. For example: Report name → Member List as a page.

You could have a form and a page list with all members you want to send the email to.

But you still need to send the email for the report and all the member lists associated with it.

If your database structure is different, your current data view could be one way to go, as you are already saving data into the table. You could also use a dashboard to have two data views, one to select the report and another one to select the members.

The links below could be helpful as it has one example on how to create a dynamic Data view (where its data will be displayed based on another field):
Work With Data Views | Five | Low-Code For Real Developers

The Book Club app has an example of a dynamic Data view as well:
10.1 - Overview | Five | Low-Code For Real Developers

If you believe you need customer development, you can contact us and request paid support.

Regards,

Elton Santos

7

Hi Elton,

I’m not too worried about how to obtain the proper data, just how to present it in five. Like my example from Access, I would love to have a list box showing my reports, where I could select one OR MORE records. If Five has that feature, can you point me to it?

Failing that, I could use a DataView, similar to the one in the GenerateReports form. That DataView shows multiple items, and clicking on any of them causes the checkmark next to it to toggle to X, and vice-versa.

I tried this on a new form, but apparently you can’t put that action page powered by a DataView on the first page of the form. Is that correct?

I can put other fields on the General page, including a drop-down choice between sending to Group Leaders, Board Members or general Members. I could have a page for each of those choices, powered by their own DataView, and show or hide those pages depending on the value of the drop-down on the General page.

Since there are multiple reports going to multiple people, the challenge is to orient the data by report (and each email address to send to) or by email address (and each report to attach). This is not a five-specific problem, I had to solve that in my Access application. I ended up designing it to be report-centric and add an email address for each person who gets that report.

This is a tricky business case. Some people get multiple reports, in which case they are going to get multiple emails if they get more than one report. If I change it to be email address-centric, then one person will get a single email with all the reports they should receive.

Sorry, rambling. Mainly, just want to know how to design the UI in a way that Five will support.

Can’t really afford custom programming from you folks. If you don’t mind simply giving me the guideline in general terms I will attempt to do this myself.

Thanks…

Also, need to know how to make a form without a data source, similar to a process. I believe I need a form so I can add pages to choose which reports I want to email. But a form requires a data source, and doesn’t seem to allow screen fields, only fields from the data source.

Is it possible to add pages to a process? that would work for me, as I don’t actually need a data source.

8

Hi Ron,

For the first question, yes, you can put that action page powered by a Data View on the first page of the form.

You can navigate to the page tab and drag the page you want to be the first.

For the second question, no, all forms must have a data source, and pages aren’t supported on processes at this stage.

Regarding your business requirements, I don’t have any guidelines because you are customizing your application, and it requires us to analyze your application and create a solution to meet it based on our features.

Have you looked at the join page? Add a Join Page | Five | Low-Code For Real Developers, you may need a structure like that (a table that links two tables), where you can select a report record and then select all emails that should be linked to.

In this way, you have all the emails by report. Of course, it creates data in the DB; therefore, before sending the email, you may need to add/change the records.

Regards
Elton S

9

I’m having trouble getting this form to work. I believe I need a form rather than a process, because I need to include a list of which reports to print/email.

I tried a form based on the Reports table, but it won’t really let me save the form because it needs at least one field to show on list. Since I want to email reports to a finite list of peope types, I think the solution is to create a table called EmailReportsTo (or something like that) and include records for GroupLeaders, BoardMembers and Members. If I base the EmailReports form on this table instead, I will then have a field to “include in list”. Therefore, when I choose the EmailReports form from the menu, I will have 3 records in the list, each being one of the choices above.

Do you think this is the proper way to accomplish this? thanks…

RELATED QUESTION:

Do you think I need a GUID key field in this new table? It will only contain the 3 report email-to types mentioned above. Even if I need to add more types later, the description of the types will be unique. The descriptions may, however, contain spaces, so is it better to include a GUID key?

10

Hi Ron,

The join table can also be created with more than 2 tables. Like in your example: GroupLeaders, BoardMembers, and Members. (All these fields will be a foreign key (GUID key).)

This approach allows using this new table (form) as a list/join page under the forms the GroupLeaders, BoardMembers, and Members because it contains a foreign key to them.

Related Question:
If the report type is already defined in another table, you don’t need to repeat it, and your suggestion on having a ‘GUID key’ should be the way to go. However, you may need to check if one of the tables you are linking via join, such as GroupLeaders, BoardMembers, and Members, already has this information; if so, you may not need to add a new field; otherwise, you may duplicate entities in your table design.

Regards,
Elton S

11

I have a join page on my BoardPositions form, called Reports, which uses the BoardPositions form. this page properly shows the available reports, each one with a switch for off or on. Can you tell me where to wire up an event when one of these switches is changed?

I know it automatically updates the BoardReports join table, but I also want other things to happen, such as running code to also update the SelectedReports table.

In the other forms, I’m using Action Pages with DataViews, and these update nothing at all, except that I run functions to do the update to the join table and the Selected… tables. Is this a better way to go than using a join page? I ask because in other forms, it will not save the record properly if I put a function name in the OnUpdate event as I mentioned in my other posts. Jo said you may be instituting an AfterUpdate event which I can use instead. But until then, I’m using DataViews and updating the underlying data myself in functions.

So I need to know if I should also do this instead of using a join page for my BoardReports form.

thanks…

12

Hi Ron,

If you want to run another function after saving a form with a join page, you can attach events to the table.

For example, we can run a function before and after a table is created/updated/deleted.

These events are server-side; therefore, the information about these fields can be retrieved via the property ‘context’.

For example, if you attached an event in the before function, the context would have the properties ‘old‘, and ‘new ‘:
The ‘old’ is related to the value before the changes are completed. (context.old.field1)
The ‘new’ is related to the new value updated the changes are completed. (context.new.field1)

Observation: You must create a transaction for this event if you intend to modify/create any table. This is one example; (you must add your database name where indicated).

1 - Create a database connection:
const db = five.getDatabaseConnectionByID(‘AddYourDatabaseName’);

if (!db.isOk()) {

    return five.createError(db1, 'Connect to database failed');

}

2 - Create a transaction:

const tx = five.startTransaction(db);

if (!tx.isOk()) {

    return five.createError(tx1, 'Create transaction failed');

}

3 - Use the transaction in the query:
let queryResults = five.executeQuery(tx, sql, 0, Field1, Field2, Field3);

4 -Commit the changes:
five.commit(tx);

Regards,
Elton S

13

Thanks for the reply Elton.

I don’t really want to run a function after the table is updated, because even editing the table in the form will cause this to happen. Don’t really want this. In my other forms using a data view, I can run a client function after clicking a control in the data view. this calls a server function which saves the record as needed. this is already working fine.

I was asking about how to trigger something right from a join page. but again, this does not seem to be easy or desirable. So I’m switching that page to an Action page powered by a data view, and updating records the same way I usually do, which is somewhat proven.

That was the purpose of my previous question.