Email Reports to Specific People

How do I?
1

My GenerateEmails form allows me to send GENERIC emails, emails to specified study group leaders (LEADERS) or to board members (BOARD). The capitalized words are keys from my EmailTypes form/table.

GENERIC works fine. I setup the TO email addresses, I select which report to attach in the Reports page, and which Study Groups to include in that page. When I click Generate Report action button, the selected report is generated, then sent to the specified email addresses as an attachment.

Now I’m trying to design the LEADERS code. Basically, I want it to iterate through the selected Study Groups, obtain their leaders, and send their report attached to an email.

Getting the particular people in the study group who should receive the report is already completed. For this type of report (LEADERS), the Send To field on the general page is ignored. The email addresses of the selected group leaders is obtained by queries and inserted into the SMTPAddresses object for sending the email.

The SelectedGroups table is an important part of the report query itself, and filters which groups are included in the report. Normally, all groups selected on the StudyGroups form page are immediately updated into the SelectedGroups table, and everything works fine.

for this workflow, I need to save which groups are selected by default in SelectedGroups, then set all groups as NOT selected, then set one particular group to selected, then generate the report, then restore the original list of selected groups. This is done because the SelectedGroups table is an integral part of the report generation.

Anyway, this is partly working. If I select 2 study groups that I happen to be the leader of, then run the reports, I end up getting 2 emails, each with an attachment.

The problem is that when I look into the attachment, it includes reports for both groups, when it should only include 1 group in the attachment for each email.

My logging seems to verify that the code that saves all selected groups, then selects only 1 group, then generates the report, works fine, because my logs show the proper GroupID. But both reports are getting created in one pdf.

Is it possible there is an issue with the reportResult variable (which I declare early and reuse) is getting reused without being cleared between reports?

the report generation works properly if I generate the report with both groups still selected. I then get a single report containing both groups, which is what I want. But if I clear SelectedGroups, and only select 1 group then generate the report and save the reportResult object in a dictionary or array, then select the other group and repeat the steps, it turns out that both reportResult objects are duplicated. they have individual file names (I think that’s what it is, it says “Roster_” plus a bunch of GUID-like characters). But both file names / attachments to 2 different emails contain the same 2 reports instead of 1 report in one attachment and the other report in the other attachment.

I know this is a long problem statement. Please advise if you don’t follow what I’m saying and need more information.

I mainly need to know if I’m hitting some kind of five limit when I try to save multiple reports (reportResult objects actually). I can’t figure out why this may be happening.

Thanks…

2

I can clarify the problem with some evidence. To recap, if a report has IsFiltered=true, then while processing reports, instead of the entire report containing all selected groups, it should contain only 1 group.

So I read a report record, then if it is NOT filtered, I generate a report on the server containing all selected groups.

If the report is filtered, I iterate the selected groups and generate a report on the server for that group only. This is accomplished by temporarily setting only that group as IsSelected=true in the SelectedGroups table. when the report is done, it should only include the one group. rinse and repeat for the other selected groups. finally, restore the original SelectedGroups state before moving on to the next report.

I ran this with several log statements to demonstrate the actual issue. This report IS filtered. Two groups, Acting Out and Advances in Science, were selected. Here are some log lines:

Selected groups: [
{"StudyGroupKey":"63955759-3104-4baa-8fc3-f7d792d52351","StudyGroupName":"Acting Out"},
{"StudyGroupKey":"b6fe9ea5-c2ec-47d7-85c5-35c1befcd4f9","StudyGroupName":"Advances in Science"}
]

This was captured at the very beginning of the process.

Here is where the iterating of groups starts.
In each iteration, I log the condition of SelectedGroups , then filter it for the group, then log it again. I then log the first 2 lines of the report query itself.

currentSelectedGroups: [
{"StudyGroupKey":"63955759-3104-4baa-8fc3-f7d792d52351","StudyGroupName":"Acting Out"},
{"StudyGroupKey":"b6fe9ea5-c2ec-47d7-85c5-35c1befcd4f9","StudyGroupName":"Advances in Science"}
]

newSelectedGroups: [
{"StudyGroupKey":"63955759-3104-4baa-8fc3-f7d792d52351","StudyGroupName":"Acting Out"}
]

First 2 lines of report query results:

