MI Transaction in H5 – Part 2

In our previous post, we saw how we can execute MI transaction in H5 using the MIUtiltool utility tool we created. In this post we will be expanding on this tool to include a helper in retrieving output fields on the executed transaction.

Let’s look back on the transaction we’ve executed

let miTrans = new MIUtilTool.Transact("CRS530MI");
miTrans.set("FCOM", userContext.CONO);
miTrans.set("TCOM", userContext.CONO);
miTrans.set("FDIV");
miTrans.set("TDIV");
miTrans.set("FEMN", "CSANTOS");
miTrans.set("TEMN", "Y500");
miTrans.execute("LstEmployee", this.onSuccess, this.onFail);

Running the code above will give us an Object. The records we need are stored on an array named MIRecord. MIRecord’s value are made up of object, that has property named NameValue.  This NameValue is another array for each output fields. In short the structure of the output is Object>Array>Object>Array. h5MIOutput

We need to traverse this should we want to retrieve a specific value. But i don’t want to do that every single time i perform an MI transaction. Ideally, i would like to be able to query the fields with this syntax: result.getField("EMNO");

And it will return me an array of “EMNO” or a string if the output only consist of one record. As such, I created a helper class named Output that does the work for me. We will be placing this class inside our MIUtilTool. It will only be consist of one method:setField() that will accept the ‘output field’ as the parameter. We would need to pass the ‘result Object’ when creating an instance of this class. A sample usage will look like this:

let out = new MIUtilTool.Output(result);
console.log(out.getField("EMNO"));

This will result to:
h5MIUtilOutput.gif

And here is our updated MIUtilTool:

namespace MIUtilTool {
   export class Transact {
        private url: string;
        private paramsUrl: string;
        private params: any = {};

        constructor(program: string) {
            this.url = '/execute/' + program + '/';
            this.paramsUrl = '';
        }

        public set(field: string, value?: userContext | string): Transact {
            this.params[field] = value;
            return this;
        }

        public execute(transaction: string, onSuccess, onFail, constraints ?: IApiConstraints, excludeEmpty: boolean = false): void {
            this.url += transaction + ';metadata=true' +
                    ';cono=' + userContext.CurrentCompany +
                    ';divi=' + userContext.CurrentDivision +
                    ';excludempty=' + excludeEmpty;

            if (constraints) {
                if (constraints.maxRecords) {
                    this.url +=  ';maxrecs=' + constraints.maxRecords;
                } else {
                    this.url +=  ';maxrecs=999999';
                }

                if (constraints.outputFields) {
                    this.url += ';returncols=' + constraints.outputFields.toString();
                }
            }

            this.url += '?';

            $.each(this.params, (key, value) => {
                this.url += (key + '=' + value + '&');
            });

            this.url = this.url.slice(0, -1);

            ScriptUtil.ApiRequest(this.url, onSuccess, onFail);
        }
    }

    interface IApiConstraints {
        maxRecords?: number;
        outputFields?: Array<string>;
    }

    export class Output {
        private result;
        private recLength: number;
        private fieldLength: number;

        public getField(field: string, single: boolean = true) : any {
            if (this.recLength === 1 && single) {
                let fieldValue: string;
                this.result.MIRecord[0].NameValue.some(function(val, ind) {
                    if (val.Name === field) {
                        fieldValue = val.Value.trim();
                    }
                    return val.Name === field;
                });
                return fieldValue;
            }

            let fieldArray: string[] = [];
            for (let i = 0; i < this.recLength; i++) {
                this.result.MIRecord[i].NameValue.some(function(val, ind) {
                    if (val.Name === field) {
                        fieldArray.push(val.Value.trim());
                    }
                    return val.Name === field;
                });
            }
            return fieldArray;
        }

        public createArrayObjects(): any {
            let fieldArray: any[] = [];
            for (let i = 0; i < this.recLength; i++) {                 let test = {};                 this.result.MIRecord[i].NameValue.forEach(element => {
                   test[element.Name] = element.Value.trim();
                });
                fieldArray.push(test);
            }
            return fieldArray;
        }

        constructor(result) {
            this.result = result;
            this.recLength = 0;
            this.fieldLength = 0;
            if (result.MIRecord) {
                this.recLength = result.MIRecord.length;
                this.fieldLength = result.MIRecord[0].NameValue.length;
            }
        }
    }
    MIUtilTool.Init = function(){};
}

Update 20170413: I have updated the script above to handle MI Outputs of large records. By default the MI REST service will only return a maximum of 500 records if the number of desired records is not indicated on the request.

You can download the complete typescript file here or the converted javascript here.divider2

