Terje Hals
Maybe you cannot 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 export the item’s current standard cost (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 based on an M3 table and has additional columns left joined to it with other auxiliary M3 tables.
To set up this information category, we need to understand 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 cost data, 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 add 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” fields. Note that using different prefixes makes it 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 master table. 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 from the 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. 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 can use this category when creating a custom list MI in CMS015. However, before we do this, we need to do a couple of other things.
First - Create a view containing the fields we wish to see in the custom list MI
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 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:
Second – Make sure to have the proper sorting order for the information category
The sorting order for an information category can be accessed through option 23 under “related options” in CMS010. It is important to make sure we have the proper sorting order because this will determine what fields we can filter by in the API. We want to filter by cost type, facility, item number, and costing date range in this example. 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). We will only need to consider the 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 based on facility and cost 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 option. Note that we have connected the view we created to this sorting option, but this is not a requirement since we can override this view when creating the API.
If you’d like to learn more about creating custom list APIs, Click here to view part 2.
Download content as .PDF