Extracting Data from M3 via the APIs

There are many ways to extract data from the APIs in M3, but often you need information that isn’t provided via the native APIs or the native APIs purely don’t exist for the table you want to query.

But over the years there have been a number of ways that you can extract data natively from the APIs without expensive mods or using webservices. The methods to do this can seem a little convoluted and had some shortcomings where you can quickly run out of sort orders on popular tables in the older versions of M3, but by the nature of the native methods we enforce good practices of using indexes. I typically make a concerted effort to use these APIs rather than webservice calls as they tend to be quicker, and require less configuration outside of the M3BE.

We have 3 different ways I’m aware of to set up custom data extraction via the APIs and this post provides a rough and ready run through of them.

MNS185 – M3 Browse Definition. Open
MDBRead
CMS010 – M3 Information Browser Category. Open

MNS185 – M3 Browse Definition. Open

This is the oldest that I’m aware of. MNS185 allows us to select an index and then output fields. We can do some fairly primitive filtering (mainly because it looks like it is built for creating populating data in browse windows), and then consume through CRS990MI

MNS185 allows the extraction of data from a single table and can return more data that you want (given the way browse works in M3).

I’m not sure how long this has been available.

File is the table we want to extract data from, the last two digits are the index. This defines the arguments we will use for filtering.

We will return data from these columns in CUGEX.

The definition keys is what we will filter on. We set our filters to 2, so we will filter on only the first two fields, company and the file.

Using MITest, we use the FLDI corresponds to the Field defined in MNS185, VARI corresponds to the Variant.

And we have our output.

MDBREAD

This one was a bit of a quest to find back in those dim dark days, but it was introduced with one of the feature packs to 10.1. We use an external program which adds new transactions under the MDBREADMI call. This again is based on an index and again only extracts data from a single table. MDBREAD is pretty funky, quick and works well. It can be a little bit of a pain where you do have to clear the M3 API metadata cache before you can use the new call – minor annoyance but I’ve seen people get tripped up by this regularly resulting in environments being bounced needlessly. (pet peeve of mine).

In the MvxAPI toolkit we have MDBRead

We can determine the port from the grid management pages.

Log in with MDBRead

And we see the already defined transactions.

MITMAS is the table we are going to look up, we are going to use index 00.

The define new transaction ITNO is an argument which is determined by the index. We can select the table fields so they appear in the Outbound list. We are going to use the Create Lst Transaction so we will get a list of items from MITMAS.

Clear the metadata cache.

Now lets test the call.

CMS010 – M3 Information Browser Category. Open

This is tooling which is what starts to make Mashups pretty kewl, and is functionality which allows us to create some pretty awesome and customised views in some of the toolbox b panel windows. We can create relationships with different tables, virtual fields and new views etc. We can then publish our CMS010 entries as APIs via CMS015, this will then create a new transaction under CMS100MI (it will also create the CMS100MI entry if it doesn’t already exist).

This then gets consumed through CMS100MI.

M3 Information Browse was added in 13.1 / 15.1.1 and CMS015 13.3 / 15.1.3

InforXtreme has an article KB 1624717 which provides some background. Related NCR are 6863, 5428 and 4337.

Note, Infor does warn about performance with the API and where possible, MDBREAD should be used instead.

This example we will get data from MITBAL and MITMAS.

Panel F allows us to create the link relationship

We need to select the fields we want from MITMAS. We do a Related Options -> Add Field to Field Group

We can select a field

CMS015 allows us to create an API call from the CMS010 Information Browser Set up.

And finally, testing.

This entry was posted in Development, M3 / MoveX, Misc. Bookmark the permalink.

16 Responses to Extracting Data from M3 via the APIs

  1. maxric says:

    Hi Scott. Think that MNS185 is available from M3 7.1, replacing the famous DCS100 we had before.

  2. FRANCISCO AMORES TORRES says:

    Hi,
    which would be the best approach if we have to extract very large volume of data from M3 Cloud? we can have a custom M3 API program but I don’t think calling via REST is going to be efficient

    Thanks

    • potatoit says:

      Hi Fancisco,

      in 13.4 there is a foundation API available for streaming bulk data to and from M3. If you do a search for the M3 Foundation Integration Guide for Developers it has details.

      There is SQL access, but I believe Infor require you to sign a waiver around SLAs – though if you are extracting bulk data then I suspect they’ll be reluctant.

      Cheers,
      Scott

      • Hi,
        Thanks very much for this feedback. I will have a look. I’m not M3 expert but we are working in an integration between Oracle EPM and M3 infor cloud

        Thanks!

      • potatoit says:

        Ah, then perhaps you should take a look at ION – ION is the preferred integration point with M3 and it supports a variety of connection methods and the standardised ‘ION APIs’, and as I understand it, there are a lot more coming in the near future. There may even be a connector back to Oracle EPM now.

        Cheers,
        Scott

      • Hi,

        I had a look at the Foundation API. My concern is that we can call multiple transactions in one call but can we use this API to export the data to file in the cloud and then use any other API to download the file?

      • potatoit says:

        The API call would be done from an on-prem application, so you wouldn’t need to download it from the cloud. Then you’d perform whatever transformations are needed on the data and push that to your other system.

        I think you’d struggle to get Infor to allow a third party application on the servers they host.

        Cheers,
        Scott

      • Ok, so the solution you are pointing is to have our client in their hosted server. I don’t think we can install our Oracle EPM Suite in M3 servers as we need a dedicated infrastructure for it.
        We are waiting for feedback from Infor but my guess is that ION is going to be most suitable solution.
        I will keep you posted.

        Thanks

      • potatoit says:

        Sorry, but no. I’m saying that you need your client hosted elsewhere. I can’t see Infor allowing 3rd party applications to be installed in their environments. Your client makes REST calls to the bulk API via the VPN to the M3 cloud, the data is returned to the client as the response to the REST call.

        Cheers,
        Scott

  3. Hello,

    which would be the best approach to extract very large amount of data from M3 Cloud? Something we can kick off externally.

    Thanks

  4. Dom says:

    Hi,

    I have a kinda complex requirement and I wanted to know if it’s possible with CMS015.

    The goal is to get results from MNS410MI.LstRolesByUser by using the user ID of the one who logged in. Each role will then be matched against CUGEX3 rows using CUSEXTMI.GetAlphaKPI.

    This approach was executed via script but performance suffers too much as it loops through all roles against CUGEX3 roles so I am leaning towards this option from CMS015.

    • potatoit says:

      In the notes of CMS100MI Infor mention that it can be slow compared to MDBREADMI and I would expect the GetAlphaKPI – so I doubt you’re going to get benefits from CMS015 and related tables.

      You could take a look at using MWS to wrap an SQL query to create the relationship between the two tables and extract the data.

      Cheers,
      Scott

  5. Vishnu B says:

    Hi Scott
    Is there any way of doing de-allocation from MMS120\B panel against an item in a particular warehouse and is allocated to a manufacturing Order and is at status 23 or 33 rather than going into each and every order lines.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s