Advertisements

MI Transaction in H5 – Part 1

To perform an MI Transaction in H5, we use ScriptUtil.ApiRequest(). It accepts three parameters: the url for the transaction, the callback when transaction succeeds and the callback when it fails. A sample usage of this would be like this

let userId = userContext.USID;
let url = "/execute/MNS150MI/GetUserData?USID=" + userId;
ScriptUtil.ApiRequest(url, this.onSuccess, this.onFail);

Pay attention to the url string. Personally, i don’t find this to be intuitive at all, rather it is a mistake-prone line once the MI transaction used accepts a lot of input parameters. Take for example CRS530MI’s LstEmployee transaction, we will be setting up six input paramaters, FCOM, TCOM, FDIV, TDIV, FEMN, TEMN. The url string for this would look something like this:

url = "/execute/CRS530MI/LstEmployee?FCOM=" + userCono + "TCOM=" + userCono + "FDIV=TDIV=FEMN=CSANTOS&TEMN=Y500"; 

Of course, you can break this big string and build it one part/field at a time, like so:

url = "/execute/CRS530MI/LstEmployee?FCOM=" + userCono;
url += "TCOM=" + userCono
url += "FDIV=TDIV=FEMN=CSANTOS&TEMN=Y500" ;

But still, i don’t find this desirable at all. It is very easy to misplace “&” or “=” sign in that big chunk of string. In ISO’s scripting, there is a class called MIRecord() that helps you build your record string.

Since i can’t see a class similar to MIRecord(), i created my own simple utility script named, MIUtilTool. To use this utility class, you need first to create an instance of the transact class, passing the MI program you are going to use. The transact itself has two methods, set and execute.

The set method accepts two parameters, the first is the name of the field and the second is the value of the field, if the second parameter is not provided it will default to blank. The execute method will call the ScriptUtil.execute. It accepts four parameters, the first is the name of the transaction and the next two will be the callback for when the transaction succeeds and when it fails, the fourth parameter is optional, it accepts an object with property of maxRecords, a number and outputFields, an array of string

  • maxRecords – determines the number of output record to retrieved
  • ouputFields – an array of string that tells what are the fields needed to be retrieved.

A sample usage will look something like this:

let miTrans = new MIUtilTool.Transact("CRS530MI");
miTrans.set("FCOM", userContext.CONO);
miTrans.set("TCOM", userContext.CONO);
miTrans.set("FDIV");
miTrans.set("TDIV");
miTrans.set("FEMN", "CSANTOS");
miTrans.set("TEMN", "Y500");
miTrans.execute("LstEmployee", this.onSuccess, this.onFail);

And here is the complete code for the MIUtilTool

module MIUtilTool {
    export class Transact {
        private url: string;
        private paramsUrl: string;
        private params: any = {};

        constructor(program: string) {
            this.url = '/execute/' + program + '/';
            this.paramsUrl = '';
        }

        public set(field: string, value?: userContext | string): Transact {
            this.params[field] = value;
            return this;
        }

        public execute(transaction: string, onSuccess, onFail, constraints ?: IApiConstraints, excludeEmpty: boolean = false): void {
            this.url += transaction + ';metadata=true' +
                    ';cono=' + userContext.CurrentCompany +
                    ';divi=' + userContext.CurrentDivision +
                    ';excludempty=' + excludeEmpty;

            if (constraints) {
                if (constraints.maxRecords) {
                    this.url +=  ';maxrecs=' + constraints.maxRecords;
                } else {
                    this.url +=  ';maxrecs=999999';
                }

                if (constraints.outputFields) {
                    this.url += ';returncols=' + constraints.outputFields.toString();
                }
            }

            this.url += '?';

            $.each(this.params, (key, value) => {
                this.url += (key + '=' + value + '&');
            });

            this.url = this.url.slice(0, -1);

            ScriptUtil.ApiRequest(this.url, onSuccess, onFail);
        }
    }

    interface IApiConstraints {
        maxRecords?: number;
        outputFields?: Array<string>;
    }
}

Next post, we will be extending MIUtilTool to handle the output of the MI Transaction.

Update 20170413: Constructor and method set are now chainable, which means that the above example can now be written like so:

new MIUtilTool.Transact("CRS530MI")
    .set("FCOM", userContext.CONO)
    .set("TCOM", userContext.CONO)
    .set("FDIV")
    .set("TDIV")
    .set("FEMN", "CSANTOS")
    .set("TEMN", "Y500")
    .execute("LstEmployee", this.onSuccess, this.onFail);

or if you prefer a single line:

