All notable changes to this project will be documented in this file.
The format is based on Keep a Changelog, and this project adheres to Semantic Versioning.
- We have introduced additional processing actions on custom values that allow you to use one or more columns from your Excel file to create comment lines either above or below the imported line on sales and purchase documents. You can read more about custom values here or check how it applies to sales and purchase documents.
- New events:
OnBeforeCreateDefaultCustomValues- You can use this event if you want to create default custom values.
OnAfterAddDefaultCustomValueBeforeInsert- You can use this event if you want to update a default custom value before it gets inserted.
- Resolved an issue on the sales order import for items configured to assemble to order where the program would not create the reservation entries correctly.
- New events
OnAfterAssignFieldValue- You can use this event to perform special processing after updating a specific field during import.
OnAfterPurch_InitQuantitiesOnCopyLineForCustomValue- use these events if you want to update a new line created by a custom value
- Resolved an issue on the Transfer Order import in relation to the item tracking lines.
- Added translations for Italian, Spanish and Danish
- Resolved an issue where you could not open the Excel Mapping Template page if the app did not recognise a setting.
- Resolved an issue with the Production Order Import where the Refresh Production Order setting was not respected during the import.
- You can now specify a Row Filter on all Excel Mapping Templates. The Row Filter allows you to specify when the app should import a row or when the app should skip a row. You can read more about this feature here.
- Added the Import from Excel action the the Job G/L Journal.
- Additional variables added to the Event
- An error could occur when updating the settings for Production Import Mapping Templates where the error would error and say the setting type was invalid. This same error could also get raised during an upgrade.
- You can now download a sample configuration package in German. The Excel Importer Setup Wizard will default the language based on your user settings, but you can change it if you like.
- Resolved an issue with the Excel Importer Setup Wizard where errors would occur while applying the configuration package. Due to this issue, it was not possible to finish the process. The errors occurred due to validation added to Business Central, which required changes to the configuration package. We have also changed how the configuration package gets applied to handle future unforeseen issues.
- Resolved an issue with the Fixed Asset Import that would occur when the imported fixed asset number was empty but specified in a template. The app will now assign a fixed asset number as expected when the number provided is not specified.
- Upgraded the settings for Fixed Asset Import to enums. You can now change the settings in the settings fact box.
- Upgraded the settings for General Journal to enums. You can now change the settings in the settings fact box.
Page 70253933 "ExcelImportSettings_EI_TSL"is a sub page on the Excel Mapping Template Card, which will be removed in a future version. The Settings Fact box replaces this page.
- Resolved an issue on the Price List Import where the Amount Type (Defines) was ignored from the file or the constants specified in the template. This is only applicable to Business Central 20.1 and greater.
- When you specify a constant value on the Excel Field Mapping, then app now updates the Default Values field to Use Constant if Empty and raises a notification instead of displaying an error.
- Changed the available fields on the Price List Import to exclude fields that are duplicated in the standard tables.
- Resolved an issue related to Custom Values. If the custom value had dimensions specified and it was used with a sales line that did not already have default dimensions, then the app would not copy the custom value dimensions to the additional sales line.
- New Events
OnBeforeImportExcelData- allows you to perform custom logic after the Excel File is loaded but before the processing starts.
OnAfterImportSalesOrPurchaseLine- allows you to perform processing after a sales or purchase line is inserted.
- Sales and Purchase Document Templates created in older versions are now updated with the default settings values if they were not previously specified. This is required because the new features that validate settings combinations now expect all settings values to be specified (even if they are default)
- Resolved an issue with the upgrade of template settings that caused existing templates not to work correctly. This impacted sales and purchase document templates.
OnBeforeGetSettingsValueCSVTextEvent allows to to specify allowed values for custom settings when you extend Excel Importer
AddToSettingsText, `` function is now available to extensions if you add custom settings
- Transfer Order Import: Excel Importer now allows you to import new transfer orders. You can find more details about this new import type here.
- Assembly Order Import: Excel Importer now allows you to import new and update existing Assembly documents. You can find more details about this new import type here.
OnInsertCustomTemplateToBufferevent - you can optionally subscribe to this event if you want to specify additional details about a custom import type.
OnBeforeValidateSettingValueForSettingTypeevent - you can optionally subscribe to this event if you want to validate a setting value selected against a setting type.
- Updated sample configuration package and sample data to include Transfer Orders and Assembly Orders
OnBeforeLookupTemplateNameis no longer required because we now use the Import Type enum to form the template name.
OnEnterConstValueForRelatedFieldis replaced by the new event OnBeforeValidateSettingValueForSettingType.
- Various obsolete public functions and events marked with the tag
18.xhave now been removed.
- Sales and Purchase Documents
- When changing the document type on a document with a negative total, the sign of the quantity and the unit amount would get reversed under certain conditions.
- Using the setting Replace Document No. would cause the imported document number to use the same posting number series as the document number series on sales invoices. Note that this issue resulted from changes made in [184.108.40.206].
- Added a new setting called Negative Document Totals for sales and purchase document imports. This allows you to specify how to handle documents when the total value is negative and allows you to Ignore, Skip, Cancel and Change Document Type.
OnFinalizeImportBeforeCommitAndClearTemporaryStorageevent - use this to construct a user feedback message before temporary storage gets cleared
OnFinalizeImportAfterCommitAndClearTemporaryStorageevent - use this if you want to handle the display of the feedback message. Temporary Storage is cleared at this stage and the changes are committed.
OnBeforeGetCommaSeparatedDimensionFieldIDTextevent - used to specify the field ids that contain global/shortcut dimensions so that Excel Importer correctly handles the dimension fields on custom templates.
- Added support to use a number from the number series when importing Fixed Assets
OnAfterValidateDimensionCodeare deprecated to simplify handing dimension fields for custom templates - replaced by
OnBeforeShowingSuccessMsgfor sales and purchase documents gets replaced
OnFinalizeImportBeforeCommitAndClearTemporaryStorage, which can be used for all templates.
- Assisted Setup: Excel Importer can get configured from Assisted Setup, and we have created a setup guide to assist assist with permission assignment and sample templates.
- Guided Tours: We have implemented guided tours on several pages to assist with learning
- Excel Importer Product Videos in Business Central: We have added Excel Importer product videos to Business Central. Currently, there are two videos. We intend to create more in the future.
- Price List Import: Excel Importer now supports the new Business Central extended pricing. The import significantly reduces the effort required to load sales and purchase prices.
- Production Order Import: It is now possible to import new or update existing production orders using Excel Importer. More details...
- Physical Inventory Orders: Excel Importer provides a more flexible way of importing inventory recordings than the standard CSV import/export.
- Output and Consumption Journals: Some companies initially record shopfloor activity in a different system to Business Central but want to process the inventory transactions in Business Central. If this data is available in Excel, you can now import this using Excel Importer. Both journals use the existing Item Journal Import.
- Customer Remittance Import - Transferring Customer Remittance Lines: This new feature allows you to move payments to different customers when one entity pays on behalf of other entities. The new feature is part of the existing Customer Remittance Import functionality. More details...
- Custom Values: Custom Values allow you to map columns from Excel to placeholders when you cannot map the values directly to a field in Business Central. We only have one standard processing action available on Sales and Purchase imports in this release. Your partner can extend this if you have specific requirements.
- Temporary Storage: This is a single instance codeunit that is initialised at the start of each import as well as at the end. The codeunit supports passing records and saving them to temporary record reference instances. Up to 10 instances are supported for custom templates. You can also use the temporary storage to store up to 10 text lists. The benefit of this change is to reduce the amount of code you need to write when extending Excel Importer.
You can use this to keep track of records being imported so that you can do things like:
- Release all imported sales orders
- Check that there are no duplicate lines being imported (e.g. on the price import this is used)
- Initialise values based on the context of where the import is run (e.g. on the Price list import, the page that runs the import defaults Price Type and Source Group)
- Settings Helper: Introduced a settings helper which reduce the amount of code required to create custom templates when extending Excel Importer. It's used to:
- Provide a lookup for settings values. The lookup functionality has events that allow the valid values to be shown in a string menu and also allow entirely custom lookup functionality to be provided.
- Validate partial entry of a settings value
- Staging Record Reference: A staging record reference is now available on some events. The staging record is populated from the imported fields without running validation. Where this is available, you can use the values in the fields to determine how to process the date being imported. Previously you needed to find the values in the imported field buffer. This record reference is available on new events and is also available on the OnBeforeFindExistingRecord event. Note that if you make changes to this record reference they will be ignored.
OnBeforeExcludeExcelBufferRowFromImportevent: you can subscribe to this event to exclude importing data based on date in the Excel Row. For example your spreadsheet may have sub total lines that need to be excluded.
OnBeforeSkipImportOfRecordevent: you can subscribe to this event if you want to skip the import of a row based on the values of fields. This event includes the staging record reference.
- Enums instead of hard coded text values: Introduced extensible enums for settings and import types. This replaces the previous method of having hardcoded values. It is backwards compatible with the old method but the old methods will be deprecated.
OnBeforeFindExistingRecordevent: This event is used if you want to add values to the imported fields before finding a record. Additional parameters have been added to this event to simplify the logic when doing this.
- Enums and Options: The logic to evaluate an option field has been enhanced to better support scenarios where only part of the option/enum text value are provided.
- Sales and Purchase Document Imports: In some cases it was not possible to select a purchase or sales header field on a template even though it was available for selection. The issue that was causing this has been resolved.
- Item Imports:
- An error would occur if you specified the base unit of measure when importing items. This issue is resolved.
- You can now apply templates to items when importing. This relies on the new templates feature being enabled.
- Added an Import from Excel action to Intercompany General Journal
- Updated permission sets to address permission issues that users experienced with the remittance advice import.
- Warehouse Journal Import: If you have a location set up for directed picks and putaways, you can now use Excel Importer to import Warehouse Item Journals and Warehouse Reclassification Journals.
- Import purchase quotes, sales quotes, sales return orders, purchase return orders: Added an Import from Excel action to the lists of the above sales and purchase document types.
- Import Dimension Values: Added an Import from Excel action to the Dimensions page.
- Permission Sets: The permission sets for Excel Importer are defined using the new permission set objects. The permission sets got renamed during this process. When the app gets upgraded, the permission assignments to user groups and users are updated to reference the new names. The actual permissions granted have not changed.
- Option/Enum values get tested when entered as a constant value on a related field
- Resolved an issue with dimension mapping that occurred when a dimension already defaulted
- Journal Imports to call the SetupNewLine functions for General Journal Line, Item Journal Line, and FA Journal Line tables to support Business Central defaults such as numbering and default reason codes. By introducing this change, there is a possibility that some fields may default from the batch which was previously not defaulting. Where this causes an issue, the fields can get added to the template with constant values to assign different values.
- Excel sheet name got truncated to 30 characters. The Excel maximum of 31 characters is now supported.
- Added a feature that allows you to import a customer remittance advice and perform automatic matching to open customer ledger entries.