We can use the tableTruncate method from SqlDataDictionary to truncate a table in FinOps, useful for a huge SQL table.

public static void main(Args _args)
{
    DictTable dictTable = new DictTable(tablenum(KA_TBYTDStaging));
 
    if (dictTable && dictTable.enabled())
    {
        str sqlTableName = dictTable.name(DbBackend::Sql);
        SqlDataDictionary sqlTable = new SqlDataDictionary();
 
        if (sqlTable.tableExist(sqlTableName))
        {
            new SqlDataDictionaryPermission(methodstr(SqlDataDictionary, tableTruncate)).assert();
            sqlTable.tableTruncate(tablenum(KA_TBYTDStaging), false);
            CodeAccessPermission::revertAssert();
        }
    }
}

SQL Datatype​

SQL Command​

SQL Constraints​

• NOT NULL: Specifies that this column cannot store a NULL value.

CREATE TABLE student
  (
     id      INT(8) NOT NULL,
     name    VARCHAR(30) NOT NULL,
     address VARCHAR(50)
  ); 

• UNIQUE: Specifies that this column can have only Unique values, i.e the values cannot be repeated in the column.

CREATE TABLE Student
(
ID int(8) UNIQUE,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);

If you are using d365fo.tools to Import a bacpac file into a Tier1 environment and you got an error

The d365fo.tools use Invoke-D365InstallSqlPackage to install SqlPackage, but its version is not up to date, compare to the bacpac files generated from LCS database export. To solve the issue, we need to downlaod the latest version as a zip file, unblock, unzip and replace files in the SqlPackage folder C:\Temp\d365fo.tools\SqlPackage with the new version.

1. Login to LCS – and select the F&O environment that is enabled for Synapse Link
2. Validate the platform version of the environment under Environment version information as shown bellow.

3. If the platform version of your environment is lower than corresponding rows bellow – for example above environment platform version is 7.0.7036.113 and required version is 7.0.7036.133 or Higher. The above environment must be updated to the latest quality update.

4. There are two ways to update the F&O environment with quality update. Self-update using the latest quality update and second wait for the PQU (Proactive quality update) to be pushed to your environment by Microsoft

5. Above table list out the PQU schedule when your environment will receive the PQU, based on the PQU schedule Release schedule for PQU

6. Customer can also self-update the environment by self-applying the latest quality update instead of waiting for PQU, follow the steps bellow to self-update to latest quality update Reference documentation

   • In the LCS environment page, go to Available updates section and click on view update.
   • Click on save package.
   • Click on Save package, on the Save package to asset library dialog opens, give a name and click on Save package. This action will save the latest cumulative version to your LCS project Asset library.
   • Follow the steps to apply update to cloud environment Apply updates to cloud environments - Finance & Operations | Dynamics 365 | Microsoft Learn

References​

• X++ exception handling

How to​

Prerequisite​

For the purpose of testing, I had this data set of CustGroup. This can also be found on Legal Entity USRT of demo data comes with a new environment of Dynamics 365 Finance.

I added two new buttons on CustGroup form:

• Bulk update Desc: This button will loop through each and all records in the above table and update the Description to Description + + Updated except one containing "Employee". They will become like below.

• Reverse Bulk update Desc: This button will update all Description back to its original by removing the word + Updated.

Let's do it​

When we don't use throw​

In the extension class named CustGroup_ButtonHandlers, I put these codes which will handle the event of clicking those two buttons.

References​

• XppPrePostArgs Class (Microsoft.Dynamics.Ax.Xpp)

How to​

Take a very safe table to play around: CustGroup (Customer Group), we will see how we can utilise XppPrePostArgs here to save a parameter before the update is commited to the database and then take it out for us to execute futher logic.

The tricky part is that such table does not even have an update method.

Normally saying, if a method is available on the common, we can navigate to the design of it and copy the Pre- and Post-Event Handler from here.

References​

• Use Azure Data Explorer data in Power BI
• .create function command
• Azure Data Explorer
• Kusto Query Language (KQL)

How to​

Let's create demo data for testing​

For the purpose of testing and demonstrating, I will create a new table in my testing ADX cluster (everyone can create their own free cluster following Create a free Azure Data Explorer cluster). Let's call it UserAsset.

The table will have two fields:

• User - type string
• NumberOfLaptop - type integer

References​

• Use pre-processed RDP classes
• Microsoft Dynamics AX 2012 White Paper: Report Programming Model

How to​

Pre-process RDP has become very commonplace these days, especially to ones who work with SSRS reports on Dynamics 365 FO a lot.

Recently, I faced an issue when no data was inserted into or modififed in my temporary table during run-time despite the fact that RDP class (and its related ones within the same framework) was executed successfully. Even debugging did not show me what was going wrong. There was simply no SQL statements in the trace that I captured.

Turned out that I will need to take ownership of the temp table instance before manipulating any data in it.

My code used to be (when it did not work):

internal final class CSG_PostedServiveOrderWithoutInvDP extends SrsReportDataProviderPreProcessTempDB
{
    CSG_PostedServiveOrderWithoutInvTmp           tmpTable;

    [SRSReportDataSetAttribute(tableStr(CSG_PostedServiveOrderWithoutInvTmp))]
    public CSG_PostedServiveOrderWithoutInvTmp getTmpTable()
    {
        select tmpTable;
        return tmpTable;
    }

    public void processReport()
    {
        ttsbegin;
        this.populateTmpTable();
        ttscommit;
    }

    private void populateTmpTable()
    {
        // Insert data to tmp table here.
    }
}

Open Run

VS2017 and prior %UserProfile%/Documents/Visual Studio 2015\settings\

VS2019 and later %UserProfile%/Documents/Visual Studio Dynamics 365

Open DynamicsDevConfig.XML, change the default model in DefaultModelForNewProjects property

<?xml version="1.0" encoding="utf-8"?>
<DynamicsDevConfig xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/dynamics/2012/03/development/configuration">
	<AddInPaths xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
		<d2p1:string>T:\Bin\AddInExtensions</d2p1:string>
	........
	<DBSyncInBuild>true</DBSyncInBuild>
	<DatabaseServer>localhost</DatabaseServer>
	<DefaultCompany></DefaultCompany>
	<DefaultModelForNewProjects>FleetManagement</DefaultModelForNewProjects>
	<DefaultWebBrowser>Google Chrome</DefaultWebBrowser>
	<DisableBPCheck>false</DisableBPCheck>
	<DisableFormStaticCompile>false</DisableFormStaticCompile>
	<EmitTraceEvents>true</EmitTraceEvents>
	<EnableNativeDebugging>false</EnableNativeDebugging>
	<EnableOfflineAuthentication>true</EnableOfflineAuthentication>
	<EnableSymbolLoadingForSolutionOnly>true</EnableSymbolLoadingForSolutionOnly>
	<FallbackToNativeSync>false</FallbackToNativeSync>
	.......
</DynamicsDevConfig>

We can change the form design property Show Delete Button, Show New Button to No.

Or using X++ code in form's init method

[Form]
public class DEV_TestForm extends FormRun
{
    public void init()
    {
        this.form().design().showNewButton(0);
        this.form().design().showDeleteButton(0);
        super();
    }
}

For COC Extension

[ExtensionOf(formStr(SMAServiceOrderTable))]
final class SMAServiceOrderTable_Form_DEV_Extension
{
    public void init()
    {
        this.form().design().showNewButton(0);
        this.form().design().showDeleteButton(0);
        next init();
    }
}