new MIUtilTool.Transact("CRS530MI").set("FCOM", userContext.CONO).set("TCOM", userContext.CONO).set("FDIV").set("TDIV").set("FEMN", "CSANTOS").set("TEMN", "Y500").execute("LstEmployee", this.onSuccess, this.onFail);

divider2

How to set and get field values in H5

Since H5 is based on HTML and javascript, there are a couple of  ways in getting and setting the value of a field in H5. We can do this via ScriptUtil, IInstanceController, Jquery or via plain old javascript. This is for demonstration purposes since I would assume that Infor’s Product Development team will not advise in using the other three options other than ScriptUtil.

For context, we will be using MMS001/E‘s fields, specifically ITNE, ITDS, FUDS, and DWNO. I have put an annotation to show where these fields are located.

Image 995

We will place these script to see the different ways in getting the field values in H5.

let MMITNE = ScriptUtil.GetFieldValue("MMITNE");
let MMITDS = controller.GetValue("MMITDS");
let MMFUDS = $("#MMFUDS").val();
let MMDWNO = document.getElementById("MMDWNO").value;

As you can see above, MMITNE will be retrieve via ScriptUtil, MMITDS via IInstanceController, MMFUDS via Jquery and MMDWNO via plain javascript. We will write the output to console and see if the values are correctly retrieved

Image 996.png

 

Likewise, changing these fields value can be achieve in different ways. I added a button in MMS001/E that will change these fields value once it is clicked.

Here is the script for the button click event

button.click(() => {
      ScriptUtil.SetFieldValue("MMITNE","changed by ScriptUtil");
      this.controller.SetValue("MMITDS", "changed by controller");
      $("#MMFUDS").val("changed by Jquery");
      document.getElementById("MMDWNO").value = "changed by Javascript";
});

Showing it in action:

h5setandgetfieldvalue

And here is the complete script. Please note that this is created in TypeScript

class SetandGetValue {
    private scriptName = "SetandGetValue";
    private controller: IInstanceController;
    private log: IScriptLog;
    private contentElement : IContentElement;

    constructor(args: IScriptArgs) {
        this.controller = args.controller;
        this.log = args.log;
    }

    private addButton() {
        const buttonElement = new ButtonElement();
        buttonElement.Value = "Change Fields!";

        const button = ControlFactory.CreateButton(buttonElement);
        button.Position = {
            Width: 120,
            Top: 110,
            Left: 300
        };

        button.click(() => {
            ScriptUtil.SetFieldValue("MMITNE","changed by ScriptUtil");
            this.controller.SetValue("MMITDS", "changed by controller");
            $("#MMFUDS").val("changed by Jquery");
            document.getElementById("MMDWNO").value = "changed by Javascript";
        });

        this.contentElement.Add(button);
    }

    public run(): void {
        const controller = this.controller;
        this.contentElement = this.controller.GetContentElement();
        this.log.Info("...Script running");

        let MMITNE = ScriptUtil.GetFieldValue("MMITNE");
        let MMITDS = controller.GetValue("MMITDS");
        let MMFUDS = $("#MMFUDS").val();
        let MMDWNO = document.getElementById("MMDWNO").value;

        console.log("via ScriptUtil: " + MMITNE);
        console.log("via IInstanceController: " + MMITDS);
        console.log("via Jquery: " + MMFUDS);
        console.log("via plain javascript: " + MMDWNO);

        this.addButton();
    }

    /**
     * Script initialization function..
     */
    public static Init(args: IScriptArgs): void {
        new SetandGetValue(args).run();
    }
}

divider2

Proper Formatting in MAK

You might have noticed that when modifying standard M3 programs, you often encounter misalignments when you try to add your custom code. The reason being is that standard M3 codes are using their default formatting rules while the customized codes will be using the current formatting setup on the Eclipse IDE.

M3 Standard Format

M3 programs are created using spaces with as the default width for indentations. You can check this by opening any standard M3 programs and activating the toolbar ‘Show Whitespace Characters’
Image 425.png
Once activated your editor will show spaces, tabs and other whitespace characters.
Image 426
 represents spaces •»  represents tabs

Common Problems with wrong formatting

Inserting new codes
prob_1

Copying and pasting codes
prob_2

Auto-Formatting

Using the default auto formatting features of Eclipse will most likely mess the structure of the code.
prob_3

Copying the standard M3 Formatting

Eclipse allows users to define their own custom code formatting. We will try to replicate the formatting that standard M3 code uses.

In Eclipse navigate to Windows>Preference>Java>Code Style>Formatter and then click New... to create our new custom formatting.

Image 430

Click OK. A new window will appear. Here we can set a different rules on how Eclipse will handle code formatting. At the very least we only need to tweak the Tab policy, Intentation size and Tab size to correct the common misalignments.

Image 431

However if you want to be detailed on how Eclipse formats your codes you can explore the different tabs on the Fomatting window. I have exported my settings (makformat.epf), which you can downlod here, so that you can readily use it on your Ecplise. You can import this by going to File>Import. On the dialog box select Preferences under General. Locate the preference file (makformat.epf).

Image 432

Import all

I would recommend in having the Import all checkbox checked. However, if you have a custom preference set on your eclipse it will overwrite all this. In this case you can just choose the 3 checkboxes under the Import all, but this will not include setup that are not part of this 3 (e.g. under General>Editor and Java>Editor).

makformat.epf

Below are the areas makformat.epf will affect:

General > Editors > Text Editors
  • Set ‘Displayed tab width’ to 3
  • Checked ‘Insert spaces for tabs’
  • Configure visibility for ‘Show whitespace characters

Java > Code Style > Formatter
Click the Edit... button to show the detailed setups for the Active format profile (Eclipse-MAK). Note that the setup here are what works for me and are based on the results of my testing that i think are the closest to how M3 codes are formatted. You can of course define your own rules or edit the parts that works better for you.

Java > Editors > Save Actions
Activates miscallaneous actions when saving your codes such as removing all trailing spaces.

Java > Editors > Templates
Template are bits of code that has defined placeholder. You can activate a template by typing the template name and then pressing  CTRL+Space

tem_1

    Below are the included M3 templates that might help in speeding your coding

  • alpnum – Alphanumeric to Numeric COMNUM
  • chn – M3 CHAIN
  • chnl – M3 CHAIN_LOCK
  • cln – M3 clearNOKEY
  • comdat – COMDAT
  • mstring – MvxString declaration
  • mwhile – M3 While loop
  • numalp – Numeric to Alphanumeric COMNUM
  • pmet – M3 new method
  • reade – M3 READE
  • readel – M3 READE_LOCK
  • redpe – M3 REDPE
  • redpel – M3 REDPE_LOCK
  • setgt – M3 SETGT
  • setll – M3 SETLL

Activate ‘Show Whitespace Characters’ toolbar
White characters will be visible by default

Better Action Markings

There is a tool in eclipse that will greatly help in properly formatting your action markings called ‘Block Selection Mode’

Block Selection Mode

You can toggle ‘Block Selection Mode’ by clicking the toolbar icon or pressing Alt+Shift+A

Image 433

Once active, the mouse pointer will change to a crosshair and sometimes eclipse fonts will change, too (depending on the version and current setup of your eclipse).

Usage

Block selection mode allows you to create multiple cursors at the same time, this means you only need to do the action once for multiple duplicate actions (e.g. putting action markings)
bam_1

You can also use this tool if you need to remove a block of code
bam_2

divider2.png

Showing all values of a Selected row in subfile

Here is a simple script to show all the field values of a selected row in a subfile along with the name of each fields. We will be using the class PanelState to get the instance of the subfile.


import System;
import System.Windows;
import System.Windows.Controls;
import MForms;

package MForms.JScript {
    class GetValueList {
    	var listControl;
        public function Init(element : Object, args : Object, controller : Object, debug : Object) {

        	//	Get an instance of the subfile
            listControl = controller.PanelState.ListControl;

            //	Check if a row is selected
            if (listControl.ListView.SelectedItems.Count > 0) {
            	/*
            	 * Get the name of the columns by the property "ColumnNames" of
            	 * the listControl
            	 */
	            for (var fieldName in listControl.ColumnNames) {
	            	/*
	            	 * "GetColumnValue" will retrieve the value of the field.
	            	 */
	            	debug.WriteLine(fieldName + ": " + listControl.GetColumnValue(fieldName));
	            }
            }

        }
    }
}

A sample output in CRS610/B will look like this:

OKCUNO: CONTRACT
OKCUNM: Contract Internal Payer
OKPHNO: TEL NO 1
OKSTAT: 20
OKWHLO: B10
OKCUTP: 7

divider2

ODBC Connection on MOM

Most of the time when the client wants to modify certain reports, they require fields that are not readily available in the streamfile. Personally for me, the best approach in doing so is to add the needed fields via MAK modification. This is the best method you can do to achieve this. But sometimes, clients request to do a direct SQL connection from the StreamServe instead, probably to avoid the additional license payment for MAK modifications or for some other reasons. There are also StreamServe consultants that are not familiar with MAK modifications and thus results to ODBC Connection.

Here i will try to show how to setup the environment in order to do a direct SQL statements from StreamServe.

1. Create an ODBC driver

In MOM Server, Go to Start > All Programs > Administrative Tools > ODBC Data Sources.
For 64-bit computer there will be two ODBC Data Sources. You only need to add driver for 64-bit.

In ‘System DSN’ tab, click ‘Add’ to create a new driver.
Image 974

In the pop up dialog box, select the correct odbc driver.
Image 968

Fill up the correct server where the database is located.
Image 969

In the next window, select ‘SQL authentication’ and input the correct userID and password fort the sql server.
Image 970

Clicking ‘Next’ will authenticate the the userID and password. If everything is ok the next window will show. Else, a popup window will appear stating that the connection failed.
Image 971

Click ‘Next’ on all remaining windows until a summary popup window is shown.
Image 972

Check that the new driver was created.
Image 975

Note: You need to do all this for every environment of MOM (DEV, TST, PRD, etc.)

2. Create function file in Global Resource

Add a new function file in Global Resource of any StreamServe project. In this example I will be using APS141PF. The name of the function file will be MOD.fcn. Inside this function file, declare a function for OBDC login. I will be naming this function, ODBClogin().


/**
 * This Function will accept the PORT number as its parameter
 */

func ODBClogin () {
 log(9,"################# PORT: " + #1 + " #################");
 // DEV
 if (#1 = "33200") {
  $ok=odbcconnect("M3","PHMAVWM3SNDBX2_DEV","mdbusr","mdbuser");
  $db="M3FDBTRN.MVXJDTA";
 }

 // TST
 if (#1 = "33300") {
  $ok=odbcconnect("M3","PHMAVWM3SNDBX2_TST","mdbusr","mdbuser");
  $db="M3FDBTST.MVXJDTA";
 }

 // PRD
 if (#1 = "33400") {
  $ok=odbcconnect("M3","PHMAVWM3SNDBX2_PRD","mdbusr","mdbuser");
  $db="M3FDBPRD.MVXJDTA";
 }

 log(9,"################ db connection = "+$ok + " #################");
 log(9,"################ data base = "+$db + " #################");
 Return $ok;
}

Once the function file is created we need to execute the ODBClogin() in the preprocess phase of the runtime. This is done in the ‘Before Script‘ of the ‘Runtime’.

Image 976

Add the codes below:

$phase=preproc();
if ($phase = "0") {
 ODBClogin($mvx_port);
}

3. Update start.arg

To test if our MOD.fcn is working we need to export the project and deploy it in Control Center. After deploying the project file, we won’t be able to start the service because ‘start.arg‘ has no reference to the MOD.fcn we created. So open the ‘start.arg‘ and add the MOD.fcn. The service will now be able to start.

Drop an APS141PF streamfile in the input folder or test via ISO process. Either way, once an APS141PF is process, you will be able to see in Control Center logs if the ODBC connection is correct. Here is what you will see if everything is setup correctly.


0516 182312 (1155) 4 ################# PORT: 33200 #################
0516 182312 (1150) 4 ODBC connection: Open new ODBC connection to PHMAVWM3SNDBX2_DEV
0516 182312 (1155) 4 ################ db connection = 1 #################
0516 182312 (1155) 4 ################ data base = M3FDBTRN.MVXJDTA #################

4. Test the sql connection

Once the ODBC connection is established, we need to test if we can retrieve data with database schema we’ve setup in MOD.fcn, in this case “M3FDBTRN.MVXJDTA” for DEV.
In the ‘Script Before‘ of the Body of ‘APS1410H-Letter‘, i’ve placed this sql statement to retrieve the Address Line 1 of the Company/Division we are using.


$SQL_Company_Address1 = "SELECT CCCOA1 FROM "+$db+".CMNDIV where CCCONO = "+ &0HZZCONO +" and CCDIVI = '" + &0HZZDIVI + "'";
ODBCgetone("M3",$SQL_Company_Address1,$Company_Address1);

log (9, "######### Company Address: " + $Company_Address1 + " ############");

Export and deploy again. And check the logs in ControlCenter if the SQL statement above is correct.


0516 182315 (5216) 4 Entering PageOUT process: APS1410H-Letter
0516 182315 (2049) 3 ODBC: Executed: SELECT CCCOA1 FROM M3FDBTRN.MVXJDTA.CMNDIV where CCCONO = 001 and CCDIVI = 'AAA'
0516 182315 (1155) 4 ######### Company Address: MNS100/E Address Line 1 ############

Check in ISO that this is the correct value

Image 977.png

divider2