Automate Emailing Reports

How do I?
21

Thanks for answering my questions. I just need a bit of clarity here.

Keeping in mind that I need to print the reports on demand client side, and also need to generate and email from server side, and also that I will eventually have several reports:

Is a screen variable attached to each report the most efficient way to specify the user key value?

I’m still not sure about the difference between using a process or using a form to generate the reports. In my Generate Reports form, it works just fine. Can you please elaborate when one is better than the other?

For the automated process, I sometimes have to email reports to our board members, and sometimes have to email reports to our study group leaders. There are tables which define who are the study group leaders, and also who are the board members are and which reports they each receive. I was hoping to have a form similar to my Generate Reports form, but instead of generating a report client side, it could do the mail merge and email those reports server-side. On this form, I could have a choice between board reports and study group reports, with choices for each of those types defining which reports to send and/or which board officers to send to.

It seems like this is fairly complex for a process but maybe not for a form. I hope you can help me with these questions. Thanks…

22

I’m running into problems here, because my report query has 2 parameters, UserKey1 and UserKey2. They are both set to the same thing, five.variable.UserKey.

Question 1 (less important):
I’m using 2 parameters because in the query there are 2 question marks, both wanting the UserKey value. Is this the proper way to do this?

Question 2 (more important):
The report query parameters, as mentioned, use five.variable.UserKey. This works fine for client-side on-demand report. It does NOT work for server-side process to mail-merge the report. Putting in a screen variable for the report is problematic, because I don’t want another screen where I need to click the “go” button. It is unclear if this would even work in a server-side process, since there is no screen to show screen variables. Would I be better off creating a table for the report, then truncating/filling the table from a client-side function, then basing the report on the table rather than a simple query? If so, how would I load the table in the process? Is the process itself running client-side? If so, could it load the table using the five.variable.UserKey before invoking a server-side mail merge? OR, is there a better way to setup the query parameters so if running server-side it loads the correct parameter values (perhaps setting up a context in the client-side part first)?

sorry this seems to be rambling, but I just need to be able to run a given report either on-demand client-side or in a mail-merge server-side. Thanks…

23

I have modified my report as instructed. I added a screen field called UserKey. I removed the default value of {{five.variable.UserKey}}. I created a function called SetUserKey which contains

five.field.UserKey = five.variable.UserKey

This is called from the report’s On Show event. So before the report is rendered, the screen field screen comes up and I can see the actual UserKey value. I had thought it would populate automatically if the screen field name agreed, but it did not, so I had to add the SetUserKey function to populate that screen field.

In any case, even though the screen field is populated, and even though the query for the report has 2 parameters (UserKey1 and UserKey2) which are both populated with {{five.field.UserKey}} at design-time, the report now has no data and comes up blank. NOTE: I need 2 parameters because the query uses 2 ?'s, both wanting the same user key.

I can’t figure out what is going wrong. It used to work fine using {{five.variable.UserKey}} for the parameter values. I only added the screen field for UserKey because you told me to do it so it would still work server-side. This is why I went to the field instead of the variable. Now nothing works.

UPDATE: If I switch the report query parameters back to a value of {{five.variable.UserKey}}, the report generates fine. The only reason I went to {{five.field.UserKey}} was on your instructions so it would also work server-side.

Thanks…

24

The last time somebody replied to this post was 10 June. I have replied 3 times since then. It is now 5 July. Can somebody please respond to this topic? I can’t get my application working if I can’t reliably generate reports both on client-side and using server-side mail merge.

25

Hi Ron,

We would need access to your FDF.

Kind regards,
Jo

26

Hi Ron,

You are correct, for reports that are being directly rendered from the menu, the field values are sent as variables, ie {{five.variable.xyz}}, we refer to these as screen fields and reference them as variables, where as a form field on a form page are referred to as fields, currently this is how they are interpreted in Five as the report page is “not” a form. We will give this further thought on how we could this easier to work with going forward, but for now you will need to refer to them as variables.

Kind regards,
Jo

27

Thanks for the reply Jo. What you say makes sense but it’s a problem because variables aren’t available to the server. There report is based on a query which has parameters that use variables. Catch 22. Is there any way to supply the query parameters at runtime instead of design time?

Ron Mittelman

28

Hi Ron,

Yes, you can, have a read of this thread:

How to attach a server-side function to a form-page button? - How do I? - Five Community Forums

As you read through the thread, you will see how it is done.

I have added a link to get some more information on executeAction() too.

executeAction() | Five | Low-Code For Real Developers

Kind regards,
Jo

29

Thanks for sending this response, Jo.
Not sure if I understand the other example, but here is what I did.

Snippet of my report query (rqStudyGroupRoster):

WHERE
  (`StudyGroups`.`Active` = true)
AND
  (`SelectedGroups`.`IsSelected` = true)
AND
  (`SelectedGroups`.`UserKey` = ?)
  -- (`SelectedGroups`.`UserKey` = '10000000-0000-0000-0000-000000000001')
AND
  (`SelectedPortfolios`.`IsSelected` = true)
AND
  (`SelectedPortfolios`.`UserKey` = ?)
  -- (`SelectedPortfolios`.`UserKey` = '10000000-0000-0000-0000-000000000001')

Don’t worry about the commented parts of the where clause. They are simply for testing my query in design-time, by commenting the lines with the “?”'s and un-commenting the lines with the hard-coded UserKey. This returns proper data by the way.

Here is the parameter tab values:

image
image1842×370 18.6 KB

Here is the function code that calls the report:

