Data update tool for Infor M3 - documentation
Introduction
Data update tool provides exchange data between Infor Cloudsuite M3 and Excel seamlessly via API. Create items, upload pricelists and use many more add/update possibilities or download data with no more than a few clicks.
Tutorial
Getting started with the Data update tool
Components of Excel template
1. Configuration sheet (parameters on the sheet)
Server address
To get the server address for the API call there is an Excel formula on the configuration sheet, you have to copy the usual URL from your browser and insert it into the sheet, the formula will calculate the address from it. If you have other systems, then you can change this area according to your needs.
Go to the Infor M3 tab in your browser and copy the URL address. It begins often with "https://mingle-portal”. Paste this address into the Configuration sheet in the "Infor M3 browser address" field.
Token
The Infor cloud version offers one authentication type instead of the usual username and password pair; this is called a token.
The token has to be generated after you log into Infor M3. It is a long string, based on it Infor identifies the user and the system where it was generated.
You have to generate the token and paste it to the Excel, it can be used several times, for about 2 hours. In this way, there is no need to enter a username and password to be able to use the automation.
To get the token you need to open the Infor M3 and press Ctrl + S, then enter command “/grid/rest/security/sessions/oauth” and confirm it with the Enter.
Copy the whole string that will appear, and paste it to the Configuration sheet in the Token field. If you use a Form in the Excel, you can paste it there, but make sure that the configuration sheet uses this value.
* The use of one token is limited by time. You can use it for 2 hours starting from the generation.
** If you use the sample file the token should also be completed in the Item Form, in cell E6.
2. Supported Transactions sheet
Supported Transaction sheet is used to find the relationship between fields and transactions and programs.
It allows you to see which programs and transactions can update or retrieve fields. Based on this sheet you can easily add fields to the sample sheets or create new ones.
The mandatory fields have to be set for the transactions, otherwise, the transaction won't be sent and notification will be shown. If you use the form once corrected, then you can use it in the future, there is no need to change this many times.
3. Item Form
The Item form sheet is a sample for a real use case, showing how we can connect the form and the datasheet.
When the Item Form sheet is ready, you can go to the Item Form Datasheet to review how data will be loaded into the system. Adapt it according to your needs.
4. Datasheet
The Datahseet has a fixed structure on the header part, but it can be changed easily by adding columns or rows to it.
What appears on this sheet will be processed to perform a transaction. What is not on the sheet, it will have no effect on the transaction.
If you use multiple transactions in one run (as the Form does) then the data from the form and the sheet are split to transactions. When processing, it will be processed based on transactions row by row until the end of the content.
5. Other sheets in the sample file
The sample sheets are to show the logic of the automatization. You can use any transaction based on the Supported Transactions sheet. You have to configure the header and put data below it based on your planned action.
To be able to use more transactions contact us.
Small remark if you are not familiar with MMS200MI:
MMS200MI has many transactions to update item basics in MMS001 or Item/Warehouse in MMS002 or Item/Facility in MMS003.
Sample update item master file
It is a simple setup to update item parameters, currently it updates 12345678 item number and sets the value of the ITDS field to “NEW description of 12345678” It use program: MMS200MI, and transaction: UpdItmBasic
Sample upd item/warehouse connection in MMS002
It is a more complex setup to update item parameters in MMS001 and when it is ready then it starts processing the next part, to update MMS002.
Current steps:
-
Update 1: it updates 12345678 item number and sets the value of the ITDS field to “NEW description of 12345678”
-
Update 2: it updates 23456789 item number and sets the value of the ITDS field to “NEW description of 23456789”
-
Update 1 and Update 2 use program: MMS200MI and transaction: UpdItmBasic
-
Update 3: Item number 12345678 in WH1 warehouse is sets MISQ value to 1
-
Update 4: Item number 23456789 in WH2 warehouse is sets MISQ value to 10
-
Update 3 and Update 4 use program: MMS200MI and transaction: UpdItmWhs
You can add rows and columns (and different transactions) to the sheet. As you can see one transaction is processed till it’s and and then the next transaction is processed. In this way it is possible to process different length of transactions.
Sample LstItmByItmGr - List items by item group
LstItmByItmGr transaction can download filtered data based on itemgroup (ITGR). In this case it will download all items in itemgroup “1234”. After processing the results it will write the content to LST_Result sheet.
Sample LstFacByItem - 1 item
LstFacByItem transaction can download filtered data based on item number from MMS003. It will list every facility where the item is created. In this case it will list all facilities of 12345678 item number. After processing the results it will write the content to LST_Result sheet.
Sample LstFacByItem more items
The transaction is like before. But after the first run with 12345678 item number. After processing the results the content will be written to LST_Result sheet. Then it will start a new request with the 23456789 item number, after processing the results the content will be written to LST_Result sheet.
FAQ
Where can I find results after running an upload? Where can I find results after running an upload?
The result of a run is always in the Excel file. Its place can be different, depending on the setting on the configuration sheet and of the transaction type.
When you perform a LST transaction then the result “table” will be written to the LST_Result sheet. If the sheet was there before with some content, the new results are placed under the content. If the sheet is not there then it will be inserted to the file.
To which sheet is written, is controlled on the configuration sheet with this parameter: How to write results to Excel (ParameterID=9). If it is 2 then a new sheet is generated based on the original sheet name, and all the data and results are written there. If this parameter is 0 or 1 then the original sheet will be used to write the results in. But in this case the original data is written to the sheet, if you use formulas they will be lost. Therefore if you use formulas on the datasheet you need to use value 2.
The result message was Not authorized:
If the token is expired or it is generated in another environment then it is set in the Excel file then you can get this result. Or you are not authorized to use the program or transaction that you try to use.
Some transactions OK, some results NOK:
The result of a transaction depends on the system setting and the sent data. If you set a value that is not in the system but it should be, then the result will be an error (NOK). For example, the item group ABC is not defined in the system, but if we try to use them in the API call then the result will be NOK, or NOK and the error description.
How are system error messages handled?
If there is an error we try to display the error message from the system, if the system results only “NOK” then we will display this.
What if I run a transaction what has an output value i.e. Item copy?
If the result of a call has additional information, like in this case the new item number then it will be written to the Excel also.
Why downloading table by i.e. ExportMI is limited to max 10 000 lines, there are more records in the table?
Infor set this limitation, because there were issues when users missed to filter the results in the right way and they got the full table as a result, this small query mistake can create big problems in the system.
Because with our tool it is possible to send many transactions after each other, with good filtering it is possible to download more lines in one automatization run. But in this case we have to create very precise filtering, not to miss or duplicate lines.
Is it possible to use PPS370MI or OIS100MI to enter orders into a system?
Using these modules first we have to generate a “reference number” and then we can use this number to connect/add data to it and finally release it using an (PO, CO) order.
That means few transactions are needed to work with these functions (1. get “reference number”; 2. insert header; 3. insert lines; 4, release).
With the current version we can run all the transactions but the result of the first call can’t be processed automatically as the input to the next transaction. That means if you generate the “reference number” and you add it to the datasheet in the Excel then the next steps (2,3,4) would be performed fine. But using it in only one step is currently not possible. The planned next version will handle this situation.
I got a message about “Needed field is missing“ and I can’t see the results:
Before sending data to the server the automatization checks if every mandatory fields are set. If not then it creates a list from the missing fields. If you put these fields (and its values) to the datasheet and run again, this message will disappear. To find mandatory fields you can use the “SUPPORTED_TRANSACTIONS” sheet in the sample file.
If you want to use multiple transactions (i.e. MMS200MI/UpdItmBasic; MMS200MI/UpdItmWhs; MMS015MI/Add; MMS030MI/Add…) on one datasheet we recommend to create and test them separately and if every part works fine then you can merge it to one sheet. The checking is performed when the first message is created with the transaction. That means if the first transaction is finished then it will check the next one, but if it has this issue then the automatization will step to the next transaction. In this way the first transaction will be fine, the next one will be missing and the last one could be fine, this could be a bit confusing.
If I call many API transactions in one run, how can I manage to call the same transaction multiple times with different parameters?
The automatization is looking for transactions and lines/parameters on the processed sheet. Each column refers to one transaction, which is processed in one step. Therefore if you put multiple transactions with different parameters next to each other (in one line) then it will be handled as one part, which will result in an error.
In this case, the solution is to put data in different rows. You have to list all the fields that will be needed for any transaction. If a few fields are not needed in a few cases then you can leave them blank. Blank cells are ignored from the processing.
