D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Cue / query for empty sales orders

  • 1.  Cue / query for empty sales orders

    Posted Jul 18, 2019 04:44 PM
    Hi

    I am trying to save a view of all sales orders created which are empty (no sales order lines). We just went live with one of our global sales companies and i was hooping to review this to reuse some existing records and could potentially perform periodic cleanup

    Is it possible to create this as a query on ' all sales orders' grid and then save it as a cue for reference?

    Thanks

    KP

    ------------------------------
    Karan Pathak
    Application Team Lead, Enterprise
    Demant
    ------------------------------


  • 2.  RE: Cue / query for empty sales orders

    TOP CONTRIBUTOR
    Posted Jul 19, 2019 03:06 AM
    Hi KP,

    I don't think it'll be possible to build that filter using the standard Advanced Filter/Sort. Best I can think is that its not possible to Confirm a Sales order which has no lines. Depending on how you are using Sales order confirmations, you could filter for Sales orders where the Document status = None.

    ------------------------------
    Guy Terry
    Application Consultant
    Annata
    Portsmouth
    ------------------------------



  • 3.  RE: Cue / query for empty sales orders

    Posted Jul 19, 2019 09:34 AM
    #AX2012

    Hi Guy

    Thanks for your prompt response. I tried using the 'document status' filter before but there are many open and canceled orders seen (not confirmed) with sales order lines included

    Thanks

    KP​

    ------------------------------
    Karan Pathak
    Application Team Lead, Enterprise
    Demant
    ------------------------------



  • 4.  RE: Cue / query for empty sales orders

    SILVER CONTRIBUTOR
    Posted 30 days ago
    Hi Karan,

    I believe Demant/Oticon have Atlas, at least in the Denmark offices. This is about a 5-10 minute report build in Atlas. I started from SalesTable then did a merge with an outer join to SalesLine, returning a count on the RecID column from SalesLine for each order number in SalesTable. I'm describing the methodology in case you don't have access to an Atlas user license. If you do have access to Atlas, I'll email you this report and you can refresh it on your data.



    Regards,
    Jason

    ------------------------------
    Jason Carter
    Solutions Enablement - Atlas
    Insightsoftware
    Fargo ND
    ------------------------------



  • 5.  RE: Cue / query for empty sales orders

    Posted 28 days ago
    Hi Jason
    This requirement is specific to a new sales company where we have implemented AX. I dont think (and I am not sure) if any of our global colleagues (I am based in US and the sales company location is ITA) have access to ATLAS

    Thanks for your response.

    Best Regards,

    ------------------------------
    Karan Pathak
    Application Team Lead, Enterprise
    Demant
    ------------------------------



  • 6.  RE: Cue / query for empty sales orders

    TOP CONTRIBUTOR
    Posted 28 days ago
    We've added the ability to do not exist joins in the Advanced Filter dialogue in R3 and prior, we haven't done it yet for D365.  If you do add that functionality, its actually really useful, and it would allow users to create the query you describe.

    There's more info here: https://kurthatlevik.com/2014/11/19/dynamics-ax-2012-r3-to-exist-or-not-that-is-the-question/

    and an Idea submitted that you should vote for: https://experience.dynamics.com/ideas/idea/?ideaid=aa63ac99-3686-e911-80e7-0003ff68897c

    ------------------------------
    Colby Gallagher
    Manufacturing Systems Consultant
    Agility Business Solutions
    Brecksville OH
    ------------------------------



  • 7.  RE: Cue / query for empty sales orders

    Posted 28 days ago
    Hi Colby
    Thanks for your input. I really like the idea of 'notExistsJOIN' to build advanced queries like this in AX 2012 and I am all in to have this rolled in for D365

    Best Regards,

    ------------------------------
    Karan Pathak
    Application Team Lead, Enterprise
    Demant
    ------------------------------



  • 8.  RE: Cue / query for empty sales orders

    Posted 28 days ago
    Karan,

    It's a little like running a query for something that doesn't exist.  You're looking for empty sales orders.  As soon as you link the order lines table in a Sales Order List, you'll eliminate all the SO's without lines.  So you could export your full SO list, then link the order lines list, merge them, remove duplicates and that's your list.

    That's really crude.  Would love a better way as well.

    Bryan Hunsberger

    ------------------------------
    Bryan Hunsberger
    The Master's Craft Flooring Company
    West Plains MO
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role