Sometimes you want to know how many records you have of a certain type in a list. If I am looking at purchase order lines I might want to know how many open order lines I have per supplier, or how many delayed order lines we have at the moment. If I am working with manufacturing orders I might want to know how many open orders we have in my facility, or how many orders we have of a certain type. How do we show this in a simple way?
The answer is to use the functionality we have in the configurable list standard. You can find all the standard programs where this standard is available in CMS005, but don´t worry, if you can´t find your program in that list you can build your own using CMS010. (If you check my older blog posts you will find information how to do this)
In this blog post I will build a list that shows me how many open purchase order lines I have per buyer/supplier, and how many delayed order lines I have.
I will start my work from CMS005, and the program I want to connect this functionality to is PPS220.
The first thing I need to do is to create a sorting order that suits my needs. In this case I chose a sorting option that sorts on buyer and supplier, and I have entered requested delivery date and status as selection fields. I have also ticked the boxes in the lower left corner to make aggregation possible.
The next step is to create a virtual field that will make it possible for me to calculate the number of lines I have in the different selections I want to do. I chose related option “Virtual fields” from CMS005, and I have named my new field &COUN. The syntax is always & followed by 4 characters.
The type of my new numerical field is logical formula. I have entered a condition that is always true, since I want the number “1” to be entered in this field for all lines.
When the new field is ready, I need to connect it to the view. I have to enter 30 (summarize) as aggregation display rule to be able to get the totals I am looking for.
Now I have a sorting order, my virtual field and my view, time to test! I open PPS220 with my sorting order and view. I enter a status selection that will only show open order lines, and I chose to aggregate on buyer/supplier. Once this is done I have a list where my counter-field shows how many open order lines I have per buyer/supplier!
And if I enter a selection that only shows open lines with a requested date in past time, I get a list showing how many delayed order lines I have per buyer/supplier.
This way of using the virtual fields is really useful! If you have any smart ways of using virtual fields that you want to share, please let me know and maybe we can share your idea on the blog as well! 😊