Skip to main content

Extending Excel Importer

We designed Excel Importer to be extensible so that our customers could handle new scenarios or create additional import types. This section provides information on how to extend Excel Importer, including the events that are available. Partners can use this information to extend Excel Importer.

info

To extend Excel Importer, you need to have a Premium plan.

Need help or additional events?

If you need some guidance extending Excel Importer or you want to request an additional event or helper to assist with your extension, please get in touch with us. Remember to tell us what outcome you want to achieve - you never know; we might want to add this feature to Excel Importer and save you the trouble.

Adding a new Import Type

Example: Adding a new Import Type (Basic Example)

This example uses the Standard Cost Worksheet import type as an example, which does not have any special processing.

  • Extend the enum "ImportType_EI_TSL" with your custom import type
enumextension 50000 "ImportType" extends ImportType_EI_TSL
{
value(50000; "Standard Cost Worksheet")
{
Caption = 'Standard Cost Worksheet';
}
}

  • Create a codeunit similar to the one below:
codeunit 50000 "StdCostWorksheetMgt"
{
internal procedure ImportStandardCostWorksheet();
var
ExcelImportManagement: Codeunit "Excel Import Management_EI_TSL";
begin
ExcelImportManagement.Import(Enum::ImportType_EI_TSL::"Standard Cost Worksheet", Database::"Standard Cost Worksheet", false);
end;
}
  • Create a page extension to add the Import from Excel action to your page and call your function
pageextension 50000 "StandardCostWrksheet" extends "Standard Cost Worksheet"
{
actions
{
addfirst("F&unctions")
{
action("ImportFromExcel")
{
ApplicationArea = All;
Caption = 'Import from Excel';
Image = ImportExcel;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
ToolTip = 'Allows you to import standard costs from Excel in a customisable layout.';

trigger OnAction()
var
StandardCostWrksheetImport: Codeunit StdCostWrksheetMgt;
begin
StandardCostWrksheetImport.ImportStandardCostWorksheet();
end;
}
}
}
}

Adding New Import Types or Changing Existing Imports

This section references events that are available to extend Excel Importer. It is not a complete list of the available events but includes events that get used frequently. You can use these events when you have more complex processing that must get handled. You can use these events when you create new import types or if you want to change the way existing import types work.

Object - EventPurpose
table "Exl. Mapping Template_EI_TSL" - OnGetCustomTableIDSpecifies the table associated with the import type.
table "Exl. Mapping Template_EI_TSL" - OnInsertCustomTemplateToBufferyou can optionally subscribe to this event if you want to specify additional details about a custom import type
table "Exl. Mapping Template_EI_TSL" - OnCheckIfFieldFromCustomEntityCanBeEditedUse this event if you want to protect certain fields from being updated by the import. Using this event will cause fields to be filtered out of lookups so they cannot be selected.
table "Exl. Mapping Template_EI_TSL" - OnBeforeValidateSettingValuesForSettingTypeUse this event to check if the setting type and value specified is valid for the import type.
table "Exl. Mapping Template_EI_TSL" - OnBeforeGetRelatedFieldsAndSettingsForCustomEntityUse this event to specify the available settings as well as any related tables that get updated by the import (e.g. Header/Line)
codeunit "SettingsHelper_EI_TSL" - OnCustomLookupSettingValueUse this event to specify the available setting values for the setting type in relation to the import type.
table "Exl. Mapping Template_EI_TSL" - OnBeforeGetCommaSeparatedDimensionFieldIDTextUse this event associate global/shortcut dimension fields with the dimension code.

Example - Handling Global/Shortcut Dimensions

You use this event so that the Excel Importer will update the Global/Shortcut Dimension fields as expected.

    [EventSubscriber(ObjectType::Table, Database::"Exl. Mapping Template_EI_TSL", 'OnBeforeGetCommaSeparatedDimensionFieldIDText', '', false, false)]
local procedure OnBeforeGetCommaSeparatedDimensionFieldIDText(ImportType: Enum ImportType_EI_TSL; var CommaSeparatedFieldIDText: Text; var IsHandled: Boolean);
var
ExlAccDimMapLine: Record ExlAccDimMapLine_EI_TSL;
begin
if ImportType <> ImportType::"Account/Dimension Mapping" then
exit;
CommaSeparatedFieldIDText := Format(ExlAccDimMapLine.FieldNo("Shortcut Dimension 1 Code")) + ',' + Format(ExlAccDimMapLine.FieldNo("Shortcut Dimension 2 Code"));
IsHandled := true;
end;
note

The events are shown in the order in which they fire.

EventPurpose
OnAfterCopyExcelBufferToExlImpBufferThis event is raised after the data from the current row is transferred into a temporary record, before any processing is done and before the app checks criteria for skipping the row. You can use this event if you want to perform special processing using a footer record. The event includes all buffers so you need to use the record type to determine which before to use. The LastImportedRecordRef will contain the values imported from a previous row as it has not been initialised as this stage.
OnBeforeCheckImportedValuesBeforeUpdateUse this event if you want to do your validation before importing data. You will use this if you want to suppress some of the standard validation added by Excel Importer and add your own.
OnAfterCheckImportedValuesBeforeUpdateUse this event to add additional validation before importing data.
OnBeforeImportExcelDataUse this event to perform custom logic after the Excel File is loaded but before the processing starts
OnBeforeExcludeExcelBufferRowFromImportUse this event if you want to exclude an Excel row from the import. At this point Excel Importer has not transferred any values to fields
OnBeforeFindExistingRecordThis event fires before Excel Importer checks if there is an existing record. This is commonly used to manipulate the imported data - for example replacing an item reference with the actual item number. The StagingRecordRef parameter contains values that are mapped to fields.
OnBeforeSkipImportOfRecordUse this event if you want to skip the import based on values on the line. The StagingRecordRef parameter contains values that are mapped to fields. This is before Excel Import has tried to find an existing record.
OnFindExistingRecordThis event is fire if Excel Importer finds an existing record with the Record ID as the record being imported
OnInsertImportedDataThis event fires before a record is inserted. At this stage no values will be transferred to the NewRecordRef unless the record exists already. You can use StagingRecordRef to get the mapped field values. You can also insert the record if it needs to exist before updating other field values
OnBeforeApplyDataThis event fires before the import values are copied to the record.
OnAfterAssignFieldValueThis event gets fired after a value gets assigned to a field. You can use this event to perform special processing after updating a specific field during import.
OnBeforeModifyOrInsertDataThis event fires before the record is insert or modified. The dimension set is also not applied that this point. You can use this event to handle the insert or modify yourself or do some processing immediately before the insert/modify.
OnAfterApplyandSaveDataThis event fires after the dimension set is applied and the record has been insert/modified. You can use this event to handle processing that needs to happen after the record is updated.
OnPostImportDataThis event fires after all records have been imported, but before the transaction has been committed.

Sales and Purchase Document Events

EventPurpose
OnBeforeAssignDocumentNoUse this event if you want to change how the document number gets assigned
OnAfterFieldCannotBeEditedThe app has built in rules to prevent certain fields from being updated directly. Use this event to restrict more fields. Fields that are restricted do not show in lookups
OnBeforeFieldCannotBeEditedSimilar to the previous event, however this event allows you to override the built in rules
OnAfterInsertNewSalesHeaderBeforeModifyAllows you to implement custom logic directly after all fields are transferred to a sales header before it gets modified.
OnAfterInsertNewPurchaseHeaderBeforeModifyAllows you to implement custom logic directly after all fields are transferred to a purchase header before it gets modified.
OnAfterImportSalesOrPurchaseLineAllows you to implement custom logic directly after all fields are transferred to a sales/purchase line and the line has been inserted.

Physical Inventory Journal Events

EventPurpose
OnClearPhysicalQuantityOnJournalBeforeUpdatePhysQtyYou can use this event to handle specific logic required when resetting the physical quantity on the Physical Inventory Journal before importing the recording.

Adding a New Import Type

