M3 MI Data Import for Web Services (MDIWS)

I just learned the existence of the M3 MI Data Import for Web Services (MDIWS), which is the equivalent of the traditional M3 Data Import (MDI) but using the M3 API REST/JSON endpoint instead of the traditional proprietary binary endpoint.

The tool and documentation are straightforward, so I will just promote it here with some screenshots:

Here are the executable and documentation:

Here is a sample semicolon separated CSV file with data, no header:

Here is the tool in action:

As a reference, here is the traditional M3 Data Import tool:

Thanks Björn P. for the tool.

That’s it!

Easy export/import M3 with JavaScript 6

Last week I had to copy several M3 Supplier CRS620 records, from one company CONO into another company, and I found this elegant solution using JavaScript 6.

Classic approach

Traditionally, I would have used M3 API or SQL to export the records to a CSV file in my computer, change the company number, and use M3 Data Import (MDI) to import the CSV into the other company. But the API LstSuppliers does not output all the fields, so I would have had to use GetBasicData, record by record. It is trivial, but it is tedious because of the many steps and tools involved, and it is antiquated.

A new approach

Instead, I have been wanting to learn JavaScript 6 (ECMAScript 6th Edition), so I challenged myself to learn this new solution using the M3 API REST web service and ES6.

Using ES6 is elegant because the result is very concise (only two lines of relevant code), and it is very expressive using: arrow functions (the new anonymous functions), map/reduce and array comprehension, Promises (the new deferred; chaining, fluid programming), and the fetch API (the new XMLHttpRequest).

Here is the source code in ES6:

var baseUrl = "https://hostname:21108/m3api-rest/execute/CRS620MI/";
["1069112004", "1069112005", "1069112006", "1069112007", "1069112008", "1069112009", "1069112010"]
    .forEach(SUNO => GetBasicData(930, SUNO)
        .then(data => AddSupplier(702, "USA", data)));

function GetBasicData(CONO, SUNO) {
    var url = baseUrl + "GetBasicData;cono=" + encodeURIComponent(CONO) + "?SUNO=" + encodeURIComponent(SUNO);
    return fetch(url, {
        credentials: 'same-origin',
        headers: { 'Accept': 'application/json', }})
        .then(response => response.json());
}

function AddSupplier(CONO, DIVI, data) {
    var url = baseUrl + "AddSupplier;cono=" + encodeURIComponent(CONO) + ";divi=" + encodeURIComponent(DIVI) + "?";
    data.MIRecord[0].NameValue.map(o => url += "&" + o.Name + "=" + encodeURIComponent(o.Value));
    return fetch(url, {
        credentials: 'same-origin',
        headers: { 'Accept': 'application/json', }});
}

/*
optional:
console.log([data.MIRecord[0].NameValue.find(o => o.Name === "CONO").Value, data.MIRecord[0].NameValue.find(o => o.Name === "SUNO").Value]);
console.log([response.url.split("?")[1].split("&").find(pair => pair.split("=")[0] === "SUNO").split("=")[1], data.Message]);
*/

We have to run this in the JavaScript console of a browser that supports ES6 and the fetch API, such as Google Chrome, and we have to login to the Infor ION Grid Management Pages to have an authenticated session.

Here is the result:
result1

And we can inspect the request/responses in the network tab:result2

That’s it!

Thanks for reading. Leave us a comment in the section below. And if you liked this, please subscribe, like, share, and come write the next blog post with us.

M3 API protocol dissector for Wireshark

Have you ever needed to troubleshoot M3 API calls in Wireshark? Unfortunately, the M3 API protocol is a proprietary protocol. Consequently, Wireshark does not understand it, and it just gives us raw TCP data as a stream of bytes.

Abstract

I implemented a simple protocol dissector for Wireshark that understands the M3 API protocol, i.e. it parses the TCP stream to show the M3 API bytes in a human-readable format, with company (CONO), division (DIVI), user id (USID), and MI program (e.g. CRS610MI). The dissector is currently not complete and only parses the MvxInit request phase of the protocol.

Reverse engineering

I reverse engineered the M3 API protocol thanks to MvxLib, a free and open source client implementation of the protocol in C# by Mattias Bengtsson (now deprecated), and thanks to MvxSockJ, the official and closed-source client implementation of the protocol in Java (up-to-date).

3 2

MvxInit

The MvxInit phase of the protocol is the first phase of the protocol for connection and authentication, and it has the following structure:

struct MvxInit
{
   struct request {
      int size;
      char Command[5]; // PWLOG
      char CONO_DIVI[32];
      char USID[16];
      char PasswordCiphertext[16]; // password ^ key
      char MIProgram[32]; // e.g. CRS610MI
      char ApplicationName[32]; // e.g. MI-TEST
      char LocalIPAddress[16];
   };
   struct response {
      int size_;
      char message[15];
   };
};

Wireshark Generic Dissector

I used the Wireshark Generic Dissector (wsgd) to create a simple dissector. It requires two files: a data format description, and a file description.

The data format description m3api.fdesc is very similar to the C struct above, where the header is required by wsgd:

struct header
{
   byte_order big_endian;
   uint16 id;
   uint16 size;
}
struct body
{
   struct
   {
      uint32 size;
      string(5) Command;
      string(32) CONO_DIVI;
      string(16) USID;
      string(16) PasswordCiphertext;
      string(32) MIProgram;
      string(32) ApplicationName;
      string(16) LocalIPAddress;
   } MvxInit;
}

Given the limitations of wsgd and its message identifier, I could not solve how to parse more than one type of message, so I chose the MvxInit request, and the rest will throw errors.

I made a test M3 API call to M3BE, I captured it in Wireshark, and I saved the TCP stream as a binary file (I anonymized it so I can publish it here):

Then, I used wsgd’s byte_interpret.exe (available on the wsgd downloads), using that test binary file, to fine tune my data format description until it was correct:
byte_interpret.exe m3api.fdesc -frame_bin Test.bin

Then, here is the wsgd file description m3api.wsgd; note how I listed the TCP port numbers of my M3 API servers (DEV, TST, PRD):

PROTONAME M3 API protocol
PROTOSHORTNAME M3API
PROTOABBREV m3api

PARENT_SUBFIELD tcp.port
PARENT_SUBFIELD_VALUES 16305 16405 16605 # DEV TST PRD

MSG_HEADER_TYPE header
MSG_ID_FIELD_NAME id
MSG_SUMMARY_SUBSIDIARY_FIELD_NAMES size
MSG_TOTAL_LENGTH size + 4
MSG_MAIN_TYPE body

PROTO_TYPE_DEFINITIONS
include m3api.fdesc;

To activate the new dissector in Wireshark, simply drop the wsgd generic.dll file into Wireshark’s plugins folder, and drop the two above files into Wireshark’s profiles folder:

Then, restart Wireshark, and start capturing M3 API calls. Wireshark will automatically parse the TCP stream as M3 API protocol for the port numbers you specified in the data format description.

Result

Here is a resulting capture between MI-Test and M3BE. Note how you can filter the displayed packets by m3api. Note how the protocol dissector understands the phase of the M3 API protocol (MvxInit), the company (CONO), division (DIVI), user id (USID), and MIProgram (CRS610MI). Also, I wrote C code to decrypt the password ciphertext, but I could not solve where to put that code in wsgd, so the dissector does not decrypt the password.

Limitations and future work

  • I am using M3BE 15.1.2. The M3 API protocol may be different for previous or future versions of M3.
  • I am doing user/password authentication. The M3 API protocol supports other methods of authentication.
  • Given the limitations of wsgd and its message identifier, I will most likely discontinue using wsgd.
  • Instead, I would write a protocol dissector in LUA.
  • Ideally, I should write a protocol dissector in C, but that is over-kill for my needs.

Conclusion

That was a simple M3 API protocol dissector for Wireshark that parses and displays M3 API bytes into a human readable format to help troubleshoot M3 API calls between client applications and M3 Business Engine.

About the M3 API protocol

The M3 API protocol is a proprietary client/server protocol based on TCP/IP for third-party applications to make API calls to Infor M3 Business Engine (M3BE). It was created a long time ago when Movex was on AS/400. It is a very simple protocol, lean, efficient, with good performance, it is available in major platforms (IBM System i, Microsoft Windows Intel/AMD, SUN Solaris, Linux, 32-bit, 64-bit, etc.), it is available in major programming languages (C/C++, Win32, Java, .NET, etc.), it supports Unicode, it supports multiple authentication methods, and it has withstood the test of time (since the nineties). It has been maintained primarily by Björn P.

The data transits in clear text. The protocol had an optional encryption available with the Blowfish cipher, but that feature was removed. Now, only the password is encoded with a XOR cipher during MvxInit. If you need to make secure calls, use the M3 API SOAP or REST secure endpoints of the Infor Grid.

For more information about M3 API, refer to the documentation in the M3 API Toolkit:
4

Call M3 API from Event Analytics rules

Here is how to call M3 API from a Drools rule in Infor Event Analytics; this is a common requirement.

Sample scenario

Here is my sample business case.

When a user changes the status of an approval line in OIS115 (OOAPRO), I have to find the order type (ORTP) of the order to determine which approval flow to trigger in Infor Process Automation (IPA), but ORTP is not part of the table OOAPRO, for that reason I must previously make a call to OIS100MI.GetHead.

I could call M3 in the approval flow, but false positives would generate noise in the WorkUnits.

Is it possible?

I asked Nichlas Karlsson, Senior Architect – Business Integration at Infor, if it was possible to call M3 API directly in the Drools rule. He is one of the original developers of Event Hub and Event Analytics and very helpful with my projects (thank you) although he does not work with these products any longer. He responded that Event Analytics is a generic software with no specific connection to M3, so unfortunately this is not possible out of the box, however it is a common requirement. He said I could solve it using MvxSockJ to call M3 APIs in my own Java class, included in a jar that I put in the lib folder. He added to not forget that the execution time for all rules within a session must be less than the proxy timeout, i.e. 30s. And I would also need to manage host, port, user, password and other properties in some way.

Instead of MvxSockJ I will use the MI-WS proxy of the Grid as illustrated in my previous post.

Sample Drools rule

Here is my sample Drools rule that works:

package com.lawson.eventhub.analytics.drools;

import java.util.List;
import com.lawson.eventhub.analytics.drools.model.Event;
import com.lawson.eventhub.analytics.drools.model.HubEvent;
import com.lawson.eventhub.EventOperation;
import com.lawson.grid.node.Node;
import com.lawson.grid.proxy.access.SessionId;
import com.lawson.grid.proxy.access.SessionProvider;
import com.lawson.grid.proxy.access.SessionUtils;
import com.lawson.grid.proxy.ProxyClient;
import com.lawson.grid.registry.Registry;
import com.lawson.miws.api.data.MIParameters;
import com.lawson.miws.api.data.MIRecord;
import com.lawson.miws.api.data.MIResult;
import com.lawson.miws.api.data.NameValue;
import com.lawson.miws.proxy.MIAccessProxy;

declare HubEvent
	@typesafe(false)
end

rule "TestSubscription"
	@subscription(M3:OOAPRO:U)
	then
end

rule "TestRule"
	no-loop
	when
		event: HubEvent(publisher == "M3", documentName == "OOAPRO", operation == EventOperation.UPDATE, elementOldValues["STAT"] == 10, elementValues["STAT"] == "20")
	then
		// connect to MI-WS
		Registry registry = Node.getRegistry();
		SessionUtils su = SessionUtils.getInstance(registry);
		SessionProvider sp = su.getProvider(SessionProvider.TYPE_USER_PASSWORD);
		SessionId sid = sp.logon("Thibaud", "******".toCharArray());
		MIAccessProxy proxy = (MIAccessProxy)registry.getProxy(MIAccessProxy.class);
		ProxyClient.setSessionId(proxy, sid);

		// prepare input parameters
		MIParameters p = new MIParameters();
		p.setProgram("OIS100MI");
		p.setTransaction("GetHead");
		MIRecord r = new MIRecord();
		r.add("CONO", event.getElementValue("CONO"));
		r.add("ORNO", event.getElementValue("ORNO"));
		p.setParameters(r);

		// execute and get output
		MIResult s = proxy.execute(p);
		List<MIRecord> records = s.getResult(); // all records
		if (records.isEmpty()) return;
		MIRecord record = records.get(0); // zeroth record
		List<NameValue> nameValues = record.getValues(); // all output parameters
		String ORTP = nameValues.get(4).getValue(); // PROBLEM: somehow nameValues.indexOf("X") returns -1

		// make decision
		if (ORTP.equals("100")) event.postEvent("ApprovalFlowA");
		if (ORTP.equals("200")) event.postEvent("ApprovalFlowB");
		if (ORTP.equals("300")) event.postEvent("ApprovalFlowC");
end

Note: You will need to drop foundation-client-10.1.1.3.0.jar in the lib folder of Event Analytics, and restart the application

Limitations

There are some limitations with this code:

  • The execution time must be less than the 30s proxy timeout
  • Limit the number of return columns; there is currently a bug with Serializable in ColumnList, see Infor Xtreme incident 8629267
  • If the M3 API returns an error message it will throw the bug with Serializable in MITransactionException, see Infor Xtreme incident 8629267
  • Somehow NameValue.indexOf(name) always returned -1 during my tests, it is probably a bug in the class, so I had to hard-code the index value of the output field (yikes)
  • I do not know how to avoid the logon to M3 with user and password to get a SessionId; I wish there was a generic SYSTEM account that Event Analytics could use
  • For simplicity of illustration I did not verify all the null pointers; you should do the proper verifications
  • The code may throw MITransactionException, ProxyException and IndexOutOfBoundsException
  • You can move the Java code to a separate class in the lib folder; for that refer to my previous post

