movex blog: 2011

As part of a project to pull out modifications from a M3 installation I'm making a lot of use of APIs. Whether it's using the cool new Jscript functionality that allows calling APIs within LSO, or the tried and true method building mini-applications in Excel for mass-manipulation of data I seem to be looking at and using APIs every day.

Occasionally I come across situations where an API doesn't exist for the function I want to perform. If it's a Add or Update function I'll build a Web Service or get a custom API written, but for Get and List functions there is a (it seems not well known) function that allows the creation of a custom API against M3 table.

For example I needed to query the material data safety information in the M3 database (MSS051).

Looking at MRS001 I saw that there wasn't an existing API for the MSS05x tables. So I needed to create one.

To do this there are a number of programs I'll use:

M3 / Database metadata - there are a number of tools available that show the table associated with a M3 program. Use your favourite one.
Database query tool - I use WinSQL as it's free and good. Any query tool that will allow you to see the indices on the database tables will do.
MNS185 - allows the creation of a custom browse definition that can then be queried by an API.
CRS990MI - the API that will allow us to query the custom browse definition.

Step 1 - determine the database table and index to use
Querying my trusty metadata tool I see that MSS051 data is stored in the mitphy table. Looking at the indices on this table I can see that mitphy00 allows me to query the table based on the following fields:

This is good as those are the fields that I will have available in my application to query against. If I couldn't find an existing index that meets my needs I'd check CRS021 to see if I can create a custom index on this table. See Lawson documentation for how CRS021 works or ask a question in the comments. Conceptually I could also create an index on the table directly from SQL, but that would probably be unsupported by Lawson.

Step 2 - create the custom browse definition
The M3 program MNS185 allows me to create a custom browse definition. Note that when creating custom browse definitions you should use sorting order 2. First I define a field and variant code. It doesn't seem to matter what you enter here, but following the standards shown in sorting order 1 are a good idea.

In the E panel you specify the browse program (MSS051 in this case) and the number of filters you want applied in the API call. This is particularly important, as you can create both Get and List browse definitions by changing the number of filters. In this case by specifying 2 filters I am querying on the CONO and ITNO fields to get a List of all PROIs returned. If I instead changed the filters to 3 I would query on the CONO, ITNO and PROI fields and Get a single record returned.

In MNS186 you then specify the data you want returned from your custom API call. You can specify up to 15 fields from the table.

Once that is done you will have your browse definition created in MNS185:

From there you can query it using MITest, an Excel spreadsheet, JScript or any other API tool

Step 3 - query the custom browse definition
Testing your custom browse definition is easy. Fire up MITest and connect to CRS990MI and perform the query: