Hi everyone,
Recently, I got the opportunity to explore Excel Add-ins in Dynamics 365 Finance & Operations. I was working on a requirement to add a custom field to the BudgetRegisterEntryEntity data entity.
To achieve this, I extended the standard data entity and the staging table for BudgetRegisterEntryEntity, and added the custom field as a data source field. After building the changes, I tested data import using the Excel Add-in.
While the standard fields were imported successfully, the custom field data was not getting imported. This led me to dive deeper into how Excel Add-ins interact with extended entities and staging tables in D365 F&O.
Upon further investigation, I cross-checked the entity setup and found that the data entity was configured with SetBasedProcessing = Yes. When an entity is set-based enabled, it supports bulk import/export operations through the Data Management framework.
To handle the custom field, I created an extension of the copyStagingToTarget method and added the necessary update statement for the custom field mapping. This worked successfully when importing data through Data Management.
However, the Excel Add-in, which interacts with D365 F&O via OData (POST operations), does not support importing data for certain custom fields in entities that use set-based processing.
To overcome this limitation, I duplicated the standard BudgetRegisterEntryEntity and its staging table, then added the required custom field directly to both.
To make the new entity available for users, I created two X++ classes to add the duplicate data entity to the Excel Add-ins menu on the BudgetTransaction form.
This allowed users to seamlessly import data through Excel Add-ins using the newly created entity. including the custom field that wasn’t supported in the original standard entity.
using Microsoft.Dynamics.Platform.Integration.Office;
internal final class BudgetTransactionFormOpenInExcel
{
/// <summary>
/// Eventhandler of form on initializing
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
[FormEventHandler(formStr(BudgetTransaction), FormEventType::Initializing)]
public static void BudgetTransaction_OnInitializing(xFormRun sender, FormEventArgs e)
{
FormRun formRun = sender as FormRun;
if(formRun)
{
OfficeFormRunHelper officeHelper = formRun.officeHelper();
if(officeHelper)
{
officeHelper.OfficeMenuInitializing += eventhandler
(BudgetTransactionFormOpenInExcel::officeMenuInitializingHandler);
}
}
}
private static void officeMenuInitializingHandler(FormRun _formRun, OfficeMenuEventArgs _eventArgs)
{
const str CustomExportLinesToExcelOptionId = 'BudgetRegisterEntryEntityCopy';
// Modify the OfficeMenuOptions available on the OfficeMenuEventArgs.menuOptions() as necessary.
OfficeMenuOptions menuOptions = _eventArgs.menuOptions();
// Find the entity options if they were included by default.
// OfficeMenuDataEntityOptions entityOptions = menuOptions.getOptionsForEntity(tableStr(BudgetRegisterEntryEntityCopy));
/* if (!entityOptions)
{
// The entity options were not included. Add them.
entityOptions = OfficeMenuDataEntityOptions::construct(tableStr(BudgetRegisterEntryEntityCopy));
menuOptions.dataEntityOptions().addEnd(entityOptions);
}*/
OfficeGeneratedExportMenuItem menuItem = OfficeGeneratedExportMenuItem::construct(tableStr(BudgetRegisterEntryEntityCopy), CustomExportLinesToExcelOptionId);
menuItem.displayName("BudgetRegisterEntryEntityCopy");
menuOptions.customMenuItems().addEnd(menuItem);
menuItem.getDataEntityContext += eventhandler(BudgetTransactionFormOpenInExcel::getDataEntityContextHandler);
}
private static void getDataEntityContextHandler(OfficeGeneratedExportMenuItem _menuItem, FormRun _formRun, ExportToExcelDataEntityContext _dataEntityContext)
{
ExportToExcelDataEntityContext context = _dataEntityContext;
const str CustomExportLinesToExcelOptionId = 'BudgetRegisterEntryEntityCopy';
BudgetTransactionHeader budget = _formRun.dataSource(formDataSourceStr(BudgetTransaction,BudgetTransactionHeader)).cursor();
if (_menuItem.id() == CustomExportLinesToExcelOptionId)
{
ExportToExcelDataEntityContext::setContext(context, tableStr(BudgetRegisterEntryEntityCopy), tableFieldGroupStr(BudgetRegisterEntryEntityCopy, AutoReport));
// context.addEntityDefault(tableStr(BudgetRegisterEntryEntityCopy));
ListEnumerator enumerator = context.entities().getEnumerator();
while (enumerator.moveNext())
{
ExportToExcelDataEntityInfo entity = enumerator.current();
LegalEntityDataAreaId legalEntityId = curExt();
if (entity.entityName() == tableStr(BudgetRegisterEntryEntityCopy))
{
ExportToExcelFilterTreeBuilder filterBuilder = new ExportToExcelFilterTreeBuilder(tableStr(BudgetRegisterEntryEntityCopy));
// FilterNode filterExpression = filterBuilder.areEqual(fieldStr(BudgetRegisterEntryEntityCopy,EntryNumber),budget.TransactionNumber);
// entity.filter(filterExpression);
var filter = filterBuilder.and(
filterBuilder.and(
filterBuilder.areEqual(fieldStr(BudgetRegisterEntryEntityCopy, LegalEntityId), legalEntityId),
filterBuilder.areEqual(fieldStr(BudgetRegisterEntryEntityCopy, DataAreaId), legalEntityId)),
filterBuilder.areEqual(fieldStr(BudgetRegisterEntryEntityCopy, EntryNumber), budget.TransactionNumber));
entity.filter(filter);
}
}
}
}
} Both classes enable the new Excel Add-ins menu option, allowing users to export or import data with automatic filtering based on the selected record in the form.
CoC : ExportToExcelDataEntityContext class
using Microsoft.Dynamics.Platform.Integration.Office;
using Microsoft.Dynamics.Platform.Integration.Office.MetadataCache;
[ExtensionOf(classStr(ExportToExcelDataEntityContext))]
internal final class DataEntityContext_Extension
{
public static ExportToExcelDataEntityContext setcontext(ExportToExcelDataEntityContext _context,str _entityName = '', str _fieldGroupName = '')
{
ExportToExcelDataEntityContext context = _context;
if (_entityName)
{
EntityMetadata dataEntity = ExportToExcelMetadataCache::getEntity(_entityName);
if (dataEntity == null)
{
throw error(strfmt("@ApplicationPlatform:ExportToExcel_DataEntityNotPublic", _entityName));
}
// Add the entity information.
ExportToExcelDataEntityInfo entityInfo = new ExportToExcelDataEntityInfo();
entityInfo.entityName(dataEntity.Name);
context.entities().addEnd(entityInfo);
// Add the binding information.
ExportToExcelDataEntityBindingInfo bindingInfo = new ExportToExcelDataEntityBindingInfo();
bindingInfo.entityName(entityInfo.entityName());
if (_fieldGroupName)
{
if (dataEntity.HasFieldGroup(_fieldGroupName))
{
bindingInfo.useFieldGroup(true);
bindingInfo.fieldGroupName(_fieldGroupName);
}
else
{
throw error(strFmt("@ApplicationPlatform:ExportToExcel_InvalidFieldGroup", _fieldGroupName, dataEntity.Name));
}
}
context.bindings().addEnd(bindingInfo);
}
return context;
}
}Additionally, I modified the mapEntityToDataSource and updateEntityDataSource methods at the data entity level to correctly map the custom field between the data entity and its data source, ensuring smooth Excel Add-ins import/export functionality.
To enable lookups for fields in the Excel Add-in, it’s important to create entity relations. This ensures that lookup values are displayed correctly when users open or edit data in Excel.
public boolean updateEntityDataSource(DataEntityRuntimeContext _entityCtx, DataEntityDataSourceRuntimeContext _dataSourceCtx)
{
boolean isValid = true;
isValid = BudgetTransactionManager::validateInUseBy(this.InUseBy, this.EntryNumber) && isValid;
switch (_dataSourceCtx.name())
{
case dataentitydatasourcestr(BudgetRegisterEntryEntityCopy, BudgetTransactionHeader):
BudgetTransactionHeader header = _dataSourceCtx.getBuffer();
this.mapHeaderForUpsert(header);
isValid = BudgetTransactionManager::checkWorkflowStatusForUpdate(this.DocumentWorkflowStatus, this.EntryNumber) && isValid;
// We perform the validation here because we can't get BudgetTransactionHeader buffer
// from the entity's validateWrite().
isValid = BudgetRegisterEntryEntityCopy::validateBudgetTransactionCode(header) && isValid;
break;
case dataentitydatasourcestr(BudgetRegisterEntryEntityCopy, BudgetTransactionLine):
BudgetTransactionLine line = _dataSourceCtx.getBuffer();
line.ToCompany = this.ToCompany;
Line.TransactionCurrencyAmount = this.TransactionCurrencyAmount;
Line.AccountingCurrencyAmount = this.AccountingCurrencyAmount;
break;
}
if (!isValid)
{
// methods above will log errors, throw an exception to stop the update.
throw Exception::Error;
}
return super(_entityCtx, _dataSourceCtx);
}
public void mapEntityToDataSource(DataEntityRuntimeContext _entityCtx, DataEntityDataSourceRuntimeContext _dataSourceCtx)
{
super(_entityCtx, _dataSourceCtx);
if (_dataSourceCtx.getDatabaseOperation() == DataEntityDatabaseOperation::Insert)
{
switch (_dataSourceCtx.name())
{
case dataentitydatasourcestr(BudgetRegisterEntryEntityCopy, BudgetTransactionHeader):
BudgetTransactionHeader header = _dataSourceCtx.getBuffer();
this.setEntryNumber(header);
break;
case dataentitydatasourcestr(BudgetRegisterEntryEntityCopy, BudgetTransactionLine):
BudgetTransactionLine line = _dataSourceCtx.getBuffer();
line.AccountingCurrencyAmount = this.AccountingCurrencyAmount;
line.TransactionCurrencyAmount = this.TransactionCurrencyAmount;
line.ToCompany = this.ToCompany;
if (!line.BudgetType)
{
MainAccountNum mainAccount = LedgerDimensionFacade::getMainAccountIdFromLedgerDimension(this.Dimension);
if (mainAccount)
{
line.BudgetType =BudgetTransactionLine::determineBudgetTypeFromMainAccountCopy(mainAccount);
}
}
break;
}
}
} Conclusion:
Exploring the Excel Add-ins functionality in Dynamics 365 Finance & Operations gave me a deeper understanding of how data entities, OData services, and staging tables work together. This task not only helped me resolve the custom field import issue but also enhanced my knowledge of entity extensions, mappings, and form integrations.
Overall, it was a great learning experience — from debugging and customizing the entity to enabling seamless Excel integration for end users.