0

I want to find jobs that have lines where "Work status" is DISPATCHED and "Reason ID" is empty (in the picture upper one marked with red). With my current inquiry it still finds also jobs that have lines where work status is right but there is random reason ID (in the picture lower one marked with red). https://i.stack.imgur.com/RmBVH.png

Here is my current query:

https://i.stack.imgur.com/8jHDd.png

Any idea what is wrong with my inquiry? So I want jobs where in "Stage reasons table work status is DISPATCHED and ReasonID is empty. The way tables are connected is 1:n. Im using Microsoft AX 2009

Heka
  • 11
  • 3
  • What query (or what form in standard AX) are you using? – 10p Jul 23 '14 at 09:46
  • Im in "Work Details" form then I press Ctrl + F3 to get to this query options which are showed in 2nd picture – Heka Jul 23 '14 at 10:26
  • The `ProdTable` form? I don't see the "Stage reasons" datasource there. – 10p Jul 23 '14 at 10:45
  • The table name doesn't matter. Problem is how I can make it check that every row has my desired terms. So "status" = DISPATCHED and "readonid" = empty – Heka Jul 24 '14 at 05:37
  • The filter from your second screenshot is correct. However it is not clear what query is being used in the form, hence I wanted to check it in standard AX. If the standard form had been modified (e.g. new datasources added, etc.) then you should have clarified what query/datasources are currently used there. Presently it isn't clear whether there is an inner, outer, or maybe even delayed join between the datasources. You should expand your question with all info you have about the query you're using. There is simply not enough information to help you. But the filter seems to be OK. – 10p Jul 24 '14 at 08:55
  • The way tables are connected is 1:n. So I right clicked "Word order" and choose 1:n then I chose "Stage reasons" – Heka Jul 25 '14 at 08:48

1 Answers1

0

Possible explanation: a work order is linked to more than 1 stage reason. Your filter returns all work orders that have at least 1 stage reason with empty Reason Id. However, the same work orders can also have other stage reasons linked to it, the ones with not empty Reason Id.

If you need only work orders that don't have stage reasons with filled in Reason Id's, notexists join should be used in the code. It isn't possible to join the tables that way in the Inquiry form in standard AX without special customization.

10p
  • 5,488
  • 22
  • 30