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
andQueryBuildDataSource
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
andDivision
) 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:
Post a Comment