Custom Lists in Infor M3: Advanced Tips

Infor M3 has made tremendous progress in recent years at allowing users to define custom lists to present information within the application itself, rather than rely upon outside reporting using tools like Crystal Reports or SQL Server Reporting Services. Giving users access to the views they need within M3 allows them to take immediate action on the information provided, rather than flipping back and forth between some outside report and the ERP system.

There are some nice blog posts out there which cover the mechanics of creating your own custom lists (such as this one here), but there is an advanced aspect of custom list building which I would like to share with you today.

The basic idea with custom lists is that you start with a particular table (for example, the item master MITMAS) and link out to other Related Tables (such as the item/warehouse record, MITBAL).

Let’s say for the purposes of this discussion that we want to show the on-hand balances available in warehouses AAA, BBB and CCC. We will call this browser ITEM_BAL in CMS010. Normally, that means you would start with MITMAS, and define MITBAL as a related table. So that means you end up a record for each combination of MITMAS and MITBAL, like so:

Item	Description	Warehouse	On Hand Balance
ZZZTEST1	Widget	AAA	23
ZZZTEST1	Widget	BBB	42
ZZZTEST1	Widget	CCC	21
ZZZTEST2	Deluxe Widget	AAA	101
ZZZTEST2	Deluxe Widget	BBB	78
ZZZTEST2	Deluxe Widget	CCC	85
ZZZTEST3	Ultra Widget	AAA	121
ZZZTEST3	Ultra Widget	BBB	143
ZZZTEST3	Ultra Widget	CCC	105

This is OK, but what if we could have just one line per item, and show the warehouse balances in different columns instead, like the following?

Item	Description	AAA	BBB	CCC
ZZZTEST1	Widget	23	42	21
ZZZTEST2	Deluxe Widget	101	78	85
ZZZTEST3	Ultra Widget	121	143	105

That’s where we get a little bit fancy with our Related Tables.

Linking to the same Related Table multiple times

In order to get things down to one line per item, and a column for each warehouse, what we will do is start with our item master MITMAS, but then we will set up MITBAL as a related table three times, one for each warehouse we want to see.

The key here is that when defining the related tables in CMS011, you designate a different two-character prefix for each time you link into MITBAL, and also specify the unique warehouse which this link will reference. In this case, let’s use AA to link to warehouse AAA, BB to link to BBB, and CC to link to CCC:

Then on panel CMS011/F, where you define how the tables are linked together, you specify “AAA” as the value for MBWHLO, so you are only grabbing records from that warehouse:

And on CMS011/N, I will add the field STQT (on hand approved balance) to our field group. Note that when I add STQT, it shows up as AASTQT thanks to the prefix we designated on the way in. At this point, change the “Overriding name” field to AAA, so the column heading will show “AAA” for these values:

Now, go back and do the same for BBB and CCC. Once complete, if you pull up the related tables for our inquiry (ITEM_BAL), it should look like this:

Since we have our tables and fields all together, let’s create a View to set up our inquiry. Back in CMS010, use Related Options->View on our ITEM_BAL record, and create a new view called TEST:

Now if you go back to CMS010 and do Related Options->Simulate List, you can see our balances for the various warehouses listed side-by-side, as requested:

This is just one example of how to leverage the power of Related Tables to create a more robust view within M3 than one might normally consider possible. Stay tuned for more tips on building custom lists, and other areas of Infor M3.

Note: If you enjoyed this post and could use some help or assistance with Infor M3 (functional or technical), I am available for part-time after hours consulting. Please send a note through my Contact page or connect with me on LinkedIn for further details!