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

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

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.

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?

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.

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.

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.

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.

Thanks for this MDBREADMI post, However we would like to use SelTrans, how it differs from GET. Can you pls provide example.
We have created SelTrans but I don’t see the difference between Sel & Get. Could you pls guide for SelTrans as we wanted to give SQL Where Clause Filter Condition with Proper Index.
Appreciate your advise.