D365FO : Script for update Storage Dimension Group for the Items with no transactions. x++

We have a problem on finance test... The storage dimension group = Mlok is setup to use warehouse management. But we don't have all the necessary information on the item to use warehouse management. So we have created a new storage dimension group called Mlok2, that do not use warehouse management.. But now we have a lot of items with Mlok, that need to be changed to Mlok2, none of them have a transaction. But it is very time-consuming to change manually.

 /// <summary>

/// This Runnable class is used to update the new Storage Dimension group for the item 

/// As the existing dimension does not have all the necessary information on the item to use warehouse management, they have decided to update the dimension to a new one

/// Since it has 400k records custom script is failing due to limitation, after data update this class will be deleted.

/// </summary>

internal final class MK_UpdateStorageDimensionsGroup

{

    /// <summary>

    /// Class entry point. The system will call this method when a designated menu

    /// is selected or when execution starts and this class is set as the startup class.

    /// </summary>

    /// <param name = "_args">The specified arguments.</param>

    public static void main(Args _args)

    {

        const str OLD_GROUP = 'Mlok';

        const str NEW_GROUP = 'Mlok2';

 

        EcoResStorageDimensionGroup oldGroup, newGroup;

        InventTable                 inventTable;

        EcoResProduct               ecoResProduct;

        EcoResStorageDimensionGroupItem    storageDimGroupItem;

        EcoResStorageDimensionGroupProduct storageGroupProduct;

        InventTrans                 inventTrans;

 

        // Find storage dimension groups

        oldGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(OLD_GROUP);

        newGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(NEW_GROUP);

 

        if (!oldGroup.RecId || !newGroup.RecId)

        {

            throw error(strFmt("Storage dimension groups %1 or %2 not found", OLD_GROUP, NEW_GROUP));

        }

 

        // Select only InventTable linked to OLD_GROUP (via item or product)

        while select inventTable

            join ecoResProduct

                where ecoResProduct.RecId == inventTable.Product

            outer join storageDimGroupItem

                where storageDimGroupItem.ItemId == inventTable.ItemId

                    && storageDimGroupItem.StorageDimensionGroup == oldGroup.RecId

            outer join storageGroupProduct

                where storageGroupProduct.Product == ecoResProduct.RecId

                    && storageGroupProduct.StorageDimensionGroup == oldGroup.RecId

                    && inventTable.ItemType == ItemType::Item

        {

            if (!ecoResProduct.RecId)

                continue;

 

            if (storageGroupProduct.RecId || storageDimGroupItem.RecId)

            {

                // Skip items with transactions

                select firstonly RecId from inventTrans

                    where inventTrans.ItemId == inventTable.ItemId;

 

                if (inventTrans.RecId)

                {

                    info(strFmt("Item %1 skipped because it has transactions", inventTable.ItemId));

                    continue;

                }

 

                ttsBegin;

                if (storageGroupProduct.RecId)

                {

                    storageGroupProduct.selectForUpdate(true);

                    storageGroupProduct.StorageDimensionGroup = newGroup.RecId;

                    storageGroupProduct.update();

                }

 

                if (storageDimGroupItem.RecId)

                {

                    storageDimGroupItem.selectForUpdate(true);

                    storageDimGroupItem.StorageDimensionGroup = newGroup.RecId;

                    storageDimGroupItem.update();

                }

 

                ttsCommit;

                //To wrap everything in a single ttsBegin/ttsCommit. If it hit an error midway it roll back everything. Better to commit per item.

                info(strFmt("Item %1 updated from %2 → %3",inventTable.ItemId, OLD_GROUP, NEW_GROUP));

            }

        }

        info("Storage dimension group update completed.");

    }

}

How to Disable “Advanced Filter or Sort” and Enforce Custom Filters on Any D365FO Form

 In Dynamics 365 Finance and Operations, users can apply filters through the “Advanced filter or sort” feature found under the Options tab. But sometimes, you want to:

• Prevent users from modifying queries manually

• Enforce organization-specific filtering logic (e.g., based on dimensions, roles, or personnel)

This post explains how to do that generically, so you can reuse this logic for any form or datasource.

✅ What We'll Do

• 🔒 Disable "Advanced filter or sort" and shortcut filters

• 🧹 Clear user-applied filters

• 🎯 Apply organization-controlled filters

• 🧼 Use a clean helper + event-handler pattern

🧩 Step 1: Disable “Advanced Filter or Sort” in the Form

Extend the form and override the task() method to block the SysQuery task:

Generic Form Extension Example:

[ExtensionOf(formStr(PurchTable))]

final class MKPurchTableForm_Extension

{

    public int task(int _taskId)

    {

        #Task

        FormRun formRun = this as FormRun;

        FormDataSource purchTable_ds = formRun.dataSource(formDataSourceStr(PurchTable, PurchTable)) as FormDataSource;       

        // Always call next task() first (per best practices)

        int ret = next task(_taskId);

        // Only act if it's a filter task

        if (_taskId == #taskFilter || _taskId == #taskShortCutMenuFilter)

        {

            info("Advanced filtering is disabled.");

            if (purchTable_ds)

            {

                POAccessHelper::applyPOAccessFilter(purchTable_ds);

                purchTable_ds.executeQuery(); // Re-run query with our custom filters

            }

            // Override the result to suppress filter behavior

            ret = 0;

        }

        return ret;

    }

}

Filter the PurchTableListPage in D365FO on Dimensions: BusinessUnit and Division using DimensionProvider class.

 In D365FO, dimensions are a powerful tool for categorizing and analyzing transactional data. But what if you want to filter purchase orders based on specific dimension values—say, BusinessUnit and Division? This blog shows you how to do just that using X++ and the DimensionProvider class.

Scenario

You want to filter the PurchTableListPage (Purchase Orders) to only show entries where:

• BusinessUnit = 02

• Division = 31700

This is useful for reports, user-specific views, or tailored processing logic.

Let’s walk through the code and see how it works.

The Code: Filter PurchTable by Dimensions

internal final class TestDimFilter

{

    public static void main(Args _args)

    {

        Query query = new Query();

        QueryRun queryRun;

        QueryBuildDataSource queryPurchTable;

        PurchTable purchTable;

        DimensionProvider dimensionProvider = new DimensionProvider();

        DimensionAttributeValueSetStorage dimAttrValueSetStorage;

        int i;

        // Add PurchTable as root datasource

        queryPurchTable = query.addDataSource(tableNum(PurchTable));

        // Add dimension filter: BusinessUnit = "02"

        dimensionProvider.addAttributeRangeToQuery(

            query,

            queryPurchTable.name(),               // Data source alias

            identifierStr(DefaultDimension),      // Dimension field

            DimensionComponent::DimensionAttribute,

            "02",                                 // Filter value

            "BusinessUnit",                       // Dimension name

            true                                  // Add as a new range

        );

        // Add dimension filter: Division = "31700"

        dimensionProvider.addAttributeRangeToQuery(

            query,

            queryPurchTable.name(),

            identifierStr(DefaultDimension),

            DimensionComponent::DimensionAttribute,

            "31700",

            "Division",

            true

        );

        // Run query

        queryRun = new QueryRun(query);

        while (queryRun.next())

        {

            purchTable = queryRun.get(tableNum(PurchTable));

            // Read and print the dimension values

            dimAttrValueSetStorage = DimensionAttributeValueSetStorage::find(purchTable.DefaultDimension);

            for (i = 1; i <= dimAttrValueSetStorage.elements(); i++)

            {

                info(strFmt("PurchId %1 -> Dim Name %2: value %3",

                    purchTable.PurchId,

                    DimensionAttribute::find(dimAttrValueSetStorage.getAttributeByIndex(i)).Name,

                    dimAttrValueSetStorage.getDisplayValueByIndex(i)));

            }

        }

    }

}

Breakdown: What's Happening?

• Query and QueryBuildDataSource are used to construct a query against the PurchTable.

• DimensionProvider::addAttributeRangeToQuery() injects a dimension-based filter directly into the query.

• The two dimension names used (BusinessUnit and Division) must match exactly what's defined in your D365FO instance.

• Once the query runs, we iterate over results and print out each purchase order's dimension values for verification.

Real-World Use Cases

• Security/Personalization: Limit purchase order views based on the user's assigned business unit.

• Reporting: Generate targeted reports for specific departments or divisions.

• Data Exports: Extract dimension-specific purchase order data for external systems.

Final Thoughts

This approach keeps your logic clean and reusable, especially in custom forms or batch jobs. The DimensionProvider class simplifies what used to be a tricky process—filtering ledger or default dimensions in queries—into readable and maintainable code.

Cleaning Up Duplicate Records in SQL Server: A Practical Walkthrough

🧹 How to Identify and Remove Duplicate Records in SQL Server

Duplicate records are a common headache in databases. They mess up reporting, inflate numbers, and introduce inconsistencies that can quietly break things. Whether you're cleaning legacy data or preventing future problems, knowing how to find and eliminate duplicates is an essential SQL skill.

Here’s a practical approach using a sample table we'll call xxyyzz. 

Step 1: Explore the Table

Start by reviewing what’s in the table:

SELECT * FROM xxyyzz;

This gives you the lay of the land. Before doing anything destructive, it's crucial to understand which columns matter when determining if two rows are considered duplicates.

Step 2: Detect Duplicates

Let’s say duplicates are defined by a combination of columns — for example, everything except a unique ID or timestamp. You can group by those columns and count how many times each combination appears.

SELECT 

    column1, column2, column3, ..., columnN,

    COUNT(*) AS DuplicateCount

FROM xxyyzz

GROUP BY 

    column1, column2, column3, ..., columnN

HAVING COUNT(*) > 1;

Replace column1 through columnN with the fields that define a unique row in your context.

Step 3: Remove Duplicates Using a CTE

Now comes the cleanup. You want to keep one row from each group and delete the rest. This is where a Common Table Expression (CTE) with ROW_NUMBER() comes in.

WITH DuplicatesCTE AS (

    SELECT *,

        ROW_NUMBER() OVER (

            PARTITION BY column1, column2, column3, ..., columnN

            ORDER BY ID -- or CreatedDate, or another tie-breaker

        ) AS rn

    FROM xxyyzz

)

DELETE FROM DuplicatesCTE WHERE rn > 1;

What this does:

• The ROW_NUMBER() function assigns a row number to each record in each duplicate group.

• Rows are grouped by the columns that define uniqueness.

• The row with rn = 1 (usually the oldest or lowest ID) is kept.

• Everything else (rn > 1) is deleted.

⚠️ Always back up your data or test in a staging environment before running deletes.

✅ Optional: Prevent Future Duplicates

If this table should never have duplicates in the first place, consider enforcing a constraint with a unique index:

CREATE UNIQUE INDEX UX_xxyyzz_UniqueRows

ON xxyyzz (column1, column2, column3, ..., columnN);

This will block any future insertions that would create another duplicate.

Final Thoughts

Cleaning duplicates is routine, but it pays off every time. It enhances data accuracy, streamlines queries, and optimises performance. The combo of GROUP BY, ROW_NUMBER(), and a targeted approach DELETE gives you a clean, reliable fix.

Let this be part of your standard SQL toolkit — and if you need help customising it for a specific dataset or situation, just ask. 

 

How to Convert Between List and Container in X++ for JSON Serialization

How to Convert Between List and Container in X++ for JSON Serialization

In Dynamics 365 Finance and Operations (D365FO), working with data serialization, especially when dealing with lists or arrays, can be a bit tricky. The native List class and container type don't work in exactly the same way when it comes to serialization in JSON. In this blog post, we'll explore how to convert between a List of strings and a container, a fundamental task for serializing collections of data correctly in D365FO.

What Are Lists and Containers?

• List: A dynamic collection of elements. In X++, the List class is used to store collections of objects, such as strings, integers, or other data types.

• Container: A generic type in X++ that can store multiple data types. It's often used to hold values of different types (like integers, strings, or even other containers). For serialization, container is the preferred type when dealing with collections in Dynamics 365.

When you need to serialize a collection of strings (such as tags, categories, etc.) as part of a DataContract class for an API or service, you might encounter issues since X++ doesn't natively allow you to directly serialize a List of strings into JSON. Instead, you'll need to convert the List to a container, serialize it, and later convert it back to a List.

Why Do We Need This Conversion?

When using AIF (Application Integration Framework) or Data Contracts in D365FO, the container type is automatically supported for JSON serialization. However, List is not directly serializable. As a result, we need to implement conversion methods to handle this transformation when dealing with collections like strings or integers.

Here, we'll walk through how to create two utility methods that help convert between a List and a container in X++.

The Conversion Methods

1. List to Container Conversion

This method converts a List of strings into a container. The key here is iterating over each item in the List and adding it to a new container:

x++
public static container listToContainer(List listOfStrings)
{
    container result;
    ListEnumerator enumerator = listOfStrings.getEnumerator();

    while (enumerator.moveNext())
    {
        result += enumerator.current();
    }

    return result;
}

Explanation:

• We initialize an empty container called result.

• The ListEnumerator allows us to loop through the List one item at a time.

• We use the += operator to add each item from the List into the container.

• Finally, the container is returned, now holding the same items as the original List.

2. Container to List Conversion

The reverse operation converts a container back into a List of strings. Here's the implementation:

x++
public static List containerToList(container con)
{
    List result = new List(Types::String);
    int i;

    for (i = 1; i <= conLen(con); i++)
    {
        result.addEnd(conPeek(con, i));
    }

    return result;
}

Explanation:

• We initialize an empty List of type string called result.

• The conLen function gets the number of elements in the container.

• We loop through the container, extracting each element using conPeek() and adding it to the List using addEnd().

• Finally, we return the populated List.

---

Using the Conversion Methods in Your Code

Let's see how you can use these helper methods in a typical use case, such as managing shipment tags in a Data Contract class.

Updated ShipmentTags Method Example

Here’s how you would use the conversion methods in a ShipmentTags property in your Data Contract:

x++
[DataMemberAttribute(#ShipmentTags), AifCollectionTypeAttribute(#_ShipmentTags, Types::String)]
public List ShipmentTags(List _ShipmentTags = containerToList(fpShipmentTags.parmValue()))
{
    if (!prmisDefault(_ShipmentTags))
    {
        fpShipmentTags.parmValue(listToContainer(_ShipmentTags));
    }

    return _ShipmentTags;
}

Explanation:

• Serialization: When the method is called, if a List of strings is passed, it gets converted to a container for serialization. This is achieved through the listToContainer() method.

• Deserialization: When deserializing data, the container is converted back to a List of strings using the containerToList() method.

Now, you can easily assign values to the ShipmentTags property as follows:

x++
// Create a list of strings
List shipmentTagsList = new List(Types::String);
shipmentTagsList.addEnd("Fragile");
shipmentTagsList.addEnd("Express");
shipmentTagsList.addEnd("HandleWithCare");

// Create an instance of your contract class
CarIntApiTrkCreateContract contract = CarIntApiTrkCreateContract::construct();

// Assign the list to the ShipmentTags property
contract.ShipmentTags(shipmentTagsList);

This will serialize into the following JSON:

json
"shipmentTags": ["Fragile", "Express", "HandleWithCare"]

Conclusion

By implementing the ListToContainer and ContainerToList conversion methods, you can easily manage collections of primitive types (like strings) for JSON serialization in Dynamics 365 Finance and Operations. These utility methods allow you to seamlessly work with List objects in your code while ensuring compatibility with D365FO's serialization framework.

If you're building a custom service or API that involves sending or receiving lists of data, these conversion methods will be crucial for ensuring that data is properly serialized and deserialized. They streamline your development process, avoiding the need for manual conversion every time you work with lists in Data Contracts.

 

Secure API Integration in D365FO Using OAuth2 and Azure Key Vault (X++)

Integrating with external APIs securely is a common requirement in Microsoft Dynamics 365 Finance and Operations (D365FO) projects. In this post, we will walk through a complete, secure, and reusable approach for integrating a third-party API using OAuth2 and Azure Key Vault to retrieve secrets like the client secret.

Use Case

You want to:

• Authenticate using OAuth2 client credentials flow.

• Securely retrieve the client secret from Azure Key Vault.

• Send a JSON-based POST request to an external API.

• Build this in a reusable way using standard .NET classes in X++.

Solution Overview

We'll create a reusable class NarviAPIClient with two core methods:

1. getOAuthTokenFromKeyVault() - Retrieves an access token securely.

2. sendPostRequest() - Sends a POST request with Authorization header and JSON payload.

Step-by-Step Implementation

Step 1: Create NarviAPIClient Class in AOT

class NarviAPIClient

{

    public static str getOAuthTokenFromKeyVault(str clientId, str tenantId, str resource, str secretName)

    {

        str tokenEndpoint, clientSecret, formData, responseText, accessToken;

        System.Net.HttpWebRequest tokenRequest;

        System.Net.HttpWebResponse tokenResponse;

        System.IO.Stream responseStream;

        System.IO.StreamReader streamReader;

        System.Text.UTF8Encoding encoding;

        System.Byte[] bytes;

        Map tokenMap;

        KeyVaultParameters keyVaultParameters = KeyVaultParameters::construct();

        clientSecret = KeyVaultCertificateHelper::getSecretValueFromKeyVault(secretName, keyVaultParameters);

        tokenEndpoint = strFmt("https://login.microsoftonline.com/%1/oauth2/token", tenantId);

        formData = strFmt("grant_type=client_credentials&client_id=%1&client_secret=%2&resource=%3",

                          clientId, clientSecret, resource);

        try

        {

            tokenRequest = System.Net.WebRequest::Create(tokenEndpoint) as System.Net.HttpWebRequest;

            tokenRequest.set_Method("POST");

            tokenRequest.set_ContentType("application/x-www-form-urlencoded");

            encoding = new System.Text.UTF8Encoding();

            bytes = encoding.GetBytes(formData);

            tokenRequest.get_RequestStream().Write(bytes, 0, bytes.Length);

            tokenResponse = tokenRequest.GetResponse();

            responseStream = tokenResponse.GetResponseStream();

            streamReader = new System.IO.StreamReader(responseStream);

            responseText = streamReader.ReadToEnd();

            tokenMap = RetailCommonWebAPI::parseJSON(responseText);

            accessToken = tokenMap.lookup("access_token");

            return accessToken;

        }

        catch (Exception::CLRError)

        {

            error(strFmt("OAuth token error: %1", AifUtil::getClrErrorMessage()));

            return "";

        }

    }

    public static str sendPostRequest(str url, str jsonBody, str accessToken)

    {

        System.Net.HttpWebRequest httpRequest;

        System.Net.HttpWebResponse httpResponse;

        System.IO.Stream requestStream, responseStream;

        System.IO.StreamReader reader;

        System.Text.UTF8Encoding encoding;

        System.Byte[] bytes;

        str responseText;

        try

        {

            httpRequest = System.Net.WebRequest::Create(url) as System.Net.HttpWebRequest;

            httpRequest.set_Method("POST");

            httpRequest.set_ContentType("application/json");

            httpRequest.Headers.Add("Authorization", strFmt("Bearer %1", accessToken));

            encoding = new System.Text.UTF8Encoding();

            bytes = encoding.GetBytes(jsonBody);

            requestStream = httpRequest.GetRequestStream();

            requestStream.Write(bytes, 0, bytes.Length);

            httpResponse = httpRequest.GetResponse();

            responseStream = httpResponse.GetResponseStream();

            reader = new System.IO.StreamReader(responseStream);

            responseText = reader.ReadToEnd();

            return responseText;

        }

        catch (Exception::CLRError)

        {

            return strFmt("POST error: %1", AifUtil::getClrErrorMessage());

        }

    }

}

Step 2: Use the Class from a Job or Service

static void CallNarviAPIClient(Args _args)

{

    str tenantId     = "<your-tenant-id>";

    str clientId     = "<your-client-id>";

    str resource     = "<your-api-resource-uri>";

    str secretName   = "your-client-secret-keyvault-name";

    str apiUrl       = "https://your-api-endpoint.com/api/post";

    str jsonPayload, accessToken, response;

    System.IO.StringWriter writer = new System.IO.StringWriter();

    Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(writer);

    jsonWriter.WriteStartObject();

    jsonWriter.WritePropertyName("ProjId");

    jsonWriter.WriteValue("000123");

    jsonWriter.WritePropertyName("TransDate");

    jsonWriter.WriteValue("2025-05-13");

    jsonWriter.WriteEndObject();

    jsonPayload = writer.ToString();

    accessToken = NarviAPIClient::getOAuthTokenFromKeyVault(clientId, tenantId, resource, secretName);

    if (!accessToken)

    {

        error("Token not retrieved.");

        return;

    }

    response = NarviAPIClient::sendPostRequest(apiUrl, jsonPayload, accessToken);

    info(strFmt("API Response: %1", response));

}

Benefits of This Approach

• Secure: Client secrets aren't hardcoded.

• Reusable: Centralized logic for token generation and POST request.

• Modular: Easily extendable for GET requests, token caching, retries, etc.

• Production Ready: Compatible with batch and service classes.

Prerequisites

• Azure Key Vault configured in LCS.

• The app must have permissions to read secrets.

• Secrets must be stored in Key Vault.

Enable Power Platform Integration: Set up D365FO integration with Dataverse

 

Set up D365FO integration with Dataverse

 This document shows how to set up D365FO integration with Dataverse.

Create a security role

1. Sign in to the Power Platform admin centre and select an environment.

2. Select Settings > Users + permissions > Security roles.

3. Select + New role.

4. Enter a role name.
5. Select a business unit.
6. To allow team members to inherit the privileges of this role when it's assigned to a team, accept the default Member's privilege inheritance setting, which is Direct User (Basic) access level and Team privileges.
7. Select Save. The properties of the new role are displayed.

8. Enter your table name in the Search input field to find your app's table.

9. Select your table and set the Permission settings. Then select the Save button.
10. Assigning new role to user
1. Select user and click 3 dots.

2.      Select Manage security roles.

3.      Select newly created role and save.