Open entries are transactions that haven't reached their final status yet, and are not included in the Open documents section. You can only post open entries when the corresponding master data is already imported. In a common scenario, the open entries include:
- Customer entries: It means all the money each customer owes on the day of the migration; basically, accounts receivable
- Vendor entries: It means all the money the company owes to each of their vendors on the day of the migration; basically, accounts payable
- Bank entries: It means the money the company has in each bank account
- Item entries: It means the stock the company has in each location on the day of the migration
- Accounting balances: It means the balance that each account has on the day of the migration
- Fixed asset entries: It means all the company's assets with their initial cost and the amount depreciated, as on the day of the migration
All these entries must be posted through their corresponding journal and must use a specific posting date. The posting date must be at least one day prior to the migration date. For instance, if you choose to go live on January 1st, you should use December 31st as the posting date for all the open entries. This way, we will start off with a fresh year with the new data and it reflects when you actually start off with a new system. The easiest way to migrate the open entries is to use the configuration worksheet described earlier in this chapter.
Customer entries refer to all the money that each customer owes on the day of the migration. We need to create at least one customer entry to summarize all the money that the customer owes. If the company wants to control the due dates from Dynamics NAV for the open entries, we need to create at least one summarized entry for each due date, or we can create one entry for each pending invoice.
The minimum information needed is as follows:
- Posting date: Use one day before the migration day for all the entries.
- Account type: Use the
Customeroption for all the entries. - Account number: Use the customer code given to the customer.
- Document number: You can use the invoice number extracted from the old system, or you can give it a document number such as
OPENING. - Description: Give the entry a description. You can use the invoice description extracted from the old system, or you can give a description such as
Opening Entriesto all the entries. - Currency: Leave it blank if the amounts are in local currency. Write the currency code otherwise. Keep in mind that if a currency code is filled, the amounts must be in that currency.
- Amount: It's the money the customer owes. Write a negative amount if it's the company which owes money to the customer, either because of credit memos or advance payments.
Other information that can be provided are as follows:
- Document date: In case you create one entry for each pending invoice, the document date corresponds to the date of the original invoice
- Due date: In case you create one entry for each pending invoice, the due date corresponds to the date when the customer has to pay his debt
- Payment method: In case you create one entry for each pending invoice, the payment method corresponds to how the debt will be paid
Actually, you can provide information for any field included in the Gen. Journal Line table. However, for migration purposes, the previously listed fields are enough.
Let's see with an example how to migrate the customer entries. We'll just take the minimum information needed. The following steps are involved while migrating a customer entry:
- Provide an Excel template; we'll use RapidStart Services. The data has to be imported into the General Journal to create the customer entries when posted.
- Create an Excel template for the table
81and include the fieldsAccount Type,Account No.,Posting Date,Document No.,Description,Currency Code, andAmount. Refer to the Create the migration structure section in this chapter for more information on this step. - Ask someone in the company to fill in the template, extracting data from the old system using the extraction tools available. You are a Dynamics NAV expert, and you may not know how the data is stored in the old system, so don't try to do it yourself.
Tip
Remember that your job is to import data into Dynamics NAV the way Dynamics NAV expects it. It is the company's responsibility to assure that the data is consistent and of good quality.
As a Dynamics NAV expert, you are responsible for filling in the fields corresponding to the primary key of the table. In this case, these are the
Journal Template Name,Journal Batch Name, andLine No.fields. - Once the template is completely filled, it's time to import it to Dynamics NAV and apply it. Refer to the Migrate your data section in this chapter for more information on this step.
- Open the General Journal. The data is almost ready to be posted. Once posted, Dynamics NAV won't allow you to delete or modify the created entries, so take your time before posting. Check, check, and check your work. Once you are done, check it again. Also ask the user who provided you the information to check it. Use this checklist:
Question
Answer
Does the Total Balance shown in the Journal correspond with the total Accounts Receivable?
Does your Accounts Receivable match that of the General Ledger A/R account?
Does each Customer owe the Amount shown in its Journal line?
Once the lines are posted, new customer ledger entries will be created. G/L entries will also be created. When a new
Gen. Journal Linetable is created, Dynamics NAV copies the posting group from the customer card to theGen. Journal Linetable. The receivables account found in each posting group is used to determine which account must be used to post the amount each customer owes. Now, add another question to your checklist:Question
Answer
Group all the lines by posting group. Get the receivables account for each posting group. Will each account receive the expected amount?
Since G/L entries will be created, the accounting rules must be followed. One rule says that any transaction must be balanced. The sum of the debit amounts in each line must equal the sum of the credit amounts. The following screenshot shows the General Journal page of the Default Journal Batch:
In Dynamics NAV, the Total Balance entry shown at the bottom of the General Journal field must be 0.
- In our example, the total available balance is 1,999.00. We need to perform an extra step to make it 0 and balance the whole transaction. There are a few options we can use to accomplish this. Let us explain two of these options which we are aware of:
- Fill in the Bal. Account Type field with G/L Account.
- Fill in the Bal. Account No. field with the receivables account on the customer posting group assigned to each customer. In the example, both customers have the domestic customer posting group. The receivables account for them is 13100.
Note that there are some accounts where you will not be able to directly post into. This is because Dynamics NAV has a mechanism to prevent accounts included in any posting group from receiving entries directly. You will have to skip this control in order to post the customer open entries. Go to the account card and uncheck the Direct Posting field. Don't forget to check it again when the migration process is over!
Your journal lines will now look like the following screenshot, and the transaction will be balanced and ready to post:
Of course, these two new fields can be added to the migration template to fill them at the outset.
Let's look at the general ledger entries that have been created after the posting process:
As you can see, the same account has been used. The balance of the account is 0.00, even though it has four entries. If you run a balance report, you will see that no amount is shown in the Accounts Receivable line. It feels weird, doesn't it? Don't worry, this will be solved once the balance open entries are imported.
- We are done for the accounts receivable! You can repeat this process as many times as you want.
Vendor entries are pretty much the same as customer entries. Just follow the steps described in the previous section. There are a few differences explained as follows:
- When you fill in the data migration template, the account type must have the vendor value
- You have to reverse the sign of the amounts
- The balancing account will be found in the Payables Account field in the Vendor Posting Group table
Bank entries are pretty much the same as customer entries. Just follow the steps described in the previous section. The few differences are explained as follows:
- When you fill in the data migration template, the account type must have the bank account value
- The balancing account will be found in the G/L Bank Account No. field in the Bank Account Posting Group table
Item entries are a bit different from the entries described so far. First of all, another journal must be used – the item journal. Also, you can choose whether the posting of the item entries creates general ledger entries or not.
The data migration tool has limitations here, so follow the recommendations to work around them.
The minimum information needed is:
- Posting date: Use one day before the migration day for all the entries
- Entry type: Use Positive Adjmt. for all the entries
- Document number: You can use a generic document number, such as
OPENING - Item number: Use the item code given to the item
- Location code: Leave it blank if the company is not using locations; otherwise, write the location code
- Quantity: Fill in the quantity in terms of the base unit of measurement of the item
- Unit cost: Fill in the unit cost in the base unit of measurement of the item
Note
Note that the Item Journal Line table contains a field called Unit of Measure Code. So, you can use a different unit of measurement and therefore the quantity and unit cost will refer to the new unit. When you import data using RapidStart Services, the OnValidate trigger of each field is run. By default, the fields are validated in the same order that they are declared in the table.
The Unit Cost field has the field number 17, whereas the Unit of Measure Code field has the field number 5407. The Unit Cost field will be validated before the Unit of Measure Code field. If you fill in the Unit of Measure Code field in the template, code will be run. In this particular case, the unit cost will be recalculated and you will not get the unit cost you filled in the template.
To avoid this situation, you have to change the default validation order, as explained in the RapidStart Services section.
Usually, the automatic cost posting is disabled, since in most scenarios it is not recommended that this functionality should be used.
Even if, in your case, the automatic cost posting must be used, disable the functionality while posting the initial item open entries. The cost will be posted in the corresponding account later on, when the accounting balances are imported.
Run the data migration tool to import the data into the item journal and post it. The item entries will be created.
Migrating fixed assets is a bit tricky. Here, we are not talking just about the assets that have pending depreciation but all the active assets in the company. Two types of entries have to be posted: cost entries and depreciation entries. Plus, there is more than one account involved with a singular asset. You can post the fixed asset entries from two different journals:
- The General Journal will post the fixed asset entries as well as the general ledger entries
- The fixed asset journal will only post the fixed asset entries; the general ledger entries will not be posted
We will now explain how to post the fixed asset entries using the fixed asset journal. Accounting entries related to them will be posted while importing the accounting balances later on.
To use the fixed asset journal, you must uncheck the G/L integration for the acquisition cost and the depreciation. Go to Departments | Financial Management | Fixed Assets | Setup | Depreciation Book. Open the Depreciation Book Card page and uncheck the fields, as shown in the following screenshot:
From the fixed asset journal, the minimum information needed for the acquisition cost entries is:
- FA posting date: Use one day before the migration day for all the entries
- Document number: You can use a generic document number, such as
OPENING - FA number: Use the fixed asset code given to the asset
- FA posting type: Use the Acquisition Cost value
- Amount: Fill in this field with the amount of the original invoice
Import this information using the data migration tool and post it.
From the fixed asset journal, the minimum information needed for the depreciation entries is as follows:
- FA posting date: Use one day before the migration day for all the entries
- Document number: You can use a generic document number, such as
OPENING - FA number: Use the fixed asset code given to the asset
- FA posting type: Use the depreciation value
- Amount: Fill in this field with the total amount already depreciated for each asset
- Number of depreciation days: Count 30 days for each month depreciated
Import this information using the data migration tool, and post it. Do not forget to check the G/L integration again in the Depreciation Book Card. If you have been using a temporary account in the previous sections, we recommend that you post the general ledger entries for the fixed assets entries that you just posted.
Summarize all the asset acquisition cost entries, grouped by posting groups. In the General Journal, create one line for each posting group. Use the acquisition cost account found in the FA posting group. Use the FA - Opening Cost entries account to balance the whole transaction.
Do the same with the depreciation entries and use the FA - Opening Depreciation entries account to balance the transaction.
General Ledger balances are the backbone of all open entries. When the accounting balances are posted, everything else must match. It is like putting in the last piece of a puzzle. The sad part is that sometimes you find that your last piece does not fit. Don't worry about this right now; at the end of this section, we will explain how to check whether everything is okay and how to solve problems.
While other open entries could be imported and posted in many iterations, the accounting balances must be posted all at once because the whole transaction must be self-balanced. Follow the steps described in the Customer entries section of this chapter, but keep in mind these few differences:
- When you fill in the data migration template, Account Type must have the G/L account value
- If an account has a debit amount, the amount for that account must be positive
- If an account has a credit amount, the amount for that account must be negative
Make sure that the amount you are about to post is the same as the sum of all the corresponding entries. You can run the following reconciliation reports:
- Aged Accounts Receivable: This report and the Aged Accounts Payable. report can be found at Departments | Financial Management | Receivables | Reports and Departments | Financial Management | Payables | Reports respectively
- Inventory to G/L Reconcile: This report can be found at Departments | Financial Management | Inventory | Reports
No standard reconciliation report for the bank accounts or fixed assets exists, so you will have to check it yourself.
Since accounting must always be balanced, if the reconcile reports show any difference, it will mean that some other account does not have the correct balance. Find this other account and you will find the solution to your problem.