First Only on a field in a query.

Hi all, I have a query as a datasource on a form.

The data I am working with has many lines per order, but I would only like to see the first record per order.

I have know there is a property FirstOnly, but this is not what I want.

How can this be achieved using an AOT query in the froms datasource?

Why don’t you want to use FirstOnly? Is there any additional requirement that prevents that?

Example data:

Order1 item1

Order1 item2

Order 2 item1

As there are multiple lines with orders, I only need to display the first line for that order.

Expected outcome:

Order1 item1

Order 2 item1

If you us the FirstOnly property you only get the first record on the whole dataset.

So the limitation is that you need to join them together, right? If you had an order and lines linked (e.g. by a Delayed link such as in PurchTable form), you would simply set FirstOnly for the datasource representing lines.

If you need to join the tables, you’re quite limited by AX version of SQL, namely the lack of support for subqueries. Maybe you can find a range usable in your situation (e.g. LineNum == 1). Maybe you will rather find all first lines (min(LineNum) group by ID), save them to a TempDB table and use that for filtering (exists join) lines in your original query. Or maybe you’ll find a better idea than I’m able on Friday evening. [:)]