function GenerateReport(five, context, result)  {
    five.showMessage('userKey: ' + five.field.UserKey);
    five.selectAction(five.field.ReportID, {'UserKey' : five.field.UserKey});
    return five.success(result);
}

Please note, I used to use {{five.variable.UserKey}} for both parameters. It used to work, but now I get an empty report, but I do see the message box popping up with the correct key value.

If I understood the forum topic you pointed me to, it seems that the report can use {{five.field.whatever}} without needing to have a screen field, because of the context sent to the report itself. Is this correct?

Am I correct that if I use the “?” multiple times in the query, I need to have the same number of parameters defined in the Parameters tab?

Would I be better off using a left join on the Selected… tables instead of multiple AND’s? That might reduce the need of multiple parameters, right?

So it “seems” like I did this similar to the example you pointed me to, but it doesn’t seem to work, the report renders blank.

Thanks…

30

I followed your suggestions based on the article you pointed me to, but it still doesn’t work. Can you please address this so I can continue development on my project? It’s been over 2 weeks.

31

Now it’s been over a month. Can you please help with this?

32

Hi Ron,

I have added answers to your questions.

If I understood the forum topic you pointed me to, it seems that the report can use {{five.field.whatever}} without needing to have a screen field, because of the context sent to the report itself. Is this correct?

You are pretty much correct, except you need to use {{five.variable.whatever}} as the parameter values, the context values will be passed in as five.variable.

Am I correct that if I use the “?” multiple times in the query, I need to have the same number of parameters defined in the Parameters tab?

You are correct, the number of “?” must match the number of parameters.

Would I be better off using a left join on the Selected… tables instead of multiple AND’s? That might reduce the need of multiple parameters, right?

The query you design is entirely up to you, and ultimately the number of “?” you need for you query, simply must match the parameter count.

Kind regards,

Jo

33

Thanks for replying Jo.

The last 2 questions/answers are straightforward and understood. I’m having trouble with the first question and answer. Here’s my GenerateReport code:

function GenerateReport(five, context, result)  {
    five.showMessage('userKey: ' + five.variable.UserKey);
    five.selectAction(five.field.ReportID, {'UserKey' : five.variable.UserKey});
return five.success(result);

So you can see it is using the variable now, but the report comes up empty, even though it should show 2 study groups.

Please know that my intent is to be able to run the report both on-demand in the UI, and also as part of a mail merge process. But for now, I can’t even run it on-demand anymore. you see that the function above puts the proper value into the context.

The report itself, from the OnShow events calls SetUserKey. this may be my issue.

function SetUserKey(five, context, result) {
five.field.UserKey = five.variable.UserKey;
return five.success(result);
}

I tried changing the statement to five.field.UserKey = context.UserKey, but that doesn’t work either.

34

Hi Ron,

My Name is Elton Santos, and I am part of the Five team.

From your previous image, I can see that your Query’s parameters are defined as ‘UserKey1’ and ‘UserKey2’.

However, the function ‘GenerateReport’ only passes one parameter, and it does not match the property name (When passing a parameter to an action, it is treated as a property. Therefore, the name must be the same).

Could you please try:

five.selectAction(five.field.ReportID, {‘UserKey1’ : five.variable.UserKey, ‘UserKey2’ : five.variable.UserKey});

Instead of:

five.selectAction(five.field.ReportID, {‘UserKey’ : five.field.UserKey});

Regards,

Elton Santos

35

Thanks for the answer, Elton.

I have made the replacement you suggested, and still the report does not generate, I just get a blank page. Here is the new function:

function GenerateReport(five, context, result)  {
    five.showMessage('userKey: ' + five.variable.UserKey);
    five.selectAction(five.field.ReportID, {'UserKey1' : five.variable.UserKey, 'UserKey2' : five.variable.UserKey});
    // five.selectAction(five.field.ReportID, {'UserKey' : five.variable.UserKey});
    return five.success(result);
}

The query parameters do in fact reference five.field.UserKey1 and .UserKey2.

I was advised earlier that sending a context object in the selectAction command would cause those fields to be populated with the proper values, and the parameters would be filled in dynamically.

Can you please advise what you think I’m doing wrong?

PS: My account renewal date is coming up very soon, and I had decided to not renew, because after a year of trying I can’t get the report to print properly. If you can answer this fairly quickly, I will reconsider my decision and renew for another year. But we need to figure this out prior to renewal date of 10-October.

I need to be able to generate the same report both client-side and server-side via mail merge. There must be a way to make this work, right?

Thanks…

36

Hi Ron,

Could you please share the latest version of your application? You can create a download link and share it with me via elton@five.co.

Meanwhile, I will be testing your scenario on a previous version.

Thank you.
Elton S.

37

Thanks Elton. I have copied the FDF file to my OneDrive shared FDFs folder. I’ve sent a share link to that folder via email.

38

Hi Ron,

Could you please try what Elton has sent you via email, he has it working in his account so if it does not work for you, he will be happy to show you.

In regard to it not working before, we have made a change so it will work how you need it to, and it is in the upgrade we have performed on your account.

Let us know how you go as Elton is happy to assist further if needed.

Kind regards,

Jo

39

Yay! This is finally working. Thanks so much for your help. We can close this issue and move on to others. Thanks again!!!

Now that this works, I have some questions about automating multiple reports to be emailed to multiple recipients. please advise if I need a new topic. The name of this topic seems to be appropriate for the new questions. thanks…

40

Hi Ron,

Could you please have a look at this question; it may have the answer you are looking for.

If you still have any questions, please add a new topic with as many details.

Regards,
Elton S