You’re offline. This is a read only version of the page.
90
Good day all,
I have a request from our sales team to provide them a cue/queue which displays the last 2 days sales orders that do not contain a certain part number (as the call centre are instructed to add this part to all orders that match certain criteria, and this is to catch the ones that have slipped through). I based my cue on the form MCRSalesTableOrderHistory, located Call centre/Inquiries/Sales orders.
I have put in several filters but I cannot get the cue to return orders that do not contain a specific part number. I have tried adding in the table "Order Lines" and setting the filter to Order lines/Order lines/Model number/!P1 (as "P1" is the part number in question we need to add to all orders).
This does not work as it is still returning orders that contain lines with "P1" in. I guess this is because they also contain order lines that are not P1.
I understand you can use some SQL in these to so tried Order lines/Order lines/Model number/(SalesLine.ItemId != "P1") but this gives me an error "Query extended range failure: SalesLine.ItemId is not a valid datasource.field pair near pos 22."
As I know zero about SQL I am probably going about this the wrong way, but would appreciate any help on what form/filters I could use to do this, or will I have to get a bespoke report written up please?
Many thanks.
*This post is locked for comments
Hi CXF,
Guy is correct that you need a customization here. You need to have a not exists join which is by default t not possible with the advanced quesry. Kurt did post a blog how to enable this: kurthatlevik.com/.../dynamics-ax-2012-r3-to-exist-or-not-that-is-the-question
I'm not sure if the procedures from Kurt might be possible on Dynamics 365 for Finance and Operations as it needs to be extension based coding only. You can also create a custom report or form to show the records that needs attention. Not exists joins in coding is supported out of the box: msdynamicsxx2012.blogspot.com/.../how-to-use-not-exists-join-in-axapta.html
I don't think you'll be able to do it without a customisation. If you try to build a normal query, the best question you can ask is 'Show me Sales orders which have an order line which does not contain Item P1'. Since all of your sales orders will have at least one line which contains a different item, they will all show in the result of the query. It would be possible to do it the other way around (Show me Sales orders which have a line that contains item P1), but I don't think this will help too much. I suppose you could do two Cues. One for 'all orders in the last two days' and one for 'all orders in the last two days which have item P1'. When they don't match, something needs investgating.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (