Use virtual fields to show calculations

In my last blog post I explained how to create a virtual field that converts a value into a different currency in Infor M3. In this blog post I will explain how you can use virtual fields when you need to do calculations to get the correct value to show on the screen.

I will stay in the purchase area and use PPS220, purchase order lines, as an example. On the PO line you have a purchase price which is stored in MPLINE. But if you have discounts, the purchase price minus discounts isn't saved in any table, but still you might want to see both the purchase price, and the price including discounts in the list view. The solution to this is to create a virtual field which shows purchase price including discounts.

In M3 you can have three different discounts on a PO line, and they are stored in MPLINE in the fields IBODI1, IBODI2 and IBODI3.

Picture 1: You can have three discounts on a PO line

To calculate the price minus discount you need to use the following formula:

PUPR* (1- ((DISC1+DISC2+DISC3)/100))

The calculation functionality in the new list standard is quite simple, so you need to do the calculation in four steps. For each step you need to create a virtual field, which is used as input for the next step. The following calculations needs to be done:

Calculate the discount (IBODI1+IBODI2+IBODI3) => &DISC

Divide the discount (&DISC) with 100 => &DIS2

Calculate 1 minus &DIS2 => &DIS3

Multiply the price (IBPUPR) with &DIS3 => &PDIS

We will start from CMS005, just like we did when we created the currency conversion. Use option 12 to open CMS012, virtual fields. This time we will use type calculation, and we need to use the field for calculation sequence to get the correct result.

Below you can see printscreens for all steps.

Step 1 – calculate total discount

Step 2 – divide the discount by 100

Note that the calculation sequence is higher than the sequence in the first step, and that we need 2 decimals in this step:

Step 3 – calculate 1 minus the discount

Step 4 – multiply the purchase price with the percentage we should pay

Now we just have to add the fields to the view in PPS220, and the users can see the price both with and without discount!

You can use the calculated virtual fields for all programs available in CMS005. The functionality is also available within the custom list functionality, which means that you can use the virtual fields in views showing data from all tables, as long as you use a mashup to show the result to the users. (Custom list is available from version 10.1 of M3)

Discuss this post

Recommended posts

Sometimes you need a simple way of following changes of values for certain fields in M3. By using the event log in M3 you have a handy solution 😊 Follow the steps below and you will have a log of changed values for supplier bank accounts in just a few minutes!

If you are running Infor OS/H5 you have the possibility to create really nice homepages for your users. The homepages are the starting points for all roles, and one of the widgets you probably want to have on your homepage is the menu-widget. On the menu widget you can add links to M3 programs for example, but by using bookmarks you can make the links smart, and let them open the programs with predefined selections and searches.

In this blog post I'm describing how to create bookmarks in M3. Since we are moving towards Infor OS and H5, we need to handle that there are some differences in how you manage bookmarks in the different user interfaces for M3.