Display/count number of orders that an item appears upon

rsaritzky · 2016-11-01

Hi all,

I'd like to create a factbox for the Item Card that contains:

Number of Open Orders (that this item appears on)
Number of Open Invoices (that this item appears on)
Number of Open Credit Memos
Number of Posted Credit Memos
<...you get the idea>

Note this is NOT "Quantity on open orders" - this is a standard flowfield

So I want to COUNT the orders where a line EXISTS on that order. If the item is on the same order twice, I only want to count it once (yes we do that - think of same item, different variant as an example).

I've tried to construct a query object that might do this, but have been unable to do so.

I know I can create a SQL query that would do this, but I'm trying to avoid a "LinkedObject" type of table.

And I know I can create functions on the table which will loop through the lines and count them and return the count, but I'm looking for an alternative.

Has anyone tried this?

Thanks

Ron

Kishorm · 2016-11-01

Basically you can use the Query object for your requirements and group by Document No. so that you end up with 1 row per document, then you just use the number of rows. Have a look at this link which shows you how you can create a query to group by document no. (using "count" as a "method" field value)...

https://community.dynamics.com/nav/w/designpatterns/152.select-distinct-with-queries

archer89 · 2016-11-02

you could solve that using a temp sales line record within the factbox, where add only the sales lines you need count the documents in a loop.

rsaritzky · 2016-11-03

Hi Kishorm,

Creating a query that has one line per document is the first step. But now I want/need to group by Item. So if my query result is

Item   Order     Lines
ABC    12345    2
ABC    12378    1
DEF     12121    1
DEF     12333    3
DEF     12223    4

Now I want to group the results by Item

Item   No. of orders
ABC    2
DEF    3

It's the second part that I haven't been able to figure out if a query object can be built to do this.

Yes, I can create a function in code to do this - and Jonathan's suggestion about creating a temporary table doesn't provide any benefit (in this particular case). I'm trying to be a little creative and see if I can minimize (even eliminate) any code by using a query object. As I mentioned before, I can create a SQL Query that will do exactly what I need - I'm just trying to figure out if this can be done in a query object instead.

Thanks for all the suggestions.

Ron

Howdy, Stranger!

Quick Links

Categories

Display/count number of orders that an item appears upon

Answers