Skip to main content

Truncate a table using X++

· One min read
Max Nguyen
FinOps Ranger

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 cheatsheet

· 3 min read
Max Nguyen
FinOps Ranger

SQL Datatype

SQL DatatypeDescription
StringCHAR(size)A fixed-length string containing numbers, letters or special characters. Length may vary from 0-255.
VARCHAR(size)Variable-length string where the length may vary from 0-65535. Similar to CHAR.
TEXT(size)Can contain a string of size up to 65536 bytes.
TINY TEXTCan contain a string of up to 255 characters.
MEDIUM TEXTCan contain a string of up to 16777215 characters.
LONG TEXTCan contain a string of up to 4294967295 characters.
BINARY(size)Similar to CHAR() but stores binary byte strings.
VARBINARY(size)Similar to VARCHAR() but stores binary byte strings.
BLOB(size)Holds blobs up to 65536 bytes.
TINYBLOBIt is used for Binary Large Objects and has a maximum size of 255bytes.
MEDIUMBLOBHolds blobs up to 16777215 bytes.
LONGBLOBHolds blobs up to 4294967295 bytes.
NumbericBIT(size)Bit-value type, where size varies from 1 to 64. Default value: 1
INT(size)Integer with values in the signed range of -2147483648 to 2147483647 and values in the unsigned range of 0 to 4294967295.
TINYINT(size)Integer with values in the signed range of -128 to 127 and values in the unsigned range of 0 to 255.
SMALLINT(size)Integer with values in the signed range of -32768 to 32767 and values in the unsigned range of 0 to 65535.
MEDIUMINT(size)Integer with values in the signed range of -8388608 to 8388607 and values in the unsigned range of 0 to 16777215.
BIGINT(size)Integer with values in the signed range of 9223372036854775808 to 9223372036854775807 and values in the unsigned range of 0 to 18446744073709551615.
BOOLEANBoolean values where 0 is considered as FALSE and non-zero values are considered TRUE.
FLOAT (p)The floating-point number is stored. If the precision parameter is set between 0 to 24, the type is FLOAT() else if it lies between 25 to 53, the datatype is DOUBLE()
Date & TimeDATEStores date in YYYY-MM-DD format with dates in the range of '1000-01-01' to '9999-12-31'.
TIME(fsp)Stores time in hh:mm:ss format with times in the range of '-838:59:59' to '838:59:59'.
DATETIME(fsp)Stores a combination of date and time in YYYY-MM-DD and hh:mm:ss format, with values in the range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIMESTAMP(fsp)It stores values relative to the UnixEpoch, basically a Unix Timestamp. Values lie in the range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
YEARStores values of years as a 4digit number format, with a range lying between -1901 to 2155.

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)
);

SqlPackage - The Element or Annotation class SqlStatistic does not contain the Property class AutoDrop

· One min read
Max Nguyen
FinOps Ranger

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

Error output was

Error importing database:An error occurred during deployment plan generation. Deployment cannot continue. The Element or Annotation class SqlStatistic does not contain the Property class AutoDrop.

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.

Self-Update to latest quality update

· 2 min read
Max Nguyen
FinOps Ranger
  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.

Platform version

  1. 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.
Application (Platform)Required platform versionPQU schedule
10.0.36 (PU 60)7.0.7036.133 or Higher10.0.36 PQU-3 (January 22, 2023 to February 25, 2024)
10.0.37 (PU 61)7.0.7068.109 or Higher10.0.37 PQU-2 January 8, 2024 to February 11, 2024
10.0.38 (PU 62)7.0.7120.59 or Higher
  1. 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

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

  3. 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. alt text
    • Click on save package. alt text
    • 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. alt text
    • Follow the steps to apply update to cloud environment Apply updates to cloud environments - Finance & Operations | Dynamics 365 | Microsoft Learn

Throw behaviour in X++

· 3 min read
Kome Hoang
Maintainer of Automaly

References

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.

Customer groupDescription
10Wholesale customers
20Major customers
30Retail customers
40Internet customers
50Employees
80Wholesale customers
90Wholesale customers

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.
Customer groupDescription
10Wholesale customers + Updated
20Major customers + Updated
30Retail customers + Updated
40Internet customers + Updated
50Employees
80Wholesale customers + Updated
90Wholesale customers + Updated
  • 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.

tip

This type of FormControlEventHandler will only be needed when you are extending a standard form. For a totally custom form, you can add a Clicked() method method for any buttons on the form.

Using XppPrePostArgs to pass parameters through a method

· 2 min read
Kome Hoang
Maintainer of Automaly

References

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.

Using Kusto functions in Power BI datasource

· 2 min read
Kome Hoang
Maintainer of Automaly

References

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

No data is inserted/modified in temporary table when processing an SRS report using Pre-process

· 2 min read
Kome Hoang
Maintainer of Automaly

References

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.
}
}

Set default model for Dynamics 365 Finance Operations project

· One min read
Max Nguyen
FinOps Ranger

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

DynamicsDevConfig.XML
<?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>

How to hide New and Delete buttons (command buttons) in form

· One min read
Max Nguyen
FinOps Ranger

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

Alt text

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();
}
}