Exploring Alternatives for SELECT TOP in X++ - D365 Finance and Supply Chain

-
Recently, I had to write a query that retrieves Batches with the oldest Manufacturing Date in D365 Finance and Supply Chain, utilizing the "SELECT TOP" functionality. However, as you may be aware, the TOP keyword is natively supported in SQL but not when composing a select statement in D365 and X++. Nevertheless, there are alternative techniques to accomplish the same objective. Additionally, I have included some performance metrics to assist you in choosing the most appropriate method based on your specific requirements.

Scenario: Write a query to get the top 3 batches and locations which have the oldest Manufacturing Date for an item and have Available Physical > 0, grouped by the Item, Batch Id, Manufacturing Date and the location.

Assumptions: One legal entity, with one site and warehouse

Here's how the SELECT TOP Clause would look like in SQL:



Method 1: Select statement using a break 
Once we reach the required number of records, we break from the while loop.


Performance:


Method 2: Query
The Query class does have a topRows method which allows you to specific the max number of records to return.

Performance:


Method 3: SysDa framework
The SysDa Framework also has a top method on the SysDaQueryObject to indicate the number of records to return.

Performance:


Hope this helps in giving you some ideas and making the right decision for your business requirements.


Comments

  1. Anonymous18 May 2023 at 00:55

    Nice post. Thanks Shail. I did not know about topRows() exists in Query class. Good to know. Meantime, SysDa is still something mystical for me, I have not worked with that yet))

    ReplyDelete

Post a Comment

Popular posts from this blog

Override a System Defined Refresh button in D365O

-
Image
Essentially this post is going to discuss calling custom code while refreshing a form in D365O, however, you can find more information about other system defined buttons and how you can run extra code or suppress them in this article:  System-Defined buttons Firstly the datahelper() method of the formRun framework class can be used to provide services that allow managing the datasources of the form. How can I add custom code to run when I click refresh? We are going to use eventing (pre-event handler and post-event handler) to do this: 1. Write your method that you want to call when the refresh button is clicked 2. In the Form.int subscribe using the datahelper().Refreshing event 3. On refresh click you'll see your message
Read more

Printing PDF Attachments in MSDyn365FO

-
Image
In this post I’ll detail how you can print PDF document attachments using AXs DocumentContract framework. I have used this forum post as a reference: https://community.dynamics.com/ax/f/33/p/222925/785091#785091 1. Create extension of the DocuView form to introduce a new Print button. 2. Next thing is to create a handler class that will have all our event handlers. a. Firstly, we create a class with the reference to DocumentContract AX framework. In my case, I am also using Reporting framework. b. Let’s subscribe to the OnActivated event of the main DataSource which is DocuRef. This is so we can enable or disable printing by checking if the reference is a document and is of type ‘pdf’ c. Then we can create subscribe to the OnClicked event of the new button Print button. We will write our printing logic here. d. Once you click print, you’ll get the following message and you have managed to successfully print an attachment:
Read more