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).
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.
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.
Excel spreadsheet, JScript or any other API tool
Testing your custom browse definition is easy. Fire up MITest and connect to CRS990MI and perform the query:
Note that I'm using LstBrowse as I want a list of all matching records.
Looking at one of these returned results I can see the information as shown in M3:
And that's all there is to it. Creating custom Get and List APIs is easy with MNS185 :-)