Unleashing Dynamics 365 Excellence: Your Source for Pro Tips and Snippets at D365Snippets.com

Our blog provides a curated collection of tips, tricks, and code snippets that streamline your workflow and enhance your proficiency.
Unleashing Dynamics 365 Excellence: Your Source for Pro Tips and Snippets at D365Snippets.com

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO

0

In this article I will show you how to execute a macro in SQL statements in D365FO or How to process data using a macro in a SQL statement in D365FO, before going to the depth of the topic we have to learn the basic concept of macros in Microsoft dynamics 365 finance and operations.

If you are a beginner or new to dynamics 365 Finance & Operations, you can use Microsoft's Free Virtual Machine. Learn How to Set Up a Free Virtual Machine for Dynamics 365 Development

what-is-macro-in-d365-how-to-execute-macro-in-sql-statement-in-d365

What is Macro?

In a standard D365 FO application, there are macros that are reused many times across the application. By Using the macros approach, we can save development time by allowing you to reuse the piece of X++ SQL statements. These macros are actually full or partial X++ SQL queries that can be called with various arguments. If your macro is going to be used in several places it would make sense to create the macro in the AOT as you will then be able to reuse the macro.

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 as per our requirements.

In this article, we will create one small macro which holds a single where clause to display the active but expired items from two different products tables TblMedcines and TblSnacks. Then, we will create a class that uses the created macros to display a product list. As we know the purpose of the macro is can be used a number of times in various places. So our macro you can call anywhere you required to get the list of products that are expired, but still active in the tables.

Basic Steps

  1.  Create a new Dynamics 365 Project and create two tables TblSnacks and TblMedcines
  2.  Create one macro named ExpiredActiveProducts and add the below codes.
  3.  Create a new Runnable class called ProductTableMacro.

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO

How it works

First, we have created two tables TblMedcines and TblSnacks. TblMedcines stores the basic details of medicines with columns MedicineId, MedicineName, MedicineDescription, ExpiryDate, and Status as shown in the figure.

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO
Same way TblSnacks stores the basic details of sweets with columns SweetId, SweetName, SweetDescription, ExpiryDate, and Status as shown in the figure.

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO


Second, we have created one 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 as per our requirements. Here I put an easy example for easy understanding 

(%1.Status == "Active" && %1.ExpiryDate>= Today()).

This macro is defined with one argument %1 which we will input as table names. The condition of the macro is 

TbaleName. Status="Active" and TableName. ExpiryDate= Today().

Here Today() is a built-in function that returns the current date. So this macro returns all the active records which are expired and inactive from any table which holds two columns with the names "Status" and "ExpiryDate".

Next, we created one job with the select statement. here we called the previously created macro in the where clause and passed the table name as the argument (%1).

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

For TblMedicines

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO

In the above code, we are calling the macro and passed argument TblMedicines. The macro is called with a syntax #ExpiredActiveProducts(), which returns all the medical records which are expired but active in the table. 

When we run the job and check the results, as shown in the following screenshot:

Run the job and check the results, as shown in the following screenshot:


For TblSnacks

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO

In the above code, we are calling the macro and passing the argument as TblSnacks. The macro is called with a syntax #ExpiredActiveProducts(), which returns all the sweets records which are expired but active in the table. 

When we run the job and check the results, as shown in the following screenshot:

What is Macro in D365 ? How to Execute Macro in SQL Statement in D365FO


Copy and paste the codes below to your visual studio runnable class.

class ProductTableMacro
{
     
   public static void main(Args _args)
   {
       TblMedcines medicines;
 
       while select medicines
       where #ExpiredActiveProducts(medicines)
       {
           info(strFmt( "%1 - %2", medicines.MedicineName, medicines.MedicineDescription));
       }
 
   }
 
}  
From the above example, it is very clear that a macro is which can be used a number of times in various places will increase the speed and reduce the length of the code as well. 
Happy coding with D365 Snippets 🙂.

Post a Comment

0Comments
Post a Comment (0)