3. Chuẩn bị mẫu nhập liệu - DATAVIEW

• Prepare an import template:

Click on the + icon to add a new record, let name the code for this dataset as ILA (Import Ledger A Transaction)

• Select a default connection from the connection lookup window.

• Click on the Add Table button

• Select table laatransactions

• Click on the Save button

Now, click on the Run button to run this dataset. It would show a dataset with all columns of the laatransactions table.

Click on the Excel button to download it. You should have an Excel template file looks like this:

Change the name of column A from id to posting, like this:

There is an easier way to prepare the import template. Go and download it directly from this link: ILA

Now, let’s add a new sheet, named DATAVIEW, to the above normalized data file. Make sure DATAVIEW is the first sheet of the Excel data file. The file should have 4 sheets like this:

The formlars starting from row 2 downward are as follows:

• posting: A2 ="B"

• ledger: B2 ="a"

• journalno: C2 =LG!M8

• journalline: D2 =IF(C2 <>C1,1,D1+1)

• journaltype: E2 =LG!L8

• transactionref: G2 =LG!J8

• period: H2 =LG!K8

• transactiondate: I2 =CONCATENATE(YEAR(LG!C8),"-",MONTH(LG!C8),"-",DAY(LG!C8))

• duedate: J2 =IF(BD2>0,BD2,"")

• description: K2 =LG!D8

• accountcode: L2 =LG!S8

• currencycode: R2 = "VND"

• currencyrate: S2 = 1

• operator: T2 = "/"

• amount: V2 =LG!O8

• d_c: W2 =LG!N8

• addresscode: AP2 =LG!R8

• invoiceno: BC2 =LG!V8

• invoicedate: BD2 =IF(LG!Y8 = "", "",CONCATENATE(YEAR(LG!Y8),"-",MONTH(LG!Y8),"-",DAY(LG!Y8)))

• entrydate: CE2 =CONCATENATE(YEAR(LG!A8),"-",MONTH(LG!A8),"-",DAY(LG!A8))

Make sure you select the entire row from column B2 to CF2 and pull the formulas downward for all transactions of the LG sheet.

Now, we need to define our data range. Define a named range data with stretch over exactly our data. In our sample data, it stretches from cell A1 to cell CF1326.

For your convenience, here is the url to download the finished file: Sample Data - SmartPro

• Upload and test your data.

Go to Kế Toán Sổ Cái > Journal Import and click on the import icon

• Browse to and select your file.

• Click on the Preview 500 to take a quick look at up to the first 500 records.

• DON'T click on the button. We need to prepare the Thiết Lập Chungs before they can be imported.