Display/count number of orders that an item appears upon

rsaritzky
Member Posts: 469
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
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
Ron
0
Answers
-
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-queries0 -
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.best regards
Franz Kalchmair, MVP
Alias: Jonathan Archer
please like / agree / verify my answer, if it was helpful for you. thx.
Blog: http://moxie4nav.wordpress.com/0 -
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 isItem 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 ItemItem 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.
RonRon0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions