Sometimes, when we need to extract data from Infor M3(M3) using Vince Excel (VXL), you may find that the standard APIs that are available are not quite up to snuff. Maybe you are not able to export the fields that you need, or perhaps you are not allowed to filter the output in the way you would like. Fortunately, there are ways to create your own APIs in M3, and perhaps the most flexible and intuitive way is to create a custom list MI in CMS015.
The best way to show how to create a custom list MI is to show with an example. Let’s assume that we want to create an API that shows all the standard cost product costings of a given type that have been calculated for a range of items, within a specified time period, and for a given facility. This information is stored in the MCHEAD table and is usually viewed in M3 through PCS300. In the API that we want to create we also want to be able to export the current standard cost of the item (from the table MITFAC and shown in MMS003/E) plus the make/buy code found in the item master (MITMAS or MMS001/E).
The first step we need to take to set up a custom list MI is to create an information category in CMS010. In this context of creating a custom list API, an information category is essentially a custom table that we can create which is based on a M3 table and which has additional columns left joined to it with other auxiliary M3 tables.
To set up this information category, we need some understanding of what we want to achieve with our API. In our example, we want to select all records that satisfy a certain costing type, facility, item number, and costing date, all of which are fields in the MCHEAD table. Therefore, we will want to create an information category with the master table being MCHEAD.
Since we also want information from MITMAS and MITFAC, we will need to add these tables as “Related tables.” We can do this with by adding a so-called related table in CMS011/B1, which is essentially a left join of the related table to the master table. Such a related table can be added either by click “Create New” or by entering our table + a prefix in the “Table” and “Pfx” field. Note that using different prefixes, it is possible to join the same table multiple times, but this will not be covered here today.
We are then taken to CMS011/F where we need to match the key-fields in a given sorting option (for the related table) with the fields in the related table with the fields in our mastertable. Think of it as an “ON” command in an SQL-query.
After a related table has been “connected” to our master table, we must also specify which fields in from related table we are interested in using in our information category. This is done with option 7 “Add field to field group” in CMS011/B1, which takes us to CMS011/N. Here we can add specific fields by clicking “select field” and selecting the fields we want from the related table. In our case we want UCOS (Cost price) from MITFAC.
Once we have an information category with all the related tables and fields we want, we are then able to use this category when creating a custom list MI in CMS015. However, before we do this, there are a couple of other things that we need to do.
In this first step, we need to create a view which must contain the fields we want to see in the custom list MI that we are creating. Even though we have all the fields that you need in the information category that we just created, we also need to create a custom view for this information category. Why? Because when we will create the API in CMS015, the fields used as the output of this API are defined by the fields in the view that we connect to the API. This view is created by going to option 22 (“View”) in CMS010. The view is created as any other view you would create for a program in CRS020 and CRS016, and below is the view that has been created for this example:
The sorting order for an information category can be accessed through option 23 under “related options” in CMS010. The reason it is important to make sure we have the proper sorting order is that this will determine what fields we are able to filter by in the API. In this example, we want to filter by costing type, facility, item number, and costing date range. But since the item number and costing date will be filtered using a range, we won’t consider these when creating a sorting order (it will be apparent why later on), and we will only need to consider costing type and facility in the sorting order.
Since the master-table was MCHEAD, our information category will have the automatically created sorting orders, one for each sorting option that exists for MCHEAD (see CRS021)
Because in our example we want to filter on the basis of facility and costing type, we will need to create a sorting order that has these two fields as the first keys in its sorting option. As we can see in the screenshot above, none of the existing sorting options have both facility (KOFACI) and costing type (KOPCTP) as the first two keys, so you will need to create one from scratch.
With your new sorting option, we also need to create a sorting order that uses this sorting option. Note that we also have connected the view we created to this sorting option, but this is not a requirement since we can simply override this view when we create the API.