Related articles

That’s it. Let me know what you think in the comments below.

How to call M3 API from the Grid application proxy

Here is how to call M3 API using the MI-WS application proxy of the Infor Grid.

This is useful if we want to benefit from what is already setup in the Grid and not have to deal with creating our own connection to the M3 API server with Java library, hostname, port number, userid, password, connection pool, etc.

Note: For details on what Grid application proxies are, refer to the previous post.

MI-WS application proxy

The MI-WS application is part of the M3 Business Engine Foundation. We will need foundation-client.jar to compile our classes:
1b

Step 1. Logon to the Grid

First, login to the Grid from your application and get a SessionId and optionally a GridPrincipal.

From a Grid application:

import com.lawson.grid.proxy.access.GridPrincipal;
import com.lawson.grid.proxy.access.SessionController;
import com.lawson.grid.proxy.access.SessionId;

// get session id
SessionId sid = ??? // PENDING
GridPrincipal principal = ??? // PENDING;

From a client application outside the Grid:

import com.lawson.grid.proxy.access.GridPrincipal;
import com.lawson.grid.proxy.access.SessionId;
import com.lawson.grid.proxy.access.SessionProvider;
import com.lawson.grid.proxy.access.SessionUtils;
import com.lawson.grid.proxy.ProxyException;

// logon and get session id
SessionUtils su = SessionUtils.getInstance(registry);
SessionProvider sp = su.getProvider(SessionProvider.TYPE_USER_PASSWORD);
SessionId sid;
try {
    sid = sp.logon(userid, password.toCharArray());
} catch (ProxyException e) {
    ...
}
GridPrincipal principal = su.getPrincipal(sid);

Step 2. Call the M3 API

Second, call the M3 API, for example CRS610MI.LstByNumber, and get the result:

import java.util.ArrayList;
import java.util.List;
import com.lawson.grid.proxy.ProxyClient;
import com.lawson.grid.proxy.ProxyException;
import com.lawson.miws.api.data.MIParameters;
import com.lawson.miws.api.data.MIParameters.ColumnList;
import com.lawson.miws.api.data.MIRecord;
import com.lawson.miws.api.data.MIResult;
import com.lawson.miws.api.MITransactionException;
import com.lawson.miws.proxy.MIAccessProxy;

// get the proxy
MIAccessProxy proxy = (MIAccessProxy)registry.getProxy(MIAccessProxy.class);

// login to M3
ProxyClient.setSessionId(proxy, sid);

// prepare the parameters
MIParameters paramMIParameters = new MIParameters();
paramMIParameters.setProgram("CRS610MI");
paramMIParameters.setTransaction("LstByNumber");
paramMIParameters.setMaxReturnedRecords(10);

// set the return columns
ColumnList returnColumns = new ColumnList();
List<String> returnColumnNames = new ArrayList<String>();
returnColumnNames.add("CONO");
returnColumnNames.add("CUNO");
returnColumnNames.add("CUNM");
returnColumns.setReturnColumnNames(returnColumnNames);
paramMIParameters.setReturnColumns(returnColumns);

// execute
MIResult result;
try {
	result = proxy.execute(paramMIParameters);
} catch (MITransactionException e) {
	...
} catch (ProxyException e) {
	...
}

// show the result
List<MIRecord> records = result.getResult();
for (MIRecord record: records) {
	record.toString();
}

Note: When I use ColumnList it throws java.io.NotSerializableException: com.lawson.miws.api.data.MIParameters$ColumnList. It appears to be a bug in that the ColumnList class is missing implements Serializable. I reported it in Infor Xtreme incident 8629267.

That’s it. Please let me know what you think in the comments below.

Hosting a Custom Web Service with the M3 API Toolkit

There are a few tools that can be used to communicate with M3 outside of smart office including report writers like DB2 or MySQL for reading, M3 Enterprise Collaborator (MEC) for running transactions and of course my favorite the M3 API toolkit. Out of all these options there are drawbacks to each. The report writing is limited to reading data unless you are living life dangerously. The MEC tool can be complicated and time-consuming to set up and pretty much can’t be done without training or a consultant. The M3 API is not all that user-friendly and can be time-consuming especially with long transactions (like adding new items) and deployment can be a bit of a nightmare.

As mentioned above the M3 API toolkit is by far my favorite way of interacting with M3 outside of smart office typically with some added functionality of table lookups which is a much better way to get info out rather than an API call. The reason for choosing the API is simple. The documentation is excellent and the possibilities are endless! That being said there are still some drawbacks.

  1. While the API toolkit supports many different languages if you want to use more than one platform transactions will have to be completely rewritten.
  2. Deployment can be difficult. The toolkit needs to be installed on every computer or device that wants to communicate with M3.
  3. If database access is desired drivers are required and permissions will need to be granted for every client.
  4. Some transactions are long and time-consuming to set up.

There is good news though. Hosting your own custom web service using WCF that uses the M3 API toolkit eliminates all of these drawbacks. If your web service is well thought out expanding your functionality and streamlining day-to-day business activities becomes easy.

So let’s get started. Out of all of the transactions in M3 one of the simplest transactions is confirming a pick list because it only requires two inputs. For the sake of getting your feet wet with this new setup without overwhelming you we will start with this transaction. As we run through this example realize that while this transaction is simple the true power of the web service becomes more obvious with more complicated transactions.

Step 1 Start a new project

In Microsoft Visual Studio start a new project using the template WCF Service Application. I’ve named my project M3Ideas. (creative right?)

OpenProject

Once the project opens you will see two important files in the solution explorer on the right. One will be called Service1 and the other will be called IService1. Service1 is a class where all of the code for actually running transactions using the API will take place and IService1 is an example of what our client applications will see and be able to use. Notice that there is both Service Contracts with Operation Contracts which are the functions that our tablets or computer programs will call and there are Data Contracts with Data Members which is how data will be presented to our software. This is what makes the Web Service powerful, we get the ability to create our own objects and essentially make a wrapper class for the M3 API Toolkit that can be used by any program we want that needs to interact with M3.

NewWebService

So lets start renaming the items to suit our needs. Since our goal is to report Pick Lists I’ll chose to rename the IService1 interface to MWS420 after the M3 program for reporting pick lists. Do this in the solution explorer on the right and Visual Studio will rename it everywhere. I’ll also make just one Operation Contract for now called ConfirmPickList which takes two integers, the delivery number and the suffix. Right now I’ll go ahead and delete the CompositeType class below but don’t forget how to make Data Contracts this interface won’t be using them but with longer transactions they are pretty much the greatest thing on earth. At this point my interface looks something like this.

MWS420

Remember this is just a prototype for what the client applications will get to use. You might be wondering why I named the interface after only one program. What if you want to use more than one program in you web service? The reason I did this is simply for organization and clarity when making the client applications. When I go to run transactions in other programs I will make new interfaces which will look just like this one only with their own name. This will make it so that the client has to not only specify which transaction to run but which interface the transaction comes from. This enables me to use similar function names for more than one program and still know exactly which program the transaction goes with. A good example of this is if I wanted to confirm manufacturing operations in PMS070 I can use similar function names and the client application will easily know which program each transaction belongs to even if the name isn’t as descriptive as it probably should be. It will become more clear what this will look like in future posts where we connect to the web service from our various clients.

Step 2 Set up the transactions

Ok lets look at the Service1.svc file now which is where the code for this transaction will be placed. Go ahead and rename this file to M3.svc and rename the class M3 as well. This is where all the code for the transactions will go. The single most important thing in this file is the interface implementation right after the class name. In an effort to be organized we will use several partial classes rather than one class. Each partial class will implement one of the interfaces we set up for our program. The code will look like this.

PartialClasses

Notice that each partial class has a colon before the interface name that it implements. Since I’ve used partial classes each one implements just one of my interfaces. If you really wanted you could use just one regular class that implements all of the interfaces. All you would have to do is list them off and separate them by a comma. I think doing it this way will be a bit more straight forward though.

So now lets get to the fun part and set up the M3 APIs and show the program how to connect to M3 and make the transactions come to life. The first thing we need to do is add a reference to the M3 API. In this example we will use the 64 bit library although you can use whichever one you want. It is interesting to know that the target platform that this service will run on is completely unrelated to the programs that will connect to it. This is another huge advantage to using the web service instead of each client using the API toolkit directly.

To add the reference go ahead and right click on references in the solution explorer and select add reference. On the left select browse and again browse at the bottom and locate the file MVXSockNx64.dll. The file should be located in C:\MvxAPI. Once the file is added you should see the file in the list of references.

Reference

