Microsoft Dynamics AX (Archived)

Query firstOnly having problems

(0) Share

Report

Posted on by lukbel

282

Hi all

Running AX 2012 R3 CU9

Building a query to feed a view I'm making, and I'm having trouble with duplicate records.

In this query, I'm linking the SalesLine table to multiple transaction tables (CustConfirmTrans, CustPackingSlipTrans, CustInvoiceTrans) and I'm trying to get the first record for each of them for KPI values.

For the most part, I've used the firstOnly property on the query table's properties, and it usually seems to work. But in this instance, the link to CustConfirmTrans isn't limiting it, I'm still getting multiple records against that table. I've attached a screenshot of the query table layout along with the properties of the child data source.

Pls let me know if there's any other information that would be helpful.

3652.Capture.PNG

Thanks very much in advance

Cheers

Luke

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sohaib Cheema 48,887 User Group Leader on at

Like (0)

Report

RE: Query firstOnly having problems

Hi Luke,

I can think that your query is being translated into SQL server in a way that is resulting as LEFT OUTER JOIN between SalesLine and CUSTCONFIRMTRANS

So what does that means by having left outer join? That means bring all records from child table (CUSTCONFIRMTRANS) that are matching with the salesLine. This also means if a sale order has been confirmed more than one time, you would get duplicate record for that sales order in the table CUSTCONFIRMTRANS. Hence your query will return multiple records for some cases, whereas you are expecting to get TOP 1 RECORD as you have used FirstOnly.

That is how AX works. Please have a look at next blogPost

Now, lets come to the point, on how you can fix this.

To get this what you are trying to do, please write computed columns for each field that you want to display from CUSTCONFIRMTRANS

While you will write the computed column you would mention Top 1 in your query and also you would write where clause to get single record from CUSTCONFIRMTRANS so that you get the latest confirmation line, as sales order can be confirmed multiple times.

Was this reply helpful? Yes No
lukbel 282 on at

Like (0)

Report

RE: Query firstOnly having problems

Hi Sohaib

Thanks for your response

I had been using computed columns for the columns, however it was slowing it down running multiple viewmethods which went to the same table, as it is running across a LOT of records.

If firstOnly does not return TOP 1, then what is the purpose of it? Is there no way to force a TOP 1 value on the query without making multiple computed columns for every single field you want from the child table?

Cheers

Luke

Was this reply helpful? Yes No
Sohaib Cheema 48,887 User Group Leader on at

Like (0)

Report

RE: Query firstOnly having problems

Hi Luke,

If performance is a factor here you can try another idea that can make you get rid of these computed columns.

You would want to use Having node of the Query. If you see your query, you have a node there called Having. So you will make use of having with Max(ConfimationDate). Remember in this case you would need to add fields under GroupBy as well. And any aggregation fields will be dropped under list of fields without adding those into group-by clause.

Here is the example

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/walkthrough-creating-an-aot-query-that-has-group-by-and-having-nodes

Was this reply helpful? Yes No
lukbel 282 on at

Like (0)

Report

RE: Query firstOnly having problems

Hi Sohaib

I added Group By CustConfirmTrans.RecId and Having Min(CustConfirmTrans.RecId) and got a sync error.

This is the T-SQL it was trying to create:

This is the query as it stands:

I'm not sure why HAVING isn't showing up in the final product, everything is definitely synchronised etc. (Also, not sure why GROUP BY is showing all those additional fields)

In addition, I have a question about how this query will work logistically using HAVING MIN(RECID) - would it allow a null value to be returned by the LEFT OUTER JOIN? I am still trying to pick up lines which don't have a confirmation journal line yet.

Thanks very much for your help so far

Was this reply helpful? Yes No
Sohaib Cheema 48,887 User Group Leader on at

Like (0)

Report

RE: Query firstOnly having problems

Hi Luke,

One should not add RecId under group by or Having Node.

If you read my previous reply, you would find that I suggested to add Max(ConfirmDate) under having node and under group by field only add those fields which are not of Real Type and never the RecId. add only fields that are needed. keep the Dynamic = NO;

Remove columns from data-sources that you have added to group by and keep only those columns under fields node, which are of type aggregate and those which are in group by node.

If you will go through next example, it can give you a good idea.

docs.microsoft.com/.../walkthrough-creating-an-aot-query-that-has-group-by-and-having-nodes

Was this reply helpful? Yes No