Adding a new import type is very straightforward.

  • Extend the "ImportType_EI_TSL" Enum to add your Import Type
  • Subscribe to the OnGetCustomTableID

Custom Values

Custom values get used to map values required for processing when these values do not relate to a specific field.

ObjectEventDescription
Table CustomValue_EI_TSLOnAfterRefreshCustomValueActions Use this event to publish a new custom action. When you subscribe to this you can use the AddActionToNameValueBuffer function described below
Table CustomValue_EI_TSLOnAfterCheckValueTypeSelectedForProcessingAction
Table CustomValue_EI_TSLOnAfterGetDefaultValueType Allows you to specify the default value type for your custom processing action.
Table CustomValue_EI_TSLOnBeforeCopyAccountDimensionValuesToSalesLine You can use this event to change how the information gets copied from the account dimension mapping. You can only make changes to the NewSalesLine parameter.
Table CustomValue_EI_TSLOnAfterGetDefaultRoundingPrecision Allows you to specify the rounding precision on the custom value. Only use this if the default rounding precision will only work for you.
Table CustomValue_EI_TSLOnBeforeCreateDefaultCustomValues You can use this event if you want to create default custom values.
Table CustomValue_EI_TSLOnAfterGetDefaultRoundingPrecision You can use this event if you want to update a default custom value before it gets inserted.
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnBeforeAddNewSalesUsingValueAsAmount Allows you to add custom logic to the AddNewSalesPurchaseLineUsingValueAsAmount processing action for sales lines before the processing action gets executed.
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnBeforeAddNewPurchaseLineUsingValueAsAmountAs above, but applies to purchase documents
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnAfterAddNewSalesUsingValueAsAmountBeforeInsertNewSalesLineAllows you to add custom logic to the AddNewSalesPurchaseLineUsingValueAsAmount processing action for sales lines after the processing action gets executed.
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnAfterAddNewPurchaseLineUsingValueAsAmountBeforeInsertNewPurchaseLineAs above, but applies to purchase documents.
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnCustomSalesPurchaseValueProcessActionAllows you to implement your own custom processing action.
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnAfterSales_InitQuantitiesOnCopyLineForCustomValuethis event fires when the new sales line is copied from the existing purchase line.
Codeunit "Sales/Purch. Doc. Imp._EI_TSL"OnAfterPurch_InitQuantitiesOnCopyLineForCustomValuethis event fires when the new purchase line is copied from the existing purchase line.

Helpers for Custom Values

ObjectFunction
Table CustomValue_EI_TSLAddActionToNameValueBuffer
Table CustomValue_EI_TSLGetActionText Used to retrieve the caption or extended description for the given action. You can use this if you want to display a message to the user and provide a user friendly reference to the action.
Table CustomValue_EI_TSLCopyAccountDimensionValuesToSalesLine Use this function if you want your custom processing action to copy values from the account dimension mapping

Example: Adding a new Processing Action

Publishing the Processing Action

The code snippet below shows you how to add a new processing action that you can select in the custom values list. The example adds a processing action that you can use on a sales import template.

var
ProcessingActionIDLbl: Label 'ProcessingActionCode_TSL', Locked = true, MaxLength = 250;
ProcessingActionCaptionTxt: Label 'Add a new sales line or purchase line if the value is not zero', MaxLength = 250;
ExtendedDescriptionTxt: Label 'Specify details about what this action does and how to use it.', MaxLength = 2048;

[EventSubscriber(ObjectType::Table, Database::CustomValue_EI_TSL, 'OnAfterRefreshCustomValueActions', '', false, false)]
local procedure OnAfterRefreshCustomValueActions(ImportType: Enum ImportType_EI_TSL; var TempNameValueBuffer: Record "Name/Value Buffer");
begin
case ImportType of
ImportType::Sales:
AddSalesProcessingActions(TempNameValueBuffer);
end;

end;

local procedure AddSalesProcessingActions(var TempNameValueBuffer: Record "Name/Value Buffer")
var
CustomValue: Record CustomValue_EI_TSL;
begin
CustomValue.AddActionToNameValueBuffer(
TempNameValueBuffer, ProcessingActionIDLbl, ProcessingActionCaptionTxt,
ExtendedDescriptionTxt);
end;

Implementing the Processing Action

Here is an example of how you can implement the processing logic on the sales import template.

    var
ProcessingActionIDLbl: Label 'ProcessingActionCode_TSL', Locked = true, MaxLength = 250;
ProcessingActionCaptionTxt: Label 'Add a new sales line or purchase line if the value is not zero', MaxLength = 250;
ExtendedDescriptionTxt: Label 'Specify details about what this action does and how to use it.', MaxLength = 2048;

[EventSubscriber(ObjectType::Codeunit, Codeunit::"Excel Import Management_EI_TSL", 'OnAfterApplyandSaveData', '', false, false)]
local procedure OnAfterApplyandSaveData(var RecRef: RecordRef; var ValuesImported: Record "Exl. Imp Field Buffer_EI_TSL"; var TableID: Integer; var PropertyBuf2: Record "Name/Value Buffer"; var RowNo: Integer; var FirstRowNo: Integer; var TmpDimSetEntry: Record "Dimension Set Entry"; ImportTypeAsEnum: Enum ImportType_EI_TSL);
begin
if ImportTypeAsEnum = ImportTypeAsEnum::Sales then
SalesOnAfterApplyandSaveData(RecRef, ValuesImported);
end;

local procedure SalesOnAfterApplyandSaveData(var RecordRef: RecordRef; var ExlImpFieldBuffer: Record "Exl. Imp Field Buffer_EI_TSL");
var
SalesLine: Record "Sales Line";
begin
RecordRef.SetTable(SalesLine);
ProcessSalesLineCustomValueActions(SalesLine, ExlImpFieldBuffer);
end;

local procedure ProcessSalesLineCustomValueActions(ImportedSalesLine: Record "Sales Line"; var ExlImpFieldBuffer: Record "Exl. Imp Field Buffer_EI_TSL")
var
TempCustValueExlImpFieldBuffer: Record "Exl. Imp Field Buffer_EI_TSL" temporary;
CustomValue: Record CustomValue_EI_TSL;
begin
TempCustValueExlImpFieldBuffer.FilterOnCustomValuesUsingImportedFields(ExlImpFieldBuffer);
if not TempCustValueExlImpFieldBuffer.FindSet() then
exit;
repeat
case CustomValue."Processing Action" of
ProcessingActionIDLbl:
HandleMyNewProcessingAction(ImportedSalesLine, TempCustValueExlImpFieldBuffer, CustomValue);
end;
until TempCustValueExlImpFieldBuffer.Next() = 0;
end;

local procedure HandleMyNewProcessingAction(ImportedSalesLine: Record "Sales Line"; ExlImpFieldBuffer: Record "Exl. Imp Field Buffer_EI_TSL"; var CustomValue: Record CustomValue_EI_TSL)
var
NewSalesLine: Record "Sales Line";
ExcelImportManagement: Codeunit "Excel Import Management_EI_TSL";
RecordRef: RecordRef;
FieldRef: FieldRef;
TotalLineAmountExcl: Decimal;
begin
if ExlImpFieldBuffer."Cell Value as Text" = '' then
exit;
// convert the text to an amount
RecordRef.Open(Database::"Sales Line");
// the next 4 lines get the decimal value. It uses the functions in Excel Importer because there are various transformations that can
// be done using the Excel Mapping. It is recommended you use this method instead of handling it yourself
FieldRef := RecordRef.Field(NewSalesLine.FieldNo("Unit Price")); // this can be any decimal field as we only want to get the decimal value
ExlImpFieldBuffer."Validate Field Method" := ExlImpFieldBuffer."Validate Field Method"::Never; // important - we just want the value
ExcelImportManagement.EvaluateFieldRef(FieldRef, ExlImpFieldBuffer);
TotalLineAmountExcl := FieldRef.Value();
if TotalLineAmountExcl = 0 then
exit;
NewSalesLine := ImportedSalesLine;
// do something like add a new line or make changes to the line that was imported
end;