Saturday, November 5, 2011

Creating custom List and Get APIs

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:
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 :-)

8 comments:

  1. Great entry, thanks for sharing!

    ReplyDelete
  2. Awesome idea! I didn't know about it. /Thibaud

    ReplyDelete
  3. I'm encountering an issue where List APIs return a maximum of 100 lines. A Lawson consultant confirmed that it's an API limitation. He also thinks that there is a parameter to change this. Do you happen to know?

    ReplyDelete
    Replies
    1. Hi,

      You want to use SetLstMaxRec to address this.

      e.g. SetLstMaxRec 999

      See my post on creating Excel spreadsheets to call APIs (http://movexblog.blogspot.co.nz/2010/03/interfacing-excel-with-m3-via-apis.html) for an example of how this works.

      Cheers, Al.

      Delete
    2. Thanks Al, but I'm not using Excel yet. I'm testing my web services inside LWS Studio.

      I just found a setting in the web services server view configuration. Called 'M3 Maximum Records Sent' and it was set to 100. I've changed it but it hasn't worked yet. I believe I have to restart the WebSphere application server first. I will have to do that after office hours. I will give an update later on how that goes.

      Delete
  4. Hi,

    May I Know whether the API dll file (MVXSOCKX_SVRLib) can be use in the windows mobile 6 profesional? because i keep prompt with error "COM object with CLSID '{CAD60D3F-90DF-11D1-8DF6-0004AC6568DE}' cannot be created due to the following error: Class not registered"

    Thank you,

    ReplyDelete
    Replies
    1. Hi Mohd, I've just recently come across http://mvxlib.sourceforge.net/ which appears to be an open source implementation of the MvxSock functionality sold by Infor in the API toolkit. This may be an option for you.

      Regards,

      Al.

      Delete
  5. Hi Mohd, I would expect (but do not know definitively sorry) that the MVXSOCK_SVRLib dll would not work on windows mobile development as the dll will be x86 code whereas windows mobile is from memory ARM architecture.

    I haven't seen anyone publish an API sockets library for windows mobile. A couple of other approaches:
    - LWM from Lawson for windows mobile communicates via a service running on a windows server.
    - I have published web applications and accessed them via the windows mobile web browser.

    Cheers,

    Al.

    ReplyDelete