Overview
m-Power External Objects (SQL/RPG/Java) can be invoked from your m-Power applications for many purposes, whether that’s for input validation or performing additional database transactions. One of the most commonly asked questions I have heard when working with clients who possess strong programming backgrounds is “How can I use m-Power to report over a dataset created entirely by my external program?”. This tech blog post will show how to accomplish this. The general steps involved in this process include:
- Register your program as an External Object to m-Power
- Create a Single Row maintainer to invoke the External Object
- Redirect to a retrieval/report over the table your External Object has populated
The overall flow of events when all is completed would be as follows: The end-user will load the maintainer application and fill out any inputs in the form (if necessary) to pass to the program. Once they press the Accept button, the program will run and populate the dataset. Immediately after the user will be automatically redirected to the appropriate retrieval/report application built over the dataset that was populated.
Step 1 – Register your program as an External Object
For this example, I will utilize and register a SQL Stored Procedure (source code here). While my example will utilize a SQL Stored Procedure, you are welcome to create your program in RPG/CL or Java if desired. If you need more information on registering your program to m-Power, please see the following links:
My sample External Object will take in three parameters: A start date, an end date, and a user name. The dates will be inputted by the end-user before the program runs. The user who is triggering the External Object will also be passed to be program, but this is a specific system variable that the end-user need not pass themselves. The current user signed into the m-Power application can be accessed simply by creating a parameter with the description as *USER during the External Object registration process. Whenever the user invokes the External Object, I will use this parameter to pass the username from m-Power and write this to a column in the result set. Not only will this identify who created the records in my result set, but will allow me to later on update or delete and re-insert their records from the table the next time they run the External Object.
Step 2 – Create a Single Row maintainer to invoke the External Object
This maintainer should utilize the “Single Row Web Form” template. The data you select for this maintainer does not matter, as you will not be using the maintainer in the traditional fashion of writing to the selected table; Rather, this maintainer will serve only as a trigger for the External Object whenever the form is submitted. Therefore in Tables/Fields, pick at least one table and then any one field from this table.
If your External Object expects parameters to be supplied at runtime, you may create parameter type calculations for each parameter value you would like to pass the program. In my example, I have created two dates parameter calculations.
Next, add your External Object to this maintainer application. Be sure to map the necessary parameter calculations to the External Object and call it at the location *AFTERACT.
*Note that if you are passing the current user as a parameter, you do not need to map any field to the *USER parameter.
Once the maintainer is built, you will need to go to Edit Properties and set the Skip Database Action property to ‘Yes’. This will ensure no data ever writes back to the selected table this maintainer is built over.
Additionally, if you created parameter calculations, they will by default appear as read-only values in the form when the maintainer is ran. Back in the m-Painter editor, you may use the “Field” option in the m-Painter toolbar to re-insert those parameter calculations into the form page as text inputs and may even incorporate external dropdowns or calendar pickers (if a date) on your parameter inputs as desired.
Step 3 – Redirect to a retrieval/report over the table your External Object has populated
When the user submits the maintainer, the External Object will fire and populate the data into a “results” table. This table being populated by the program must be registered to the data dictionary and will be what you build your new retrieval or report application over. Once this application is created you will need to go back to edit the Single Row Web Form application created in Step 2 and open the Workflow Designer. Within the workflow, create an action to redirect to the created retrieval/report whenever the maintainer is submitted.
After the maintainer is submitted with supplied dates, here is the report application I automatically redirect the end-user to:
Notice in my report image above, I have the username column first in my report as I am using the Secure by Username property to ensure the end-user only sees the records they’ve created in the report output.