Infor M3 #2 -- Custom list API to fetch data from multiple tables without M3 modifications

Custom list API (CMS100MI) to fetch data from multiple tables without M3 modifications

Custom list API is used to create an API (Movex Interface's transaction under CMS100MI) just like we create MDBREADMI transactions from MRS002 and MRS003, here we can have multiple tables data, virtual fields, selection criteria, filters and aggregation unlike in MDBREADMI.

All of us know that maintaining MAK fixes is a big headache, if you are looking to extract data from multiple tables without writing Java code then you are right place.

This API can be created with below properties

1. Without selection criteria, without filters and without aggregation
2. With selection criteria, with filters and with aggregation
3. Without selection criteria, with filters and without aggregation
4. Without selection criteria, without filters and with aggregation
5. With selection criteria, without filters and without aggregation

Let's create an API for first two scenarios by considering an example. Let's take a scenario where user wants to get item details (MITMAS) along with style details (MITMAH).

Let's segregate the entire process into five steps to understand it better. For the above five types step 1,2,3 and 5 are same, only step 4 changes.

Step 1 : Create a transaction ID in CMS015

• Open CMS015, write down the transaction ID and create by using related option 1
• Transaction ID has a rule, ID must be starting with Lst
• In our example let's take MITMAS (Item master) as Master table as we want to fetch item related data

• In next panel you can configure the properties like description, name, Information cat etc.,

Step 2 : Define Information category

• Press F4 twice from CMS015/E, system would redirect you to CMS010 to choose the Information category
• If you have the Information category defined already then you may choose it or else you may create new by following the below process
• Define new Information category related to your list by using option 1, in Custom list API we don't need to create Related options for Information cat so in this process we would skip it

• In next panel you can define the Information category properties like the master table for report, description of Information cat etc.,

Add Related tables

• Related tables are the tables which we use to fetch the related data by making a connection between master table and related table

• Add all related tables which we want in report by selecting Related option 11 from CMS010

• Write down table name and use create option, here we can configure the table properties like giving table fields a prefix (in case you want use the same table for different purposes), sequence by which tables have to be loaded (if there is any dependency on the other table’s data), fields selection (you can choose only the fields which you want from the particular table or you can choose all) which would be used in list/report
• You can add multiple related tables

Add Virtual fields 

• Virtual fields are generally used for numeric calculations, date conversions, currency conversions, fetch details from CSYTAB, merge data of multiple fields and logical formula
• Virtual field name should start with & and the field name must be 5 characters in size
• Add virtual fields by selecting Related option 12

• You can configure the virtual field properties in next panel like what kind of field it is, what are the inputs and what is the expected output

Check Field group

• All fields from master table, fields which you have added from related tables and virtual fields would be available for use. In case you want to add more then go to related tables and virtual fields to add new fields
• You can check field group using Related option 21

Sorting option and View

• By default you will be able to see all the sorting options and views related to your master table
• You can either select any standard sorting order and view which is suitable for your requirement or you can create your own sorting orders and views by using create option
• Use Related option 22 to check/create the sorting order and Related option 23 to check/create view

Step 3 : Choose sorting order and a view

• After choosing the Information category, press enter. You would see few more fields populated below to it to configure the inputs and outputs of the API
• Choose the sorting option which would fulfill your requirement
• All primary keys from sorting order would be your API's inputs
• Choose a view related to the selected sorting order, views can be populated based on the selected sorting order
• All fields from view are API's output fields which may include virtual fields as well

Step 4 : Choose filters, selection criteria and aggregation

This step differs for the different types of API's earlier we have talked about

Without filters, without selection criteria and without aggregation

• Choose filter as no filter, which enables API to have all inputs as non mandatory
• Leave selection criteria as blank, which would not add any from-to selection in API's input
• Leave aggregation as no aggregation to make API's output as it is from the selected view

With filters, with selection criteria and with aggregation

• Based on the selected sorting order, you can put filters to your API
• Choose the available filters from the drop down, which will make API's inputs mandatory
• Maximum three selection fields can be made, select fields which you want to use as selection fields
• This will add additional inputs to your API as From field value and To field value
• Choose aggregation based on which field it should happen and which field should be aggregated. This would add a field in your API's output
• Aggregation will not calculate anything, but just gives you a record in output to represent the subtotal place

• Differences between these two you will see later in last step

Step 5 : Create MI transaction

• Once you are done with all of the above, create MI transaction using Related option 20 in CMS015

• Update MI repository will change the status of Transaction ID to 20 which means active, once the status is 20 then you will not be able to change it. You need to delete it by using option 21 then again recreate using option 20

• Update MI repository will create a transaction in MRS002 and input/output fields in MRS003 under CMS100MI

• Use option 22 to check the created API details

Without filter, without selection criteria and without aggregation

Inputs :

Outputs :

With filter, with selection criteria and with aggregation

Inputs :

Outputs :

**If you want to change the input/output fields in future, you just need to add those fields in sorting order and view and use options 21 and 20 to delete the current API and generate new API respectively.

MI Test results : 

Without filter, without selection criteria and without aggregation

Inputs :

Output :

With filter, with selection criteria and with aggregation

Inputs :

Output :

That's it, come back here and tell us about the before-and-after. I bet you’ll have something to say!

Please let me know your thoughts in comment section below!

If you enjoyed this post, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook or LinkedIn.

Check out Custom list to prepare simple lists/reports without programming by clicking here!

Next week I’ll post about the Addition of new columns into list panel (panel B) without M3 modifications.

Thanks for reading!

--Saiteja Saadi