Once the reference has been added you can start using the library to communicate with M3. All you need to do is add the using statement at the top of the file and you can start using the library to run transactions. Don’t forget there is a help file that is well documented that will show you how to set up the transactions. Although running these transactions isn’t that elegant the documentation will tell you how to get it done.

To run transactions you will need the port number that the API uses to connect to M3 (there is one of these for each environment), a username and password that is set up in M3 and has permissions to use the APIs that you want to use, as well as the host name. When we are done our transaction will look like this. Note my port numbers might be the same as yours but they don’t have to be. Yours could be different.

ConfirmPickList

I went ahead and put some of the constant Information in a static class called Info. This will make it so I don’t have to type in the data each time and I can use it in all of my partial classes. I’ve also set up the transaction which is exactly how the documentation says to do it. This includes padding the spaces so that each input is in the correct position of the string.

Step 3 Publish

Now that we have our first transaction set up lets publish it and test it. Once it’s been tested we can change to the port to production. To host the web service you will need a computer or virtual machine that is running IIS. You might need to enable the feature in windows. If you are unsure how to enable the feature a simple google search will walk you through it.

Ok to publish the web service right click on project in the solutions explorer and select publish. Set up a publish configuration to publish to the file system in a folder of your choosing. We’ll copy these files to the computer that will host the service. You will also need to locate the file MvxSockx64.dll file and copy that to folder as well. Go ahead and put it in the bin subfolder with the other libraries that got published. Next copy that folder to the C drive of the computer that will host the service and open IIS. On the left side of the screen expand the tree and right click Default Web Site and select add application. Then show IIS what folder your files will be and name your service.

IISSetup

To verify that your service is up and running expand the tree on the left more and select the application you just added. Then on the far right select browse and it should open a browser. Select the SVC file and it should bring you to a screen with directions how to use it. In the next post I’ll run through some samples on how to use the service to streamline reporting pick lists.

Here is the screen that you should be able to get to. If something happened to go wrong it will be displayed on this screen.

BrowserM3Service

If you have any questions on what the web service can be used for please feel free to ask in the comments. Also if you run into any problems please let me know.

Happy coding.

-The Engineer

How to get the text panel in a Mashup

I often forget how to add a TextPanel, for example CRS610/T, into a Mashup for Infor Smart Office, so here is the solution so I can remember next time, and maybe it will help you too.

First, we need to understand how to enter text in M3 using the T panel. Then, we need to understand how the text is stored in M3, in the tables for text headers and text lines, and the TXID. Then, we need to understand how to get that text using a series of three transactions of the M3 API CRS980MI. Finally, we build the Mashup around it using the MI controls and XAML.

How to enter text

To enter text, for example for M3 Customer. Open – CRS610:

  1. Go to Smart Office.
  2. Open CRS610.
  3. Add Panel T to the Panel Sequence.
  4. Select a record, select Options > 2-Change, and click Next until you reach the Panel T; the M3 Text popup will open.
  5. Enter some text, for example Hello World lorem ipsum, on multiple lines.
  6. Click Next. The popup will close.
  7. To enter a second Text block, go back to the popup and click Text block.
  8. Once you enter two text blocks or more, when you go back to the popup, it will first show the M3 Text blocks (text headers), select one, and then it will show the M3 Text (text lines).
  9. You can create text blocks for different languages.

Here’s an example of CRS610/B with Panel Sequence T:
1

Here’s an example of the text headers popup:
2

Here’s an example of the text lines popup:
3

Where is the text stored?

The text is stored in the M3 database in a pair of tables: there’s a table for the text headers and a table for the text lines. The pair of tables depends on the originating M3 Program, for example for CRS610 the tables are OSYTXH and OSYTXL. It’s all tied together by a Text Identity field TXID, for example the Customer table OCUSMA has a TXID column, so do OSYTXH and OSYTXL. The text headers are identified by the fields CONO, DIVI, TXID, TXVR, and LNCD (language). And the text lines are identified by the foreign keys of their text header, and by LINO (line number).

I never rememeber which M3 Program stores text in which pair of tables, for example it took me a while to remember that Customer text is stored in OSTYXL. There’s probably a short way to remember: perhaps somewhere in the M3 Companion, perhaps reading the cryptic M3 Java source code. I usually go to M3 MetaData Publisher (MDP), I search for tables with the word “text”, and then I go fishing for my text with SQL, searching the contents of the tables one by one. Yeah I know…there’s got to be a better way.

Here’s me searching in MDP (I had tried all the pairs of table Text head and Text line, one by one, starting at the top letter A, until I found OSYTXL way below and took this screenshot):
0

