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.

Important

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.

Automating Files Imports

The app now includes an API that allows you to automate the process of importing files using the Excel Importer. You can read more about this here.

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 - Event	Purpose
`table "Exl. Mapping Template_EI_TSL" - OnGetCustomTableID`	Specifies the table associated with the import type.
`table "Exl. Mapping Template_EI_TSL" - OnInsertCustomTemplateToBuffer`	you can optionally subscribe to this event if you want to specify additional details about a custom import type
`table "Exl. Mapping Template_EI_TSL" - OnCheckIfFieldFromCustomEntityCanBeEdited`	Use 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" - OnBeforeValidateSettingValuesForSettingType`	Use this event to check if the setting type and value specified is valid for the import type.
`table "Exl. Mapping Template_EI_TSL" - OnBeforeGetRelatedFieldsAndSettingsForCustomEntity`	Use 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" - OnCustomLookupSettingValue`	Use this event to specify the available setting values for the setting type in relation to the import type.
`table "Exl. Mapping Template_EI_TSL" - OnBeforeGetCommaSeparatedDimensionFieldIDText`	Use 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.

Event	Purpose
`OnAfterCopyExcelBufferToExlImpBuffer`	This 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.
`OnBeforeCheckImportedValuesBeforeUpdate`	Use 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.
`OnAfterCheckImportedValuesBeforeUpdate`	Use this event to add additional validation before importing data.
`OnBeforeImportExcelData`	Use this event to perform custom logic after the Excel File is loaded but before the processing starts
`OnBeforeExcludeExcelBufferRowFromImport`	Use 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
`OnBeforeFindExistingRecord`	This 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.
`OnBeforeSkipImportOfRecord`	Use 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.
`OnFindExistingRecord`	This event is fire if Excel Importer finds an existing record with the Record ID as the record being imported
`OnInsertImportedData`	This 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
`OnBeforeApplyData`	This event fires before the import values are copied to the record.
`OnAfterAssignFieldValue`	This 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.
`OnBeforeModifyOrInsertData`	This 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.
`OnAfterApplyandSaveData`	This 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.
`OnPostImportData`	This event fires after all records have been imported, but before the transaction has been committed.

Sales and Purchase Document Events

Event	Purpose
`OnBeforeAssignDocumentNo`	Use this event if you want to change how the document number gets assigned
`OnAfterFieldCannotBeEdited`	The 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
`OnBeforeFieldCannotBeEdited`	Similar to the previous event, however this event allows you to override the built in rules
`OnAfterInsertNewSalesHeaderBeforeModify`	Allows you to implement custom logic directly after all fields are transferred to a sales header before it gets modified.
`OnAfterInsertNewPurchaseHeaderBeforeModify`	Allows you to implement custom logic directly after all fields are transferred to a purchase header before it gets modified.
`OnAfterImportSalesOrPurchaseLine`	Allows 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

Event	Purpose
`OnClearPhysicalQuantityOnJournalBeforeUpdatePhysQty`	You 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.

Object	Event	Description
`Table CustomValue_EI_TSL`	`OnAfterRefreshCustomValueActions` 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_TSL`	`OnAfterCheckValueTypeSelectedForProcessingAction`
`Table CustomValue_EI_TSL`	`OnAfterGetDefaultValueType` Allows you to specify the default value type for your custom processing action.
`Table CustomValue_EI_TSL`	`OnBeforeCopyAccountDimensionValuesToSalesLine` 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_TSL`	`OnAfterGetDefaultRoundingPrecision` 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_TSL`	`OnBeforeCreateDefaultCustomValues` You can use this event if you want to create default custom values.
`Table CustomValue_EI_TSL`	`OnAfterGetDefaultRoundingPrecision` 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"`	`OnBeforeAddNewPurchaseLineUsingValueAsAmount`	As above, but applies to purchase documents
`Codeunit "Sales/Purch. Doc. Imp._EI_TSL"`	`OnAfterAddNewSalesUsingValueAsAmountBeforeInsertNewSalesLine`	Allows 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"`	`OnAfterAddNewPurchaseLineUsingValueAsAmountBeforeInsertNewPurchaseLine`	As above, but applies to purchase documents.
`Codeunit "Sales/Purch. Doc. Imp._EI_TSL"`	`OnCustomSalesPurchaseValueProcessAction`	Allows you to implement your own custom processing action.
`Codeunit "Sales/Purch. Doc. Imp._EI_TSL"`	`OnAfterSales_InitQuantitiesOnCopyLineForCustomValue`	this event fires when the new sales line is copied from the existing purchase line.
`Codeunit "Sales/Purch. Doc. Imp._EI_TSL"`	`OnAfterPurch_InitQuantitiesOnCopyLineForCustomValue`	this event fires when the new purchase line is copied from the existing purchase line.

Helpers for Custom Values

Object	Function
`Table CustomValue_EI_TSL`	AddActionToNameValueBuffer
`Table CustomValue_EI_TSL`	`GetActionText` 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_TSL`	`CopyAccountDimensionValuesToSalesLine` 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;

Extending Excel Importer

Need help or additional events?​

Automating Files Imports​

Adding a new Import Type​

Example: Adding a new Import Type (Basic Example)​

Adding New Import Types or Changing Existing Imports​

Events related to Import Template Configuration​

Example - Handling Global/Shortcut Dimensions​

Events related to Import Processing​

Sales and Purchase Document Events​

Physical Inventory Journal Events​

Adding a New Import Type​

Custom Values​

Events related to Custom Values​

Helpers for Custom Values​

Example: Adding a new Processing Action​

Publishing the Processing Action​

Implementing the Processing Action​