Effortless Data Import Across Multiple Companies in Business Central: A Seamless Approach Using Excel by Vishal Mittal

Have you ever considered the possibility of seamlessly inserting or modifying data across multiple companies using just one Excel sheet, and all of this without any configurations in Business Central?
Thinking along the same lines, I created a handy toolkit to make it easy.

Let's start!

We'll need an Excel sheet containing the necessary data along with a few additional basic details. Below is the design of Excel Sheet:
Note: The primary key fields should be incorporated into the initial data columns, following the same sequence as defined in the table.

Let us consider a scenario where a table comprises three fields in its primary key. In the event of updating the value '3' in the B2 column, denoted as the Primary Key Count (PK Count), the system will interpret the initial three columns, commencing from the C column, as the primary key values for that particular table.


How does my solution work?

Prerequisite - Download the objects from the below link or get the complete package with demo objects at the blog's end.

1. Go to Page - "Import Records from Excel".









2. Keep the Excel sheet prepared with your data. Select "Import Records from Excel" action.











After successful import, data will be populated in on this page.











3. Once data is successfully imported to "Import Records from Excel" table. You are ready to select "Process Data" action to import this data to the corresponding tables in the given company.











Demo:

I have created a new table named it as "Dummy Table" with most of the supported Datatypes in this functionality. (Please find all the demo objects in the package attached in end of this blog.)










I will create a demo video on this in next few days.

Note - Include only the fields in the Excel file that need to be uploaded in BC, incorporating the primary key fields. No columns should be created for fields where no changes in data are required.
This toolkit currently has a restriction of 20 fields for Non-Primary Key in which data can be inserted/modified from excel. If required more than that feel free to add new fields in the code.

Limitation - System doesn't call any trigger while inserting the data.

I have utilized Erik Hougaard's Excel Tools Codeunit in this feature. Special Thanks to him.

You can download complete AL Project of this feature along with Demo Objects and sample excel file from below link - 


Thank you for reading. If you find yourself frequently engaging in such tasks, adopting this feature could be a time-saving game-changer for you. 

Your feedback and suggestions are always welcomed.

Comments

Post a Comment

Popular posts from this blog

How to flow a new field from Purchase Header to G/L Entry during Purchase Order Processing. ?

How to flow a new field from Purchase Header to G/L Entry during Purchase Order Processing. ? Lets move to the solution directly. Below is the list of tables where we need to add new field. 1. Table 38 - Purchase Header 2. Table 81 - Gen. Journal Line 3. Table 17 - G/L Entry Note: It is not necessary to create fields with the same id in above tables.  Below are the objects and their functions on which code modification is required.  1. Table 81 -  Gen. Journal Line Function :  CopyFromPurchHeader (Assignment of new field from Purchase Header to Gen Jnl Line is required) 2. Table 17 -  G/L Entry Function :  CopyFromGenJnlLine  (Assignment of new field from Gen Jnl Line to G/L Entry is required) Code Change suggested after function name is enough to pass a field from Purchase Header to G/L Entry. If you are using business central than you can subscribe onAfter events of the functions required for code modification.   
Read more