Query creation

Hello All,

I have a requirement in AX 2012 which is as follows;

For example : A single sales order has multiple lines in it and for each line there are multiple charge codes assigned to it.

I want to fetch the first record from sales line and first record from charge code assigned to that line.

So for above scenario how can i create an AOT query ?

I have used the first only property but in vain.

Please help.

Do you want to get the first for a single order only, or first lines for multiple orders? (The first case is trivial, the latter is tricky.)

As the example i have given above,based on that first record of charge code for the first sales line for all sales orders.

That’s something what you can’t easily do in AX, because it requires subqueries (e.g. you want to run a subquery for every sales order to select a single sales line with the lowest line number).

I think you could do it if you used a subquery to get an ID of the first line (see my blog post about subqueries in views) and joined the view with SalesLine table. But it’s questionable if it’s worth doing.

You can also consider changing your data model, so you can filter on what you need in your query.

Thanks Martin.

Let me check the blog and return to you with an update whether it worked for me.

Hello Martin,

The blog thing worked for me. It gives the output in the form of string but can you help me lastly how to cast it into a date field as the computed column is of Date data type.

Sorry for the previous message . It was my mistake. Its solved.

Thanks once again Martin.