All notable changes to this project will be documented in this file.
- File import wizard
- We enhanced the Customer Remittance pre-import validation to check for more types of data errors.
- Excel Importer can now import values into the Statistical Account Journal. You can read more about this here.
- Resolved an issue with the text file import where a file containing header and detail records always started importing from the second row - even if there were no column headings in the file.
- Resolved an issue on the Customer Remittance Import where an error would be displayed if you still needed to configure a template.
- It is now possible to import Requisition Lines into the Requisition Worksheet. You can read more about this here.
- The sample data that you can download from the Excel Importer Setup Wizard has been changed to a zip file that contains an Excel file and two text files as the sample templates include text file examples.
- We have added a new video available from within Business Central to show you how to import CSV files. To view the video, search for Product Videos from tell me. Here is a link to the same video.
- You can now import text files using Excel Importer. The app supports variable-length files (e.g. CSV) and fixed-length files.
- The app now supports having different record types in one file. You can specify the criteria for identifying headers, footers or detail lines.
- We enhanced the Account/Dimension to map up to eight dimensions directly. Previously, it was only possible to directly map Global Dimensions 1 and 2.
- You can now enter either the field caption or the field id when defining mapping. Previously, you had to enter the field number or select it from the list. If the text you specify resolves to multiple fields, the app displays the possible fields so you can see the correct field.
- It is now possible to import customers and vendors using Excel Importer. You can read more about this here.
- Updated the online help for Importing Customer Remittance Advices.
- We have added a custom processing action to set the balancing account to the control account for the customer, vendor, bank, employee or fixed asset. This feature would be used only when importing opening balances for a new company. You can read more about this here. We have also included an example here.
- New event
OnCustomJournalProcessCustomValueActionto add custom processing actions when importing journals.
- The Fixed Asset import now ensures that the FA Posting Group is specified against the FA Depreciation Book. The app will default this value from the FA Subclass on the asset, but you can override this if you include the column in the file.
- We have added the ability to import text into blob fields. An example of where this can be used is to import text into the work description on Sales Documents. Here's an example of how to do this mapping.
- Added a new event
OnBeforeValidateSettingValuesForSettingType. This event will replaces
OnBeforeValidateSettingValueForSettingTypebecause additional parameters were required.
OnBeforeValidateSettingValueForSettingTypewill be removed in a future version.
- Errors relating to invalid dimension values are now part of the collectable errors we introduced in 220.127.116.11.
- We have added a common setting to all templates that import dimension values to enable the import to create dimension values if they do not exist in Business Central. You can read about this here.
- Added filename as a parameter to the
OnBeforeModifyOrInsertDataevents so that you can keep track of the files you have imported.
- Decimal numbers got parsed incorrectly if you had set your regional settings to German.
- Resolved an error in the Physical Inventory Journal import where tracking entries would get created with incorrect quantities when the lot was not present in a file.
- Account/Dimension Mapping errors are now part of the collectable errors we introduced in 18.104.22.168.
- Row Filters now get evaluated before applying mapping or transformation and after applying mapping or transformation. The key driver for this change was to prevent mapping errors on lines that the app would otherwise skip.
- Added a new event called
OnClearPhysicalQuantityOnJournalBeforeUpdatePhysQty, which you can use to handle specific logic required when resetting the physical quantity on the Physical Inventory Journal before importing the recording.
- It is now possible to import a file into the Physical Inventory Journal to update the journal with the actual quantities. You can read more about this here.
- We have changed the error handling in the app to support collectable errors. This means that if you import a file with errors, you will receive a list of errors instead of stopping when the first error occurs. We have provided information about this here. Note: all new templates now get a common setting to enable/disable this feature. The feature gets enabled by default.
- We have added a common setting to all templates that let you decide whether to perform validation before importing the data or only while importing the data. You can read about this here.
- Added new events
OnAfterCheckImportedValuesBeforeUpdatethat allow you to extend or change the data validation that gets done before importing data.
GetTextArrayhelper functions to Temporary Storage which you can use when extending Excel Importer.
- Added a sample Item import template
- All templates have been updated to support the settings fact box fully. The Settings lines are no longer visible on the Excel Mapping Template Card because you can change all settings on the settings fact box. Read here for information on how to do this.
- When selecting fields on the Excel Mapping Template Card, the import sequences get defaulted in the sequence of the columns instead of the sequence of the field numbers.
- Removed obsolete events and functions.
- You can now use Excel Importer to import Job Journals. Read here for more information.
- Updated the sample templates for Business Central 2023 Wave 1 (V22)
- We have updated our Item Journal import to support the new feature in Business Central 2023 Wave 1, where you can update the item tracking details directly in the Item Journal. The app supports batches where you want to edit the tracking details in the journal and batches where you do not want to do this.
- 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 [22.214.171.124].
- 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.