For those of us that used to use MDIWS or other such tools, you’ll know that as of November 2020 certificate based authentication to the M3BE APIs was finally shutdown, this has put an end to several tools that people have been using over the years to push data in to M3. External programs now need to leverage the ION APIs which is a bit more complex to leverage than what we are used to.
November 2020 (or October?) also saw the introduction of EVS100, a program which allows us to import data from an Excel spreadsheet or from a .csv for which there is a M3 API in to M3. Refer to NCR 12180 / KB 2132563 and the Infor Docs https://docs.infor.com) for details.
In a nutshell, EVS100 will allow us to read an Excel .xlsx file with multiple tabs or a .csv from the MvxFileTransfer\FileImport directory on the CFT Agent and it will execute APIs to push data in to M3.
I’ve found that the spreadsheet version is quite useful as it allows me to upload data from multiple sheets in one ‘session’. This happens on the server so it shouldn’t suffer from the round-trip latency you get when running your own home-grown application from your machine.
It does have a few quirks at the moment (it’s particular about missing values in columns and doesn’t like spaces in file names) but it is improving.
When you are using a spreadsheet it does require a fairly specific format, and as I am inherently lazy, I didn’t really want to manually create a spreadsheet – so I hacked together a quick script which allows you to select a transaction from MRS002, click the Generate Sheet button and it will generate a template spreadsheet. It will also highlight the API mandatory fields 🙂
Screenshot of the template
The Script adds the Generate Sheet button
After uploading the script to the tenant, the script needs to be attached to MRS002/B
You can download the MRS002_GenerateSheet.js script from the Samples -> js directories from this github Repo https://github.com/potatoit/MRS002_GenerateSheet
It is something quick that has been thrown together, I’m sure that there’s lots of room for improvement…
The full Visual Studio solution is available for download – if you do, then please note that this leverages the xlsx-style project, so please keep the license with the distribution.
Happy generating templates! 🙂
Hi Scott, the format of the input sheets matches 1:1 the SmartData format. This means, it is possible to use sheets which have been created for SmartData, and vice versa, SmartData can be used to generate input sheets for EVS100 as well.
How to clear field values in the import? If you leave the field empty, then it will not overwrite. For ex. I have an prognosis logic, i want to remove this. I can’t just write 0, then it will import 0 and result in junk data in the database.
it depends on your settings, but you can try using a ? in the field. From memory that is the default character to clear a field.