rqStudyGroupRoster rows=[
{"Category":"Movie Groups","CkNo":"2421","DayOfWeek":"Sunday","FromSFV":"0","GroupName":"Acting Out","Grouptype":"c","MaxMembers":"12","MemberAddress":"491 Twin Oaks Court, Thousand Oaks, CA 91362","MemberCount":"13","MemberEmail":"frank.bonoff@gmail.com","MemberName":"Frank Bonoff","MemberSeq":"1","Paid":"2024-06-01T00:00:00Z","Portfolio":"C","Position":"","PrimaryPhone":"(818) 522-3356","PrimaryPhoneType":"c","PriorGroup":"Acting Out","Registrar":"Judith Stalk","RegistrarAddress":"38 Marimar St, Thousand Oaks, CA 91360","RegistrarEmail":"jstalk@icloud.com","RegistrarPhone":"(310) 350-8400","SecondaryPhone":"","SecondaryPhoneType":"","SortName":"Bonoff, Frank","ValidTo":"2025-06-30T00:00:00Z","Vp":"Frona DeCovnick","VpAddress":"5544 Ranthom Ave., Woodland Hills, CA 91367","VpEmail":"kidzathart@aol.com","VpPhone":"(818) 883-1247","Weeks":"2, 4"},
{"Category":"Movie Groups","CkNo":"6406","DayOfWeek":"Sunday","FromSFV":"0","GroupName":"Acting Out","Grouptype":"c","MaxMembers":"12","MemberAddress":"5544 Ranthom Ave., Woodland Hills, CA 91367","MemberCount":"13","MemberEmail":"kidzathart@aol.com","MemberName":"Frona DeCovnick","MemberSeq":"2","Paid":"2024-06-09T00:00:00Z","Portfolio":"C","Position":"","PrimaryPhone":"(818) 883-1247","PrimaryPhoneType":"h","PriorGroup":"Acting Out","Registrar":"Judith Stalk","RegistrarAddress":"38 Marimar St, Thousand Oaks, CA 91360","RegistrarEmail":"jstalk@icloud.com","RegistrarPhone":"(310) 350-8400","SecondaryPhone":"(818) 621-0129","SecondaryPhoneType":"c","SortName":"DeCovnick, Frona","ValidTo":"2025-06-30T00:00:00Z","Vp":"Frona DeCovnick","VpAddress":"5544 Ranthom Ave., Woodland Hills, CA 91367","VpEmail":"kidzathart@aol.com","VpPhone":"(818) 883-1247","Weeks":"2, 4"},

Here is the next group in the iteration:

currentSelectedGroups: [
{"StudyGroupKey":"63955759-3104-4baa-8fc3-f7d792d52351","StudyGroupName":"Acting Out"}]

newSelectedGroups: [
{"StudyGroupKey":"b6fe9ea5-c2ec-47d7-85c5-35c1befcd4f9","StudyGroupName":"Advances in Science"}
]

And the first 2 lines of report query:

{"Category":"Miscellaneous","CkNo":"4842","DayOfWeek":"Wednesday","FromSFV":"0","GroupName":"Advances in Science","Grouptype":"w","MaxMembers":"15","MemberAddress":"30810 Marseille Way, Westlake Village, CA 91362","MemberCount":"12","MemberEmail":"cohan.steven@gmail.com","MemberName":"Steven Cohan","MemberSeq":"1","Paid":"2024-06-24T00:00:00Z","Portfolio":"A","Position":"","PrimaryPhone":"(301) 503-1672","PrimaryPhoneType":"c","PriorGroup":"Advances in Science","Registrar":"Diane Cohan","RegistrarAddress":"30810 Marseille Way, Westlake Village, CA 91362","RegistrarEmail":"cohan.diane@gmail.com","RegistrarPhone":"(301) 385-9102","SecondaryPhone":"","SecondaryPhoneType":"","SortName":"Cohan, Steven","ValidTo":"2025-06-30T00:00:00Z","Vp":"Alvira Klain","VpAddress":"6619 Daryn Dr., West Hills, CA 91307","VpEmail":"etty10@hotmail.com","VpPhone":"(818) 399-4960","Weeks":"2"},
{"Category":"Miscellaneous","CkNo":"7029","DayOfWeek":"Wednesday","FromSFV":"0","GroupName":"Advances in Science","Grouptype":"w","MaxMembers":"15","MemberAddress":"17455 Flanders St., Granada Hills, CA 91344","MemberCount":"12","MemberEmail":"raven0457@aol.com","MemberName":"Jerry Davidson","MemberSeq":"2","Paid":"2024-06-20T00:00:00Z","Portfolio":"A","Position":"","PrimaryPhone":"(818) 363-9777","PrimaryPhoneType":"c","PriorGroup":"Advances in Science","Registrar":"Diane Cohan","RegistrarAddress":"30810 Marseille Way, Westlake Village, CA 91362","RegistrarEmail":"cohan.diane@gmail.com","RegistrarPhone":"(301) 385-9102","SecondaryPhone":"(818) 384-1432","SecondaryPhoneType":"h","SortName":"Davidson, Jerry","ValidTo":"2025-06-30T00:00:00Z","Vp":"Alvira Klain","VpAddress":"6619 Daryn Dr., West Hills, CA 91307","VpEmail":"etty10@hotmail.com","VpPhone":"(818) 399-4960","Weeks":"2"},

So I believe this proves that the filtering of SelectedGroups to only show the current group selected is working. The report query only includes rows for the group currently being iterated, even though originally 2 groups were selected.

So I think the problem is somewhere in the report generating phase. For some reason, both groups are showing in the report attachment itself.

This logic should totally work, and in fact this is exactly how I’m doing this process in my Access application, which provides the proper output for each group.

I am exporting the latest FDF and it is on the OneDrive folder.

If you want to test this yourselves, please follow these steps:

  1. Go into Setup>Members and Groups>Members and load Mittelman, Ron record. Change the email address to your preferred test email.
  2. Go into Processing>Generate Emails>Study Group Leaders
  3. Go to Attach Reports page, and select Study Group Roster by clicking on it
  4. Go to Include Study Groups page, and select first 2 groups
  5. Go to General page and click Generate Emails action button

Within a minute or 2, you’ll get a message that the emails were sent.

What you should get is 2 emails, one for each group, with a single study group roster attached. What you will get is both study groups on both attachments.

Is this enough information to help you solve the issue? Thanks…

3

Hi Ron,

Thank you for detailing this scenario.

After investigating it, the issue seems to be related to the logic, not an issue in Five.

The best way for you to test it is to run the query that generates the report, passing the same values when the code executes.

Therefore, just before executing the report, check (by logging it) the values you are passing to the execute action, execute the query passing the same values, and you will notice that both study groups are returned, meaning the report is correct, as it will always display the same value returned by the query.

I believe your query should know when to generate data for all groups or only one.

Regards,
Elton S

4

Hi Elton,

Thanks for the quick reply. However, I believe you may be mistaken in your explanation.

First, it is important to know that the report query uses SelectedGroups as one of the join tables, so the output is filtered by SelectedGroups, among other tables.

Take a look at my last message on this thread. The first output demonstrates that I selected 2 study groups. Since this report IS filtered, I need to iterate through the selected study groups. The next 2 outputs above demonstrate that SelectedGroups still has the original 2 groups selected. then I clear selections for all except the first group in the iteration. You can see that currentSelectedGroups was still the original 2 groups, and newSelectedGroups is now only 1 group, Acting Out.

The next output is what the report query, rqStudyGroupRoster returns. It’s important to know that I only included the first 2 records for the group for brevity. The report query actually returns a row for each member of the group.

But you can see that both records are for the single group, Acting Out, if you scroll the output to the left. So the report, since it uses the same query, should only have one group.

Next group iteration, everything is repeated for the new group. So you see currentSelectedGroups is still for Acting Out, like it was the prior group iteration. But more importantly, newSelectedGroups now shows only Advances in Science, as it should.

The report query now shows 2 records, both for Advances in Science. Again, this is only the first 2 records of several, I showed the first 2 only for brevity.

Since I’m showing the report query itself in the iteration, this is what should be on the report also, just one group.

I really think this report should only include a single group for each time it generates the report..

Please advise if you see anything wrong with my analysis.

PS: You can demonstrate how this is supposed to work by using Processing>Generate Reports. Select only the StudyGroupRoster, and select a single study group. when you click Generate button, a roster containing a single study group appears on the screen.

This uses the same tables, queries and code that the mail-merge version uses. you can query the SelectedGroups table to verify that only one of the groups is selected.

I’ve verified that the state of the tables and queries is correct in the automated process. I can only guess that something in the server process is not working properly, unless you can point to something I’m doing wrong.

5

Hi Ron,

My analysis is based on the report with action ‘Roster‘, which is generated by the query rqStudyGroupRoster.

This query receives/expects 2 parameters.

If I follow the steps:

Go into Setup>Members and Groups>Members and load Mittelman, Ron record. Change the email address to your preferred test email.

  1. Go into Processing>Generate Emails>Study Group Leaders

  2. Go to Attach Reports page, and select Study Group Roster by clicking on it

  3. Go to Include Study Groups page, and select first 2 groups

  4. Go to General page and click Generate Emails action button

Then, run this query, passing both parameters; the result will have both groups 'Acting Out‘ and ‘Advances in Science‘. This result is the same as that generated by the report.

image
image1911×919 82.4 KB

Therefore, I don’t see any issue with the report functionality.

Please let me know in case of any misinterpretation.

Regards,
Elton S

6

Thanks for trying Elton,

I guess I’m having an issue clearly explaining the problem. There are 2 different use-cases:

1: Generic emails:
Choose Generate Emails. Choose Generic. Either type email address(es) into the To field, or go to Members page and select 1 or more members. Go to Attach Reports and select Study Group Roster. Include Portfolios already has all selected, just ignore that. Go to Include Study Groups and select the first 2. Go back to General page and click Generate Emails. You should receive an email with Roster attached, including both selected groups.

2: Study Group Leaders emails:
The purpose is to send each study group leader/coordinator their own roster.
Choose the Study Group Leaders record. Go to Attach Reports and select Study Group Roster. Again, ignore Portfolios page. Go to Study Groups page and select the first 2 groups. Go back to General page and click Generate Emails. you should receive 2 different emails, one for the first group and one for the second group.

#2 is the one I’m having issues with. It’s important to understand that the report query includes joins to SelectedPortfolios and SelectedGroups table, so the report will only include Groups or Portfolios that you have selected.

What should happen during processing:

The selected reports are iterated first. if the Reports record’s IsFiltered field is true, that signals the processing to iterate each group in the list of SelectedGroups and generate the report for that group only. Therefore, the SelectedGroups table is first modified so only that group is selected. Then the report is generated. It should only include that group. then next group in SelectedGroups is processed similarly. The SelectedGroups table is updated so only this second group is selected. When the report is generated, it should only include the second group.

The logging I did during processing verified that the SelectedGroups table first included both groups we selected for the emails. Then as processing continues, the logging shows that SelectedGroups was changed so only the first group was selected. The report query logging shows the first 2 records in the report. I could have included all records for each member of the group, but didn’t want to. But you’ll notice that that logging shows both records belonging to the first group. If this query, when run manually by code includes only the one group, then it should also do that when the report itself is being generated. Logging also shows correct data for the second group. Only that group selected and report query only returning records for that group, not both. But when the report is generated, it includes both groups.

I think the problem is because of timing and delays while the server is generating the report. This is why: Not all reports are “IsFiltered=true”. so the process iterates the selected reports and if it finds one that is NOT filtered, it prints normally and includes all groups originally selected.

But if it IS filtered, each group is iterated, the SelectedGroups table is modified, and the call to the report generation is made. After all selected groups have been iterated, and the report generated, the original SelectedGroups (in this case, the first 2) are restored, because the next report may not be “Isfiltered=true”. So after both selected reports are generated (at least the function “thinks” they’ve been generated), the SelectedGroups is restored.

I think the process being called to generate the report on the server is taking so long that by the time it’s actually generated, the SelectedGroups table has already been restore to both groups being selected, and the report query at that time just includes both.

So the real fix (if you agree with my opinion) is to somehow generate the report and wait until done before iterating to the next selected group.

Can It be done to Await that generation before continuing? I don’t know Five enough to answer that. The whole point is to generate in the background, and allow foreground processing to continue.

Please think about this reply and if possible recommend another way to process this without blocking foreground thread. I think, since it’s all being controlled by a server function, it shouldn’t matter if the generating part is Awaited, because the client function doesn’t wait for the server function to finish.

Sorry for the long rant, but I think this is the issue.

7

If I can’t use Await (ChatGPT says there is not a contract, so Await won’t work), how about if I create a staging table for the report data, then create the entire data from the existing query, but this time write it into the staging table (INSERT - SELECT) with the current UserKey and a unique RunKey (GUID) for each report run. Once this data is in the staging table, there should not be a race or latency condition when the report is actually generated. As long as the new report query simply uses the RunKey and UserKey to filter the staging table.

Each report I run will add to the staging table with UserKey and RunKey, but I can add code to clear the staging table of all records for the current UserKey when user logs in.

Can you please advise if this logic will help with the race condition I seem to be having?

If you think it will work, can’t I call executeAction and send report name, UserKey and RunKey as the context?

Thanks…