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.
Potato IT 
Hi Scott. Think that MNS185 is available from M3 7.1, replacing the famous DCS100 we had before.
Hmmm…never came across DCS100 – data collection interface. Thank you, I’ll have to do some investigation.
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
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!
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?
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
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
Hello,
which would be the best approach to extract very large amount of data from M3 Cloud? Something we can kick off externally.
Thanks
There is a bulk API available under 13.4 which is described in the M3 Foundation Integration Guide for Developers. You can write a small program to call this or even a Powershell script.
Cheers,
Scott
Thanks,
It’s Francisco again. I thought 1st post did not work
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.
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
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.
Hello, I have successfully created a transaction, but when I try to access it via Rest URL (i.e. https://123.456.789.5:20169/m3api-rest/v2/execute/CMS100MI/LstTranaction) i get error Program CMS100MI not found
If you look in MRS001 do you see CMS100MI and under it in MRS002 then does LstTranaction exist? (and should that be LstTransaction)? If not, then it sounds a little like the API isn’t activated in CMS015.
Hi ,
I have a requirement to get a data of object MITMAS, MITBAL on daily basis required by 3rd party i am using M3 Cloud version, what will be the best approach?
Hi Kushagra,
it really depends on a variety of factors and this is generally where you’d want some experienced consulting input.
If you’re looking at retrieving bulk data (I’m assuming that you’re looking at getting hundreds of records or more), then it’s probably not the right approach to use the M3 APIs, you should be looking at extracting the data from Data Lake.
There are some IONAPIs available for extracting larger datasets and tools available to push that data in to a local db (if that’s your goal).