Automate Emailing Reports

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…

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…

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…

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.

Hi Ron,

We would need access to your FDF.

Kind regards,
Jo

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

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

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

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:

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…