1

I have a grid that displays lines from a table. Now, I have these two requirements:

  1. Show only the lines that contains values in the "hour" fields. The "hour" field is an array type.
  2. Show the lines from a project and its subproject

My problem is this: to meet requirement #1, I need to use a select statement in my datasource since I cannot access array value using QueryBuildDataSource (this is a known limitation in Dynamics AX).

But to meet requirements #2 I need to have two "exists join", and this is not possible in Dynamics AX. For example the following code would not work:

select from table where
exists join tableChild where projectId = MyProjectId OR
exists join tableChild where parentProjectId = MyProjectId

How would someone address this issue?

Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
Thierry Roy
  • 8,452
  • 10
  • 60
  • 84

2 Answers2

2

From the select statement example you provided, it appears it may not be necessary to have two exist joins. A single exists join with the "OR" for the two possible conditions in its where clauses may be sufficient, of course, you should still have some relationship between table and tableChild for the join to make logical sense.

select from table
exists join tableChild
where (tableChild.projectId = MyProjectId || tableChild.parentProjectId = MyProjectId)
Vince Perta
  • 364
  • 1
  • 6
0

You can use array values in query ranges providing field IDs using fieldID2Ext function.

You can build view and apply confitions on top of it.

user423060
  • 1
  • I tried using fieldID2Ex, but I couldn't make it work like a "Or". For example "where dimension[1]>0 OR dimension[2]>0". can you elaborate on "build view and apply confitions on top of it"? – Thierry Roy Aug 18 '10 at 13:47
  • You must use datasource name when using array fields http://www.axaptapedia.com/Expressions_in_query_ranges#Filter_on_array_fields – user423060 Oct 10 '10 at 05:23