- D365FO
- X++
- Currency
- Dynamics
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 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
- Create a new Dynamics 365 Project and create two tables TblSnacks and TblMedcines.
- Create one macro named ExpiredActiveProducts and add the below codes.
- Create a new Runnable class called ProductTableMacro.
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.
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
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:
For TblSnacks
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:
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. 