Here’s me fishing in SQuirreL (bingo! I finally found my text):
0__

It seems every four years I go thru this learning process all over again, as for the first time, each time making detailed notes and screenshots and telling myself this time it would be for good, and then four years later I forget it all again. Yikes! If someone has a better way to find the tables please let me know.

How to get the text using M3 APIs?

Several years ago, the M3 Product Development team finally introduced an M3 API CRS980MI to get the text. Before that we had to use good ol’ SQL. There are three transactions (methods) to call. First, we need to get the TXID based on the originating table and key, in my case table OCUSMA and key Company CONO and Customer number CUNO. Then, we need to get the text headers for that TXID. Then, we need to get the text lines for a selected text header.

Step 1 – Get the TXID

To get the TXID:

  • M3 Program: CRS980MI
  • Transaction: GetTextID
  • Input fields:
    • FILE, in my case OCUSMA00
    • KV01, in my case the CONO, for example 735
    • KV02, in my case the CUNO, for example ACME
  • Output field:
    • TXID, for example 544

In the Mashup, we’ll call that using a hidden MIPanel control.

Step 2 – Get the text headers

To get the text headers:

  • M3 Program: CRS980MI
  • Transaction: LstTxtBlocks
  • Input fields:
    • CONO, for example 735
    • DIVI, for example AAA
    • TXID, for example 544
    • TFIL, for example OSYTXH
  • Output fields, a list of:
    • TXVR, for example THIBAUD
    • LNCD, for example GB
    • TX40
    • TXEI

In the Mashup, we’ll call that using a MIListPanel control.

Step 3 – Get the text lines

To get the text lines:

  • M3 Program: CRS980MI
  • Transaction: SltTxtBlock
  • Input fields:
    • CONO, for example 735
    • DIVI, for exapmle AAA
    • TXID, for example 544
    • TXVR, for example THIBAUD
    • LNCD, for example GB
    • TFIL, in my case OSYTXH
  • Output fields, a list of:
    • LINO
    • TX60

In the Mashup, we’ll call that using a MIListPanel control.

Build the Mashup

Here’s the XAML source code (I forgot to un-hard-code the CONO):

