Download code from GitHub
In this chapter, we will cover the following recipes:
- Creating a new project, package, and model
- Creating a new number sequence
- Renaming the primary key
- Adding a document handling note
- Using a normal table as a temporary table
- Copying a record
- Building a query object
- Using a macro in a SQL statement
- Executing a direct SQL statement
- Enhancing the data consistency checks
- Using the date effectiveness feature
This chapter focuses on data manipulation exercises in all new Dynamics 365 for Finance and Operations. These exercises are very useful when doing data migration, system integration, custom reporting, and so on. Here, we will discuss how to work with query objects from the X++/C# code. We will also discuss how to reuse macros in X++ SQL statements and how to execute SQL statements directly to the database. This chapter will explain how to rename primary keys, how to merge and copy records, how to add document handling notes to selected records, and how to create and read XML and comma-separated files. The chapter ends with a recipe about the date effectiveness feature.
Elements in Dynamics 365 for Finance and Operations represent every individual element of AOT such as class, table, form, and so on. Elements in Dynamics 365 for Finance and Operations are stored on disk as XML files; these files contain the metadata and source code for the element. The XML files are the unit of Source Control.
Projects works the same as AX2012, but in D365 an element can be customized only once they are added to a specific Visual Studio project. The project may only belong to one model.
A Dynamics 365 for Finance and Operations model is a group of elements. Standard elements are part of a standard model; you can add them into your model and do customization. A model is a design-time concept. An example of models: warehouse management model, a project accounting model, and more. Models can have one or more projects. Models may only belong to one package.
A Dynamics 365 for Finance and Operations package is a deployment and compilation unit of one or more models. It includes model metadata, binaries, cubes, and other associated resources. One or more D365 packages can be packaged into a deployment package, which is the vehicle used for deployment on UAT and production environments. Packages are packaged into a deployable package file for deployment to Sandbox or production environments. A package can have one or more models. Packages can have references to other packages, just like .NET assemblies can reference each other.
To create a new project, follow these steps:
- Open Visual Studio as admin.
- On the
Filemenu, point toNew, and then clickProject. - In the list of template types, expand the
Installednode. - Expand the
Templatesnode. - Select the
Microsoft Dynamics 365 for Operationscategory. - Select the
D365 Projecttemplate. - Enter the name and location for the new project.
- Select
Create directory for solutionif you want to create a new solution for this project, uncheck if you want to add in your current solution.
To create a new model, follow these steps:
- Open Visual Studio as admin.
- On the
Dynamics 365menu, point toModel managementand selectCreate model.
- Give a model, publisher name, and other values:
- Now here you can create a new package or select any existing package. We could create a new package and select the required package as referenced packages:
- Double-check the summary with details. Select
Create new projectif you want to create a new project in this model once created. You can mark this model to all your new projects by selecting options:
As you saw, there was one more step while creating a model, Select referenced packages. When you create your own package you can select from an existing package to add them as references in your new package. You may need to add some standard package reference if you want to add them into your customization.
Here are the steps to create a new package:
- Open Visual Studio as admin.
- On the
Dynamics 365menu, point to Model management and selectCreate model. - Give a model, publisher name, and other values.
- On the next step select
Create new package - Give a name to your package.
- Next select the existing package as a reference to this new package.
- Click on
Finish.
So now you have your own model with a new package.
Number sequences in Dynamics 365 for Finance and Operations are used to generate specifically formatted numbers for record identification. These number sequences can be anything from voucher numbers or transaction identification numbers to customer or vendor accounts.
When developing custom functionality, often one of the tasks is to add a new number sequence to the system in order to support newly created tables. Adding a number sequence to the system is a two-step process. First, we create the number sequence itself; second, we start using it in some particular form or from the code.
D365 contains a list of NumberSeqApplicationModule derivative classes, which hold the number sequence's setup data for the specific module. These classes are read by the number sequence wizard, which detects existing number sequences and proposes to create the missing ones or newly added ones. The wizard is normally run as a part of the application initialization. It can also be rerun any time later when expanding the D365 functionality used, where a setup of additional number sequences is required. The wizard also has to be rerun if new custom number sequences are added to the system.
In this recipe, we will do the first step, that is, add a new number sequence to the system. In a standard application, the customer group number is not driven by any number sequence, so we will enhance this by creating it. The second step is explained later in the Using a number sequence handler recipe in Chapter 3, Working with Data in Forms.
Carry out the following steps in order to complete this recipe:
- Create a new
NumberSeqModuleCustomer_packtclass in the D365 Project that extends theNumberSeqModuleCustomerclass in the Application and add the following code snippet at the bottom of theloadModule_Extension()method:
class NumberSeqModuleCustomer_packt extends
NumberSeqModuleCustomer
{
public void loadModule_Extension()
{
NumberSeqDatatype datatype = NumberSeqDatatype::construct();
datatype.parmDatatypeId(extendedTypeNum(CustGroupId));
datatype.parmReferenceHelp("Customer group ID");
datatype.parmWizardIsContinuous(false);
datatype.parmWizardIsManual(NoYes::No);
datatype.parmWizardIsChangeDownAllowed(NoYes::Yes);
datatype.parmWizardIsChangeUpAllowed(NoYes::Yes);
datatype.parmWizardHighest(999);
datatype.parmSortField(20);
datatype.addParameterType(
NumberSeqParameterType::DataArea, true, false);
this.create(datatype);
}
} Note
Downloading the example code You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files emailed directly to you.
- Create a new runnable class (Job) with the following lines of code, build the solution and run it:
class loadNumSeqCustPackt
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void Main(Args args)
{
//define the class variable
NumberSeqModuleCustomer_packt nymberSeqMod = new
NumberSeqModuleCustomer_packt();
//load the number sequences
nymberSeqMod.loadModule_Extension();
}
} - Run the number sequence wizard by clicking on the
Generatebutton underNumber sequenceby going toOrganization administration|Common|Number sequenceand then click on theNextbutton, as shown in the following screenshot:
- Click on
Detailsto view more information. Delete everything apart from the rows whereAreaisAccounts receivableandReferenceisCustomer group. Note the number sequence codes and click on theNextbutton, as shown here:
- On the last page, click on the
Finishbutton to complete the setup, as shown in the following screenshot:
- The newly created number sequences now can be found in the
Number sequenceform, as shown in the following screenshot:
- Navigate to
Organization administration|Number sequences|Segment configurationand notice the newCustomer groupreference under theAccounts receivablearea:
- Navigate to
Accounts receivable|Setup|Accounts receivable parametersand select theNumber sequencestab. Here, you should see the new number sequence code:
- The last thing to be done is to create a helper method for this number sequence. Create a new extension class
CustParameters_Extensionfor theCustParameterstable and add it to the Dynamics 365 Project and then create the following method and build the solution:
[ExtensionOf(tableStr(CustParameters))]
final class CustParameters_Extension
{
/// <summary>
/// Gets the number reference customer group id.
/// </summary>
/// <returns>
/// An instance of the <c>NumberSequenceReference</c> class.
/// </returns>
client server static NumberSequenceReference
numRefCustGroupId()
{
NumberSequenceReference NumberSeqReference;
NumberSeqReference = NumberSeqReference::findReference
(extendedTypeNum(CustGroupId));
return NumberSeqReference;
}
} We start the recipe by adding a number sequence initialization code into the NumberSeqModuleCustomer_packt class. As understood from its name, the number sequence initialization code holds the initialization of the number sequences that belong to the Accounts receivable module.
The code in the loadModule_Extension() method defines the default number sequence settings to be used in the wizard, such as the data type, description, and highest possible number. Additional options such as the starting sequence number, number format, and others can also be added here. All the mentioned options can be changed while running the wizard. The addParameterType() method is used to define the number sequence scope. In the example, we created a separate sequence for each Legal entity.
Before we start the wizard, we initialize number sequence references. This should be done as a part of the Dynamics 365 for Finance and Operations initialization checklist, but in this example, we execute it manually by calling the loadModule_Extension() method of the NumberSeqApplicationModule_packt class.
Next, we execute the wizard that will create the number sequences for us. We skip the welcome page and in the second step of the wizard, the Details button can be used to display more options. The options can also be changed later in the Number sequences form before or even after the number sequence is actually used. The last page shows an overview of what will be created. Once completed, the wizard creates new records in the Number sequences form for each company.
The newly created number sequence reference appears in the Segment configuration form. Here, we can see that the Data area checkbox is checked, which means that we will have separate number lists for each company. The number sequence setup can be normally located in the module parameter forms.
- The Using a number sequence handler recipe in Chapter 3, Working with Data in Forms
Most of you who are familiar with the Dynamics 365 for Finance and Operations application, have probably used the standard Rename function. This function allows you to rename the primary key of almost any record. With this function, you can fix records that were saved or created by mistake. This function ensures data consistency, that is, all the related records are renamed as well. It can be accessed from the Record information form (shown in the following screenshot), which can be opened by selecting Record info from the right-click menu on any record:
A new form will open as follows:
Click on the Rename button to rename the Vendor Account field value.
When it comes to mass renaming, this function might be very time-consuming as you need to run it on every record. An alternative way of doing this is to create a job that automatically runs through all the required records and calls this function automatically.
This recipe will explain how the record's primary key can be renamed through the code. As an example, we will create a job that renames a vendor account.
Carry out the following steps in order to complete this recipe:
- Navigate to
Accounts payable|Vendors|All vendorsand find the account that has to be renamed, as shown in the following screenshot:
- Click on
Transactionsin theActionpane to check the existing transactions, as shown in the following screenshot:
- Create a new project, create a runnable class named
VendAccountRename, and enter the following code snippet. Use the previously selected account:
class VendAccountRename
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
VendTable vendTable;
ttsBegin;
select firstOnly vendTable
where vendTable.AccountNum == '1002';
if (vendTable)
{
vendTable.AccountNum = 'US-1002';
vendTable.renamePrimaryKey();
}
ttsCommit;
}
} - Select class
VendAccountRenameand right-click and then selectSet as startup object. Execute the class by clickingStartin Visual Studio and check whether the renaming was successful, by navigating toAccounts payable|Vendors|All vendorsagain and finding the new account. The new account should have retained all its transactions and other related records, as shown in the following screenshot:
- Click on
Transactionsin theActionpane in order to see whether the existing transactions are still in place, as shown in the following screenshot:
In this recipe, we first select the desired vendor record and set its account number to the new value. Note that only the fields belonging to the table's primary key can be renamed in this way.
Then, we call the table's renamePrimaryKey() method, which does the actual renaming. The method finds all the related records for the selected vendor account and updates them with the new value. The operation might take a while, depending on the volume of data, as the system has to update multiple records located in multiple tables.
Document handling in Dynamics 365 for Finance and Operations is a feature that allows you to add notes, links, documents, images, files, and other related information to almost any record in the system. For example, we can track all the correspondence sent out to our customers by attaching the documents to their records in Dynamics 365 for Finance and Operations. Document handling on most of the forms can be accessed either from the Action pane by clicking on the Attachments button and selecting Document handling from the Command menu under File or selecting the Document handling icon from the status bar.
Document handling has a number of configuration parameters that you can find by navigating to Organizationadministration | Setup | Documentmanagement. Please refer to Dynamics 365 for Operations Manuals to find out more.
Dynamics 365 for Finance and Operations also allows you to add document handling notes from the code. This can come in handy when you need to automate the document handling process. In this recipe, we will demonstrate this by adding a note to a vendor account.
Before you start, ensure that document handling is enabled on the user interface. Open Document management parameters by navigating to Organization administration | Setup | Document management and make sure that Use Active document tables is not marked, as shown in the following screenshot:
Then, open the Document types form from the same location and pick or create a new document type with its Group set to Note, as shown in the following screenshot. In our demonstration, we will use Note.
Carry out the following steps in order to complete this recipe:
- Navigate to
Accountspayable|Vendors|Allvendorsand locate any vendor account to be updated, as shown in the following screenshot:
- Create a Dynamics 365 for Operations Project, create a new runnable class named
VendAccountDocument, and enter the following code snippet. Use the previously selected vendor account and document type:
class VendAccountDocument
{
static void main(Args _args)
{
VendTable vendTable;
DocuType docuType;
DocuRef docuRef;
vendTable = VendTable::find('1005');
docuType = DocuType::find('Note');
if (!docuType ||
docuType.TypeGroup != DocuTypeGroup::Note)
{
throw error("Invalid document type");
}
docuRef.RefCompanyId = vendTable.dataAreaId;
docuRef.RefTableId = vendTable.TableId;
docuRef.RefRecId = vendTable.RecId;
docuRef.TypeId = docuType.TypeId;
docuRef.Name = 'Automatic note';
docuRef.Notes = 'Added from X++';
docuRef.insert();
info("Document note has been added successfully");
}
} - Run the class to create the note.
- Go back to the vendor list and click on the
Attachmentsbutton in the form'sActionpane or selectDocument handlingfrom theCommandmenu underFileto view the note added by our code, as shown in the following screenshot:
All the document handling notes are stored in the DocuRef table, where three fields, RefCompanyId, RefTableId, and RefRecId, are used to identify the parent record. In this recipe, we set these fields to the vendor company ID, vendor table ID, and vendor account record ID, respectively. Then, we set the type, name, and description and inserted the document handling record. Notice that we have validated the document type before using it. In this way, we added a note to the record.
Standard Dynamics 365 for Finance and Operations contains numerous temporary tables that are used by the application and can be used in custom modifications too. Although new temporary tables can also be easily created using the Dynamics 365 for Operations Project, sometimes it is not effective. One of the cases where it is not effective can be when the temporary table is similar to an existing one or exactly the same. The goal of this recipe is to demonstrate an approach for using standard non temporary tables in order to hold temporary data.
As an example, we will use the vendor table to insert and display a couple of temporary records without affecting the actual data.
Carry out the following steps in order to complete this recipe:
- In the Dynamics 365 Project, create a new class named
VendTableTmpwith the following code snippet:
class VendTableTemp
{
public static void main(Args _args)
{
VendTable vendTable;
vendTable.setTmp();
vendTable.AccountNum = '1000';
vendTable.Blocked = CustVendorBlocked::No;
vendTable.Party = 1;
vendTable.doInsert();
vendTable.clear();
vendTable.AccountNum = '1002';
vendTable.Blocked = CustVendorBlocked::All;
vendTable.Party = 2;
vendTable.doInsert();
while select vendTable
{
info(strFmt(
"%1 - %2",
vendTable.AccountNum,
vendTable.Blocked));
}
}
} - Run the class and check the results, which may be similar to this:
The key method in this recipe is setTmp(). This method is available in all the tables, and it makes the current table instance behave as a temporary table in the current scope. Basically, it creates an InMemory temporary table that has the same schema as the original table.
In this recipe, we create a new class and place all the code in its main() method. The reason why we create a class, not a job, is that the main() method can be set to run on the server tier by specifying the server modifier. This will improve the code's performance.
In the code, we first call the setTmp() method on the vendTable table to make it temporary in the scope of this method. This means that any data manipulations will be lost once the execution of this method is over and the actual table content will not be affected.
Next, we insert a couple of test records. Here, we use the doInsert() method to bypass any additional logic, which normally resides in the table's insert() method. We have to keep in mind that even the table becomes temporary; all the code in its insert(), update(), delete(), initValue(), and other methods is still present and we have to make sure that we don't call it unintentionally.
The last thing to do is to check for newly created records by listing the vendTable table. We can see that although the table contains many actual records, only the records that we inserted were displayed in the Infolog window. Additionally, the two records we inserted do not appear in the actual table.
Copying existing data is one of the data manipulation tasks in Dynamics 365 for Finance and Operations. There are numerous places in the standard D365 application where users can create new data entries just by copying existing data and then modifying it. A few of the examples are the Copy button in Costmanagement | Inventoryaccounting | Costingversions and the Copy project button in Projectmanagement and accounting | Projects | All projects. Also, although the mentioned copying functionality might not be that straightforward, the idea is clear: the existing data is reused while creating new entries.
In this recipe, we will learn two ways to copy records in X++. We will discuss the usage of the table's data() method, the global buf2buf() function, and their differences. As an example, we will copy one of the existing ledger account records into a new record.
Carry out the following steps in order to complete this recipe:
- Navigate to
General ledger|Chart of accounts|Accounts|Main accountsand find the account to be copied. In this example, we will use130100, as shown in the following screenshot:
- Create a Dynamics 365 for Operations Project, create a runnable class named
MainAccountCopywith the following code snippet, and run it:
class MainAccountCopy
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
MainAccount mainAccount1;
MainAccount mainAccount2;
mainAccount1 = MainAccount::findByMainAccountId(
'130100');
ttsBegin;
mainAccount2.data(mainAccount1);
mainAccount2.MainAccountId = '130101';
mainAccount2.Name += ' - copy';
if (!mainAccount2.validateWrite())
{
throw Exception::Error;
}
mainAccount2.insert();
ttsCommit;
}
} - Navigate to
General ledger|Chart of accounts|Accounts|Main accountsagain and notice that there are two identical records now, as shown in the following screenshot:
In this recipe, we have two variables: mainAccount1 for the original record and mainAccount2 for the new record. First, we find the original record by calling findMainAccountId() in the MainAccount table.
Next, we copy it to the new one. Here, we use the data() table's member method, which copies all the data fields from one variable to another.
After that, we set a new ledger account number, which is a part of a unique table index.
Finally, we call insert() on the table if validateWrite() is successful. In this way, we create a new ledger account record, which is exactly the same as the existing one apart from the account number.
As we saw before, the data() method copies all the table fields, including system fields such as the record ID, company account, and created user. Most of the time, it is OK because when the new record is saved, the system fields are overwritten with the new values. However, this function may not work for copying records across the companies. In this case, we can use another function called buf2Buf(). This function is a global function and is located in the Global class, which you can find by navigating to AOT | Classes. The buf2Buf() function is very similar to the table's data() method with one major difference. The buf2Buf() function copies all the data fields excluding the system fields. The code in the function is as follows:
static void buf2Buf(
Common _from,
Common _to,
TableScope _scope = TableScope::CurrentTableOnly)
{
DictTable dictTable = new DictTable(_from.TableId);
FieldId fieldId = dictTable.fieldNext(0, _scope);
while (fieldId && ! isSysId(fieldId))
{
_to.(fieldId) = _from.(fieldId);
fieldId = dictTable.fieldNext(fieldId, _scope);
}
} We can clearly see that during the copying process, all the table fields are traversed, but the system fields, such as RecId or dataAreaId, are excluded. The isSysId() helper function is used for this purpose.
In order to use the buf2Buf() function, the code of the MainAccountCopy job can be amended as follows:
class MainAccountCopyBuf2Buf
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
MainAccount mainAccount1;
MainAccount mainAccount2;
mainAccount1 = MainAccount::findByMainAccountId('130100');
ttsBegin;
buf2Buf(mainAccount1, mainAccount2);
mainAccount2.MainAccountId = '130102';
mainAccount2.Name += ' - copy';
if (!mainAccount2.validateWrite())
{
throw Exception::Error;
}
mainAccount2.insert();
ttsCommit;
}
} Query objects in Dynamics 365 for Finance and Operations are used to build SQL statements for reports, views, forms, and so on. They are normally created in the AOT using the drag and drop functionality and by defining various properties. Query objects can also be created from the code at runtime. This is normally done when AOT tools cannot handle complex and/or dynamic queries.
In this recipe, we will create a query from the code to retrieve project records from the Project management module. We will select only the projects of the type Time & material, starting with 00005 in its number and containing at least one hour transaction. The project list will be sorted by project name.
Carry out the following steps in order to complete this recipe:
- Open the project area, create a runnable class named
ProjTableQuery, and enter the following code snippet:
class ProjTableQuery
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
Query query;
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
QueryBuildRange qbr1;
QueryBuildRange qbr2;
QueryRun queryRun;
ProjTable projTable;
query = new Query();
qbds1 = query.addDataSource(tableNum(ProjTable));
qbds1.addSortField(
fieldNum(ProjTable, Name),
SortOrder::Ascending);
qbr1 = qbds1.addRange(fieldNum(ProjTable,Type));
qbr1.value(queryValue(ProjType::TimeMaterial));
qbr2 = qbds1.addRange(fieldNum(ProjTable,ProjId));
qbr2.value(
SysQuery::valueLike(queryValue('00005')));
qbds2 = qbds1.addDataSource(tableNum(ProjEmplTrans));
qbds2.relations(true);
qbds2.joinMode(JoinMode::ExistsJoin);
queryRun = new QueryRun(query);
while (queryRun.next())
{
projTable = queryRun.get(tableNum(ProjTable));
info(strFmt(
"%1, %2, %3",
projTable.ProjId,
projTable.Name,
projTable.Type));
}
}
} - Run the class and you will get a screen similar to the following screenshot:
First, we create a new query object. Next, we add a new ProjTable data source to the query object by calling its addDataSource() member method. The method returns a reference to the QueryBuildDataSource object-qbds1. Here, we call the addSortField() method to enable sorting by the project name.
The following two blocks of code create two ranges. The first block of code shows only the projects of the time & material type and the second one lists only the records where the project number starts with 00005. These two filters are automatically added together using SQL's AND operator. The QueryBuildRange objects are created by calling the addRange() member method of the QueryBuildDataSource object with the field ID number as the argument. The range value is set by calling value() on the QueryBuildRange object itself. We use the queryValue()function from the Global class and the valueLike() function from the SysQuery class to prepare the values before applying them as a range. More functions, such as queryNotValue() and queryRange(), can be found in the Global application class by navigating to AOT | Classes. Note that these functions are actually shortcuts to the SysQuery application class, which in turn has even more interesting helper methods that might be handy for every developer.
Adding another data source to an existing one connects both the data sources using SQL's JOIN operator. In this example, we are displaying projects that have at least one posted hour line. We start by adding the ProjEmplTrans table as another data source.
Next, we need to add relationships between the tables. If relationships are not defined on tables, we will have to use the addLink() method with relation field's ID numbers. In this example, relations in the tables are already defined, so you only need to enable them by calling the relations() method with true as an argument.
Calling joinMode() with JoinMode::ExistsJoin as a parameter ensures that only the projects that have at least one hour transaction will be selected. In situations like this, where we do not need any data from the second data source, performance-wise it is better to use an exists join instead of the inner join. This is because the inner join fetches the data from the second data source and, therefore, takes longer to execute.
The last thing that needs to be done is to create and run the queryRun object and show the selected data on the screen.
It is worth mentioning a couple of specific cases when working with query objects from the code. One of them is how to use the OR operator and the other one is how to address array fields.
As you have already noted, regardless of how many ranges are added, all of them will be added together using SQL's AND operator. In most cases, this is fine, but sometimes complex user requirements demand ranges to be added using SQL's OR operator. There might be a number of workarounds, such as using temporary tables or similar tools, but we can use the Dynamics 365 for Operations feature that allows you to pass a part of a raw SQL string as a range.
In this case, the range has to be formatted in a manner similar to a fully-qualified SQL where clause, including field names, operators, and values. The expressions have to be formatted properly before you use them in a query. Here are some of the rules:
- The expression must be enclosed within single quotes
- Inside, the whole expression has to be enclosed within parentheses
- Each subexpression must also be enclosed within parentheses
- String values have to be enclosed within double quotes
- For enumerations, use their numeric values
For value formatting, use various Dynamics 365 for Operations functions, such as queryValue() and date2StrXpp(), or methods from the SysQuery class.
Let's replace the code snippet from the previous example with the following lines of code:
qbr2.value(SysQuery::valueLike (queryValue('00005')));
with the new code:
qbr2.value(strFmt('((%1 like "%2") || (%3 = %4))',
fieldStr(ProjTable,ProjId),queryvalue('00005*'),
fieldStr(ProjTable,Status),ProjStatus::InProcess+0)); Notice that by adding zero to the enumeration in the previous code, we can force the strFmt() function to use the numeric value of the enumeration. The strFmt() output should be similar to the following line:
((ProjId like "00005*") || (Status = 3))
Now if you run the code, besides all the projects starting with 00005, the result will also include all the active projects, as shown in the following screenshot:
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.
Carry out the following steps in order to complete this recipe:
- Create a Dynamics 365 for Operations Project and create a new macro named
VendTableNotBlockedwith the following code snippet:
(%1.Blocked == CustVendorBlocked::No)
- In the Dynamics 365 Project, create a new
runnableclass calledVendTableMacrowith 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()));
}
}
} - Run the job and check the results, as shown in the following screenshot:
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
Dynamics 365 for Finance and Operations allows developers to build X++ SQL statements that are flexible enough to fit into any custom business process. However, in some cases, the usage of X++ SQL is either not effective or not possible at all. One such case is when we run data upgrade tasks during an application version upgrade. A standard application contains a set of data upgrade tasks to be completed during the version upgrade. If the application is highly customized, then most likely, standard tasks have to be modified in order to reflect data dictionary customization's, or a new set of tasks have to be created to make sure data is handled correctly during the upgrade.
Normally, at this stage, SQL statements are so complex that they can only be created using database-specific SQL and executed directly in the database. Additionally, running direct SQL statements dramatically increases data upgrade performance because most of the code is executed on the database server where all the data resides. This is very important while working with large volumes of data.
This recipe will demonstrate how to execute SQL statements directly. We will connect to the current Dynamics 365 for Finance and Operations database directly using an additional connection and retrieve a list of vendor accounts.
Carry out the following steps in order to complete this recipe:
- In the Dynamics 365 Project, create a new class named
VendTableSqlusing the following code snippet:
class VendTableSql
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
UserConnection userConnection;
Statement statement;
str sqlStatement;
SqlSystem sqlSystem;
SqlStatementExecutePermission sqlPermission;
ResultSet resultSet;
DictTable tblVendTable;
DictTable tblDirPartyTable;
DictField fldParty;
DictField fldAccountNum;
DictField fldDataAreaId;
DictField fldBlocked;
DictField fldRecId;
DictField fldName;
tblVendTable = new DictTable(tableNum(VendTable));
tblDirPartyTable = new DictTable(tableNum(DirPartyTable));
fldParty = new DictField(
tableNum(VendTable),
fieldNum(VendTable,Party));
fldAccountNum = new DictField(
tableNum(VendTable),
fieldNum(VendTable,AccountNum));
fldDataAreaId = new DictField(
tableNum(VendTable),
fieldNum(VendTable,DataAreaId));
fldBlocked = new DictField(
tableNum(VendTable),
fieldNum(VendTable,Blocked));
fldRecId = new DictField(
tableNum(DirPartyTable),
fieldNum(DirPartyTable,RecId));
fldName = new DictField(
tableNum(DirPartyTable),
fieldNum(DirPartyTable,Name));
sqlSystem = new SqlSystem();
sqlStatement = 'SELECT %1, %2 FROM %3 ' +
'JOIN %4 ON %3.%5 = %4.%6 ' +
'WHERE %7 = %9 AND %8 = %10';
sqlStatement = strFmt(
sqlStatement,
fldAccountNum.name(DbBackend::Sql),
fldName.name(DbBackend::Sql),
tblVendTable.name(DbBackend::Sql),
tblDirPartyTable.name(DbBackend::Sql),
fldParty.name(DbBackend::Sql),
fldRecId.name(DbBackend::Sql),
fldDataAreaId.name(DbBackend::Sql),
fldBlocked.name(DbBackend::Sql),
sqlSystem.sqlLiteral(curext(), true),
sqlSystem.sqlLiteral(CustVendorBlocked::No, true));
userConnection = new UserConnection();
statement = userConnection.createStatement();
sqlPermission = new SqlStatementExecutePermission(
sqlStatement);
sqlPermission.assert();
resultSet = statement.executeQuery(sqlStatement);
CodeAccessPermission::revertAssert();
while (resultSet.next())
{
info(strFmt(
"%1 - %2",
resultSet.getString(1),
resultSet.getString(2)));
}
}
} - Run the class to retrieve a list of vendors directly from the database, as shown in the following screenshot:
We start the code by creating the DictTable and DictField objects to handle the vendor table and its fields, which are used later in the query. The DirPartyTable is used to get additional vendor information.
A new SqlSystem object is also created. It is used to convert D365 types to SQL types.
Next, we set up a SQL statement with a number of placeholders for the table or field names and field values to be inserted later.
The main query creation takes place next, when the query placeholders are replaced with the right values. Here, we use the previously created DictTable and DictField type objects by calling their name() methods with the DbBackend::Sql enumeration as an argument. This ensures that we pass the name in the exact manner it is used in the database-some of the SQL field names are not necessary, which is the same as field names within the application.
We also use the sqlLiteral() method of the previously created sqlSystem object to properly format SQL values in order to ensure that they do not have any unsafe characters.
The value of the sqlStatement variable that holds the prepared SQL query depending on your environment is as follows:
SELECT ACCOUNTNUM, NAME FROM VENDTABLE
JOIN DIRPARTYTABLE ON VENDTABLE.PARTY = DIRPARTYTABLE.RECID
WHERE DATAAREAID = 'usmf' AND BLOCKED = 0 Once the SQL statement is ready, we initialize a direct connection to the database and run the statement. The results are returned in the resultSet object, and we get them by using the while statement and calling the next() method until the end.
Note that we created an sqlPermission object of the type SqlStatementExecutePermission here and called its assert() method before executing the statement. This is required in order to comply with Dynamics 365 for Operation's trustworthy computing requirements.
Another thing that needs to be mentioned is that when building direct SQL queries, special attention has to be paid to license, configuration, and security keys. Some tables or fields might be disabled in the application and may contain no data in the database.
The code in this recipe can be also used to connect to external ODBC databases. We only need to replace the UserConnection class with the OdbcConnection class and use text names instead of the DictTable and DictField objects.
The standard Dynamics 365 for Finance and Operations application provides an alternate way of building direct SQL statements by using a set of SQLBuilder classes. By using these classes, we can create SQL statements as objects, as opposed to text. Next, we will demonstrate how to use a set of SQLBuilder classes. We will create the same SQL statement as we did before.
First, in a Dynamics 365 project, create another class named VendTableSqlBuilder using the following code snippet:
class VendTableSqlBuilder
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
UserConnection userConnection;
Statement statement;
str sqlStatement;
SqlStatementExecutePermission sqlPermission;
ResultSet resultSet;
SQLBuilderSelectExpression selectExpr;
SQLBuilderTableEntry vendTable;
SQLBuilderTableEntry dirPartyTable;
SQLBuilderFieldEntry accountNum;
SQLBuilderFieldEntry dataAreaId;
SQLBuilderFieldEntry blocked;
SQLBuilderFieldEntry name;
selectExpr = SQLBuilderSelectExpression::construct();
selectExpr.parmUseJoin(true);
vendTable = selectExpr.addTableId(
tablenum(VendTable));
dirPartyTable = vendTable.addJoinTableId(
tablenum(DirPartyTable));
accountNum = vendTable.addFieldId(
fieldnum(VendTable,AccountNum));
name = dirPartyTable.addFieldId(
fieldnum(DirPartyTable,Name));
dataAreaId = vendTable.addFieldId(
fieldnum(VendTable,DataAreaId));
blocked = vendTable.addFieldId(
fieldnum(VendTable,Blocked));
vendTable.addRange(dataAreaId, curext());
vendTable.addRange(blocked, CustVendorBlocked::No);
selectExpr.addSelectFieldEntry(
SQLBuilderSelectFieldEntry::newExpression(
accountNum,
'AccountNum'));
selectExpr.addSelectFieldEntry(
SQLBuilderSelectFieldEntry::newExpression(
name, 'Name'));
sqlStatement = selectExpr.getExpression(null);
userConnection = new UserConnection();
statement = userConnection.createStatement();
sqlPermission = new SqlStatementExecutePermission(
sqlStatement);
sqlPermission.assert();
resultSet = statement.executeQuery(sqlStatement);
CodeAccessPermission::revertAssert();
while (resultSet.next())
{
info(strfmt(
"%1 - %2",
resultSet.getString(1),
resultSet.getString(2)));
}
}
} In the preceding method, we first create a new selectExpr object, which is based on the SQLBuilderSelectExpression class. It represents the object of the SQL statement.
Next, we add the VendTable table to it by calling its member method addTableId(). This method returns a reference to the vendTable object of the type SQLBuilderTableEntry, which corresponds to a table node in a SQL query. We also add DirPartyTable as a joined table.
Then, we create a number of field objects of the SQLBuilderFieldEntry type to be used later and two ranges to show only this company account and only the active vendor accounts.
We use addSelectFieldEntry() to add two fields to be selected. Here, we use the previously created field objects.
The SQL statement is generated once the getExpression() method is called, and the rest of the code is the same as in the previous example.
Running the class will give us results, which are exactly similar to the ones we got earlier.
It is highly recommended that you run the standard Dynamics 365 for Finance and Operations data consistency checks from time to time, which can be found by navigating to System administration | Periodic tasks | Database | Consistency check, to check the system's data integrity. This function finds orphan data, validates parameters, and does many other things, but it does not do everything. The good thing is that it can be easily extended.
In this recipe, we will see how we can enhance the standard Dynamics 365 for Finance and Operations consistency check to include more tables in its data integrity validation.
Before we start, we need to create an invalid setup in order to make sure that we can simulate data inconsistency. Navigate to Fixed assets | Setup | Value models and create a new model, for instance, TEST, as shown in the following screenshot:
Navigate to Fixed assets | Setup | Fixed asset posting profiles and under the Ledger accounts group, create a new record with the newly created value model for any of the posting types, as shown here:
Go back to the Value models form and delete the previously created value model. Now, we have a nonexistent value model in the fixed asset posting settings.
Carry out the following steps in order to complete this recipe:
- In the Dynamics 365 Project, create a new class named
AssetConsistencyCheckwith the following code snippet:
class AssetConsistencyCheck extends SysConsistencyCheck
{
client server static ClassDescription description()
{
return "Fixed assets";
}
client server static HelpTxt helpText()
{
return "Consistency check of the fixed asset module";
}
public Integer executionOrder()
{
return 1;
}
public void run()
{
this.kernelCheckTable(tableNum(AssetLedgerAccounts));
}
} - Navigate to
System administration|Periodic tasks|Database|Consistency check, select the newly createdFixed assetsoption from theModuledrop-down list, and click onOKto run the check, as shown here:
- Now, the message displayed in the
Infologwindow should complain about the missing value model in the fixed assets posting settings, as shown in the following screenshot:
The consistency check in Dynamics 365 for Finance and Operations validates only the predefined list of tables for each module. The system contains a number of classes derived from SysConsistencyCheck. For example, the CustConsistencyCheck class is responsible for validating the Accounts receivable module, LedgerConsistencyCheck for validating General ledger, and so on.
In this recipe, we created a new class named AssetConsistencyCheck, extending the SysConsistencyCheck class for the fixed asset module. The following methods were created:
description(): This provides a name to the consistency check form.helpText(): This displays some explanation about the check.executionOrder(): This determines where the check is located in the list.run(): This holds the code to perform the actual checking. Here, we use thekernelCheckTable()member method, which validates the given table.
The classes that we just mentioned can only be executed from the main Consistency check form. Individual checks can also be invoked as standalone functions. We just need to create an additional method to allow the running of the class:
static void main(Args _args)
{
SysConsistencyCheckJob consistencyCheckJob;
AssetConsistencyCheck assetConsistencyCheck;
consistencyCheckJob = new SysConsistencyCheckJob(
classIdGet(assetConsistencyCheck));
if (!consistencyCheckJob.prompt())
{
return;
}
consistencyCheckJob.run();
} Date effectiveness allows developers to easily create date range fields. Date ranges are used to define record validity between the specified dates, for example, defining employee contract dates and defining vendor license validity.
This feature significantly reduces the amount of time that developers spend on developing business logic/code and also provides a consistent approach to implement data range fields.
This recipe will demonstrate the basics of date effectiveness. We will create a new table to implement date range validation.
Carry out the following steps in order to complete this recipe:
Run Visual Studio as admin:
- Load your earlier project.
- Add a new
TablePktEmployeeContract.
Set the property as follows:
Property | Value |
ValidTimeStateFieldType | Date |
Note the two new fields that are automatically added to the table, as shown in the following screenshot (ValidTo and ValidFrom):
- Now create a new index as follows and add fields as follows:
- Set the following mentioned property for the index here:
Property | Value |
AlternateKey | Yes |
ValidTimeStateKey | Yes |
ValidTimeStateMode | NoGap |
- Now open the table and enter some records in this table itself instead of creating a new form for the table. Right-click on
Tableand selectBrowse table:
We start the recipe by setting the ValidTimeStateFieldType property to Date in the SysEmailTable table. This automatically creates two new fields--ValidFrom and ValidTo that are used to define a date range.
Next, we add the created fields to the primary index where the EmplNum field is used and adjust the index's properties.
We set the AlternateKey property to Yes in order to ensure that this index is a part of an alternate key.
We set the ValidTimeStateKey property to Yes in order to specify that the index is used to determine valid date ranges.
We also set the ValidTimeStateMode property to NoGap in order to ensure that email templates with the same identification number can be created within continuous periods. This property can also be set to Gap, allowing noncontiguous date ranges.
