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)