You’re offline. This is a read only version of the page.
280
Hi all
Running AX 2012 R3 CU9.
Have spent a long time with workarounds for this in various projects because I can't get the simple methods to work.
I have created a small simple query to show the problem I'm having. See below query TEE_FirstConfirmation linking SalesLine parent table with CustConfirmTrans child table :
As you can see I've selected "firstOnly" property (on both of these data sources, though I've also tried only header and only footer). But when I look at an order which has multiple CustConfirmTrans records, it's still splitting and giving me multiple CustConfirmTrans.
I need a way of selecting only the first CustConfirmTrans record - currently I use a view which fetches the first record in behaviour similar to a subquery, but that small complication magnifies into long wait times on reports with multiple similar joins.
I'm so sure there must be something simple I'm missing on such a common use case. Any help would be much appreciated
Thanks very much in advance
Luke
*This post is locked for comments
You couldn't select fields from SalesLine if no fields were to be returned, as discussed before.
I didn't understand what you wanted to achieve by your originally query; I got an idea only after looking at your solution. It's what I would do.
Hi all
With lack of finding a better alternative, I simply made a view which linked SalesLine and CustConfirmTrans, displaying SalesId, LineNum, and Min(CustConfirmTrans.RecId), and then made a query which linked this view to the CustConfirmTrans table proper by Min(RecId) = RecId.
Again, I'm sure there must be a more efficient way of doing this but I had a deadline :/
Hi Martin
I mean when you create a view or a form looking at the query as mentioned above, "SalesLine" is not a valid entry in the dropdown, and can't be picked from the bottom. See below:
(Since making this post, I did a full AOT/CIL/SQL build and compile on the environment and this didn't go away so I'm assuming it's not a cacheing problem)
I also don't think an ExistsJoin is the right solution - I was proposed a solution in the comments and I thought I may as well test it.
If you can shed some light on why the initial question of the FirstOnly property isn't working as I'd expect, it'd be much appreciated - I'm willing to try anything at this point haha. The SQL command as it appears in the picture in the first post definitely has correct syntax as far as I'm aware
What do you mean by "data source is not available"? If you mean that no data is fetched for SalesLine, it's becaused the exists join. Exists joins are used just for checking condition without returning any fields; if you want field values, use a different type of join (inner join, most likely).
Hi Harry
I created a query as you suggested but in this version, when I use this as a form data source or a view data source, the child data source (SalesLine in this case) is not available. So I can't even test if it works.
Pls see query above - possibly this will help
At any rate I am confused why this would be the solution? All the records in my data set will have a link at this point - I am worried about getting rid of multiple records
Give a try to put CustConfirmTrans in first place with select firstOnly and than make a simple exists join with other tables.
Hi Crispin
It's to feed an SSRS report, this seems like an easier solution if it will just work the way I expect it to haha
This is also a simple example which illustrates a problem I have in more complex reports. As I said, I'm sure it must be a supported use case of AOT queries as the property exists
Cheers
Luke
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (