Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases now! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Dynamics 365 for Finance and Operations Development Cookbook

You're reading from   Dynamics 365 for Finance and Operations Development Cookbook Recipes to explore forms, look-ups and different integrations like Power BI and MS Office for your business solutions

Arrow left icon
Product type Paperback
Published in Aug 2017
Publisher Packt
ISBN-13 9781786468864
Length 480 pages
Edition 4th Edition
Languages
Arrow right icon
Authors (2):
Arrow left icon
Deepak Agarwal Deepak Agarwal
Author Profile Icon Deepak Agarwal
Deepak Agarwal
Abhimanyu Singh Abhimanyu Singh
Author Profile Icon Abhimanyu Singh
Abhimanyu Singh
Arrow right icon
View More author details
Toc

Table of Contents (11) Chapters Close

Preface 1. Processing Data FREE CHAPTER 2. Working with Forms 3. Working with Data in Forms 4. Building Lookups 5. Processing Business Tasks 6. Data Management 7. Integration with Microsoft Office 8. Integration with Power BI 9. Integration with Services 10. Improving Development Efficiency and Performance

Using a macro in a SQL statement

In a standard Dynamics 365 for Finance and Operations application, there are macros, such as InventDimJoin and InventDimSelect, which are reused numerous times across the application. These macros are actually full or partial X++ SQL queries that can be called with various arguments. Such approaches save development time by allowing you to reuse pieces of X++ SQL queries.

In this recipe, we will create a small macro, which holds a single where clause, to display only the active vendor records. Then, we will create a class that uses the created macros to display a vendor list.

How to do it...

Carry out the following steps in order to complete this recipe:

  1. Create a Dynamics 365 for Operations Project and create a new macro named VendTableNotBlocked with the following code snippet:
       (%1.Blocked == CustVendorBlocked::No) 
  1. In the Dynamics 365 Project, create a new runnable class called VendTableMacro with the following code:
       class VendTableMacro 
      {         
        /// <summary> 
        /// Runs the class with the specified arguments. 
        /// </summary> 
        /// <param name = "_args">The specified arguments.</param> 
        public static void main(Args _args) 
       { 
         VendTable   vendTable; 
 
         while select vendTable 
         where #VendTableNotBlocked(vendTable) 
        { 
           info(strFmt( 
           "%1 - %2", 
           vendTable.AccountNum, 
           vendTable.name())); 
        }         
       } 
 
      } 
  1. Run the job and check the results, as shown in the following screenshot:

How it works...

First, we define a macro that holds the where clause. Normally, the purpose of defining SQL in a macro is to reuse it a number of times in various places. We use %1 as an argument. More arguments can be used.

Next, we create a job with the select statement. Here, we use the previously created macro in the where clause and pass vendTable as an argument.

The query works like any other query, but the advantage is that the code in the macro can be reused elsewhere.

Remember that before we start using macros in SQL queries, we should be aware of the following caveats:

  • Too much code in a macro might reduce the SQL statement's readability for other developers
  • Cross-references do not take into account the code inside the macro
  • Changes in the macro will not reflect in the objects where the macro is used until the objects are recompiled
You have been reading a chapter from
Dynamics 365 for Finance and Operations Development Cookbook - Fourth Edition
Published in: Aug 2017
Publisher: Packt
ISBN-13: 9781786468864
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime