Thursday, 31 July 2025

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.

No comments:

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