<Grid xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:ui="clr-namespace:Mango.UI.Controls;assembly=Mango.UI" xmlns:mashup="clr-namespace:Mango.UI.Services.Mashup;assembly=Mango.UI" xmlns:m3="clr-namespace:MForms.Mashup;assembly=MForms">
    <Grid.Resources></Grid.Resources>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="1*" />
    </Grid.ColumnDefinitions>
    <Grid.RowDefinitions>
        <RowDefinition Height="1*" />
        <RowDefinition Height="Auto" />
        <RowDefinition Height="1*" />
        <RowDefinition Height="Auto" />
        <RowDefinition Height="1*" />
        <RowDefinition Height="Auto" />
    </Grid.RowDefinitions>

    <m3:ListPanel Name="CustomerList" Header="{m3:Constant Key=CR61001,File=MVXCON}" IsListHeaderVisible="True" Grid.Row="0">
        <m3:ListPanel.Events>
            <mashup:Events>
                <mashup:Event SourceEventName="Startup">
                    <mashup:Parameter TargetKey="OKCONO" />
                    <mashup:Parameter TargetKey="OKCUNO" />
                </mashup:Event>
            </mashup:Events>
        </m3:ListPanel.Events>
        <m3:ListPanel.Bookmark>
            <m3:Bookmark Program="CRS610" Table="OCUSMA" KeyNames="OKCONO,OKCUNO" IncludeStartPanel="True" SortingOrder="1" View="STD01-01" />
        </m3:ListPanel.Bookmark>
    </m3:ListPanel>

    <m3:MIPanel Name="MIGetTextID">
        <m3:MIPanel.Events>
            <mashup:Events>
                <mashup:Event SourceEventName="CurrentItemChanged" SourceName="CustomerList">
                    <mashup:Parameter TargetKey="FILE" Value="OCUSMA00" />
                    <mashup:Parameter TargetKey="KV01" SourceKey="CONO" />
                    <mashup:Parameter TargetKey="KV02" SourceKey="CUNO" />
                </mashup:Event>
            </mashup:Events>
        </m3:MIPanel.Events>
        <m3:MIPanel.DataSource>
            <m3:MIDataSource Program="CRS980MI" Transaction="GetTextID" Type="Get" InputFields="FILE,KV01,KV02" OutputFields="TXID" />
        </m3:MIPanel.DataSource>
    </m3:MIPanel>

    <Label Grid.Row="1" Content="Text headers" Style="{DynamicResource styleGroupBoxHeaderMashup}" />
    <m3:MIListPanel Name="MILstTxtBlocks" Grid.Row="2">
        <m3:MIListPanel.Events>
            <mashup:Events>
                <mashup:Event SourceName="CustomerList" SourceEventName="CurrentItemChanged" TargetEventName="Clear" />
                <mashup:Event SourceName="MIGetTextID" SourceEventName="Running">
                    <mashup:Parameter TargetKey="CONO" Value="735" />
                    <mashup:Parameter TargetKey="DIVI" />
                    <mashup:Parameter SourceKey="TXID" TargetKey="TXID" />
                    <mashup:Parameter TargetKey="TFIL" Value="OSYTXH" />
                </mashup:Event>
            </mashup:Events>
        </m3:MIListPanel.Events>
        <m3:MIListPanel.DataSource>
            <m3:MIDataSource Program="CRS980MI" Transaction="LstTxtBlocks" Type="List" InputFields="CONO,DIVI,TXID,TFIL" OutputFields="TXVR,LNCD,TX40,TXEI" />
        </m3:MIListPanel.DataSource>
        <ListView ItemsSource="{Binding Items}" Style="{DynamicResource styleListView}" ItemContainerStyle="{DynamicResource styleListViewItem}">
            <ListView.View>
                <GridView ColumnHeaderContainerStyle="{DynamicResource styleGridViewColumnHeader}">
                    <GridView.Columns>
                        <GridViewColumn Header="Text block" DisplayMemberBinding="{Binding [TXVR]}" />
                        <GridViewColumn Header="Language" DisplayMemberBinding="{Binding [LNCD]}" />
                        <GridViewColumn Header="Description" DisplayMemberBinding="{Binding [TX40]}" />
                        <GridViewColumn Header="External/internal text" DisplayMemberBinding="{Binding [TXEI]}" />
                    </GridView.Columns>
                </GridView>
            </ListView.View>
        </ListView>
    </m3:MIListPanel>

    <Label Grid.Row="3" Content="Text lines" Style="{DynamicResource styleGroupBoxHeaderMashup}" />
    <m3:MIListPanel Name="MISltTxtBlock" Grid.Row="4">
        <m3:MIListPanel.Events>
            <mashup:Events>
                <mashup:Event SourceName="CustomerList" SourceEventName="CurrentItemChanged" TargetEventName="Clear" />
                <mashup:Event SourceName="MILstTxtBlocks" SourceEventName="CurrentItemChanged">
                    <mashup:Parameter TargetKey="CONO" Value="735" />
                    <mashup:Parameter TargetKey="DIVI" />
                    <mashup:Parameter TargetKey="TXID" Value="{Binding [TXID], ElementName=MIGetTextID}" />
                    <mashup:Parameter SourceKey="TXVR" TargetKey="TXVR" />
                    <mashup:Parameter SourceKey="LNCD" TargetKey="LNCD" />
                    <mashup:Parameter TargetKey="TFIL" Value="OSYTXH" />
                </mashup:Event>
            </mashup:Events>
        </m3:MIListPanel.Events>
        <m3:MIListPanel.DataSource>
            <m3:MIDataSource Program="CRS980MI" Transaction="SltTxtBlock" Type="List" InputFields="CONO,DIVI,TXID,TXVR,LNCD,TFIL" OutputFields="TX60,LINO" />
        </m3:MIListPanel.DataSource>
        <ListView ItemsSource="{Binding Items}" Style="{DynamicResource styleListView}" ItemContainerStyle="{DynamicResource styleListViewItem}">
            <ListView.View>
                <GridView ColumnHeaderContainerStyle="{DynamicResource styleGridViewColumnHeader}">
                    <GridView.Columns>
                        <GridViewColumn Header="Line number" DisplayMemberBinding="{Binding [LINO]}" />
                        <GridViewColumn Header="Text" DisplayMemberBinding="{Binding [TX60]}" />
                    </GridView.Columns>
                </GridView>
            </ListView.View>
        </ListView>
    </m3:MIListPanel>

    <ui:StatusBar Name="StatusBar" Grid.Row="5" />
</Grid>

Here’s a screenshot of the result, with the list of customers at the top, the list of text headers for that customer in the middle, and the text lines for that text header at the bottom:
4

That’s it!

Send us your comments below, subscribe to this blog with the Follow button below, be an author and write your own ideas, share with colleagues, and enjoy.