web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Import balance account...
Small and medium business | Business Central, N...
Answered

Import balance accounts using an excel file

(0) ShareShare
ReportReport
Posted on by Gianluca Natili 71

Hello ALL,

I'm new on BC implementation.

I'm going to analyze the steps to load the opening balance accounts on my new Business Central. I see the documentation on this topic and I believe that I need to use the Configuration Package.

The table ID of my interest should be the n. 81, but I see there are a lot of Fields Available. I need an help to choose those of my interest.

On the excel to import, I immagine to have the account code source, the account code target and the balance value to load. If I need this, what are the Fields to use among those available?

Thanks for any help.

Gianluca

I have the same question (0)
  • Suggested answer
    Vaishnavi J Profile Picture
    Vaishnavi J 3,060 on at
    RE: Import balance accounts using an excel file

    Hi,

    You need to use Posting Date, Document Type,  Document No, Account Type, Account No, Description, Currency Code (if needed), Amount, Balance Account Type, Balance Account No and Dimension(if needed) in order to import your opening balance in General Journal.

    Account code source- Account No field in BC and you need to mention Account Type as well.

    Account code target- Balance Account No field  in BC and you need to mention the Balance Account Type 

    Balance value to load - Amount field in BC

    If my answer was helpful to you, please verify it so that other users know it worked. Thank you very much.

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 193 User Group Leader on at
    RE: Import balance accounts using an excel file

    Vaishnavi gave a very full list of fields but to be clear Document Type is not needed and neither are balance accounts.

    Typically, you will enter and amount to each account and all those amounts should balance to zero, thus no need to use the balance account. Quick example

    Debit cash

    Debit AR

    Debit Inventory

    Credit AP

    Credit Retained Earnings

    Credit Sales

    Debit Expense

    If only bringing in balance sheet, then sales and expense will not be included because they have been folded into retained earnings.

    Please note, this does not handle your subledgers for AR, Inventory, AP, Cash or Fixed Assets. Thats a different load and it is not recommended to combine the subledger loads with the general ledger balance load.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    Hi,

    you says that: "Please note, this does not handle your subledgers for AR, Inventory, AP, Cash or Fixed Assets. Thats a different load and it is not recommended to combine the subledger loads with the general ledger balance load."

    I had already thought about not considering the fixed assets in this specific general import. But if I need to import a balance account for the AR/AP position or for Cash, which package should I use?

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 193 User Group Leader on at
    RE: Import balance accounts using an excel file

    So for AR, I use the Sales Journal which is still table 81, just different Journal Template

    For AP, the Purchase Journal same table and different journal template

    For bank account outstanding items, I use same (difference here is that your outstanding checks will not show as checks but as deposits/transfers. You can make them show as checks if you post through Payment Journal as manual check in the bank payment type field.

    And you are correct fixed assets have their own journal.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    thank you, very useful. Sorry for my BC skills, I'm a beginner. If I understand well, for AR balances import I still need to use the package configuration. You say AR ..."I use the Sales Journal...", Sales Journal is the name of a Field ID?

  • Verified answer
    Dallefeld Profile Picture
    Dallefeld 193 User Group Leader on at
    RE: Import balance accounts using an excel file

    So when you import, there are 3 fields that are required the Journal Template, Journal Batch and line No. For general ledger balances, I use GENJNL as the template, for AR I use SALES and for AP I use PURCHASES. I use DEFAULT as the Batch.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    Is it also possible to load a "payment due date" for an AR/AP balance? i.e. I would like to load 1000 EURO debit for a Vendor id and the payment due date for that

  • Dallefeld Profile Picture
    Dallefeld 193 User Group Leader on at
    RE: Import balance accounts using an excel file

    Yes, there is a Due Date field. There is also document type (invoice, credit memo, etc), currency code, currency factor.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    If I would like to migrate an historical Credit AP on the Vendor xxx I suppose to handle the AP subledger.

    e.g. € 1000 AP historical balance with a due date 31/03/2022. I'm using the configuration package, table ID 81 with Journal template PURCHASES but I'm not sure about the fields to use or if I'm doing the right thing or if it's possible to do what I have in mind. I don't want to load the historical invoice, just an AP balance and when to pay it.

    I thought to add to the list above the field "ID Vendor" and "Due Date", I don't know if it is correct. I don't see error but I'm not able to see the record on database.

    So, my question is: if I need to load an AP balance on a specific vendor, Do I have to use the table ID 81 and the fields mentioned above? I have some doubt if I'm doing the right thing.

    Thanks

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 193 User Group Leader on at
    RE: Import balance accounts using an excel file

    You should have the following fields, Posting Date, Document Type (Invoice, Credit Memo, Payment), Document Date, Due Date, Account Type = Vendor, Account No. = Vendor Number, External Document No. (enter the vendor invoice number), Currency Code where different from company currency, amount.

    You can import a balance account but I would suggest bring in all your AP, check your total and enter an additional line to balance the journal. Since the vendor lines will post to the AP account in vendor posting setup, this total line should offset that account. If you have more than one AP account, I suggest importing, balancing and posting one at a time.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 4,237

#2
Nimsara Jayathilaka. Profile Picture

Nimsara Jayathilaka. 3,425

#3
Sumit Singh Profile Picture

Sumit Singh 2,907

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans